Sybase Performance Diagnostics Sp_sysmon_ Kernel diagnostics

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.

Sp_sysmon can be used in 18 aspects to understand the performance of the system in use, and when appropriate, the use of environmental parameters for performance tuning:

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.

Through Sp_sysmon, 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 the adjustment, and ultimately improve system performance.

Note: 1, the command execution result set at the beginning of the same as the following, each block experiment 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.

Unit One: Monitoring kernel utilization

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

Results:

Kernel Utilization (Kernel utilization)

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

Engine Busy Utilization

Engine 0 1.8%

The engine should be busy between 80% and 90%, and if it is longer than 90%, consider increasing the number of engines to improve performance. Because the internal management process cannot write to disk at this point, checkpoints require many pages to be written back to disk, and the checkpoint process is likely to increase CPU utilization to 100%, resulting in a noticeable increase in response time.

CPU yields by Engine per sec/XACT count% of total

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

Engine 0 6.6 0.6 3949 100.0

Engine Discard number of CPUs:% of total=1 engine discard times/all engine abort times, if the display engine utilization is low, can be judged by the discard number to reflect the engine stop. Adding "runnable Process Search Count" (the number of times an engine loops to find a task before the engine abandons the CPU) can increase the CPU's dwell time, and reduce the value of the parameter if you want to reduce the time that the engine checks I/O when it is idle.

Network Checks

Total network I/O Checks 0.0 0.0 0 N/A

The number of times the engine sent or received network packets. The engine frequently checks the network pack when it is idle, and if the value is low and the "CPU yields by Engine" value is high, the engine may be discarded frequently.

This may include both blocking and non-blocking checking methods. Non-blocking means I/O checks the network regardless of whether I/O is waiting or not. If the engine has been discarded and is performing a blocking network check, the network packet will remain dormant for a period of time (latency) after it arrives. Increasing the runnable process search count (default 2000) parameter reduces the latency, keeping the engine in a longer cycle check time, rather than being discarded prematurely.

Disk I/O checks disc I/O checks:

Total Disk I/O Checks 693.2 58.8 415939 N/A

Checks returning I/O 469.9 39.9 281921 67.8%

The engine's effective check of I/O (I/O completion times), such as too high or low, increases or decreases the check frequency with the "I/O polling process count" (maximum number of processes that can be performed before the scheduler of the server checks for disk I/O or network I/O). Increasing this value usually increases the throughput of applications with a large amount of disk or network I/O, whereas reducing this value can improve the response time.

AVG Disk I/Os returned n/a n/a 0.03020 n/a

Increasing the wait time for the engine during the check improves throughput because reducing the engine checking I/O time increases the time it will take to execute the process accordingly.