How to optimize MYSQL database server with high iowait

An optimization case for high iowait of a database server

1. Development feedback that SQL in a certain test environment runs slowly, while in other test environments the SQL runs very fast. The two environments have the same configuration, and only the mysql server is deployed.

2. Execute the top command and find that the disk iowait on the machine with slow SQL running is much higher than that on the machine with fast SQL running. It is speculated that this is the main reason for the slow running of SQL, because the SQL needs to read a table, the table is large, and the number of rows to be scanned is large.

3. What causes the machine's iowait to be high? When executing iotop, it is found that the process that consumes io is mainly mysql, and it is mainly the read operation on mysql.

4. There must be other high-frequency query statements that constantly query data from a large table, and the index may not be used during the query. The number of table rows to be scanned is large, resulting in high-frequency io operations, causing other sql that require io operations to run slowly.

5. What SQL is causing it? I turned on the general log and found that there were too many statements collected and the high-overhead SQL could not be easily located.

6. Turn on slow log, set long_query_time to 1 to capture slow queries, and use pt-ioprofile to track which files in the mysql data file consume more io.

7. Based on the results of slow log (which can be aggregated using pt-query-digest) and pt-ioprofile, it is found that there are indeed two typical SQL statements that need to scan the entire table and the corresponding table is very large and are frequently executed. This leads to high disk IO.

8.Then the remaining problem is to optimize the table or query. The simplest and most direct way is to improve query performance by building appropriate indexes and reduce the number of rows scanned in the table. If you need to continue to squeeze out performance, you can solve the problem by optimizing the writing method of SQL, adjusting the table structure, and adjusting parameter configuration.

9. Start with the method with the greatest profit, first evaluate the SQL statement, check the data distribution of each field according to the conditions in the statement, and evaluate the reasonableness of creating an index or a multi-column joint index on the field through explain etc. properties and create appropriate indexes.

10. Finally, it was found that after the index was built, the original statements that needed to scan the entire table could effectively reduce the number of scanned rows through the index, and then the IO operations were reduced. The server's iowait topic, the original feedback of the slower running SQL The running speed has been improved, but it is still not ideal.

11. Finally, by creating an index on the table corresponding to the slow statement and correcting the wrong value type used in the where condition, the running speed of the SQL statement is greatly improved, and the overall IO consumption of the server is greatly reduced.

12. You can use pt-query-digest to aggregate the slow log generated by the optimized mysql server and use pt-ioprofile to analyze the io occupancy of the optimized mysql data file to understand the difference before and after optimization.

The above is the detailed content of How to optimize MYSQL database server with high iowait. For more information, please follow other related articles on the PHP Chinese website!