显示慢查询状态及日志目录

show variables like '%slow_query_log%';

+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e6bec7d7160-slow.log |
+---------------------+--------------------------------------+

开启慢查询

说明: 1开启;0关闭;

set global slow_query_log = 1;

显示慢查询阈值(单位秒)

默认执行时间超过10s才会被记录到日志

show variables like '%long_query%';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

设置慢查询阈值

注意:设置后需要重新打开mysql客户端才能到最新的值

set global long_query_time = 0.8;

查看死锁的日志是否开启

show variables like "%innodb_print_all_deadlocks%";

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+

开启记录死锁

set global innodb_print_all_deadlocks=1

配置优化MySQL

#编辑文件
vim /etc/my.cnf

#添加参数
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_connections = 2000
open_files_limit = 65535
table_open_cache = 200
join_buffer_size = 64M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
innodb_buffer_pool_size = 512M
key_buffer_size = 16M
query_cache_limit = 4M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
max_allowed_packet = 64M