MySQL Database Performance Tuning

Locks

select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

select concat('KILL ',id,';') from information_schema.processlist where user='DBuser' and DB='app_dc';
select * from information_schema.processlist where user='DBuser' and DB='app_dc';

Recreate Tables

select table_rows,table_name,table_schema from information_schema.TABLES where table_name='XXX_COMMAND';

use app_dc;
create table XXX_COMMAND_new like XXX_COMMAND;
rename table XXX_COMMAND to XXX_COMMAND_bak;
rename table XXX_COMMAND_new to XXX_COMMAND;
select count(1) from XXX_COMMAND;

Connections

# 最大连接数，动态，默认151，最小1，最大100000
show variables like 'max_connections';

# 当前打开的连接数
show status like 'Threads_connected';

# 历史最大使用连接数
show status like 'max_used_connections';

max_used_connections /max_connections * 100% （理想值≈ 85%）。总体来说，该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则将会出现类似 "Too many connections" 的错误。

调整最大连接数。

set global max_connections=1000;

永久生效可以修改配置文件 my.cnf。

vim my.cnf

[mysqld]
max_connections = 1000

Slow Query Log

开启慢查询日志：

show variables like '%slow_query_log%';

set global long_query_time=10;
set global slow_query_log_file=/var/lib/mysql/38a497b84982-slow.log
set global log_output=FILE;
set global log_queries_not_using_indexes=1;
set global slow_query_log=1;

慢查询日志分析：

# -s r: rows sent
mysqldumpslow -s r -t 10 /var/lib/mysql/38a497b84982-slow.log

# -s c: count
mysqldumpslow -s c -t 10 /var/lib/mysql/38a497b84982-slow.log

# -s t: query time
mysqldumpslow -s t -t 10 /var/lib/mysql/38a497b84982-slow.log