Foglight for Sybase 5.7.5.40 - User and Reference Guide

Setting Data Retrieval Properties

Setting Data Retrieval Properties

Use the Data Retrieval set of agent properties to set the limit for the amount of data collected and written to the Foglight database.

The Top Procedures set of agent properties affect the information that appears on the Top Procedures dashboard. For more information, see Top Procedures Tab.

The Top Users set of agent properties affect the information that appears on the Top Users dashboard. For more information, see Top Users Tab.

The Cached Objects set of agent properties affect the information that appears on the Data Cache Activity dashboard. For more information, see Data Cache Activity Tab.

To set the data retrieval properties:

1	Click the list from the Limit Incoming Rows List that you want to update.

This property displays a set of criteria developed to limit the amount of data written to the Foglight database. For more information, see Limit Incoming Rows Criteria.

The default name is LIR_List_Dflt. This list can be renamed to better describe the changes made.

These values are used strictly to manage the amount of data written to the Foglight database and NOT to configure the Rules. Rules are still configured by registry variables.

2	Click Edit.

A secondary property list appears.

3	Add an entry to the list by clicking Add Row.

Fill in the dialog box fields.

CAUTION: Only the criteria in the Value column can be changed. DO NOT change the Foglight Table Group and Limit Criteria values.

4	Click Save Changes.

5	Enter the maximum number of top applications to monitor in the Top Applications - Max Rows Returned box.

This is the maximum number of applications written to the Foglight database.

The default value is 10 applications. This value specifies the maximum size of the list.

6	Enter the minimum threshold value (the minimum value for the selected criteria used for an application to be potentially included on the list based on the maximum number of entries for that table), in the Top Applications - Minimum Threshold Value box.

The default value is 10 units of the selected sorting criteria.

7	Select a sorting criteria from the Top Applications - Sort Criteria list. For more information, see Minimum Threshold Value Sorting Criteria.

An application is included on the list if:

▪	The measurement for the specified criteria is over the threshold value.

AND

▪	There is room on the list based on the Top Applications - Max Rows Returned value specified in Step 5.

8	Enter the maximum number of top SQL statements to monitor in the Top SQL -Max Rows Returned box.

The number entered is the maximum number of SQL statements written to the Foglight database.

The default value is 10 SQL statements. This value specifies the maximum size of the list. Collecting the SQL statements inside stored procedures typically results in a higher number of SQL statements. If you intend to use this feature, increase this value, as required.

9	Enter the minimum threshold value (the minimum value for the selected criteria used for an SQL statement to be potentially included on the list based on the maximum number of entries for that table), in the Top SQL - Minimum Threshold Value box.

The default value is 10 units of the selected sorting criteria.

10	Select a sorting criteria from the Top SQL - Sort Criteria list. For more information, see Minimum Threshold Value Sorting Criteria.

An SQL statement is included on the list if:

▪	The measurement for the specified criteria is over the threshold value.

AND

▪	There is room on the list based on Top SQL - Max Rows Returned value specified in Step 8.

11

Select Top SQL - Include Stored Procedure Lines - YES to display the list of stored procedures. Select NO if the stored procedures list is not to be displayed. Enabling this property allows you to collect the SQL statements inside stored procedures. The number of collected SQL statements depends on the values set by the Top SQL - Max Rows Returned property (see Step 8). It also requires a certain configuration of ASE parameters: the parameter statement pipe max messages should be greater than sql text pipe max messages. For more information about these parameters, refer to your Sybase documentation.

12	Enter the maximum number of top users to monitor in the Top Users - Max Rows Returned box.

This is the maximum number of users written to the Foglight database.

The default value is 10 users. This value specifies the maximum size of the list.database.

13	Enter the minimum threshold value (the minimum value for the selected criteria used for a user to be potentially included on the list based on the maximum number of entries for that table), in the Top Users - Minimum Threshold Value box.

The default value is 10 units of the selected sorting criteria.

14	Select a sorting criteria from the Top Users - Sort Criteria list. For more information, see Minimum Threshold Value Sorting Criteria.

A user is included on the list if:

▪	The measurement for the specified criteria is over the threshold value

AND

▪	There is room on the list based on the Top Users - Max Rows Returned value specified in step 10.

15	Enter the maximum number of cached objects to monitor in the Cached Objects - Max Rows Returned box.

The default value is 10 cached objects.

16	Enter the minimum threshold value (the minimum value for the selected criteria used for a cached object to be potentially included on the list based on the maximum number of entries for that table), in the Cached Objects - Minimum Threshold Value box.

The default value is 1000 units of the selected sorting criteria.

17	Select a sorting criteria from the Cached Objects - Sort Criteria list. For more information, see Minimum Threshold Value Sorting Criteria.

18	Select the Cached Objects - Object Type from the list. The types are:

▪	All tables

▪	System Tables

▪	Temporary Table

▪	User Tables

19	Click Save.

20	Go to Setting Benchmark Properties to set sample frequency agent properties.

Limit Incoming Rows Criteria

The Limit Incoming Rows List table displays a set of criteria which limits the amount of data written to the Foglight database.

 

Table 70. Limit Incoming Rows List Criteria

Group	Limit Criteria	Value
Blocked Users	Max rows returned	20
Blocked Users	Minimum time blocked (seconds)	30
Databases	Max rows returned	20
Databases	Minimum Database space percentage threshold	80%
Databases	Minimum Transaction Log space percentage threshold	80%
Databases	Minimum Segment space percentage threshold	80%
Disk I/O	Max rows returned	20
Disk I/O	Minimum percentage of waits over requests	1%
Error Log	Max rows returned	10
Named Caches	Max rows returned	10
Named Caches	Maximum Cache Hit Rate threshold	80
Engines	Max rows returned	10
Engines	Minimum CPU busy percentage threshold	80%
Long Running Transaction	Max rows returned	10
Long Running Transaction	Length of a long running transaction	-1
OS Processes	Max rows returned	10
OS Processes	Minimum OS Process CPU Busy threshold	80
System Waits	Minimum wait event threshold	80

Minimum Threshold Value Sorting Criteria

The Minimum Threshold Value list displays a set of criteria used to specify a quantity which orders the list of:

•	Top Applications

•	SQL Statements

•

Users

•	Cached Objects

Top Applications

 

Table 71. Top Applications Description

Criteria	Description
CPU Time (seconds)	The CPU time (in seconds) used by the application. This maps to the cpu_time field in the TopApplications Table.
Logical Reads	The number of buffers read from cache. This maps to the logical_reads field in the TopSQL Table and TopUsers Table.
Memory Usage (kB)	The amount of memory (in kilobytes) allocated to the application. This maps to the mem_usage_kb field in the TopApplication Table.
Physical Reads	The number of buffers read from disk. This maps to the physical_reads field in the TopApplication Table.
Physical Writes	The number of buffers written to disk. This maps to the physical_writes field in the TopUsers Table.

SQL Statements

 

Table 72. SQL Statements Description

Criteria	Description
CPU Time (seconds)	The number of seconds (ms) of CPU time used by the SQL statement. This maps to the cpu_time field in the TopSQL Table.
Duration	The amount of time the SQL statement took to run in seconds. This maps to the duration field in the TopSQL Table.
Logical Reads	The number of buffers read from cache. This maps to the logical_reads field in the TopSQL Table and TopUsers Table.
Memory Usage (kB)	The amount of memory (in kilobytes) used for SQL statement execution. This maps to the mem_usage_kb field in the TopSQL Table.
Packets Received	The number of packets received by Adaptive Server. This maps to the packets_received field in the TopSQL Table.
Packets Sent	The number of network packets sent by Adaptive Server. This maps to the packets_sent field in the TopSQL table.
Physical Reads	The number of buffers read from disk. This maps to the physical_reads field in the TopSQL and TopUsers table.
Physical Writes	The number of buffers written to disk. This maps to the physical_writes field in the TopSQL table.

Users

 

Table 73. Users Description

Criteria	Description
CPU Time (seconds)	The CPU time (in seconds) used by the process. This maps to the cpu_time field in the TopUsers table.
Locks	The number of locks the process currently holds. This maps to the locks_held field in the TopUsers table.
Logical Reads	The number of buffers read from cache. This maps to the logical_reads field in the TopSQL and TopUsers table.
Memory Usage (kB)	The amount of memory used by the top users in kilobytes.
Physical Reads	The number of buffers read from disk. This maps to the physical_reads field in the TopSQL and TopUsers table.
Physical Writes	The number of buffers written to disk. This maps to the physical_writes field in the TopUsers table.
Transactions	The number of transactions being processed by the top users.

Cached Objects

 

Table 74. Cached Objects Description

Criteria	Description
Cached (kB)	The amount of memory used by the cached objects in kilobytes.
Processes Accessing	The number of processes accessing the cached objects.

Setting Benchmark Properties

Setting Benchmark Properties

Use the Benchmark set of agent properties to configure BenchMarkTimes table collection parameters.

To configure the benchmark properties:

1	Click the Read Benchmark SQL from a File radio button, True or False, to enable or disable this function.

To have Foglight read the Benchmark SQL from a file, select true.

2	Specify the SQL Benchmark in the Benchmark SQL or Path box. Either:

▪	Enter the full path to the file; that is, a file containing an SQL statement that you want to run on a regular basis to track response time.

▪	Enter the SQL statement directly.

NOTE: Do NOT select true for the Read Benchmark SQL from a File property if you enter the SQL statement directly.

3	Enter the maximum amount of time in seconds that the SQL query should run in the Benchmark Response Time (seconds) box.

The rules associated with this agent property are based on a percentage of this time. For example, if the SQL query runs at or above the specified time a Warning level alert is issued. If it runs at 90 percent of the specified time, a Critical level alert is issued.

The default is 1 second.

4	Click Save.

5	Go to Setting Configuration Properties to set sample frequency agent properties.

Setting Configuration Properties

Setting Configuration Properties

Use the Configuration set of agent properties to set the log collection configuration properties.

The configuration properties allow you to define the logs that the Sybase_MDA Agent monitors. Additionally, it allows you to define the messages within the logs that the Sybase_MDA agent traps.

To set the log collection properties:

1	Enter the full path to the Adaptive Server error log in the ASE Error Log Path box.

2	Enter the full path to the Backup Server error log in the Backup Server Error Log Path box.

3	Click the list from the Message Trap List that you want to update.

Foglight ships with a default message trap list. The message trap list is a filter that traps specific error log messages. The default name of this list is ASE_Error_Log_Search_Patterns.

The number of occurrences of each message, along with the severity and general description, is relayed back to Foglight, resulting in an alarm being generated. Check the error log file for the full text and content of the error message detected.

It is also possible to use error log messages in triggered or stored procedures by printing them to the log adding them to the message trap list. Error messages in the log appear with the debug prefix and result in an alarm being fired with the severity level specified in the message trap list.

4	Click Edit.

A secondary property list appears.

5	Add an entry to the list by clicking Add new row.

Fill in the dialog box fields.

a	Search Pattern—Indicates the regular expression pattern used to match the log message.

b	Action—When set to Include, any error messages that match the search pattern are printed to the log. When set to Exclude, the matched messages are excluded from the log.

c	Severity—The severity of the alarm that is triggered by any error messages that match the search pattern.

6	Click Save Changes.

7	Select whether or not databases are to be included or excluded from the Use (Include DBs or Exclude DBs or None) list. Click one of the following:

▪	None to indicate that neither of the Exclude DBs or Include DBs lists is to be used. Every database is treated according to standard behavior for the Limit Incoming Rows agent property. For more information, see Setting Data Retrieval Properties. The default is none.

▪	Select Exclude DBs to monitor most databases and exclude a few databases.

▪	Select Include DBs to exclude most databases and monitor a few databases.

Note the following:

▪	In the Include DBs field, change the name of the of the agent property list from DB_Include_List_Dflt to SYBM_DBInclusion_<Modified Agent Instance Name>.

▪

For inclusion, <Modified Agent Instance Name> is a placeholder for the agent instance name as Foglight understands it. The Modified Agent Instance Name usually looks like Sybase_MDA@dbhost1. The '@' in the instance name is converted to a '_', so the modified name looks like Sybase_MDA_dbhost1 and the full list name looks like SYBM_DBInclusion_Sybase_MDA_dbhost1 for the inclusion list.

▪	If inclusion is activated and the list is named incorrectly, the exact name required by the agent is posted in the alert message. Then you can cut and paste the name into the appropriate list name field.

CAUTION: The agent pre-populates the inclusion list. The Add and Delete buttons should not be used.

8	Click the exclusion database list from the Exclude DBs list that you want to update.

9	Click Edit.

A secondary property list appears.

10	Add an entry to the list by clicking Add Row.

Fill in the dialog box fields.

a	For the Status Alerting, Database Space Alerting, Transaction Log Alerting, or Space Trend lists, the options are:

Standard—Foglight returns a row for this database only when it crosses the threshold. A row for the respective database may not be returned when it crosses a threshold if the number of databases crossing the threshold exceeds the “max rows returned” value during that poll cycle.

Always Include—Foglight always returns a row for this database when it crosses the threshold. The row for the respective database is always returned, even when the number of databases crossing the threshold exceeds the “max rows returned” value of that poll cycle.

Exclude—Foglight does not return a row for this database even if the threshold is crossed.

b	In the Space Trend list, the options are:

Exclude—Foglight does not return a row for this database even if the threshold is crossed.

Include—Foglight returns a row for this database.

c	Click True or False on the Disable All radio button to enable or disable this function. Set this value to true to exclude this database from Status Alerting, Database Alerting, Transaction Log, Alerting and Space Trend Alerting.

11	Click Save Changes.

12	Click the inclusion database list from the Include DBs list that you want to update.

This criteria is only applied if Include DBs is selected in the Use (Include DBs or Exclude DBs or None) property.

IMPORTANT: The agent pre-populates the inclusion and exclusion lists. The Add and Delete buttons should not be used.

13	Click Edit.

A secondary property list appears.

14	Add an entry to the list by clicking Add Row.

Fill in the dialog box fields.

a	For the Status Alerting, Database Space Alerting, or Transaction Log Alerting lists, the options are:

Standard—Foglight returns a row for this database only when it crosses the threshold. A row for the respective database may not be returned when it crosses a threshold if the number of databases crossing the threshold exceeds the “max rows returned” value during that poll cycle.

Always Include—Foglight always returns a row for this database when it crosses the threshold. The row for the respective database is always returned, even when the number of databases crossing the threshold exceeds the “max rows returned” value of that poll cycle.

Exclude—Foglight does not return a row for this database even if the threshold is crossed.

b	In the Space Trend list, the options are:

Exclude—Foglight does not return a row for this database even if the threshold is crossed.

Include—Foglight returns a row for this database.

c	Click True or False on the Disable All radio button to enable or disable this function. Set this value to true to exclude the database from Status Alerting, Database Alerting, Transaction Log, Alerting and Space Trend Alerting.

15	Click Save Changes.

16	Click Save.

Sybase_MDA Investigations

Sybase_MDA Investigations

This section describes the following investigations:

•	Investigating Application Usage

•	Investigating CPU Capacity

•	Investigating Data Cache Performance

•	Investigating Database Space

•	Investigating Disk Performance

•	Investigating Index Performance

•	Investigating Locking

•	Investigating Meta Data Cache Contention

•	Investigating Network Activity

•	Investigating Parallel Queries

•	Investigating Procedure Cache

•	Investigating Resource Utilization

•	Investigating Sybase Performance

•	Investigating the User Log Cache