Foglight 5.9.1 - Performance Tuning Field Guide

You can create custom tablespaces by modifying the storage-config.xml file, that is located in the config directory of the Management Server installation.

Group A (configuration) tables can only be created using the SQL scripts available in the <foglight_home>/scripts/sql directory. This can be done using the database tool, foglight-database-tool.jar. For complete information, about this tool, see the Foglight Installation and Setup Guide set.

Group B (observation) tables can also be added. Adding them before the server starts for the first time causes them to be created in the custom tablespace. If the default tablespace already contains group B tables at the time storage-config.xml is updated, they remain in that tablespace until the server rolls up or purges the data in those tables.

Adding group B tables after the Management Server starts causes the server to move them over to the new tablespace as it rolls up long-term data. This means that for some tables, for example, it can take as long as three months before they are created in the database. The actual period of time depends on the configured retention policies. Some tables can remain the default tablespace indefinitely. For complete information about retention policies, see the Administration and Configuration Help.

To create custom tablespaces:

Open the storage-config.xml file for editing.

Create one or more tablespaces in the database to contain the group A tables.

In the storage-config.xml file, locate the <configuration-destination> element and replace its <default> element with a <tablespace> element using the following syntax:

Where CUSTDATA, CUSTINDEX, and CUSTLOB are the names of your custom tablespaces.


	NOTE: These names are examples. You can name your custom tablespaces whatever you want.

Create one or more tablespaces in the database to contain the group B tables.

In the storage-config.xml file, locate the <destination> element and replace its <default> element with using the following syntax:

Where SAMPLEDATA, SAMPLEINDEX, and SAMPLETLOB are the names of your custom tablespaces.


	NOTE: These names are examples. You can name your custom tablespaces whatever you want.

If the database user has not been granted a quota on the newly created tablespaces, after creating tablespaces in the storage-config.xml file, the database administrator must edit the scripts/sql/oracle_create_db.sql script and add grants for the custom tablespaces, like the one that grants a quota on the USERS tablespace.

For example:

ALTER USER @db.schema.username@ QUOTA UNLIMITED ON CUSTDATA;

ALTER USER @db.schema.username@ QUOTA UNLIMITED ON CUSTINDEX;

ALTER USER @db.schema.username@ QUOTA UNLIMITED ON CUSTLOB;

Save your changes and close the file.

Creating custom tables before the server starts up for the first time only.

Run the database tool, foglight-database-tool.jar, to create the group A tables in the specified tablespaces.

Start the Management Server.

The server creates the group B tables on this initial startup, followed by creating additional group B tables, as needed, at runtime. The tables are created in the specified tablespaces.

Creating custom tables after the server starts up for the first time only.

Have your DBA move the group A tables to the configured tablespace.

Instruct the server to update the configuration and create the group B tables using the JMX console. In the JMX console, invoke the mergeConfiguration() method on the Storage Manager Service MBean in the JMX console. This only affects the Group B tables that the server creates from that time on.

Microsoft SQL Server Tuning

Foglight™ Management Server accepts the instance-level default for database recovery (regardless of the recovery model of the model database, which is purely a template used to create new databases). There is no harm in changing the recovery model from full to simple, other than point-in-time recoverability is lost. If you require the ability to perform disaster recovery on your Foglight database, you should use the full recovery model and you should make frequent transaction log backups to ensure that it does not grow beyond an undesirable threshold. If you do not require point-in-time recoverability, you can use the simple recovery model, and there will be no additional ramifications.

Troubleshooting

Management Server Load Preventing Database Connections

High Foglight™ Management Server load may consuming all connections to backend database. This results in any of the following problems:

•

Foglight Management Server is slow to respond, or does not respond at all.

•

Incoming data from monitoring agents is dropped.

•

Multiple users logged in simultaneously are experiencing problems.

•

Errors in the Management Server log files. For example:

YYYY-MM-DD HH:MM:SS.DDD ERROR [DataAccess-7-thread-4]

com.quest.nitro.util.DefaultExceptionHandler -

DefaultExceptionHandler.uncaughtException: [DataAccess-7-thread-4,

java.lang.RuntimeException: org.jboss.util.NestedSQLException: No

ManagedConnections available within configured blocking timeout

( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException:

No ManagedConnections available within configured blocking timeout (

30000 [ms] ))]

java.lang.RuntimeException: org.jboss.util.NestedSQLException: No

ManagedConnections available within configured blocking timeout

( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException:

No ManagedConnections available within configured blocking timeout

( 30000 [ms] ))

at com.quest.nitro.service.util.JDBCHelper.getConnection

(JDBCHelper.java:124)

at com.quest.nitro.service.persistence.obs.handler.query.

AbstractBatchQueryExecutor.run(AbstractBatchQueryExecutor.java:140)

at java.util.concurrent.Executors$RunnableAdapter.call

(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask

(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run

(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

Caused by: org.jboss.util.NestedSQLException: No ManagedConnections

available within configured blocking timeout ( 30000 [ms] ); - nested

throwable: (javax.resource.ResourceException: No ManagedConnections

available within configured blocking timeout ( 30000 [ms] ))

…

To increase the configured number of connections for the backend database:

Ensure that your user account has appropriate permissions to access the <FOGLIGHT HOME>\server\default\conf directory:

•

Windows®: Log in as an Administrator.

•

UNIX®/Linux®: Use the Foglight user service account.

In the above directory, open one of the following files for editing, as applicable. If you are not sure which file to Edit, contact Quest Support.

•

Microsoft® SQL Server®: sqlsvr-ds.xml

•

Oracle®: oracle-ds.xml

•

MySQLTM: mysql-ds.xml

Foglight Management Server 5.6.x and higher only.

Locate the following line in the file:

<max-pool-size>${foglight.database.max_pool_size:20}</max-pool-size>

Change the value of 20 to 50 or higher.

Foglight Management Server 5.5.8 and lower only.

Add min-pool-size and max-pool-size elements between password and before prepared-statement-cache-size, as follows:

<password>${foglight.database.password}</password>

<min-pool-size>20</min-pool-size>

<max-pool-size>50</max-pool-size>

<prepared-statement-cache-size>100</prepared-statement-cache-size>

Save your changes, stop, and then start the Foglight service (Windows) or Foglight daemon (Unix or Linux).

The described configuration changes increase the load on the Foglight database server. You must make sure that you have adequate resources for both the Foglight Management server and the database server hosting the Foglight repository.


	NOTE: If you need to enlist our professional services group to help size your environment, contact your sales representative. This is beyond the scope of Quest Support.

Management Server Load Preventing Database Connections

Troubleshooting

High Foglight™ Management Server load may consuming all connections to backend database. This results in any of the following problems:

•

Foglight Management Server is slow to respond, or does not respond at all.

•

Incoming data from monitoring agents is dropped.

•

Multiple users logged in simultaneously are experiencing problems.

•

Errors in the Management Server log files. For example:

YYYY-MM-DD HH:MM:SS.DDD ERROR [DataAccess-7-thread-4]

com.quest.nitro.util.DefaultExceptionHandler -

DefaultExceptionHandler.uncaughtException: [DataAccess-7-thread-4,

java.lang.RuntimeException: org.jboss.util.NestedSQLException: No

ManagedConnections available within configured blocking timeout

( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException:

No ManagedConnections available within configured blocking timeout (

30000 [ms] ))]

java.lang.RuntimeException: org.jboss.util.NestedSQLException: No

ManagedConnections available within configured blocking timeout

( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException:

No ManagedConnections available within configured blocking timeout

( 30000 [ms] ))

at com.quest.nitro.service.util.JDBCHelper.getConnection

(JDBCHelper.java:124)

at com.quest.nitro.service.persistence.obs.handler.query.

AbstractBatchQueryExecutor.run(AbstractBatchQueryExecutor.java:140)

at java.util.concurrent.Executors$RunnableAdapter.call

(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask

(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run

(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

Caused by: org.jboss.util.NestedSQLException: No ManagedConnections

available within configured blocking timeout ( 30000 [ms] ); - nested

throwable: (javax.resource.ResourceException: No ManagedConnections

available within configured blocking timeout ( 30000 [ms] ))

…

To increase the configured number of connections for the backend database:

Ensure that your user account has appropriate permissions to access the <FOGLIGHT HOME>\server\default\conf directory:

•

Windows®: Log in as an Administrator.

•

UNIX®/Linux®: Use the Foglight user service account.

In the above directory, open one of the following files for editing, as applicable. If you are not sure which file to Edit, contact Quest Support.

•

Microsoft® SQL Server®: sqlsvr-ds.xml

•

Oracle®: oracle-ds.xml

•

MySQLTM: mysql-ds.xml

Foglight Management Server 5.6.x and higher only.

Locate the following line in the file:

<max-pool-size>${foglight.database.max_pool_size:20}</max-pool-size>

Change the value of 20 to 50 or higher.

Foglight Management Server 5.5.8 and lower only.

Add min-pool-size and max-pool-size elements between password and before prepared-statement-cache-size, as follows:

<password>${foglight.database.password}</password>

<min-pool-size>20</min-pool-size>

<max-pool-size>50</max-pool-size>

<prepared-statement-cache-size>100</prepared-statement-cache-size>

Save your changes, stop, and then start the Foglight service (Windows) or Foglight daemon (Unix or Linux).


	NOTE: If you need to enlist our professional services group to help size your environment, contact your sales representative. This is beyond the scope of Quest Support.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight 5.9.1 - Performance Tuning Field Guide

Custom Tablespaces

Microsoft SQL Server Tuning

Troubleshooting

Management Server Load Preventing Database Connections