Mysql execution status analysis show processlist

Mysql execution status analysis show processlist execution Status Analysis l Sleep status n usually indicates that the resource is not released. If it is through the connection pool, the sleep status should be constant within a certain number of practical examples: because the database connection is not closed in time when the front-end data is output (especially to the user terminal), a large number of sleep connections are generated 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 netn occasionally appears no harm n if a large number of appear, quickly check the network connection status and traffic of the database to the front-end n case: Due to plug-ins, A large number of Intranet databases are read, and the MB exchange used by the Intranet is rapidly full, resulting in a large number of connections being blocked in waiting for net, too many database connections crash l Locked status n update operation lock n usually use innodb to reduce the generation of locked status, but remember that the update operation should use the index correctly, even low-frequency update operations 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 tablen 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 a u community is blocked for help. After investigation, its server has multiple database applications and websites, an uncommon small website database generates a horrible copy to tmp table operation, resulting in overload of Hard Disk I/o and cpu pressure. Kill the operation and restore everything. L Sending datan Sending data is not Sending data. Do not be spoofed by this name. This is the process for obtaining data from a physical disk. If you have more affected result sets, then data needs to be extracted from different disk fragments. n is occasionally connected in this status. 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 cachen is in this status. If it appears frequently, use set profiling for analysis. If there is a large proportion of resource overhead in SQL overall overhead (even if it is a very small overhead, proportion), it indicates that the query cache shards are large. n The flush query cache can be used for immediate cleanup. You can also set the n Query cache parameter of a scheduled task as needed. L Freeing itemsn in theory, there won't be many such things. Occasionally there may be no problem n if there are a large number of problems, memory, hard disk may have problems. 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 there are many other n statuses. If you encounter this problem, check the information. Basically, we encounter less blocking in other States, so we don't care about the command: show status; Aborted_clients. The number of dropped connections because the customer did not 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. Open_streams: Number of opened tables in Opened_tables (mainly used for logging. 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.