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

Today, I found that the visit to the website was slow. I conducted a survey and began to get confused. Let's sort it out below.

1. Fault analysis

First of all, judge whether the slow interview is an individual or a collective.

Personal phenomenon investigation: check personal network, pc, browser, poisoning, etc., no need to say more about Baidu

Collective troubleshooting: check core routing switching, ISP operator network, ARP***,DNS services, server status

Service status troubleshooting: zabbix monitoring; creating test page Test: static page = "dynamic page =" dynamic interactive page

Through the above investigation, when the dynamic interaction page between the test php and mysql is very slow, so it is determined that the mysql server is abnormal, log in to mysql immediately, check the server load through the top command, and find that 200% of the cpu in mysql causes such a high Mysql load. It is estimated that there is a problem with SQL slow query sentence or disk IHAGO.

Second, the idea of high MySQL load troubleshooting.

1. To determine the type of high load, the top command depends on whether the high load is CPU or disk Icano.

2. Check the current number of connections and executed sql statements under mysql.

3. Check the slow query log, it may be that the slow query causes a high load.

4. Check for hardware problems to see if they are caused by disk failures.

5. Check the monitoring platform and compare the load of this machine at different times.

1) determine the type of load (top)

Top-10:14:18 up 23 days, 11:01, 1 user, load average: 124.17, 55.88,24.70 Tasks: 138total, 1 running, 137sleeping, 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 1506250m 2.5g 4076S 257.1 49.952934.45 mysqld

2) check the number of connections to the current MySQL and the sql statements executed

# No need to login, non-interactive query, can be filtered by grep, and view the result mysql-uroot-pqiuyuetao-S / data/3306/mysql.sock-e "show full processlist;" more directly | egrep-vi "sleep" # Log in to myslq to view show full processlist; Id User Host db Command Time State Info 192 slave 8.8.142 NULL Binlog Dump 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.46 biotherm Query 57861 Sending data SELECT * FROM xxx_list WHERE tid = '1112' AND del = 0 ORDER BY id DESC LIMIT 0, 4 424917 biotherm 8.8.49 AND del 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.42 Sending data SELECT 35982 biotherm Query * 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 # slow query log path long_query_time = 2 # record SQL query statement over 2s log-queries-not-using-indexes = 1 # record sql without index

View slow query log

Tail / data/3306/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

Log parameter description:

Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 5 query time lock time query result number of rows scan number of rows # # mainly depends on the statement with a large number of rows scanned, then go to the database to add the corresponding index, and then optimize the abnormal sql statement.

Other methods:

In PHP programs, do not use persistent connections, even if you use mysql_connetct instead of pconnect (JAVA adjusts connection pooling)

When the PHP program is finished, it should be displayed to call mysql_colse # # many developers only have open without colse

SQL slow query statement optimized address: http://qiuyt.blog.51cto.com/1229789/1924958

4) extreme case kill sql process

Find out the sql that takes up too much cpu time, and execute the following command under mysql: show full processlist; determines that the next sql is in the Query state and the Time time is too long, lock its ID, and execute the following command: kill QUERY 269815764

Note: killing the sql process can result in data loss, so measure the importance of the data before execution.

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.