Server Performance Troubleshooting

MySQL Tutorials - Herong's Tutorial Examples

∟Server Performance Troubleshooting

This section provides some suggestions to troubleshoot server performance issues including checking total connections; listing running processes with their execution time; checking for locks; killing long running processes.

If your MySQL server is not responding, you can follow some suggestions described below to troubleshoot the root cause.

1. Check for current client connections. Why is there so many connections?

mysql> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| ...                               | ...   |
| Connections                       | 17865 |
+-----------------------------------+-------+

2. Check for current running processes. Why the last query is running for so long?

mysql> show processlist;
+-------+------+-----+---------+-------+-----------+---------------------
| Id    | User | db  | Command | Time  | State     | Info
+-------+------+-----+---------+-------+-----------+---------------------
| 17533 | john | web | Query   |  1543 | executing | SELECT count(*) FROM
| 17856 | lori | web | Query   | 71370 | executing | SELECT * FROM Forum
+-------+------+-----+---------+-------+-----------+---------------------

3. Kill query and keep the connection:

mysql> KILL QUERY 17856;
Query OK, 0 rows affected (0.00 sec)

4. Kill query and connection:

mysql> KILL CONNECTION 17533;
Query OK, 0 rows affected (0.00 sec)

5. Check for locks. Why is there so many locks?

mysql> show status like '%LOCK%';
+------------------------------------------+--------+
| Variable_name                            | Value  |
+------------------------------------------+--------+
| Com_lock_instance                        | 0      |
| Com_lock_tables                          | 0      |
| Handler_external_lock                    | 32     |
| Innodb_row_lock_current_waits            | 0      |
| Innodb_row_lock_time                     | 597    |
| Innodb_row_lock_time_avg                 | 66     |
| Innodb_row_lock_time_max                 | 253    |
| Innodb_row_lock_waits                    | 9      |
| Key_blocks_not_flushed                   | 0      |
| Key_blocks_unused                        | 0      |
| Key_blocks_used                          | 6698   |
| Locked_connects                          | 0      |
| Performance_schema_locker_lost           | 0      |
| Table_locks_immediate                    | 156704 |
| Table_locks_waited                       | 7      |
| ...                                      | ...    |
+------------------------------------------+--------+

Check for table locks using performance_schema.table_handles (Table locks held and requested). All 1019 table locks are ghost locks with no thread ID. They are removed only when MySQL server is restarted.

mysql> select * from performance_schema.table_handles;
1019 rows in set (0.26 sec)

mysql> select * from performance_schema.table_handles
    ->    where OWNER_THREAD_ID is not null;
Empty set (0.01 sec)

mysql> exit;

$ sudo systemctl stop mysqld

$ sudo systemctl start mysqld

mysql> select * from performance_schema.table_handles;
Empty set (0.00 sec)