mysql优化 - Mysql服务器负载很高，性能问题排查思路是怎样的？

Question

有什么好的优化方案

Answer 1

If a worker wants to do his job well, he must first sharpen his tools. Let me explain my thinking

Ideas:
1. Determine the type of high load htop, use the dstat command to see if the load is high on CPU or IO
2. Monitor specific sql statements, whether insert update or delete causes high load Load
3. Check mysql log
4. Check for hardware problems

dstat

You can see which user and process occupy relevant system resources, and who is currently using the CPU and memory

[root@cc ~]# dstat -l -m -r -c  --top-io --top-mem --top-cpu

--io/total- ------memory-usage----- --most-expensive- ----most-expensive---- -most-expensive-
 read  writ| used  buff  cach  free|  memory process |     i/o process      |  cpu process   
1.90   267 |3399M  178M 3892M  400M|php-fpm: poo 372M|init       1682k  647k|flush-202:0  0.1
   0  72.0 |3399M  178M 3892M  400M|php-fpm: poo 372M|php-fpm: po  10k  143k|php-fpm: pool2.0
   0  8.00 |3399M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 228k  229k|php-fpm: pool0.5
   0  88.0 |3399M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 102k  166k|php-fpm: pool 11
   0  38.0 |3399M  178M 3892M  399M|php-fpm: poo 372M|php-fpm: po 787k  650B|php-fpm: pool4.8
   0     0 |3399M  178M 3892M  399M|php-fpm: poo 372M|php-fpm: po 788k  723B|php-fpm: pool1.8
   0   140 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work  38k  154k|php-fpm: pool1.2
   0  12.0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 178k  364k|php-fpm: pool1.5
   0     0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 758k  639k|php-fpm: pool1.5
   0  12.0 |3400M  178M 3892M  399M|php-fpm: poo 372M|nginx: work 773k  616k|php-fpm: pool2.0
6.00     0 |3401M  178M 3892M  398M|php-fpm: poo 372M|nginx: work 994k  688k|nginx: worker1.5
   0   272 |3401M  178M 3892M  398M|php-fpm: poo 372M|nginx: work 388k  422k|php-fpm: pool1.5
   0     0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 483k  548k|php-fpm: pool1.8
   0  4.00 |3400M  178M 3893M  398M|php-fpm: poo 372M|php-fpm: po 787k  650B|php-fpm: pool1.5
   0  12.0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 223k  323k|php-fpm: pool1.5
   0     0 |3400M  178M 3893M  398M|php-fpm: poo 372M|nginx: work 371k  474k|php-fpm: pool7.8

htop

htop is an enhanced version of top, more intuitive

[root@cc ~]# htop

  1  [|||||||||||                                                                    12.4%]
  2  [|||||||||                                                                       9.5%]
  3  [|                                                                               1.0%]
  4  [||                                                                              1.9%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||3394/7869MB]
  Swp[||||||||||||||                                                              75/478MB]
        
Tasks: 71, 12 thr; 2 running
Load average: 0.39 0.39 0.31 
Uptime: 526 days(!), 17:36:38

  PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command
    1 root       20   0 19232   396   248 S  0.0  0.0  0:01.86 /sbin/init
30752 root       20   0 52532    72    56 S  0.0  0.0  0:00.16 ├─ /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf
24301 root       20   0  193M  3268  1600 S  0.0  0.0  1:41.43 ├─ /usr/sbin/snmpd -LS0-6d -Lf /dev/null -p /var/run/snmpd.pid
21361 root       20   0  902M  6500  1308 S  0.0  0.1  0:07.16 ├─ php-fpm: master process (/etc/php-fpm.conf)
28627 www        20   0  962M  202M  138M S  0.0  2.6  0:34.46 │  ├─ php-fpm: pool www-c
27537 www        20   0  965M  236M  171M R  1.4  3.0  1:19.64 │  ├─ php-fpm: pool www-c
27449 www        20   0  961M  251M  189M S  0.0  3.2  1:35.54 │  ├─ php-fpm: pool www-a
26442 www        20   0  962M  280M  217M S  0.0  3.6  2:29.71 │  ├─ php-fpm: pool www-a
26310 www        20   0  917M  251M  234M S  1.9  3.2  2:46.45 │  ├─ php-fpm: pool www-a
26162 www        20   0  962M  297M  233M S  0.0  3.8  2:37.50 │  ├─ php-fpm: pool www-b
26147 www        20   0  924M  258M  233M S  0.0  3.3  2:38.37 │  ├─ php-fpm: pool www-c
25717 www        20   0  965M  302M  238M S  0.0  3.8  2:54.50 │  ├─ php-fpm: pool www-c
24585 www        20   0  964M  324M  260M S  0.0  4.1  4:15.20 │  ├─ php-fpm: pool www-b

tcpdump

Capture mysql package analysis, generally capture the data of port 3306

[root@cc ~]# tcpdump -i eth0 -A -s 3000 port 3306 > ~/sql.log

Then use awk, sort, wc and other commands to analyze

or

[root@cc ~]# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$qn"; }
    $q=$_;
    } else {
    $_ =~ s/^[ t]+//; $q.=" $_";
    }
}'

You can see the busiest sql statements

strace

Check whether there is a problem with the system call, whether the process is blocked, and whether there is a Broken pipe

[root@cc ~]# strace -p 26578

pt-query-digest

Analyze mysql slow logs to see which sql statements take the most time

[root@cc ~]#  pt-query-digest slow.logs

# 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz  
# CURRENT DATE: Thu DEC 29 13:22:42 2014  
# Hostname: test   
# Files: slow.log  
# Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________  
# TIME range: 2011-09-10 04:03:19 TO 2011-12-29 05:02:51  
# Attribute          total     MIN     MAX     avg     95%  stddev  median  
# ============     ======= ======= ======= ======= ======= ======= =======  
# EXEC TIME          5657s      2s     33s      7s     23s      6s      5s  
# LOCK TIME            33s       0     19s    43ms    98us   715ms    38us  
# ROWS sent        323.38k       0 107.36k  426.73    0.99   6.35k       0  
# ROWS examine     323.39k       0 107.36k  426.74       0   6.35k       0  
# Query SIZE       217.95k      38     562  287.61  420.77   81.78  284.79  

show processlist

Check what the system is doing

mysql> show full processlist;
+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
| Id        | User          | Host                | db                  | Command | Time | State         | Info                      |
+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
| 184498848 | testdb_rr1356 | 10.11.211.120:61343 | testdb_rr1356_db121 | Sleep   | 1384 |               | NULL                      |
| 184508740 | testdb_rr1356 | 10.11.211.120:11809 | testdb_rr1356_db121 | Sleep   |   87 |               | NULL                      |
| 184509415 | testdb_rr1356 | 10.11.211.120:12760 | testdb_rr1356_db121 | Query   |    0 | NULL          | show full processlist     |
| 184509451 | testdb_rr1356 | 10.11.211.120:12804 | testdb_rr1356_db121 | Sleep   |   10 |               | NULL                      |
| 184509528 | testdb_rr1356 | 10.11.211.120:12919 | testdb_rr1356_db121 | Query   |    0 | freeing items | DESCRIBE test_channel     |

Check whether there is any problem with the mysql configuration parameters, causing a large amount of IO or high CPU operations

Innodb_flush_log_at_trx_commit, innodb_buffer_pool_size, key_buffer_size and other important parameters

mysql> show variables like '%innodb%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| have_innodb                     | YES                        |
| ignore_builtin_innodb           | ON                         |
| innodb_adaptive_flushing        | ON                         |
| innodb_adaptive_hash_index      | ON                         |
| innodb_additional_mem_pool_size | 2097152                    |
| innodb_autoextend_increment     | 8                          |
| innodb_autoinc_lock_mode        | 1                          |
| innodb_buffer_pool_size         | 2013265920                 |
| innodb_change_buffering         | inserts                    |
| innodb_checksums                | ON                         |

View current transactions and memory usage through show engine innodb status

mysql> show engine innodb status  \G

LATEST DETECTED DEADLOCK
------------------------
150731 10:36:50
*** (1) TRANSACTION:
TRANSACTION EBFBBEC, ACTIVE 0 sec, process no 20691, OS thread id 47345217033984 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2
MySQL thread id 143249904, query id 1286731854 10.135.21.120 tybuser2014 update

#此处具体sql省略

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2058485760; in additional pool allocated 0
Dictionary memory allocated 819282
Buffer pool size   122879
Free buffers       97599
Database pages     24313
Old database pages 8954
Modified db pages  7
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1049, created 41853, written 30401604
0.00 reads/s, 0.00 creates/s, 1.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 24313, unzip_LRU len: 0
I/O sum[45]:cur[0], unzip sum[0]:cur[0]

Finally, use zabbix or cacti monitoring to check whether there are any abnormalities in IO, CPU, MEMORY, disk, etc.

This way you can basically find out the problem

For reference only, of course, different DBAs must have other better ways

Answer 2

Io? Cpu? Open the slow query log

Answer 3

Based on your question method, I can also derive a bunch of questions. You can experience it yourself

1. Title: My website access is very slow, how can I optimize it?
   Question: Any good ideas

2. Title: I want to double the performance of my website, how to achieve it?
   Question: Does anyone know?

3. Title: My server configuration failed, what happened?
   Question: Please help me

Excuse me, can you answer these questions? Can a one-sentence question without any details attract good answers? Then others have to guess your intentions, which wastes a lot of time.