How to monitor MySQL consuming server resources

http://258xiaolei-sina-com.iteye.com/blog/764665

Start MySQL with parameter--log-slow-queries to record SQL execution time of more than long_query_time seconds

MySQL comes with slow log analysis tool Mysqldumpslow.
Slow log is a file written by MySQL based on the execution time setting of the SQL statement that is used to parse a slower statement.

Just configure it in the My.cnf file:
Log-slow-queries = [Slow_query_log_filename]
You can record SQL statements that exceed the default 10s execution time.
If you want to modify the default settings, you can add:
Long_query_time = 5
Set to 5s.

/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--user=mysql--pid-file=/var/run/mysqld/mysqld.pid-- Skip-locking--port=3306--socket=/var/run/mysqld/mysqld.sock--log-slow-queries=/var/log/mysql/slow.log



Explain to understand the state of SQL execution.
Explain select * from Wp_posts\g;



Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements.

Using the method, add explain to the SELECT statement:

Such as: Explain select Surname,first_name form A, a where a.id=b.id

Explanation of the Explain column:

Table: Shows which table the data for this row is about
Type: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all
Possible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain
Key: The actual index used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index
Key_len: The length of the index used. The shorter the length the better, without loss of accuracy
Ref: Shows which column of the index is being used and, if possible, a constant
Rows:mysql the number of rows that must be checked to return the requested data
Extra: Additional information on how MySQL resolves queries. It will be discussed in Table 4.3, but the bad examples you can see here are the using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow
The meaning of the description returned by the extra column

Distinct: Once MySQL finds a row that matches a row, it no longer searches for
Not Exists:mysql optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for
Range checked for each Record (index map:#): No ideal index was found, so for every combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index
Using Filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.
Using index: Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index
Using temporary When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by
Where used uses a WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem different connection types of interpretation (in order of efficiency)
The system table has only one row: the system table. This is a special case of the const connection type
Const: The maximum value of a record in a table can match this query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads the value first and treats it as a constant.
Eq_ref: In a connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses the index as the primary key or the unique key.
Ref: This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better
Range: This connection type uses an index to return rows in a range, such as what happens when you use > or < to find something
Index: This connection type is fully scanned for each record in the previous table (better than all because the index is generally less than the table data)
All: This connection type is fully scanned for each previous record, which is generally bad and should be avoided as much as possible.


Use show status like "handler_read%"; To understand the effect of the index.
High Handler_read_key value indicates good index effect, high Handler_read_rnd_next value indicates inefficient index.

View the current running state with show processlist.



Mysql> show Processlist;

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

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

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

|207|root |192.168.0.20:51718 |mytest | Sleep |         5 | | Null

|208|root |192.168.0.20:51719 |mytest | Sleep |         5 | | Null

|220|root |192.168.0.20:51731 |mytest | Query | 84 | Locked |

Select Bookname,culture,value,type from book where id=001

First of all, the meaning and purpose of the columns,

The ID column, an identifier, is useful when you want to kill a statement, killing the query with a command/*/mysqladmin the kill process number.

The user column shows the single-user, if not root, this command displays only the SQL statements within the scope of your permission.

The host column that shows which IP port this statement was issued from. The user that is used to track the problem statement.

The DB column that shows which database the process is currently connected to.

Command column, which displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect).

Time column, which is the duration of this state, in seconds.

The State column, which shows the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement, as an example, may need to go through copying to TMP table, Sorting result,sending data and other states can be completed,

The info column shows this SQL statement because the length of the SQL statement is not complete, but it is an important basis for judging the problem statement.

The most critical of this command is the State column, which is listed in the following categories:

Checking table
Checking the data table (this is automatic).
Closing tables
The modified data in the table is being flushed to disk, and the tables that have been exhausted are being closed. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load.
Connect out
Replication from the server is connecting to the primary 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 temporary tables to hold partial query results.
deleting from Main Table
The server is performing the first part of a multi-table delete and has just deleted the first table.
deleting from reference tables
The server is performing the second part of a multi-table delete and is deleting records from other tables.
Flushing tables
Executing FLUSH TABLES, waiting for other threads to close the data table.
Killed
Sends a KILL request to a thread, the thread checks the kill flag bit and discards the next kill request. MySQL checks the kill flag bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released.
Locked
Locked by another query.
Sending data
The record for the SELECT query is being processed, and the results are being sent to the client.
Sorting for group
Sorting is being done for GROUP by.
Sorting for order
The order by is being sorted.
Opening tables
The process should be quick, unless other factors interfere with it. For example, a data table cannot be opened by another thread until the row of the ALTER table or LOCK table statement is complete. Attempting to open a table.
removing duplicates
A query that is executing a SELECT DISTINCT method 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 obtained, but it must be changed after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again.
Repair by sorting
Repair instructions are being sorted to create an index.
Repair with Keycache
The repair instructions are using the index cache to create a new index one by one. It will be slower than Repair by sorting.
Searching rows for update
The qualifying records are being told to find out to prepare for the update. It must be completed before the UPDATE is about to modify the related records.
Sleeping
Waiting for the client to send a new request.
System Lock
Is waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing 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
The thread is notified that the data table structure has been modified and the data table needs to be reopened to obtain a new structure. Then, to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following cases: 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 insertions and is waiting for a new request.
Most of the state corresponds to a fast operation, so long as one thread remains in the same state for several seconds, a problem may occur and need to be checked.
There are other states that are not listed above, but most of them are only useful to see if there is an error in the server.

The MySQL manual has a description of all the statuses and links as follows: Http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Chinese instructions taken from http://www.linuxpk.com/5747.html

How to monitor MySQL consuming server resources