Performance Problem Solving case 01 -- sybase database memory problem

The problem was reported on the site recently. All electronic seal pages cannot be opened (in pdf format), and no errors were reported in background logs. The effect was blank:

1. First of all, I thought of the ocx control issue of the seal. I checked the ocx control installation and found that other computers could not open the document. On the test page, I can open the pdf document directly to eliminate the control issue.

2. I suspect there is a problem with document download. Check the document download function. We downloaded the pdf document to the local "My Documents" directory, and then opened the document using the ocx control, check that some files can be downloaded to the local device normally, and some files that can be downloaded to the local device can be opened normally. Check ftp and find that the file is correct. The problem lies in downloading the file from ftp.

3. There are two steps to download ftp: Go to the database to query ccbh and then download it from ftp based on ccbh. Check that the download is normal and the query is very slow, it takes up to 50 seconds to query the data. It takes four or five minutes to query the data. If you are patient, wait for a few minutes. The document can be displayed and the cause of the problem has been found, the SQL query for ccbh is very slow.

4. Analyze the SQL statement. The simplified SQL statement is:

SELECT * FROM T_ZXLD_SYYH_FJWHERE C_BH_SQ='76A1737D773BE344B7154AA479634FED'AND C_FJLX='syyhcx.zz.cxs.gy'

In the query condition, C_BH_SQ has an index. Theoretically, it should not be so slow. Check the execution plan and find:

The index is not used, and the execution time is 274 s. The index is useless, and the C_BH_SQ field is the primary key of the master table. The probability of being selected should be very high. It is suspected that there is a problem with the statistics.

UPDATE all STATISTICS T_ZXLD_SYYH_FJ

Update the statistics. After the update, the original SQL statement is executed. 0.12 s is used. The execution plan also shows that the index is used.

The problem may seem to have been solved, but sometimes it may be very slow to open (about 10 s fast, slow for several minutes). Why? During the check, it is found that not only the page is occasionally slow, but many pages are occasionally very slow. The first time the page is opened slowly, it will soon be followed, and it will be slow again after a while.

View the p6spy log and find that (the deep color column is the SQL Execution time and only records the SQL statements that are executed for more than 2 seconds). Execute the SQL statements that have been executed for a long time separately, and execute the SQL statements for many first times for dozens of seconds, if the second execution is less than 1 s, you can determine the cause of the problem. The system memory is small.

For sybase data, run the following command to view io information: set statistics io ON | OFF

First execution result:

Result of the second execution:

We can see that the content is read from the disk and memory for the first time, and the second physical reads is 0. reading from the memory is faster, of course, if the memory is small, after other query data is read into the memory, the old data is cleared from the memory, and the old data needs to be read from the disk to the memory again. Therefore, it is often slow for the first time, but it will be faster in the future. The best way is to increase the memory and cache all common data.

Solution: We recommend that you upgrade sybase to 15.7 On site. Because sybase12.5.3 does not have a 64-bit version and 32-bit uses a maximum of 2 GB memory. Many tables have millions of data records, which is slow to query.