到底是哪条SQL拖慢了你的数据库?
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
核心参数
以下就是我们要设置的核心参数
ps: 管理语句包括:ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.
确认单点还是集群
第一步要做的就是确认单点还是集群
以下是集群查询结果,可以看到是3节点集群,单点的返回结果是空的
show global status like 'wsrep_cluster_size';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_cluster_size | 3 |
+-----------------------+-------+
1 rows in set (0.00 sec)
根据不同的集群有三种情况:
如果是主从模式集群,只需要在主节点上操作,同时打开
log_slow_slave_statements
开关就可以了。如果是多主,则每个主节点都要操作一次。
如果是单点,只需要操作一次。
修改配置文件开启慢查询(要重启)
$ vim /etc/my.cnf.d/server.cnf
在mysqld节加上以下配置后重启即可
[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mariadb/data/slow.log
long_query_time = 1
log_slow_slave_statements = ON
log_slow_admin_statements = ON
免重启方式开启慢查询(重启失效)
这种方式只需要登陆Mariadb输入以下命令即可
注:使用命令 set global 修改后,,需要重新连接或新开一个会话才能看到修改值。
set global log_slow_slave_statements=1;
set global log_slow_admin_statements=1;
set global slow_query_log=1;
set global slow_query_log_file='/data/mariadb/data/slow.log';
set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
查询是否修改成功
重启mariadb,然后输入命令查询,如果和以下结果一样,就是成功打开了
show variables like '%slow%';
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_admin_statements | ON |
| log_slow_slave_statements | ON |
| slow_query_log | ON |
| slow_query_log_file | /data/mariadb/data/slow.log |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
测试是否开启成功
执行查询语句,休眠4秒,超过我们定的1秒阀值
select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)
查看慢查询日志,可以看到我们刚刚执行的select sleep(4);
$ tail slow.log
/usr/local/mariadb/bin/mysqld, Version: 10.1.20-MariaDB (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mariadb.sock
Time Id Command Argument
# Time: 190528 13:25:49
# User@Host: root[root] @ localhost []
# Thread_id: 59011860 Schema: QC_hit: No
# Query_time: 4.000110 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0
SET timestamp=1559021149;
select sleep(4);
分析慢查询语句
自带的日志分析工具mysqldumpslow:
比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow.log
另外建议在使用这些命令时结合 |
和more
或者直接 > file.log
重定向到新文件中 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/slow.log | more
着重关注哪些sql?
查询次数多且每次查询占用时间长的sql
IO大的SQL
未命中索引的SQL
缺点:慢查询日志是在查询结束后才记录,故正在执行的慢SQL并不能被定位到,可以使用show processlist
命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等等,可以实时地查看SQL的执行情况。