MySQL slow query troubleshooting
Step 1: Check the current running transaction status
select trx_state,trx_started,trx_mysql_thread_id,trx_query from information_schema.innodb_trx;
in:
-
Trx_state: Transaction status -
Trx_started: Transaction start time -
Trx_mysql_thread_id: The thread id of the transaction -
Trx_query: The query currently executed by the transaction sql
whenTrx_stateWhen the value of: "LOCK_WAIT", it means that a lock waiting has occurred. Waiting time is too long may cause the program to return to failure.
Example:
Step 2: Check the thread status
show processlist;
- The returned results include: id, host, db, Command, Time, State, etc.
- Find the record corresponding to trx_mysql_thread_id in the first step, if Command is "Sleep".
- This means that the transaction of this thread has not been committed or has been stuck. We need to kill it manually.
Step 3: Kill the thread
Kill id
- Execute the above command on the mysql client to kill the thread.
- Generally, the above command is enough to determine whether an error occurred due to a transaction waiting problem.
Step 4: Assisted judgment command
In addition, the following commands can also assist in judgment:
1. Query the row lock status:
show status like 'InnoDB_row_lock%';
Returning, Innodb_row_lock_current_waits shows the number of row locks waiting for.
2. Query the lock currently used
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
The corresponding in Mysql8 is:
select * from performance_schema.data_locks;
3. Query the transaction waiting for lock
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
The corresponding in Mysql8 is:
select * from performance_schema.data_lock_waits;
4. Query the currently locked table
show OPEN TABLES where In_use > 0;
5. Query the last deadlock log
show engine innodb status
Can be exported to local viewing:
mysql -u root -p --execute="show engine innodb status \G" > /root/
According to the above command, find the trx_id that has the waiting exception in the result, that is, the transaction id, and then go back to the first step, find the corresponding transaction from the current transaction list, and then kill the corresponding thread.
Step 5: Turn on the slow query log
By slow querying the log, record the SQL statement with execution time timed out. The default timeout is 10 seconds.
1. Check whether the slow query log is enabled and turn on the log
show variables like '%query%';
Returning, slow_query_log is "OFF", indicating that it is closed. Turn on the log:
set global slow_query_log='ON';
Step 6: Turn on the universal query log
1. Check whether the general query log is enabled
show variables like '%general%';
Returning, general_log is "OFF", indicating closed. Turn on the log:
set global general_log='ON'
Notice:
The general log will record all SQL execution statements, which will cause the log file to be too large and the execution speed will be slow. It should be closed in time after query.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.