Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MYSQL state state example analysis", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MYSQL state state example analysis" bar!

Background: with the migration of the computer room, the original report system is very slow to produce reports on the new db.

1. View connection status

Mysql > show full processlist

+-- -- +

| | Id | User | Host | db | Command | Time | State | Info |

+-- -- +

| | 1 | event_scheduler | localhost | NULL | Daemon | 2247681 | Waiting on empty queue | NULL | |

| | 121456 | system user | | NULL | Connect | 784383 | Waiting for master to send event | NULL |

| | 121457 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL |

| | 121468 | ecejadmin | 192.168.2.104 converting HEAP to MyISAM 42497 | ecejdbv3 | converting HEAP to MyISAM | 1 | | complex sql |

| | 121469 | ecejadmin | 192.168.2.10 converting HEAP to MyISAM 54423 | ecejdbv3 | converting HEAP to MyISAM | 2 | | complex sql |

| | 121470 | ecejadmin | 192.168.2.12 converting HEAP to MyISAM 9386 | ecejdbv3 | converting HEAP to MyISAM | 1 | | complex sql |

| | 121471 | ecejadmin | 192.168.2.104 converting HEAP to MyISAM 63937 | ecejdbv3 | converting HEAP to MyISAM | 2 | | complex sql |

The report sql status is as above

two。 Treatment process

1. In the terminal, set profiling=1

two。 Enter the SQL statement to be adjusted, such as complex sql

3. Type show profiles; to see the ID of the statement just now for later analysis

4.show profile for query 2

5.show profile block io,cpu for query 2

6.show profile cpu,block io,memory,swaps,context switches,source for query 5

7. From some of the queries above, you can roughly see where the execution of this SQL statement takes up too much time. What the test saw was that Copying to tmp table on disk and converting HEAP to MyISAM took up too much time. After checking on the Internet, it is found that the two parameters tmp_table_size and max_heap_table_size can be modified to adjust, so that the query with a large amount of data does not have to copy the result set to the physical disk. In this way, time will be won.

Thank you for your reading, the above is the content of "MYSQL state status example Analysis", after the study of this article, I believe you have a deeper understanding of the MYSQL state status example analysis of this problem, the specific use of the situation also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.