BlueXIII's Blog

热爱技术,持续学习

0%

MySQL参数调整

Posted on

开启Profiling

1
2
3
4
5
6
7
show variables like '%profiling%';
set profiling=1;
执行SQL
set profiling=0;

show profiles;
show profile cpu,block io for query 2;

执行计划

1
2
3
4
5
6
7
explain 
select a.id,b.real_name from order_info a
left join person_info b on b.id=a.person_info_id

explain 
select a.id,b.id from auth_info a
left join order_info b on b.id=a.order_id

查表锁

1
2
3
show status like 'table%';
Table_locks_immediate 产生表级锁定的次数
Table_locks_waited 表级锁定争用发生的等待次数

查行锁

1
2
3
4
5
6
7
8
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits 正在等待锁定的数量
Innodb_row_lock_time 锁定总时长
Innodb_row_lock_time_avg 平均等待时间
Innodb_row_lock_time_max 最长等待时间
Innodb_row_lock_waits 总共等待次数

show innodb status;

QueryCache

1
2
show variables like '%query_cache%';
show status like 'Qcache%';

网络链接

1
2
3
4
5
6
7
8
9
10
11
12
13
show variables like '%open_files_limit%';
show variables like '%max_connections%';
set GLOBAL max_connections=500;

show variables like '%thread_cache_size%';
set GLOBAL thread_cache_size=50;


show status like 'connections';
show status like '%thread%';
show processlist; 
mysqladmin -uroot -p processlist
mysqladmin -uroot -p status

my.cnf顺序

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/local/mysql/etc/my.cnf
  • ~/.my.cnf

参考文档