MySQL regular log troubleshooting

For the official MySQL database. 5.5 and earlier versions of the lock information is not comprehensive, if you can switch to mariadb version; for viewing the lock information, you can use

For the official MySQL database. 5.5 and earlier versions of the lock information is not comprehensive, if you can switch to mariadb version; for viewing the lock information, you can use

MySQL version: 5.0.82

In the test environment, the developer told me that the lock wait timeout prompt is not displayed when the database cannot insert;

The first impression is that it is locked by a specific statement. Execute show full processlist multiple times to view the operations performed on the same table. Check that show engine innodb status \ G only displays the locked statements, the statement to lock is not displayed (this is also displayed in official version 5.5. The information displayed in the mariadb branch is more detailed !);

In this case, the innodb monitor is enabled, and innodb_lock_monitor is detected once every 16 s, and nothing is found during the test program. At this time, we can only start to suspect the OS problems, cpu load, and disk IO; because the network is isolated, the network is suspected at first, and the connection is often disconnected, and the change to the same network is still invalid. Then, all the data is mysqldump (this eliminates the problem that the disk is not damaged, at least)

In this case, we can only doubt the program problem. java uses the connection pool and initially suspected the reuse of a non-commit thread. The general log is opened this time. The display format is: time thread id SQL statement; this time we have a great harvest. By comparing the number of commit and set autocommit, corresponding to the specific thread, we can know which thread has not committed the transaction; at this time, the rest is for developers to modify the code.

Summary: For the MySQL official original database. The lock information in versions 5.5 and below is incomplete, and can be switched to mariadb. for viewing the lock information, you can use the new feature in 5.5: performance_schema, which is very convenient; general log should be well utilized. It is a treasure to diagnose faults and test performance !!