MySQL Troubleshooting database execution slow through show processlist

RDS for MySQL is using the InnoDB engine. Unlike the MyISAM engine, which provides only table locks, InnoDB provides different levels of locks. However, in our daily operations, due to improper SQL operations on the database caused a long lock, causing other SQL statements to wait for a long time to execute. This phenomenon is a great hindrance to the normal use of the database. Next we'll show you how to troubleshoot whether the current instance appears in this state.

First, the user can log in to RDS (available through the client, DMS, and other tools) to execute the command in the database:

Show Processlist

Execution Result:

Let's start by describing the meanings of the following fields:

ID, the identity of the process;

User, show current users;

Host, showing the source IP and port;

DB, displaying the currently connected database;

command, which shows the execution of the current connection, is usually sleep (sleep), query, connection (connect);

Time, the duration of this state, the unit is seconds;

The State column, which displays the status of the SQL statement using the current connection, is an important column;

Info, which shows this SQL statement, because the length is limited, so long SQL statements are not complete, but an important basis for judging the problem statement.

"Waiting for Table metadata lock" appears when a user's SQL is causing another SQL to wait for a lock, and if that happens, it causes a lot of SQL build-up and an exception to the instance state. So at this point the user is required to kill the query that causes the other SQL to wait for the lock based on the previous ID, and then the other statements will execute normally.

MySQL Troubleshooting database execution slow through show processlist