Shulou(Shulou.com)06/01 Report--

Because of the preparation of a Python program, intensive operation of a Mysql library, before the amount of data is not large, did not find very slow, and then slower and slower, thought it was just the reason for the large amount of data, but then slow to unbearable, checked for a long time, the index can be used, or to execute 3 to 4 seconds, can not stand.

So some cacheable queries are cached with redis, which greatly speeds up the application.

However, there are still some things that cannot be cached, that is to say, there is no way that each query has a different result. Using the query profile of navicat, you can see that it is stuck in the Sending data section, which takes 3.5 seconds, accounting for 99% of the query time.

I checked some of it on the Internet, some because of the problem of sql sentence, but I didn't use varchar or in method at all.

So I wonder if the table is too large and may not be cached in memory, so I first check the memory occupied by the mysqld process, which is only more than 50 M, which is obviously too little. the actual capacity of that table is more than 200m, which can be seen in the object column of navicat. It should be read from disk every time, so it takes a lot of time, so I look at the disk IO of win10 and find that this is indeed the case. Disk IO reaches 100%, or solid state disk. Read about 80m per second, no wonder it is so slow.

Then checked the mysql configuration file, found that there is a configuration only 32m, adjusted to 512m, restart mysql, this time from 3.5 seconds to 0.76 seconds.

The configuration items are:

Innodb_buffer_pool_size=32M

This is the mysql5.7 default, change to 512 or 1024, and then restart, depending on your hardware configuration.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.