识别MySQL性能缓慢的原因

1.登录mysql

mysql -uroot -proot123

2.启动事务

use employees;

start transaction;

select * from departments for update;

3.再开个会话，查询

use employees;

update departments set dept_name='Development1' where dept_no='d005';

4.增加innodb_lock_wait_timeout为1小时

show variables like 'innodb_lock_wait_timeout'\G

set global innodb_lock_wait_timeout=3600; #lock_wait_timeout,获取 metadata locks锁的超时时间

interactive_timeout

wait_timeout

update departments set dept_name='Development1' where dept_no='d005';

5.查进程

show processlist;

6.查会话信息

select * from sys.session;

7.查锁等待信息

select waiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query

from sys.innodb_lock_waits;

8.杀线程

mysqladmin -uroot -p kill 25 #show processlist.id 或sys.innodb_lock_waits.blocking_pid

9.更新成功

10.查看slap脚本(mysql自带性能压力测试工具)

cat /apps/sh/slap-test-updates.sh

mysqlslap --user=root --password=root123 -concurrency=5 \

--iterations=100 --number-char-cols=4 --number-int-cols=7 \

--auto-generate-sql --number-of-queries=10000 \

--auto-generate-sql-load-type=update

11.执行脚本

sh slap-test-updates.sh

12.显示processlist

show processlist;

13..查会话信息

14.查锁等待信息

15.用ctr+c kill 掉kill /apps/sh/slap-test-updates.sh