Foglight for Sybase 5.7.5.40 - User and Reference Guide

Investigating Disk Performance

Investigating Disk Performance

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

•	See overall server I/O activity. Use this to determine peak performance and adjust disk resources or job scheduling.

•

See individual device utilization and make changes to the I/O distribution. SybaseASE allows you to control the placement of databases, tables, and indexes across your physical storage devices. This can improve performance by equalizing the reads and writes to disk across many devices and controllers.

•	Review when your Adaptive Server is experiencing any semaphore contention problems on a device.

To start with the SYBM_DiskIOSummary graph:

•	Open the SYBM_DiskIOSummary graph view, you can drill down to view the number of I/O reads and writes for each specific device.

To view more detailed information related to the SYBM_DiskIOSummary graph:

•	Drill down on the Reads line to see the detail I/O activity for each device in the SYBM_DiskIODetail graph view.

To ensure optimal I/O performance in the Adaptive Server:

1	Spread data across disks to avoid I/O contention.

2	Isolate the server-wide I/O from the database I/O.

3	Separate data storage and log storage for frequently updated databases.

4	Keep the random disk I/O away from the sequential disk I/O.

5	Mirror devices on separate physical disks.

6	Partition tables to match the number of physical devices in a segment.

To start with the SYBM_DiskIODetail graph:

•	Use the SYBM_DiskIODetail graph view to help you determine if the I/O is evenly distributed across the devices as planned:

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

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:

•	The SYBM_IndexUse graph view provides an overview of all processes and their summarized index performance. All processes are summarized by calculating the number of pages accessed with and without the use of indexes. This is presented as a server-wide percentage that is tracked.

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:

•	Use a tool like Quest Software's Spotlight on Sybase ASE or Sybase’s own DBA tools to view individual processes and objects that are being accessed.

•	Review Sybase’s Performance and Tuning manuals for a full discussion on Indexes and how their performance impacts user response times.

Investigating Locking

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:

sp_configure "number of locks", 25000

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

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:

•	Use the SYBM_MetaDataCache graph view to track the percentage used for each type of Metadata cache.

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.