識别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