北京市规划网站,做任务挣钱的网站app,上海外贸公司27号,网站开发所需费用明细redis和mysql都存在对于慢查询的日志记录#xff0c;下面将叙述一下两者的慢查询。
一#xff0c;redis[1]
redis的慢查询日志本质上是一个list对象#xff0c;不过redis并没有提供慢查询日志的key。开发者可以通过下列命令查询慢查询日志#xff1a;
#获得慢查询日志下面将叙述一下两者的慢查询。
一redis[1]
redis的慢查询日志本质上是一个list对象不过redis并没有提供慢查询日志的key。开发者可以通过下列命令查询慢查询日志
#获得慢查询日志n可以指定条数
slowlog get [n]
#慢查询日志列表当前的长度
slowlog len
#慢查询日志重置
slowlog resetredis提供了slowlog-log-slower-tan和slowlog-max-len配置slowlog-log-slower-than是慢查询日志的阈值单位是微秒默认值是10000slowlog-max-len是慢查询日志最多存储多少条。
我们可以修改配置文件来修改上述配置也可以通过在客户端动态修改如
#修改配置重启后失效
config set slowlog-max-len 1000
#将配置持久化到配置文件
config rewriteredis的慢查询日志本质上是一个队列或者说列表有固定的长度限制如果超出长度会丢失一部分慢查询日志。如果想要将慢查询日志持久化可以通过定时任务将慢查询日志的结果持久化到其他存储中[1]。
二mysql
进入mysql客户端
查询是否开启慢查询日志
#slow_query_log是否开启了慢查询日志当前状态为OFF
mysql show variables like slow_query_log- ;
-----------------------
| Variable_name | Value |
-----------------------
| slow_query_log | OFF |
-----------------------
1 row in set (0.01 sec)#开启慢查询日志
mysql set global slow_query_logon;
Query OK, 0 rows affected (0.00 sec)mysql show variables like slow_query_log;
-----------------------
| Variable_name | Value |
-----------------------
| slow_query_log | ON |
-----------------------
1 row in set (0.00 sec)慢查询日志阈值单位为秒默认10秒
mysql show variables like long_query_time;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
1 row in set (0.00 sec)
慢查询日志路径
mysql show variables like slow_query_log_file;
--------------------------------------------------------
| Variable_name | Value |
--------------------------------------------------------
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
--------------------------------------------------------
1 row in set (0.00 sec)
是否在慢查询日志中记录没有使用索引的SQL
mysql show variables like log_queries_not_using_indexes;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_queries_not_using_indexes | OFF |
--------------------------------------
1 row in set (0.00 sec)
这里为了演示我们把慢查询日志的阈值调为0s这样任何sql都会被判断为慢查询并记录在日志中。
mysql set global long_query_time0;
Query OK, 0 rows affected (0.00 sec)
我们开启另外一个shell去查看慢查询日志可以看到此时慢查询日志多了一些日志
#没有开启慢查询日志时日志文件中没有记录
[rootlocalhost usr]# cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.7.44 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
[rootlocalhost usr]# vi /var/lib/mysql/localhost-slow.log ;
#开启慢查询日志后日志中有了记录
[rootlocalhost usr]# cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.7.44 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2023-12-15T12:08:27.357678Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000328 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp1702642107;
select version_comment limit 1;
# Time: 2023-12-15T12:09:02.503218Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.003156 Lock_time: 0.000522 Rows_sent: 1 Rows_examined: 1052
SET timestamp1702642142;
show variables like slow_query_log_file;
# Time: 2023-12-15T12:10:19.797352Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000141 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642219;
set global slow_query_log on;
# Time: 2023-12-15T12:10:33.596783Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.001013 Lock_time: 0.000140 Rows_sent: 1 Rows_examined: 1052
SET timestamp1702642233;
show variables like slow_query_log;
# Time: 2023-12-15T12:10:59.163838Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000086 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642259;
set global long_query_time 0;
# Time: 2023-12-15T12:11:08.379448Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000115 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642268;
select * from emp;
# Time: 2023-12-15T12:11:23.468288Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000652 Lock_time: 0.000120 Rows_sent: 5 Rows_examined: 5
SET timestamp1702642283;
show databases;
# Time: 2023-12-15T12:11:27.571064Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000131 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp1702642287;
SELECT DATABASE();
# Time: 2023-12-15T12:11:27.571279Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000039 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use locktest;
SET timestamp1702642287;
# administrator command: Init DB;
# Time: 2023-12-15T12:11:27.573106Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000214 Lock_time: 0.000075 Rows_sent: 5 Rows_examined: 5
SET timestamp1702642287;
show databases;
# Time: 2023-12-15T12:11:27.573360Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000081 Lock_time: 0.000029 Rows_sent: 4 Rows_examined: 4
SET timestamp1702642287;
show tables;
# Time: 2023-12-15T12:11:27.573615Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000143 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642287;
;
# Time: 2023-12-15T12:11:27.573756Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000068 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642287;
;
# Time: 2023-12-15T12:11:27.573919Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000044 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642287;
;
# Time: 2023-12-15T12:11:27.573998Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000035 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp1702642287;
;
# Time: 2023-12-15T12:11:32.483758Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000207 Lock_time: 0.000108 Rows_sent: 4 Rows_examined: 4
SET timestamp1702642292;
show tables;
# Time: 2023-12-15T12:11:37.555298Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 0.000271 Lock_time: 0.000078 Rows_sent: 12 Rows_examined: 12
SET timestamp1702642297;
select * from emp;
其中Query_time为查询时间Lock_time为等待锁的阻塞时间加起来是在Mysql中查询使用的时间。
参考文章 [1]redis开发与运维ISBN 978-7-111-55797-5
