Performance Tuning article-TPS low-optimized SQL statement (i)

When performing performance tests, the problem is always strange. I've compiled some of the usual performance tests to see how the problem is viewed.

One. The SQL statement does not have a reference index:

When performing a performance test, the server is running under the circumstances:

1. Database, application CPU does not exceed 80%;
2. Sufficient memory (free memory >20m);
3. Network normal;
4. Disk input/output is normal;

There is no print error in the log, but the TPS is very low, such as only 100-120 (the performance test for a single interface request, TPS is typically at 550-60000, unless the program is complex, the TPS is smaller). In this case, you should first look at the SQL statement for the problem:

Such as:

Query statement, whether there is an index, the index has no role, (is the query statement also depends on the data table of the total data)

Case: When I tested earlier, there was a query interface that was the problem, TPS is very low, because there is no index caused, after the index, the TPS from the previous 110 to 6000 such.

Two. The SQL statement index does not work:

When performing a performance test, the server is running under the circumstances:

1. The CPU of the database is up to 90%-99.9%, and the CPU of the application is low;
2. Sufficient memory (free memory >20m);
3. Network normal;
4. Disk input/output is normal;

CPU-High Monitor diagram

The logs are not printing errors, but the TPS is very low, and you need to see if the index does not work and traverse a lot of unrelated tabular data.

Case: When I tested earlier, some interfaces were the problem, the TPS was very low, the CPU was very high, there was an index, but the SQL query was written with a problem, traversing a lot of data, causing the referenced index to fail.

You can use explain to count a query, how many times SQL will traverse the problem, and the program's statement results in a large number of table scans. The lower the index sensitivity used, whether or not the expression is non-valid, causing the index to fail, and the greater the value of rows, the more traversal is indicated:

EXPLAIN SELECT * from  tb_hlf_pos_cashier where account= ' XXXXX ';

(These are the ways to improve performance when the SQL statement is optimized at the time of performance testing.) Of course I just found the problem, the specific SQL statement optimization, is the development of colleague optimization, so I can only explain here I understand the knowledge, a lot of detailed optimization code I did not study.

Performance Tuning article-TPS low-optimized SQL statement (i)