Sybase Performance Diagnostics Sp_sysmon data caching

This article describes the adaptive server system through the Sp_sysmon of the operation of a comprehensive system to understand, is conducive to better understanding of system performance, more effective system management, rational use and allocation of system resources, to achieve system performance tuning purposes.

Learn from 18 aspects of the performance of the system and use environment parameters to tune performance when appropriate:

1, kernel Management (kernal) 2, Application Management (APPMGMT) 3, data cache management (Dcache)

4, ESP management (ESP) 5, index management (INDEXMGMT) 6, lock management (locks)

7, Memory Management (memory) 8, metadata cache Management (Mdcache) 9, Task management (TASKMGMT)

10, monitor access to the implementation of SQL (monaccess) 11, network I/O management (netio)

12. Parallel query Management (parallel) 13, process cache Management (Pcache) 14, Recovery Management (recovery)

15, transaction Management (XACTMGMT) 16, transaction profile (Xactsum) 17, disk I/O management (Diskio)

18. Work Process Management (WPM)

The English short word after parentheses is the module parameter.

Environment: 1, the user database has the data table auths and article for practice

2, the data table has 100,000 rows of data

3, the user has the query, the modification, the deletion and so on basic database table Operation Authority

Step: Perform Sp_sysmon "00:10:00" (server-level system storage process, no need to open a database), or perform the following format process to view the specific operation batch command corresponding to the system performance:

Sp_sysmon Begin_sample

SQL statement or storage process

Sp_sysmon Commit_sample

This experiment uses Sp_sysmon "HH:MM:SS", the performance module name.

Conclusion: Through this exercise, we can understand the current system in various aspects of the system operating conditions, performance problems and imbalance imbalance, learn to use the corresponding parameters and measures to solve and tune, and constantly compare the performance before and after adjustment, and ultimately improve system performance.

Note: 1, the command execution result set at the beginning of the same as the following, the block exercise is no longer one by one listed:

======================================================================

Sybase Adaptive Server Enterprise System Performance

======================================================================

Server version:adaptive Server Enterprise/11.9.2/1031/p/nt (IX86)/os 3.

Server Name:server is unnamed

Run Date:may 28, 2001

Statistics cleared at:15:57:27

Statistics sampled at:16:07:28

Sample interval:00:10:00

2, the implementation of the result set of each column information tip:

Per sec: average of each second during sampling

Per XACT: average of each transaction submitted during sampling

Count: Total value per second during sampling

% of total: percentages, depending on different circumstances

3, the result set corresponding to the performance of the description, analysis and tunable description

4, this exercise only gives some of the module monitoring results (may have deletions), with Sp_sysmon "HH:MM:SS" can see all the details.

Data cache Management

Command line: Sp_sysmon "00:10:00", Dcache

Results:

Data cache Management (cache management)

---------------------

Report data cache Spin lock contention, cache application, Cache hit misses, configure buffer pool rollover, cleaning cache (including dirty pages), prefetch requests and rejections, read dirty page requests, and so on.

Cache Statistics Summary (all caches)

-------------------------------------

Per sec/XACT count% of total

------------  ------------  ----------  ----------

Cache Search Summary cache hits and misses

Total Cache Hits 18.6 1.6 11171 89.9%

Total Cache Misses 2.1 0.2 1251 10.1%

-------------------------  ------------  ------------  ----------

Total Cache Searches 20.7 1.8 12422

Cache turnover

Buffers grabbed 0.2 0.0 102 N/A

Cache plunder. Count represents the number of cache blocks taken from the LRU end in the cache block chain.

Buffers grabbed Dirty 0.0 0.0 0 0.0%

Dirty page Plunder. You must wait for dirty pages to be written back to disk when you take a dirty page from the LRU end. If its value is Non-zero, you can find out what cache is affected, which is related to the cache hit performance problem.

Cache Strategy Summary cache policy (for all cache)

Cached (LRU) buffers 19.8 1.7 11880 100.0

Report how many cache blocks are placed on the head of the MRU/LRU chain.

Discarded (MRU) buffers 0.0 0.0 0 0.0%

How many cache blocks are reported using the fetch-discard policy, and the cache block is used to place the scrubbing mark at the cache block chain.

Large I/O Usage

0.0 0.0 0 N/A

Block I/O requests used, there is no large block I/O, it is 0 values, there is no authorization or denial of use.

Large I/O effectiveness

Block I/O use effect, low percentage value indicates that very few pages are brought into the cache for query use, you can further view the use of a single cache.

Pages by Lrg I/O Cached 0.0 0.0 0 N/A

It is useful to measure performance by the number of pages involved. A low percentage value means that the storage structure of the table is very broken, or an inappropriate cache configuration strategy.

Asynchronous Prefetch Activity

0.0 0.0 0 N/A

Asynchronous prefetching can be viewed in conjunction with disk I/O management. Can see the parameter "global async prefetch limit".

Other asynchronous Prefetch Statistics

Apfs Used 0.0 0.0 0 N/A

The number of pages that are eligible for asynchronous prefetching.

APF Waits for I/o 0.0 0.0 0 N/A

The number of times the process waits for an asynchronous prefetch to complete. Indicates that the page required by the query did not complete the asynchronous prefetch as early as possible, so the process is in a waiting state. The occurrence of a certain percentage is reasonable: The first asynchronous prefetch request for the query usually needs to wait, a prefetch request is made each time the sequential scan moves to the new allocation unit, the query must wait for the first I/O to end, and a prefetch request is issued for each nonclustered index scan to find the appropriate rowset. Also wait for the first page to return.

APF discards 0.0 0.0 0 N/A

Reports the number of pages that have been asynchronously prefetch read but discarded before being used. If the value is high, it is recommended to increase the size units of the buffer pool (for example, from 2K to 4K, 8K, 16K buffer pool) to improve performance, or that prefetching into the cache of many pages is not required by the query.

Dirty Read Behavior

Page Requests 0.0 0.0 0 N/A

Number of pages of dirty read requests with isolation Level 0.

-------------------------------------------------------------------------------

Cache:default data Cache Default cache condition:

Per sec/XACT count% of total

-------------------------  ------------  ------------  ----------  ----------

Spinlock contention N/a n/a 0.0%

Spin locks are only useful for SMP environments. When a user task changes the cache, the other task will not be able to access the cache and only wait. Although the spin lock stays for a short time, but for high transaction rate multiprocessor system performance still has a bad effect, if the spin lock ratio of more than 10%, should consider the establishment of named cache or to increase cache fragmentation.

utilization N/a n/a 100.0%

The following are the details of the cache check:

Cache searches

Cache Hits 18.6 1.6 11171 89.9%

Found in Wash 1.1 0.1 677 6.1%

Cache Misses 2.1 0.2 1251 10.1%

-------------------------  ------------  ------------  ----------

Total Cache Searches 20.7 1.8 12422

Pool turnover

2 Kb Pool

LRU Buffer Grab 0.2 0.0 102 100.0%

Grabbed Dirty 0.0 0.0 0 0.0%

-------------------------  ------------  ------------  ----------

Total Cache turnover 0.2 0.0 102

Buffer Wash Behavior

Statistics not available-no buffers entered Wash section yet

Cache strategy

Cached (LRU) buffers 19.8 1.7 11880 100.0

Discarded (MRU) buffers 0.0 0.0 0 0.0%

Large I/O Usage

Total Large I/O Requests 0.0 0.0 0 N/A

Large I/O Detail

No Large Pool (s) in the Cache

Dirty Read Behavior

         Page requests                0.0           0.0            0       n/a