Chat now with support
Chat with Support

Foglight for Sybase 5.7.5.40 - User and Reference Guide

Using Foglight for Sybase
Exploring the Sybase Dashboards About the Sybase_MDA Agent About the Sybase_RS Agent Generating SybaseMDA Reports
Reference About us

Investigating Disk Performance

Previous Next



Investigating Disk Performance

The Foglight Cartridge for Sybase assists your investigation into Sybase I/O use and availability by allowing you to:

To start with the SYBM_DiskIOSummary graph:
To view more detailed information related to the SYBM_DiskIOSummary graph:
To ensure optimal I/O performance in the Adaptive Server:
To start with the SYBM_DiskIODetail graph:
To view more detailed information related to the SYBM_DiskIODetail graph:
Drill down on the Reads lines to see the SYBM_DiskIOSemaphores graph view to check if any semaphore contention exists on that device.

This view helps track the number of I/Os requested against the number of requests that were forced to wait for synchronization of an I/O request. A high number of semaphores forced to wait could indicate I/O saturation for that device.

Investigating Index Performance

Previous Next



Investigating Index Performance

The Foglight Cartridge for Sybase assists your monitoring of general index performance metrics. You can use this information to analyze your application workload.

To start with the SYBM_IndexUse graph:

This view helps you determine if it is time to perform index maintenance, or at least investigate your index strategy. Low values in these metrics negatively impact overall user response times and indicate unnecessary resource usage.

To determine more specifically where improvements can be made:

Investigating Locking

Previous Next



Investigating Locking

The Adaptive Server protects the tables or data pages currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data across transactions. Locking is needed in a multi-user environment, since several users may be working with the same data at the same time.

Locking affects performance when one process holds locks that prevent another process from accessing needed data. The process that is blocked by the lock sleeps until the lock is released.

The Foglight Cartridge for Sybase assists your investigation into locking performance in the Adaptive Server by measuring specific locking characteristics of your server.

To start with the SYBM_LockSummary graph:
Open the SYBM_LockSummary graph view to display the number of locks requested on the system, locks that had to wait to be granted, locks that timed out, and the number of deadlocks on the system.
To view more detailed information:
Drill down on the locks time out to view the SYBM_LockWaitTime graph view to display the average wait time for locks. Use this chart to discover any specific period of time where the performance noticeably degrades. Investigate respective user and application activity during this time frame using one of the SYBM_ResourcesInUse or one of the SYBM_Top Foglight views.

A deadlock occurs when two user processes each have a lock on a separate data page, index page, or table, and each wants to acquire a lock on the other process's page or table. It is possible to encounter deadlocks when many long-running transactions are executed at the same time in the same database. Deadlocks become more common as the lock contention increases between those transactions, which decreases concurrency.

If deadlocks occur often, it severely affects the throughput of applications. Deadlocks cannot be completely avoided. However, redesigning the way transactions access the data can help reduce their frequency.

The full details of a deadlock appear in the ASE error log. Use Quest’s Spotlight on Sybase ASE product or review the messages in the Adaptive Server's error log to track down which object and users were involved.

Tracking the number of locks used by the Adaptive Server

The number of locks required by a query can vary widely, depending on the locking scheme, the number of concurrent and parallel processes and the types of actions performed by the transactions. Configuring the correct number for your system is a matter of experience and familiarity with the system.

System administrators can use sp_configure to change this limit. For example:

You may also need to adjust the sp_configure parameter total memory, since each lock uses memory.

A shortage of the number of locks available can have an impact on the overall locking scheme in place on the server.

Investigating Meta Data Cache Contention

Previous Next



Investigating Meta Data Cache Contention

The Foglight Cartridge for Sybase assists your investigation into the performance characteristics of the Metadata Cache.

The Metadata caches reside in the kernel and server structures portion of Adaptive Server memory. You configure space for each of these caches by defining the number of each type of cache that the server allocates. This is done with the respective properties to the Sybase system procedure sp_configure.

Tracking and managing individual metadata caches for databases, indexes, or objects is important for a database that contains a large number of indexes and objects and where there is an expectation of high concurrency among users.

To start with the SYBM_MetaDataCache and SYBM_MetaDataCacheDetail graphs:

The SYBM_MetaDataCacheDetail graph view tracks the number of used and free descriptors for each cache. Use these numbers as a guide if any changes to the memory configuration need to be adjusted due to high utilization (especially if the number of descriptors that are reused are greater than zero).

When descriptors need to be reused, there can be performance problems, particularly with open databases. An open database contains a substantial amount of metadata information, which means that to fill an open database, the Adaptive Server needs to access the metadata on the disk many times; the server can also have a spin lock contention problem. The Foglight rule MetaData_Open alerts you when any of the cache utilizations are over the defined threshold and when any descriptors are reused.

IMPORTANT: The Adaptive Server uses the metadata cache: When the Adaptive Server opens a database or accesses an index or an object, it needs to read information in the respective system tables: sysdatabases, sysindexes, and sysobjects.

The metadata caches for databases, indexes, or objects let the Adaptive Server access the information that describes it in the sysdatabases, sysindexes, or sysobjects row that are directly in its in-memory structure. This improves performance by allowing the Adaptive Server to bypass expensive calls that require disk access. Synchronization and spin lock contention are also reduced when the Adaptive Server has to retrieve database, index, or object information during runtime.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating