Shulou(Shulou.com)06/01 Report--

Mysql > show processlist

Show processlist; only lists the first 100 items, and all conversations can be shown with show full processlist;.

The id column, an identity, is useful when kill a statement.

The user column shows the current user. If it is not root, this command displays only the sql statements within your permissions.

The host column, which shows which port of the ip this statement is issued from. Can be used to track the user with the problem statement. The db column that shows which database the process is currently connected to.

The command column, which shows the commands executed by the current connection, which are generally sleep, query, connect.

The time column, which represents the duration of the state in seconds.

Note that state is just a state in the execution of a statement.

Info column, showing the sql statement, because the length is limited, so the long sql statement is not fully displayed, which is an important basis for judging a problem statement.

The most important thing in this command is the state column. Mysql lists the following states:

Checking table: checking the data table (this is automatic).

Closing tables: the modified data in the table is being flushed to disk and the table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that 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, temporary tables are being converted from memory storage to disk storage to save memory.

Creating tmp table: a temporary table is being created to hold some of the query results.

Deleting from main table: the server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables: the server is performing the second part of the multi-table deletion and is deleting records for other tables.

Flushing tables: executing FLUSH TABLES, waiting for another thread to close the data table.

Killed: if a kill request is sent to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

Locked: locked by other queries.

Sending data: the record of the Select query is being processed and the result is being sent to the client.

Sorting for group: sorting for GROUP BY.

Sorting for order: sorting for ORDER BY.

Opening tables: this process should be very fast, unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an Alter TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates: a query in Select DISTINCT mode is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client.

Reopen table: a lock on a table is acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting: the repair instruction is being sorted to create an index.

Repair with keycache: the repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.

Searching rows for update: we are talking about finding qualified records for updating. It must be done before Update modifies the relevant records.

Sleeping: waiting for a new request from the client.

System lock: waiting for an external system lock to be acquired. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the-- skip-external-locking parameter.

Upgrading lock:Insert DELAYED is trying to get a lock table to insert a new record.

Updating: searching for matching 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 and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs 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 processed all pending insert operations and is waiting for a new request.

Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked.

There are other states not listed above, but most of them are only useful to see if there are any errors on the server.

Mysql > show status

Aborted_clients: the number of connections that have been abandoned because the customer did not close the connection correctly.

Aborted_connects: the number of attempts to connect to the MySQL server that have failed.

Connections: the number of attempts to connect to the MySQL server.

Created_tmp_tables: the number of implicit temporary tables that have been created when the statement is executed.

Delayed_insert_threads: the number of latency insertion processor threads in use.

Delayed_writes: the number of rows written in INSERT DELAYED.

Delayed_errors: the number of rows written with INSERT DELAYED that have some errors (possibly duplicate key values).

Flush_commands: the number of times the FLUSH command was executed.

Handler_delete: the number of times rows are requested to be deleted from a table.

Handler_read_first: the number of times the first row in the table is requested to be read.

Handler_read_key: request numbers read lines based on keys.

Handler_read_next: the number of times a row based on a key is requested to be read.

Handler_read_rnd: the number of times a row based on a fixed location is requested to be read.

Handler_update: the number of times a row in the table is requested to be updated.

Handler_write: the number of times a row is requested to be inserted into the table.

Key_blocks_used: the number of blocks used for keyword caching.

Key_read_requests: the number of times a key value is requested to be read from the cache.

Key_reads: the number of times a key value is physically read from disk.

Key_write_requests: requests the number of times a keyword block is written to the cache.

Key_writes: the number of times a key block is physically written to disk.

Max_used_connections: the maximum number of connections used at the same time.

Not_flushed_key_blocks: keyblocks that have been changed in the key cache but have not been emptied to disk.

Not_flushed_delayed_rows: the number of rows in the INSERT DELAY queue waiting to be written.

Open_tables: the number of open tables.

Open_files: the number of open files.

Open_streams: number of open streams (mainly for logging)

Opened_tables: the number of tables that have been opened.

Questions: the number of queries sent to the server.

Slow_queries: the number of queries that take longer than long_query_time.

Threads_connected: the number of connections currently open.

Threads_running: the number of threads that are not sleeping.

Uptime: how many seconds did the server work?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.