mysql性能瓶颈排查

mysql性能瓶颈排查 top/free/vmstat/sar/mpstat

查看mysqld进程的cpu消耗占比

确认mysql进程的cpu消耗是%user, 还是sys%高

确认是否是物理内存不够用了

确认是否有swap产生

##使用下面工具查看

top (%cpu load %MMEM)

free -gt

vmstat -S m 1 (procs io cpu)

sar -u 1 (%user)

sar -d 1

如何优化

一：硬件优化

查看mysql线程状态 show [full] processlist

长时间的Sending data

从引擎层读取数据返回给server端

1.长时间存在的原因:

1 没有合适的索引 查询效率低下

2 读取大量数据 读取缓慢

3 系统负载高 读取缓慢

如何做：

1 加上合适的索引

2 改写sql

3 增加LIMIT限制每次读取量

4 检查&升级IO设备性能

2.长时间等待MDL锁 (waiting for table metadata lock)

原因:

DDL被阻塞 进而阻塞其他后续sql

DDL之前的sql长时间未结束，这个表未释放锁

举例：

a.开启一个事务。未提交，这个时候fege表的id=1这行有一个排它锁。

b.开启另外一个DDL事务，重新打开另外一个session连接

c.查看是什么事务进程id未释放，以及sql信息，打开另外一个mysql的session，

在事务没有完成之前，fege表上的锁不会释放，alter table同样获取不到metadata的独占锁。

d。找到未提交事物的sid，通过show processlist看不到Table上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx或者performance_schema.events_statements_current中查看到。

#执行 select * from performance_schema.events_statements_current\G; 可以看到当前未提交的sql信息

#通过上面查看到未提交的THREAD_ID查看对应的进程id

#通过查看：Select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;

这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。

#再次查看

如何做:

1 提高每条sql的效率

2 kill掉长时间运行的sql

3 把DDL放在夜间低谷时段

4 采用pt-osc执行DDL

长时间的sleep

占用连接数

消耗内存未释放

可能有行锁(甚至是表锁未释放)

如何做:

1 适当调低timeout

2 主动kill超时不活跃连接

3 定期检查锁、锁等待

4 可以利用pt-kill工具

其他状态

Copy to tmp table [on disk]

执行alter table修改表结构，需要生成临时表

建议放在夜间低谷进行， 或者用pt-osc

Creating tmp table

常见于group by没有索引的情况

需要拷贝数据到临时表[内存/磁盘上]

执行计划中会出现Using temporary关键字

建议创建合适的索引，消除临时表

Creating sort index

常见于order by没有索引的情况

需要进行filesort排序

执行计划中会出现Using filesort关键字

建议创建排序索引

其他排除方法

use information_schema; SELECT * from innodb_lock_waits;

show engine innodb status;

测试环境调低long_query_time的值 开启log_queries_not_using_indexes 分析慢日志