MySQL-负载很高排查思路

MySQL-负载很高排查思路

工欲善其事必先利其器,我说一下思路

思路:
1、确定高负载的类型 htop,dstat命令看负载高是CPU还是IO
2、监控具体的sql语句,是insert update 还是 delete导致高负载
3、检查mysql日志
4、检查硬件问题

dstat

可以看到具体是哪个用户哪个进程占用了相关系统资源,当前CPU、内存谁在使用

[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是top的增强版,更直观

[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

抓取mysql包分析,一般抓3306端口的数据

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

然后使用awk,sort,wc 等命令进行分析

或者

[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.=" $_";
    }
}'

就可以看出最繁忙的sql语句了

strace

查看系统调用是否有问题,进程是否堵塞,是否有Broken pipe

[root@cc ~]# strace -p 26578

pt-query-digest

分析mysql慢日志,查看哪些sql语句最耗时

[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

查看系统到底在干什么


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     |

检查mysql配置参数是否有问题,引起大量的IO或者高CPU操作

innodb_flush_log_at_trx_commit 、innodb_buffer_pool_size 、key_buffer_size 等重要参数

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                         |

通过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]

最后通过zabbix或者cacti等监控来查看IO、CPU、MEMORY、磁盘等是否有异常

这样基本上就可以把问题找出来了

仅供参考,当然,不同的DBA肯定还有其他更好的方式

分类: MySQL

mysql百万数据分页优化

背景:

自己的一个网站,由于单表的数据记录高达了一百万条,造成数据访问很慢,Google分析的后台经常报告超时,尤其是页码大的页面更是慢的不行。

测试环境:

先让我们熟悉下基本的sql语句,来查看下我们将要测试表的基本信息

use infomation_schema
SELECT * FROM TABLES WHERE TABLE_SCHEMA = ‘dbname’ AND TABLE_NAME = ‘product’

查询结果:

从上图中我们可以看到表的基本信息:

表行数:866633
平均每行的数据长度:5133字节
单表大小:4448700632字节

关于行和表大小的单位都是字节,我们经过计算可以知道
平均行长度:大约5k
单表总大小:4.1g
表中字段各种类型都有varchar、datetime、text等,id字段为主键

测试实验

1.   直接用limit start, count分页语句, 也是我程序中用的方法:

select * from product limit start, count
当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下(也就是记录的一般左右)                                    select * from product limit 400000, 20   3.229秒

再看我们取最后一页记录的时间
select * from product limit 866613, 20   37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时,像这种分页最大的页码页显然这种时
间是无法忍受的。

从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

2.   对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:
select id from product limit 866613, 20 0.2秒
相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈

另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
查询时间也很短,赞!

其实两者用的都是一个原理嘛,所以效果也差不多