Mysql database show processlist Result Analysis

Next, let's take a look at the mysql database show processlist result analysis. I hope this article will help you.

Mysql> show processlist;

+ -- + ----- + ------- +

| Id | User | Host | db | Command | Time | State | Info

+ -- + ----- + ------- +

| 207 | root | 192.168.0.2: 51621 | mytest | Sleep | 5 | NULL

| 208 | root | 192.168.0.2: 51622 | mytest | Sleep | 5 | NULL

| 220 | root | 192.168.0.2: 51676 | mytest | Query | 84 | locked |

Select name, culture, value, type from book where id = 1

Describe the meaning and purpose of each column,

Id column: an identifier. It is useful when you want to kill a statement.

User column: displays the current user. If it is not root, this command only displays SQL statements within your permission range.

Host column: displays the port from which the statement is sent. Users who can trace problematic statements.

Db column: displays the database to which the process is currently connected.

Command column: displays the commands executed by the current connection, which is usually sleep, query, and connect ).

Time column: the duration of this state, in seconds.

State column: displays the status of the SQL statement using the current connection. It is an important column and will be described in the future. Note that state is only a certain state in statement execution, for an SQL statement that has been queried as an example, it may need to pass through the copying to tmp table, Sorting result, Sending data and other statuses to complete.

Info column: This SQL statement is displayed. Due to the limited length, the long SQL statement is not displayed completely, but an important basis for determining the problematic statement.

The most important part of this command is the state column. mysql lists the following states:

Checking table

Checking the data table (this is automatic ).

Closing tables

Refreshing the modified data in the table to the disk and closing the used table. This is a very fast operation. If not, check whether the disk space is full or the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to store some query results.

Deleting from main table

The server is executing the first part of multi-Table deletion. The first table has just been deleted.

Deleting from reference tables

The server is executing the second part of multi-Table deletion and is deleting records of other tables.

Flushing tables

Executing flush tables, waiting for other threads to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag and discard the next kill request. MySQL checks the kill flag in each primary loop. However, in some cases, the thread may die after a short period of time. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.

Locked

It is locked by other queries.

Sending data

Processing the record of the SELECT query and sending the result to the client.

Sorting for group

Sorting for group.

Sorting for order

Sorting order.

Opening tables

This process should be fast unless it is disturbed by other factors. For example, a data TABLE cannot be opened by another thread before the alter table or lock table statement is executed. Opening a table.

Removing duplicates

A select distinct query is being executed, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records and then send the results to the client.

Reopen table

A lock is obtained for a table, but the lock can be obtained only after the table structure is modified. The lock has been released, the data table is closed, and the data table is being re-opened.

Repair by sorting

The repair command is being sorted to create an index.

Repair with keycache

The repair command is using the index cache to create a new index one by one. It is slower than Repair by sorting.

Searching rows for update

We are talking about identifying qualified records for updates. It must be completed before the related record is updated.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting for an external system lock to be obtained. If multiple mysqld servers are not running to request the same table at the same time, you can add the-skip-external-locking parameter to disable external system locks.

Upgrading lock

Insert delayed is trying to get a lock table to INSERT a new record.

Updating

Searching for matched records and modifying them.

User Lock

Waiting for GET_LOCK ().

Waiting for tables

This thread is notified that the data table structure has been modified. You need to re-open the data table to obtain the new structure. Then, in order to re-open the data table, you must wait until all other threads close the table. This notification is generated in the following situations: flush tables tbl_name, alter table, rename table, repair table, analyze table, or optimize table.

Waiting for handler insert

Insert delayed has completed all the INSERT operations to be processed and is waiting for new requests.

Most States correspond to fast operations. As long as one thread remains in the same state for several seconds, a problem may occur and you need to check it. Other statuses are not listed above, but most of them are only needed to check whether there are errors on the server.

For more information about the MySQL Show command, see:
 
Show tables or show tables from database_name or show database_name.tables;
Explanation: displays the names of all tables in the current database.
Show databases;
Explanation: displays the names of all databases in mysql.
 
Show processlist;
Explanation: displays all processes running in the system, that is, the query currently being executed. Most users can view
Their own processes, but if they have the process permission, they can view all processes, including passwords.
 
Show table status;
Explanation: displays information about each table in the currently used or specified database. Information includes the table type and the latest update time of the table.
 
 
 
Show columns from table_name from database_name; or show columns from database_name.table_name; or show fields;
Explanation: display the column name in the table (the same effect as the desc table_name command)
 
Show grants for user_name @ localhost;
Explanation: displays the permissions of a user. The display result is similar to the grant command.
 
Show index from table_name; or show keys;
Explanation: displays the table index.
 
Show status;
Explanation: displays information about specific system resources, such as the number of running threads.
 
Show variables;
Explanation: displays the name and value of the system variable.
 
Show privileges;
Explanation: display different permissions supported by the server
 
Show create database database_name;
Explanation: displays the SQL statements used to create a specified database.
 
Show create table table_name;
Explanation: displays the SQL statements used to create a specified data table.
 
Show engies;
Explanation: displays available storage engines and default engines after installation.
 
Show innodb status;
Explanation: displays the innoDB Storage engine status.
 
Show logs;
Explanation: displays BDB storage engine logs.
 
Show warnings;
Explanation: displays errors, warnings, and notifications generated by the last executed statement.
 
Show errors;
Explanation: only displays Errors generated by the last statement.
 
Most of the above commands can use like, such as show table like '% abce % '.
 
Appendix:
 
Show status result description
 
Column meaning
Name table Name
Type table Type (ISAM, MyISAM, or HEAP)
Row_format Row Storage Format (fixed, dynamic, or compressed)
Number of Rows
Avg_row_length average row length
Data_length the length of the data file
Max_data_length the maximum length of the data file
Index_length the length of the index file
Data_free is allocated but no Bytes are used
Auto_increment next autoincrement (automatically add 1) Value
Time when the Create_time table is created
Last Update Time of the Update_time Data File
Check_time: The last time when a check is run on the table.
Additional options used by Create_options and CREATE TABLE
Comment comments used when creating a table (or why MySQL cannot access some information about the table ).
Show index result description:
 
Column meaning
Table Name
Non_unique 0. If the index cannot contain duplicates.
Key_name index name
The sequence number of the column in The Seq_in_index index, starting from 1.
Column_name column name.
How to sort Collation columns in the index. In MySQL, this value can be A (ascending) or NULL (unordered ).
The number of unique values in the Cardinality index. This can be changed by running isamchk-.
Sub_part: the number of index characters if only some columns are indexed. NULL, if the entire key is indexed.
Show variables result description:
 
Aborted_clients indicates the number of dropped connections because the client fails to properly close the connection.
The number of connection times of the MySQL server that failed the Aborted_connects attempt.
The number of times that Connections attempted to connect to the MySQL server.
Created_tmp_tables: Number of implicit temporary tables created when the statement is executed.
The number of threads that Delayed_insert_threads is using to insert a delayed processor.
The number of rows written by Delayed_writes Using insert delayed.
The number of rows in which Delayed_errors writes data using insert delayed (which may duplicate key values.
The number of times Flush_commands executes the FLUSH command.
The number of rows that Handler_delete requests to delete from a table.
The number of times the Handler_read_first request reads the first row of the table.
The Handler_read_key request number is based on the key-read row.
The number of times the Handler_read_next request reads a row based on a key.
The number of times the Handler_read_rnd request reads a row based on a fixed position.
The number of times Handler_update requests to update a row in the table.
The number of times a Handler_write request inserts a row into a table.
The number of Key_blocks_used Blocks Used for keyword cache.
The number of times Key_read_requests requests read a key value from the cache.
The number of times that Key_reads reads a key value from the disk physically.
Number of times that Key_write_requests requests write a key block to the cache.
The number of times that Key_writes physically writes a key-Value block to a disk.
The maximum number of connections simultaneously used by Max_used_connections.
Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk.
The number of rows that Not_flushed_delayed_rows is waiting to write in the insert delay queue.
The number of open tables in Open_tables.
The number of open files in Open_files.
Number of open streams in Open_streams (mainly used for log recording)
The number of open tables in Opened_tables.
The number of queries sent by Questions to the server.
Slow_queries takes more than long_query_time.
The number of connections currently opened by Threads_connected.
The number of threads whose Threads_running is not sleeping.
How many seconds does the Uptime server work.
Notes:
 
If Opened_tables is too large, your table_cache variable may be too small.
If key_reads is too large, your key_cache may be too small. The cache hit rate can be calculated using key_reads/key_read_requests.
If Handler_read_rnd is too large, you may have a large number of queries that require MySQL to scan the entire table or that you have not correctly used the join operation ).