Explanation of the Reasons and Solutions of Mysql Query Slow Card in sending data

Because I wrote an Python program and intensively operated an Mysql library, I didn't find it slow when the amount of data was small before, but it became slower and slower later, thinking that it was only the reason for the large amount of data, but it was too slow to bear later. After checking for half a day, all the indexes were used, and it took 3 to 4 seconds to execute once, which was unbearable.

Therefore, all the queries that can be cached are cached with redis, which greatly accelerates the application.

However, there are still 1 that can't be cached, or that every query is different from 1 result, so there is no way. With the query overview of navicat, we can see that the stuck place is in the section of Sending data1, which takes 3.5 seconds and accounts for 99% of the query time.

I looked up some on the Internet, some of which are due to the problem of sql statement, but I didn't use varchar at all, and I didn't use in method.

So I wondered if the watch was too big. It may not be cached in memory, So first check the memory occupied by mysqld process, only more than 50 M, which is obviously too little. The actual capacity of that table is more than 200 M, which can be seen in the object 1 column of navicat. It should be read from the disk every time, so it takes time. So check the disk IO of win10 and find that this is true. The disk IO has reached 100%, and it is still a solid-state disk.

So I checked the configuration file of mysql, and found that one configuration was only 32M, adjusted to 512M, restarted mysql, and this time it changed from 3.5 seconds to 0.76 seconds.

The configuration items are:

innodb_buffer_pool_size=32M

This is the default of mysql 5.7. Change it to 512 or 1024, and then restart it, depending on your hardware configuration.

Summarize