Reasons and solutions for slow MySQL query stuck in sending data

Because I wrote a Python program and intensively operated a Mysql library. When the amount of data was not large, I didn't notice it was very slow. Later, it became slower and slower. I thought it was just because of the large amount of data. But later it became so slow that I couldn't bear it. I checked for a long time and used all the indexes that could be used. It still took 3 to 4 seconds to execute once, which was unbearable.

So I cached all the queries that could be cached using redis, which greatly accelerated the application.

But there are still some things that cannot be cached, or in other words, there is no way to cache them if the results are different every time you query them. Using Navicat's query overview, we can see that the stuck part is in the Sending data section, which takes 3.5 seconds and accounts for 99% of the query time.

I checked some information online and found that some of them were due to problems with SQL statements, but I did not use varchar or the in method at all.

So I thought maybe the table was too large and might not be cached in the memory, so I checked the memory occupied by the mysqld process, which was only more than 50M, which was obviously too little. The actual capacity of the table was more than 200M, which can be seen in the object column of navicat. It should be that each query is read from the disk, so it is very time-consuming. So I checked the disk IO of win10 and found that it was indeed the case. The disk IO reached 100%, and it was a solid-state drive, reading about 80M per second. No wonder it was so slow.

So I checked the mysql configuration file and found that there was a configuration of only 32M. I adjusted it to 512M and restarted mysql. This time the time was reduced from 3.5 seconds to 0.76 seconds.

The configuration items are:

innodb_buffer_pool_size=32M

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links