Mysql is stuck. Most threads are in the sending data state for a long time.

There is a server with a large volume of access traffic. There are nearly 600 million dynamic PVS per day, and the database query average is nearly times per second.
On the other server, the program is the same as this one, but only about dynamic PVS per day
Several times in a row, the status was
The server does not crash and the database can be logged on normally. All queries are stuck in the "sending data" status and cannot be executed for A long time. These simple SQL statements are sometimes concentrated on table A and sometimes on table B, there are also some stuck locked or update statuses.

Check the description of mysql. The sending data status indicates two conditions. One is that mysql has queried the data and is sending it to the client. The other is that, mysql already knows where to read some data and is reading it from the data file.

Mysql official said that this is not a mysql bug, but the official did not say how to deal with... so, depending on the situation, it should be a configuration problem.
First of all, I checked from the perspective of SQL optimization. The stuck SQL statements are simple queries with low consumption and excellent indexing. Therefore, I think it should not be a problem with SQL statements. In addition, there is no slow query in the slow query log.

Optimize all the tables, that is, optimize table. After a few days, it will still be stuck .....

Later, we considered increasing concurrency performance, adding a series of memory configurations such as key_buffer thread_cache, and found that it had no effect. Situation persists

Later, the query_cache was reduced to the default value of 16 M, and some data that did not change much was made static. Surprised to find that 12 days have passed and no problems have occurred ......

Later, I thought that modifying query_cache may be helpful to this problem. After all, data is updated frequently and query_cache is updated frequently. However, depending on the mysql status, the hit rate of query_cache is still quite high, almost 75%.

I think the problem may be in the program, but I did not find it. The static content is the explanatory text of some products. Generally, the description of a product contains three or fifty Chinese characters.

There are a lot of questions about the problem. There are seven or eight products on one page, which may add up to three or five hundred Chinese characters. Although there are not many, queries are very frequent, the amount of data queried from this table should be considerable, and mysql will frequently fetch data from this table. However, sometimes the stuck statement is not querying this table ......

There are no easy tools at hand. The problem seems to be okay. Put down the Record Filing first. Wait for a higher level and check again.

An important reason why MySQL is prone to full process death

It is far more difficult to build a website than I expected. In addition to economic and technical aspects, some problems cannot be solved by ordinary technicians. However, during this period of time, I also learned how to think about and solve problems. In particular, I have solved several problems consecutively. It can be said that they are not solved by developers or other technical personnel, more and more self-confidence!

Speaking of this, we must talk about the basic configuration of our website www.yes81.net, LINUX 9.0 system, JBOSS42 WEB service, MYSQL, which has been running for some time since May 1, the current traffic is about IP.

I remember a problem that occurred before and has not been solved for a long time. Once the fault occurred, the CPU ran to around 100%, the system did not respond, and the MYSQL and JBOSS processes died. It was originally solved by creating indexes for some big data tables! This problem is a bit like this. When I die, the Service almost didn't respond. By checking the MYSQL process in the background, it has already exceeded the 1000 limits I set. I changed the configuration to 1st on the seventh day, think about whether it is related to this. The recent traffic volume has increased. To be honest, I still don't believe in 1000 concurrent connections, but the fact is in front of me. Now, 1000 processes are stuck here! In the process list, it is easy to see that the process is full and every process is in the sending data status. After 2 days, the problem still cannot be solved, no matter whether you reconfigure the startup parameters or check for external attacks, increasing the temporary buffer table to MB does not help. Every added connection like this is almost stuck, and it is in the sending data state! Is it because the data cannot be sent or the query cannot be completed?

With this problem in mind, whether there is a data deadlock or no submission problem with the development communication, resulting in the query lock! And sometimes it is normal, but most of them are abnormal deadlocks! After checking for half a day, the report said that the program did not find any problems, because the accurate code of the program can be located according to the command! So what is the problem?

I think of the database corruption problem that occurred in the previous MS SQLSERVER2000 and tried to fix it. The congestion command is concentrated on several important tables. One is the restaurant information table (40 thousand records), which cannot be repaired with the repair command! I found that the set type is inoubox. I changed the type to MYISAM and then fixed it. No error was reported during the fix. However, after the system was restarted, all the problems were solved!