Mysql execution status analysis showprocesslist_MySQL

Mysql execution status analysis showprocesslist bitsCN.com

Mysql execution status analysis show processlist

Execution Status Analysis

L Sleep status

N usually indicates that the resource has not been released. if it is through the connection pool, the sleep status should be constant within a certain number.

N practice example: when the front-end data is output (especially to the user terminal), the database connection is not closed in time, resulting in a large number of sleep connections due to the network connection speed. when the network speed is abnormal, the database too connector connections is suspended.

N simple explanation: Data Query and execution usually takes less than 0.01 seconds, while network output usually takes about 1 second or longer. the original data connection can be released in 0.01 seconds, however, because the front-end program does not execute the close operation and directly outputs the result, the database connection remains in sleep state until the result is displayed on the user's desktop!

L Waiting for net, reading from net, writing to net

N occasionally appears.

If n occurs in large quantities, quickly check the network connection status and traffic from the database to the front-end.

N case: Due to plug-ins, a large number of intranet databases are read, and the Mbit/s exchange in the intranet is rapidly full, resulting in a large number of connections being blocked in waiting for net. too many database connections have crashed.

L Locked status

N has an update lock.

N The use of innodb can reduce the generation of locked state, but remember that the update operation should use the index correctly, even the low-frequency update operation cannot be neglected. As shown in the example of the affected result set above.

N in the myisam era, locked is a nightmare for many highly concurrent applications. Therefore, mysql officially began to recommend innodb.

L Copy to tmp table

N indexes and existing structures cannot cover the query conditions, so a temporary table is created to meet the query requirements, resulting in a huge I/o pressure.

N terrible search statements can cause such a situation. if it is a data analysis task or a data cleaning task in the middle of the night, it may occasionally appear. It must be optimized frequently.

N Copy to tmp table is usually related to join table queries. we recommend that you do not use join table queries.

N practice example:

A database in u community is blocked and asks for help. after investigation, its server has multiple database applications and websites, and an uncommon small website database produces a horrible copy to tmp table operation, this overload the I/o and cpu of the entire hard disk. Kill the operation and restore everything.

L Sending data

N Sending data is not Sending data. Don't be fooled by this name. this is a process for retrieving data from a physical disk. if you have a large number of affected result sets, then we need to extract data from different disk fragments,

N occasional connections in this status are not affected.

N returns to the problem that affects the result set. generally, if there are too many sending data connections, the query usually affects the result set too much, that is, the query index is not optimized enough.

N if a large number of similar SQL statements appear in the show proesslist list, all of them are in the sending data state, optimize the query index, and remember to consider using the idea of influencing the result set.

L Storing result to query cache

N is in this status. if this status occurs frequently, use set profiling for analysis. if there is a large proportion of resource overhead in the SQL overall overhead (even if it is a very small overhead, check the proportion ), the query cache has many fragments.

N you can use the flush query cache to instantly clean up or make it a scheduled task.

You can set the n Query cache parameters as appropriate.

L Freeing items

N in theory, there won't be many such things. Occasional problems

N if a large number of memory or hard disk problems occur. For example, the hard disk is full or damaged.

When the pressure on n I/o is too high, the Free items may take a long time to execute.

L Sorting...

N is similar to Sending data. The result set is too large and the sorting conditions are not indexed. you need to sort the data in the memory or even create a temporary structure.

L others

N is still in many states. If yes, check the information. Basically, we encounter less blocking in other states, so we don't care.

Command: show status;

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.

BitsCN.com