High-load Mysql troubleshooting and mysql troubleshooting

High-load Mysql troubleshooting and mysql troubleshooting

Troubleshooting of Mysql high load

Problems Found
The top command checks the server load and finds that mysql has 200% of the cpu, which causes the high load of Mysql. It is estimated that it is caused by index problems and some abnormal SQL statements.

Troubleshooting
1. determine the type of high load. The top command determines whether the high load is CPU or IO.
2. Run mysql to view the current number of connections and the executed SQL statement.
3. Check slow query logs, which may be caused by high load.
4. Check whether hardware problems are caused by disk faults.
5. Check the monitoring platform and compare the loads of the machine at different times.

 

Determine the load type(Top)

top - 10:14:18 up 23 days, 11:01,  1 user, load average: 124.17, 55.88, 24.70 Tasks: 138 total,   1 running, 137 sleeping,   0 stopped,   0 zombie Cpu(s):  2.4%us,  1.0%sy,  0.0%ni, 95.2%id,  2.0%wa,  0.1%hi,  0.2%si,  0.0%st Mem:   3090528k total,  2965772k used,   124756k free,    93332k buffers Swap:  4192956k total,  2425132k used,  1767824k free,   756524k cached                PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 30833 mysql     15   0 6250m 2.5g 4076 S 257.1 49.9 529:34.45 mysqld  

 

View the number of current connections and executed SQL statements

show processlist; Id  User    Host    db  Command Time    State   Info 192 slave   8.8.8.142:39820 NULL    Binlog Dump 58982   Has sent all binlog to slave; waiting for binlog to be updated  NULL 194 slave   8.8.8.120:41075 NULL    Binlog Dump 58982   Has sent all binlog to slave; waiting for binlog to be updated  NULL 424891 biotherm    8.8.8.46:57861  biotherm    Query   493 Sending data    SELECT * FROM xxx_list WHERE tid = '1112'  AND del = 0  ORDER BY  id DESC  LIMIT 0, 4 424917 biotherm    8.8.8.49:50984  biotherm    Query   488 Sending data    SELECT * FROM xxx_list WHERE tid = '1112'  AND del = 0  ORDER BY  id DESC  LIMIT 0, 4 .............................................. 430330 biotherm    8.8.8.42:35982  biotherm    Query   487 Sending data    SELECT * FROM xxx_list WHERE tid = '1112'  AND del = 0  

 

Slow query of records
Edit the Mysql configuration file (my. cnf) and add the following lines in the [mysqld] field:

Log_slow_queries =/usr/local/mysql/var/slow_queries.log # Slow query log Path long_query_time = 10 # records the SQL query statement log-queries-not-using-indexes = 1 # record SQL statements that do not use Indexes

 

View slow query logs

tail /usr/local/mysql/var/slow_queries.log # Time: 130305  9:48:13 # User@Host: biotherm[biotherm] @  [8.8.8.45] # Query_time: 1294.881407  Lock_time: 0.000179 Rows_sent: 4  Rows_examined: 1318033 SET timestamp=1363916893; SELECT * FROM xxx_list WHERE tid = '11xx'  AND del = 0  ORDER BY  id DESC  LIMIT 0, 4;  

Four Parameters
Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 54
The query time lock time indicates the number of rows scanned in the query result. It mainly depends on the statements with many rows scanned, then adds the corresponding index to the database, and then optimizes abnormal SQL statements.

 

In extreme cases, kill the SQL Process

Find the SQL statement that consumes too much cpu Time. Run the following command in mysql: show processlist; OK, the last SQL statement is in the Query state, and the Time is too long, lock its ID, and execute the following command: kill QUERY 269815764;

Note: killing the SQL process may lead to data loss. Therefore, the importance of data should be measured before execution.

 

Source: http://dngood.blog.51cto.com/446195/1150031