Foglight for Sybase 5.7.5.40 - User and Reference Guide

Investigating Application Usage

Investigating Application Usage

The Sybase_MDA Agent allows you to quickly view and determine which applications are using the most CPU resources.

To start with the SYBM_TopApplications table view:

•	Open the SYBM_TopApplications table view which displays application performance metrics such as CPU, I/O, and memory for the top N applications running on the Adaptive Server.

It is critical to know which applications, on any Adaptive server, are consuming resources. This understanding allows the DBA to tune the Adaptive Server resources to the priority of each application.

You can configure how many applications are returned to Foglight, the criteria by which they are sorted, and the minimum threshold that the selected criteria must cross before being returned. This is completed through the Setting Data Retrieval Properties agent property for the Sybase_MDA Agent.

TIP: If no applications are being displayed, used the procedure below.

To ensure that applications are displayed:

1	Go to the Sybase_MDA Agent properties.

2	Go to the Data Management set of agent parameters.

3	Ensure that the Performance parameter has the True button selected, so that performance data is collected.

4	Ensure that the Performance Monitoring list item Top Applications is set to True.

Investigating CPU Capacity

Investigating CPU Capacity

The Foglight Cartridge for Sybase assists your investigation into Sybase ASE CPU use and availability.

This cartridge tracks internal allocation of CPU resources by the Adaptive Server as well as the external performance characteristics of the Adaptive Server processes from the perspective of the host Operating System.

To start with the SYBM_OS_Cpu graph:

•	Open the SYBM_OS_Cpu graph view which displays how busy the Adaptive Server engines are during the time that the CPU is available to the Adaptive Server.

This graph shows total CPU utilization, as measured from within the ASE, charted against what the operating system is measuring. Remember that the ASE reports CPU usage as idle even though it may be performing housekeeping tasks which, in turn, are reflected as CPU activity from the Operating System.

What to look for when viewing this chart:

▪	If the system CPU use is high, investigate the I/O performance of the Adaptive Server. If the Adaptive Server is spending an excessive amount of time performing I/O, then the device layout and allocation of the server may need to be tuned.

▪	If the CPU utilization is high for users, but the connections in use are not increasing, go to the SYBM_TopUsers table view to see if a single user is consuming disproportional amount of resources. This view identifies users who have crossed your predefined threshold.

▪	Use this chart to watch for the point where connections saturate the CPU resource available. You should be able to project the point where the number of connections causes the CPU use to rise to levels that may cause overall ASE performance degradation.

To see more detailed information:

•	Open the SYBM_OS_CpuDetail graph view. This graph shows Operating System CPU utilization broken down by each respective ASE engine process.

This lets you monitor the CPU performance reported within the Adaptive Server. This view shows the CPU activity broken down by user, system, and idle usage level charted against the number of connections in use.

•	Open the SYBM_EnginePerfDetail graph view, where the CPU utilization levels are broken down by Adaptive Server engine.

From the SYBM_EnginePerfDetail graph view, you can drill down to the SYBM_EngineInfoDetail table view for general information on each engine, such as engine status or start time.

Investigating Data Cache Performance

Investigating Data Cache Performance

Sybase Adaptive Server reserves memory to hold the data, index, log pages currently in use, and pages recently used by the Adaptive Server. This region of the memory is called the data cache. The initial installation of Adaptive Server creates a single default data cache that is used for all data, index, and log activity.

A DBA can create and configure additional regions for data cache which are commonly referred to as named caches.

The named data caches can only be used by databases or database objects that are explicitly bound to them. All objects not explicitly bound to named data caches use the default data cache.

Data caches can be sub-divided into regions called data pools. These data pools can be of varying page sizes. The amount of space allocated to a data pool and the corresponding page size allows a DBA to tune the cache for larger I/Os and, in turn, more efficient I/O utilization than the default settings would provide.

To start with SYBM_DataCacheSummary graph:

Open the SYBM_DataCacheSummary graph view which summarizes the effectiveness of the cache design on the server as a whole. This view displays the total number of hits and misses for all caches during the collection period.

•	From the SYBM_DataCacheSummary graph, drill down on the Hits line to investigate where the cache misses are taking place with the SYBM_DataCacheDetail graph view.

•	From the SYBM_DataCacheDetail graph view, drill down on the Hits bar to see the performance characteristics of the data pools with the SYBM_DataCachePools graph view.

To view more detailed information on MetaData Cache or Procedure Cache:

•	See the Investigating Meta Data Cache Contention and Investigating Procedure Cache topics.

Investigating Database Space

Investigating Database Space

The Foglight Cartridge for Sybase assists your investigation into database space with the SYBM_DbSpace graph and SYBM_TempDbSpace graph views. Both are identical except that the temporary databases are reported separately.

These views show the data space utilization for the respective database.

Transaction log space is reported separately in SYBM_DbSpaceTransLog graph and the SYBM_TempDbSpaceTransLog graph views.

Understanding what databases are returned

Both sets of views only report on databases that have crossed a threshold defining how much space is used. This threshold is set by editing the respective agent properties.

To set the threshold:

1	Go to the agent properties by Dashboards: Administration > Agents > Agent Status.

2	Select the Sybase_MDA Agent and click Edit Properties.

3	Go to the Data Retrieval set of properties and click the Edit button.

4	Double-click a Foglight Table Group.

5	Enter a Limit Criteria Value and click the Save button.

This new value increases or decreases the number of databases that are returned to Foglight and reported in this view.

NOTE: The Limit Criteria “Max Rows Returned” limits the number of rows when the percentage threshold is low and/or the number of databases returned to Foglight increases. Once the number of Max Rows is reached, Foglight discards the rows over the Max number allowed.

See the Foglight online help for more detailed information on editing agent properties.

For each database returned, you can drill down to the SYBM_DbSpaceSegment graph view or the SYBM_TempDbSpaceSegment graph view to see space allocations per segment.

To see database utilization over a period of time, go to the SYBM_DbSpaceTrendChart graph view.

NOTE: You must set Space Trending to True (default is off) in the Data Management agent properties for this agent.