MySQL参数调整
2019-07-31 本文已影响0人
bluexiii
开启Profiling
show variables like '%profiling%';
set profiling=1;
执行SQL
set profiling=0;
show profiles;
show profile cpu,block io for query 2;
执行计划
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
查表锁
show status like 'table%';
Table_locks_immediate 产生表级锁定的次数
Table_locks_waited 表级锁定争用发生的等待次数
查行锁
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
show variables like '%query_cache%';
show status like 'Qcache%';
网络链接
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
