mysql 性能剖析

来源：互联网发布：客服机器人 php 编辑：程序博客网时间：2024/05/17 21:57

1. 首先我们进行OS层面的检查

一般来说最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的。

1）查看系统整体负载可以使用 w 或者 sar -u 2 10 或者top命令查看

[root@localhost sql-bench]# w
19:07:10 up 10 days, 8:36, 5 users, load average: 0.00, 0.00, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/2 192.168.1.249 01:11 17:55m 0.00s 0.00s -bash
oracle :0 23Dec16 ?xdm? 4:13 0.20s /usr/bin/gnome-session
oracle pts/1 :0.0 23Dec16 9days 0.09s 0.09s bash
root pts/3 192.168.1.249 Thu21 0.00s 1.45s 0.00s w
root pts/5 192.168.1.249 Thu21 17:36m 48:12 0.01s -bash

· 或者使用 sar -q 1

[root@localhost sql-bench]# sar -q 1
Linux 2.6.18-412.el5 (localhost.localdomain)2017年01月01日

19时11分06秒 runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
19时11分07秒 0 239 0.00 0.00 0.00
Average: 0 239 0.00 0.00 0.00

[root@localhost sql-bench]# sar -u 2 10 频率2秒一次收集10次
Linux 2.6.18-412.el5 (localhost.localdomain)2017年01月01日

19时08分10秒 CPU %user %nice %system %iowait %steal %idle
19时08分12秒 all 0.00 0.00 1.02 0.00 0.00 98.98
19时08分14秒 all 0.00 0.00 0.00 0.00 0.00 100.00
19时08分16秒 all 0.00 0.00 0.52 0.00 0.00 99.48
19时08分18秒 all 0.00 0.00 0.00 0.00 0.00 100.00
19时08分20秒 all 0.00 0.00 0.51 0.00 0.00 99.49
19时08分22秒 all 0.00 0.00 0.52 0.00 0.00 99.48
19时08分24秒 all 0.00 0.00 0.00 0.00 0.00 100.00
19时08分26秒 all 0.00 0.00 0.51 0.00 0.00 99.49
19时08分28秒 all 0.00 0.00 0.00 0.00 0.00 100.00
19时08分30秒 all 0.00 0.00 1.02 0.00 0.00 98.98
Average: all 0.00 0.00 0.41 0.00 0.00 99.59

2）对整理负载有了一定的了解后，查看瓶颈处在哪个子系统

[root@localhost ~]# top
top - 19:15:33 up 10 days, 8:44, 5 users, load average: 2.76, 0.66, 0.21
Tasks: 146 total, 2 running, 143 sleeping, 0 stopped, 1 zombie
Cpu(s): 3.4%us, 2.7%sy, 0.0%ni, 0.0%id, 93.2%wa, 0.3%hi, 0.3%si, 0.0%st
Mem: 2058768k total, 2047764k used, 11004k free, 102848k buffers
Swap: 2064376k total, 362832k used, 1701544k free, 551036k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
375 mysql 15 0 864m 574m 7660 S 6.0 28.6 48:14.78 mysqld
25969 oracle 15 0 90100 988 568 S 0.3 0.0 0:02.86 scim-launcher
28477 root 17 0 106m 3320 1800 S 0.3 0.2 0:00.10 sysbench
1 root 15 0 10372 688 580 S 0.0 0.0 0:01.29 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.10 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:06.30 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
23 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
27 root 10 -5 0 0 0 S 0.0 0.0 0:01.51 kblockd/0
28 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
65 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue/0
68 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
70 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
很明显是mysqld进程导致负载过高，%us 和 %wa 的值较高，表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。

查看磁盘IO

[root@localhost ~]# sar -d 1
Linux 2.6.18-412.el5 (localhost.localdomain) 2017年01月01日
19时19分05秒 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
19时19分06秒 dev3-0 86.46 66.67 4541.67 53.30 32.07 559.35 11.81 102.08
19时19分06秒 dev3-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
19时19分06秒 dev3-2 86.46 66.67 4541.67 53.30 32.07 559.35 11.81 102.08
19时19分06秒 dev253-0 521.88 75.00 4150.00 8.10 111.58 428.49 1.96 102.08
19时19分06秒 dev253-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
19时19分06秒 dev22-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
Average: dev3-0 86.46 66.67 4541.67 53.30 32.07 559.35 11.81 102.08
Average: dev3-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev3-2 86.46 66.67 4541.67 53.30 32.07 559.35 11.81 102.08
Average: dev253-0 521.88 75.00 4150.00 8.10 111.58 428.49 1.96 102.08
Average: dev253-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev22-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

确认到底哪些进程消耗的磁盘I/O资源最多

可以看到mysql实例消耗IO最多，接下来看看都是哪些sql在运行。

2、mysql层面剖析

1）首先查看当前正在执行的sql

关注time列参数，分析用户进程占用mysql连接数时长

如果运行的SQL语句太多，运行时间太长，说明mysql效率有问题。必要时可以将相应的进程kill掉。
kill 287662;

2）mysql参数检测

(1)connect_timeout
指定mysql服务等待应答连接的最大时间，单位s，默认10秒，超过这个时间mysql向客户端返回bad handshake。内网高并发环境下，

建议设置10-15s，避免bad handshake。并且关注thread_cache_size，设置为非0，具体大小视环境定。

(2)skip-name-resolve
避免域名解析，可以大大加快用户获取连接的速度，特别是在网络较差的情况下。mysql收到用户发来的请求连接时，会根据请求包中

获得的ip来反向查询请求者的主机名，然后会更具返回的主机名有一次去查询ip，如果两次获得的ip相同，则成功建立连接。在DNS不

稳定或者局域网主机过多的情况下，建立一次成功的请求连接必然会消耗很多不必要的时间。但是加入mysql服务器的ip是广域网地址，

最好不要设置skip-name-resolve.

(3)slave-net-timeout=seconds
当slave从master读取binlog失败后，等待多长时间，重新与master建立连接，并获取数据。默认3600s，如果保证数据能够及时同步，该参数应设置在10s以下。
show variables like ‘%timeout%‘;
set global slave_net_timeout = 5;

(4)master-connect-retry=seconds

当主从连接建立失败，间隔多久重试。默认60s，合理设置该参数。

(5)连接数max_connections
如果连接数达到最大连接数，那不管剩余多少资源，用户的连接请求都会阻塞在外面。
max_connections，最大连接数，默认151，一般经验设置3000。win服务器连接数支持1500-1800，linux服务器可以支持8000个左右。
另外设置max_user_connections=0，表示不限制用户的最大连接数，其最大值可以等于max_connections
show variables like '%connections%';
show global status like 'max_used_connections';

检查曾经使用最大的连接数，这个值在max_connections的85%左右比较合适，过高，则会系统使用连接数过少，系统负荷过高。

3）事务和锁

事务等待状况

SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

查看未关闭的事务

SELECT a.trx_id,a.trx_state,a.trx_started,a.trx_query,b.ID,b.USER,b.HOST,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO FROM
information_schema.INNODB_TRX aLEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.idWHERE b.COMMAND = 'Sleep';

段时间内为关闭的事务

SELECT trx_id,trx_started,trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE trx_started < date_sub(now(), INTERVAL 1 MINUTE)
AND trx_operation_state IS NULL
AND trx_query IS NULL;

系统锁状况

show status like '%lock%';

4)慢查询

5)key_buffer_size索引缓存
key_buffer_size是对myISAM表性能影响最大的一个参数。
mysql> show global status like 'key_read%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| Key_read_requests | 13915643 |
| Key_reads | 129954 |
+-------------------+----------+
key_read_requests表示总共的索引请求数，key_reads表示请求在内存中没有找到，直接从硬盘读取索引，计算索引未命中缓存：
key_cache_miss_rate=key_reads/key_read_requests*100%
保持key_cache_miss_rate在0.1%以下都是很好的，如果在0.01%以下的话，key_buffer_size分配过的，适当减少。

6)created_tmp_tables 创建临时表
mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 540 |
| Created_tmp_files | 211 |
| Created_tmp_tables | 2302 |
+-------------------------+-------+
3 rows in set (0.00 sec）
每次创建临时表，created_tmp_tables增加（内存&磁盘创建临时表），created_tmp_disk_tables磁盘创建的临时表，比较理想的配置：
created_tmp_disk_tables/created_tmp_tables*100%<=25%
临时表配置参数：
show variables where variable_name in ('tmp_table_size','max_heap_table_size');
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size | 16777216 |
+---------------------+----------+

2 rows in set (0.00 sec)
只有16MB的临时表才能全部放内存，超过的就会用到硬盘临时表。

7)open_tables打开表数量

8)thread_cache_size 线程缓存

9）query cache 查询缓存

mysql> show global status like 'qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)

Qcache_free_blocks表示缓存中相邻内存块的个数，数目越大可能有碎片，flush query cache会对缓存中的碎片进行整理。
Qcache_free_memory 表示缓存中空闲内存
Qcache_hits命中查询缓存的次数
Qcache_inserts插入一个查询到缓存的次数，命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;
如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。
Qcache_total_blocks：缓存中块的数量。

mysql> show variables like 'query_cache%';

+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)

各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

0 0