Sybase Performance Tuning

Q: After the Sybase Database runs for a period of time, the system runs slowly?

ISQL can be used to log on to the ASE for execution.
Select @ version
Go
View the database version.

If you are using ase12.0, we recommend that you use ase12.5 or above, because since Sybase has been overtaken by Oracle in the database market, Sybase has launched new products very quickly, and ase12.5 is a relatively reliable product.

In terms of use, ase12.5 is also relatively convenient, especially for high-speed memory configuration can be dynamically implemented, without the need to restart the database. This is advantageous for enterprise users.

The use of resources by ASE is relatively simplified, and few resources are wasted. After the ASE is installed, all the default parameters are relatively low and cannot meet the needs of enterprise users. It is okay to make a demo.

In the first step, you must make reasonable adjustments to the ASE parameters based on the data volume, number of users, and application features.

The common practice is:
Max memory = physical memory * 70-80%
Default data cache = max memory * 50%
Procedure cache size = max memory * 20-30%

Number of user connections = N (default = 25)
Number of lock = N * Maximum number of locks required by a single user * 120%
(Generally, this is difficult to estimate. The reference value provided by senior syabse engineers: 1.8 million for users. I estimate that 100000 is recommended for 10 Gb of data)
Number of open objects = 10000
Number of open indexed = 10000

This configuration can basically be used normally. You can download the manual from the Sybase official website for detailed configuration. The manual can be used in English, reading foreign documents, and Chinese.

Then, if you find that the performance is not as good as you expected, you need to use your DBA to optimize it. I am not dedicated to tuning, and I need to use more knowledge here. I will not elaborate on it this time.
ASE provides some tools to help you find performance bottlenecks:
Easy to use:
Sp_sysmon "00:03:00"
This will display all the Count information within 3 minutes, with four categories and 18 items.
The first 'kernel 'information shows the CPU usage and I/O busy during this period. This is very helpful.

I pay for it again: the output information of sp_configure does not need to be sent in the future, so it is useless. Give sp_sysmon information.

Note: The allocation of database size, tempdb size, and log size can improve ASE performance by using parallel Io.
The formula is as follows:
Database size = DB
Tempdb = dB * 20% (experience)
Log size = dB * 20%



Sp_sysmon
This article is based on the Sybase technology database of the Training Department of the Times Chaoyang database (formerly xiaotong database.

This article describes how to use sp_sysmon to have a comprehensive and systematic understanding of the running status of the Adaptive Server System, which is conducive to better understanding of system performance, more effective system management, and reasonable use and configuration of system resources, to achieve systematic optimization.

Sp_sysmon can learn about the performance of the system in use from 18 aspects, and use environment parameters for performance tuning as appropriate:

1. kernel Management (kernal) 2. Application Management (appmgmt) 3. Data Cache Management (dcache)

4. ESP Management (ESP) 5. index management (indexmgmt) 6. Locks)

7. Memory Management (memory) 8. Metadata Cache Management (mdcache) 9. Task Management (taskmgmt)

10. Monitor SQL Execution (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 Summary (xactsum) 17. Disk I/O Management (diskio)

18. wpm)

The short words following the brackets are parameters of this module.

Environment: 1. the user database has the data tables auths and article used for exercises.

2. Each data table has 0.1 million rows of data.

3. You have basic database table operation permissions such as query, modification, and deletion.

Step: Execute sp_sysmon "00:10:00" (server-level system storage process, you do not need to open a database), or execute the following format to view the system performance of a specific operation batch command:

Sp_sysmon begin_sample

SQL statements or stored procedures

Sp_sysmon commit_sample

In this experiment, sp_sysmon "HH: mm: SS" is used, and the performance module name is used.

Through sp_sysmon, You Can 'resolve the system running status in various aspects of the current system, and learn to use corresponding parameters and measures to solve and optimize the performance problems and imbalance, constantly compare and adjust the performance before and after, and ultimately improve the system performance.

Note: 1. The execution result set of this command starts with the same as below, and each part of the experiment is not listed one by one:

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

Sybase Adaptive Server Enterprise System Performance Report

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

Server version: Adaptive Server Enterprise/11.9.2/1031/P/NT (ix86)/OS 3.

Server Name: Server is unnamed

Run date: May 28,200 1

Statistics cleared at: 15:57:27

Statistics sampled at: 16:07:28

Sample interval: 00:10:00

2. Prompt for each column of execution result set:

Per sec: average per second during sampling

Per xact: Average value of each transaction committed during sampling

Count: the total value per second during sampling.

% Of total: percentage of the total number, which varies according to different situations.

3. The result set provides the performance description, analysis, and tonality description.

4. This exercise only displays the monitoring results of some modules (which may be deleted). Use sp_sysmon "HH: mm: SS" to view all the details.

Mona1: monitor Kernel Utilization

Command Line: sp_sysmon 00:10:00, kernal

Result:
Kernel Utilization (kernel exploitation)

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

Engine busy Utilization

Engine 0 1.8%

The engine is busy between 80% and 90%. If it is over 90% for a long time, you should consider increasing the number of engines to improve performance. Because the internal management process cannot write data to the disk at this time, the checkpoint needs to write many pages back to the disk, and the checkpoint process is likely to increase the CPU utilization to 100%, resulting in a significant increase in response time.

CPU yields by engine per sec per xact count % of total

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

Engine 0 6.6 0.6 3949 100.0%

Engine CPU discard count: % of total = 1 engine discard count/all engine discard count. If the engine utilization rate is low, you can determine whether the engine stops. Adding the "runnable process search count" parameter (the number of times the engine repeatedly searches for executable tasks before giving up the CPU to the OS) can increase the CPU resident time, if you want to reduce the time for the engine to check I/O when it is idle, you can reduce the value of this parameter.

Network checks

Total network I/O checks 0.0 0.0 0 N/

The number of times the engine sends or receives network packets. When the engine is idle, the Network Package is frequently checked. If the value is low and the value of "CPU yields by engine" is high, the engine may be discarded frequently.

It may include blocking and non-blocking methods. The network is checked for I/O regardless of whether there is any I/O wait in non-blocking mode. If the engine has been abandoned and is performing a blocking network check, the system remains asleep after the network package arrives ). In this case, the "runnable process search count" (default 2000) parameter can be added to reduce the latency, so that the engine has a long cycle check time, rather than being abandoned too early.

Disk I/O checks disk I/O check:

Total disk I/O checks 693.2 58.8 415939 N/

Checks returning I/O 469.9 39.9 281921 67.8%

The engine checks the I/O status effectively (I/O completed times), such as too high or too low, use the "I/O polling process count" parameter (the maximum number of processes that the server scheduler can execute before checking disk I/O or network I/O) to increase or decrease the check frequency. Generally, increasing this value can increase the throughput of applications with a large number of disks or network I/O resources. Otherwise, reducing this value can improve the response time.

AVG disk I/OS returned N/A 0.03020 N/
Increasing the wait time of the engine during the check can improve the throughput, because reducing the I/O time of the engine check increases the execution process time accordingly.