A query transaction causes a high CPU usage on the database

Source: Internet
Author: User
Tags informix cpu usage high cpu usage

This is a while doing a query for trading stress tests, using the Aix+informix database. Applications and databases are deployed on two machines, respectively. Use LoadRunner for concurrent testing. The historical data for the related table is 20W level. Test with 20 concurrency.

During the test, the application server load is normal, the database server disk, network utilization is normal, but the CPU utilization is about 98%. It's strange, because this machine is the best performance in a test environment, and should not behave like this. So first guess will be informix parameter configuration is wrong, so search some Informix parameters affect CPU utilization parameters, but the phenomenon is still. The SQL statements for the three main tables inside the query are viewed using the execution plan, and the query is no problem, and all falls on the index. Until the first two days to see an article, which mentions that if you are not sure which piece of code caused by the performance problem, you can use the method of segmented comment code to locate the problem of which all the code, so also want to be able to query all the tables in the transaction query the SQL statements to find out which table is the cause of the operation. Finally, when querying another table with a larger amount of data, the index is deleted in the script that leads the data, resulting in another inefficient index. Here are some of the query plans:

QUERY: (Optimization timestamp:02-12-2015 14:33:42)
------
SELECT Cust_num,bank_card from Yw_zhzj where fund_card= ' fund_card0002456 ' and zjzt= ' 0 '

Estimated cost:12456
Estimated # of Rows Returned:2

1) Ywk.yw_zhzj:INDEX PATH

Filters:ywk.yw_zhzj.zjzt = ' 0 '

(1) Index name:ywk. 413_1682
Index keys:cust_num bank_card fund_card dxjgdm CPDM (Key-first) (Serial, Fragments:all)
Index Key Filters: (ywk.yw_zhzj.fund_card = ' fund_card0002456 ')


Query Statistics:
-----------------

Table Map:
----------------------------
Internal name Table Name
----------------------------
T1 YW_ZHZJ

Type table Rows_prod est_rows rows_scan time Est_cost
-------------------------------------------------------------------
Scan T1 1 2 200000 00:00.58 12456

You can see that although the "INDEX PATH" is used, but the following rows_scan is 200000, the data volume of this table is 200000, that is, the full table scan. Later, with the index on the missing Fund_card field added to the YW_ZHZJ table, the database CPU usage dropped to below 5% immediately.

Suddenly think of the words I saw before: the database CPU usage is unusually high, it is likely that a large table is a full table scan. Unfortunately, the experience was shallow and still stuck to Informix's parameter adjustment. Now look back and summarize two points:

1,CPU usage is high, but other resource usage is normal, that is to consider the table with a large amount of data for full table scan first.

2, direction is important.

3, annotation method is the killer of positioning problem

However, for the execution of the plan, still a bit confused, like the above example, even if the query on this table falls on another index, then why did the full table scan? Because the index keys include cust_num bank_card fund_card DXJGDM CPDM, there are fund_card, and not a full-table scan. Learning is not fine, follow up to learn more!

This article from "Love Growth, Love life" blog, declined reproduced!

A query transaction causes a high CPU usage on the database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.