我们使用的是mariadb 10.1.20 版本、mysql也适用
开启慢查询日志,可以让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并不能被定位到,可以使用show processlist
命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等等,可以实时地查看SQL的执行情况。