MariaDB慢查询日志

最新推荐文章于 2024-10-10 15:24:53 发布

weixin_34050427

最新推荐文章于 2024-10-10 15:24:53 发布

阅读量701

点赞数

CC 4.0 BY-SA版权

文章标签：数据库网络 python

原文链接：https://my.oschina.net/u/3954059/blog/3012649

本文介绍如何配置MariaDB的慢查询日志，通过开启慢日志并设置超时时间，帮助分析数据库性能瓶颈。文章详细展示了配置过程，包括编辑配置文件、重启服务以及如何查看慢查询日志。

2019独角兽企业重金招聘Python工程师标准>>>

MariaDB慢查询日志

为什么要配置慢查询日志？

目的是为了帮助我们分析MariaDB的瓶颈点。

如何配置？

进入MariaDB，执行以下语句：

# mysql -uroot -pmysql
> show variables like 'slow%';

+---------------------+-------------------+
| Variable_name       | Value             |
+---------------------+-------------------+
| slow_launch_time    | 2                 |
| slow_query_log      | OFF               |
| slow_query_log_file | wangzb01-slow.log |
+---------------------+-------------------+

如果需要开启slow日志，name需要修改slow_query_log 为on

> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+

> show variables like 'long%';    //配置超时时间
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

打开配置文件/etc/my.cnf。编辑增加

slow_query_log = on
slow_query_log_file = /data/mysql/wangzb-slow.log
long_query_time = 2

# systemctl restart mysqld  //重启mysqld服务

模拟慢查询

# mysql -uroot -pmysql
> select sleep(5);   //执行语句

+----------+
| sleep(5) |
+----------+
|        0 |
+----------+

查看慢查询日志：

# cat /data/mysql/wangzb-slow.log  //查看查看慢查询日志内容如下

/usr/local/mysql/bin/mysqld, Version: 10.3.11-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: /tmp/mysql.sock
Time		    Id Command	Argument
/usr/local/mysql/bin/mysqld, Version: 10.3.11-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: /tmp/mysql.sock
Time		    Id Command	Argument
Time: 190220 20:42:14
User[@Host](https://my.oschina.net/u/116016): root[root] @ localhost []
Thread_id: 10  Schema:   QC_hit: No

扩展：

# mysql -uroot -pmysql
> show processlist; //查看所有的队列，类似系统查看所有进程

+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 11 | root        | localhost | NULL | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+

> show full processlist;  //查看所有队列时把所有内容展示出来，完整的语句展示出来。

+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info                  | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL                  |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL                  |    0.000 |
| 11 | root        | localhost | NULL | Query   |    0 | Init                     | show full processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+-----------------------+----------+

转载于:https://my.oschina.net/u/3954059/blog/3012649