Website Access slow-mysql load high (actual combat)

Today found that the site is slow to visit, once a troubleshooting, start thinking confusion, below to comb under

First, Fault Analysis

First of all, to judge the slow phenomenon of access, is individual or collective ???

Personal phenomena : Check personal network, PC, browser, poisoning, etc., do not need to say more about their own Baidu;

Collective behavior : Check the core routing switch, ISP network, ARP attack, DNS service, each server state;

Service Status Troubleshooting : Zabbix monitoring; creating test page Test: Static page = "Dynamic page =" Dynamic Interactive page

   through the above troubleshooting, when testing PHP with Mysql Dynamic interaction page is slow , So OK for MySQL server exception, log in MySQL immediately via top command View server load, find MySQL unexpectedly 200 cpu, cause MySQL The reason for this high load is estimated to have sql slow query statement or disk I/O problem;

Two, MySQL load high troubleshooting ideas
1. Determine , top command to see if the load is high on CPU or disk I/O."
2. mysql to view the current Number of connections and the sql statement .
3. Check the slow query log , which may be a slow query causing a high load.
4. Check for hardware issues, whether disk failure issue.
5. Check the monitoring platform to compare the load of this machine at different times.

1) Determine payload 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

2) View the current number of MySQL connections and the SQL statement executed

  #无需登陆, non-interactive query, can be grep filtering, more intuitive to see results   mysql -uroot -pqiuyuetao -s  /data/3306/ mysql.sock -e  "show full processlist;"  |egrep -vi  "Sleep"   #登陆myslq查看  show full 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  &nbsP; 8.8.8.42:35982  biotherm    query   487 sending  data    SELECT * FROM xxx_list WHERE tid =  ' 1112 '   and del = 0

3) record SQL slow query
Edit the MySQL configuration file (my.cnf) and add the following lines in the [Mysqld] field:

Log_slow_queries =/data/3306/slow_queries.log #慢查询日志路径 long_query_time = 2 #记录SQL查询超过2s的语句 log-queries-not-using-indexes = 1 #记录没有使用索引的sql

View Slow query log

Tail/data/3306/slow_queries.log # time:130305 9:48:13 # [email protected]: 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 the TID = ' 11xx ' and del = 0 ORDER by ID DESC LIMIT 0, 4;

Log parameter Description:

query_time:0 lock_time:0 rows_sent:1 rows_examined:54# Query time lock time query result row number Scan rows # #主要看扫描行数多的语句, then go to the database and add the corresponding indexes, and then optimizes the abnormal SQL statements.

Other methods:

PHP program, do not use persistent connection, that is, using MYSQL_CONNETCT instead of pconnect (Java Tuning Connection pool)

After the PHP program executes, the call Mysql_colse # should be displayed #很多开发只有open no Colse

SQL Slow query statement optimization address : http://qiuyt.blog.51cto.com/1229789/1924958

4) Extreme case Kill SQL process

Find SQL that takes too long CPU time and execute the following commands under MySQL: Show full processlist; Determine that the latter SQL is in the QUERY state and that the time is too long to lock its ID and execute the following command: Kill QUERY 269815764;

Note: Killing the SQL process can result in data loss, so it is important to measure the data before execution.

Because the situation is urgent no data from "Dngood": http://dngood.blog.51cto.com/446195/1150031

This article is from the "funny Brother Notes" blog, be sure to keep this source http://qiuyt.blog.51cto.com/1229789/1925182

Website Access slow-mysql load high (actual combat)