当long_query_time=1
时(表info的id为主键),呈现下面的慢日志,可能会让你吃惊
# Time: 2024-01-28T22:52:24.500491 08:00
# User@Host: root[root] @ [127.0.0.1] Id: 8
# Query_time: 7.760787 Lock_time: 7.757456 Rows_sent: 0 Rows_examined: 0
use apple;
SET timestamp=1706453536;
delete from info where id < 10;
环境信息
装备 | 参数 |
---|---|
ip | 172.17.137.12 |
hostname | dev |
memory | 16G |
cpu | 8C |
MySQL version | GreatSQL 8.0.26 |
慢查询相关参数
greatsql> select * from performance_schema.global_variables where variable_name in('slow_query_log','log_output','slow_query_log_file','long_query_time','log_queries_not_using_indexes','log_slow_admin_statements','min_examined_row_limit','log_throttle_queries_not_using_indexes') order by variable_name;
---------------------------------------- -------------------------------------
| VARIABLE_NAME | VARIABLE_VALUE |
---------------------------------------- -------------------------------------
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slow_admin_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 1.000000 |
| min_examined_row_limit | 0 |
| slow_query_log | ON |
| slow_query_log_file | /root/local/8026/log/slow.log |
---------------------------------------- -------------------------------------
8 rows in set (10.49 sec)
-
slow_query_log
:慢日志开关 -
log_output
:慢日志存储方法,FILE或TABLE -
long_query_time
:慢日志阈值 -
min_examined_row_limit
:设置慢SQL的最小examined扫描行数,主张设置为0,因为有bug:bugs.mysql.com/bug.php?id=… -
log_queries_not_using_indexes
:不运用索引的慢查询日志是否记录到索引 -
log_slow_admin_statements
:在写入慢速查询日志的句子中包括慢速管理句子(create index,drop index,alter table,analyze table,check table,optimize table,repair table)
默许是不会记录的 -
log_throttle_queries_not_using_indexes
:用于约束每分钟输出未运用索引的日志数量。每分钟答应记录到slow log的且未运用索引的sql句子次数,合作long_queries_not_using_indexes敞开运用。 -
log_slow_slave_statements
:默许OFF,是否敞开主从复制中从库的慢查询
根本原因
一、慢日志写入大致流程
-
dispatch_command(thd)
-
thd->enable_slow_log = true // 初始化enable_slow_log为true
-
thd->set_time // 设置开端时刻
-
dispatch_sql_command
-
parse_sql // 语法解析
-
mysql_execute_command // 履行SQL
- lex->m_sql_cmd->execute // 常见的CRUD操作
-
thd->update_slow_query_status // 判别是否到达慢日志阈值。若为慢查询,则更新thd的server_status状况,为写slow_log作准备
-
log_slow_statement
-
log_slow_applicable // 判别是否写入慢日志
-
log_slow_do // 开端写入
-
slow_log_write
-
log_slow
-
write_slow
-
-
-
-
二、判别是否到达慢日志阈值
- 8.0.26版别的慢日志判别规范
void THD::update_slow_query_status() {
if (my_micro_time() > utime_after_lock variables.long_query_time)
server_status | = SERVER_QUERY_WAS_SLOW;
}
// my_micro_time() 获取当时体系时刻点,单位为奇妙
// utime_after_lock 为MDL LOCK和row lock等候时刻后的时刻点,单位为奇妙
// variables.long_query_time 慢日志阈值long_query_time * 1000000 ,单位为奇妙
// 等价于:my_micro_time() - utime_after_lock > variables.long_query_time
// 不包括锁等候时刻
- 8.0.32版别的慢日志判别规范(8.0.28开端)
void THD::update_slow_query_status() {
if (my_micro_time() > start_utime variables.long_query_time)
server_status | = SERVER_QUERY_WAS_SLOW;
}
// 判别规范变成了:(句子履行结束的时刻 - 句子开端履行时刻) > 慢日志阈值
// my_micro_time() 当时体系时刻点,单位为奇妙
// start_utime:句子开端履行时刻点,单位为奇妙
// variables.long_query_time 慢日志阈值long_query_time * 1000000 ,单位为奇妙
// 包括锁等候时刻
从上面能够看出慢日志的判别规范发生了根本变化
举例说明
greatsql> select * from info;
---- ------
| id | name |
---- ------
| 1 | 1 |
| 2 | 2 |
| 5 | 5 |
| 60 | 8 |
| 40 | 11 |
| 20 | 20 |
| 30 | 30 |
---- ------
7 rows in set (0.05 sec)
greatsql> show create table infoG
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`id` int NOT NULL AUTO_INCREMENT,
`name` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
session1 | session2 |
---|---|
begin; | |
delete from info where id < 10; | |
delete from info where id < 10; |
|
session1等候一段时刻超过慢日志阈值long_query_time | |
rollback; |
• 在8.0.26版别中,是不会记录session2中的delete from info where id < 10
• 在8.0.32版别中,会记录session2中的delete from info where id < 10
三、判别是否写入慢日志
void log_slow_statement(THD *thd,
struct System_status_var *query_start_status) {
if (log_slow_applicable(thd)) log_slow_do(thd, query_start_status);
}
//----------------------------------------------------------------
bool log_slow_applicable(THD *thd) {
DBUG_TRACE;
/*
The following should never be true with our current code base,
but better to keep this here so we don't accidently try to log a
statement in a trigger or stored function
*/
if (unlikely(thd->in_sub_stmt)) return false; // Don't set time for sub stmt
if (unlikely(thd->killed == THD::KILL_CONNECTION)) return false;
/*
Do not log administrative statements unless the appropriate option is
set.
*/
if (thd->enable_slow_log && opt_slow_log) {
bool warn_no_index =
((thd->server_status &
(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED)) &&
opt_log_queries_not_using_indexes &&
!(sql_command_flags[thd->lex->sql_command] & CF_STATUS_COMMAND));
bool log_this_query =
((thd->server_status & SERVER_QUERY_WAS_SLOW) || warn_no_index) &&
(thd->get_examined_row_count() >=
thd->variables.min_examined_row_limit);
bool suppress_logging = log_throttle_qni.log(thd, warn_no_index);
if (!suppress_logging && log_this_query) return true;
}
return false;
}
-
若log_slow_applicable(thd)回来值为true,则履行log_slow_do(thd, query_start_status),写入慢日志
-
if (unlikely(thd->in_sub_stmt)) return false;if (unlikely(thd->killed == THD::KILL_CONNECTION)) return false;
a. 子查询,回来false
b. 被kill,回来false
c. 解析犯错,回来false
d. 履行犯错,回来false
-
warn_no_index 标明log_queries_not_using_indexes敞开且(未运用索引或未运用最优索引)
-
thd->server_status 该线程状况
-
SERVER_QUERY_NO_INDEX_USED 标明未运用索引
-
SERVER_QUERY_NO_GOOD_INDEX_USED 标明未运用最优索引
-
opt_log_queries_not_using_indexes 标明log_queries_not_using_indexes参数的值,默许OFF
-
-
!(sql_command_flags[thd->lex->sql_command] & CF_STATUS_COMMAND))标明该句子不是SHOW相关的指令。CF_STATUS_COMMAND常量标明履行的指令为show相关的指令。
-
log_this_query = ((thd->server_status & SERVER_QUERY_WAS_SLOW) || warn_no_index) && (thd->get_examined_row_count() >=thd->variables.min_examined_row_limit);
-
(thd->server_status & SERVER_QUERY_WAS_SLOW) 标明该SQL为慢查询
-
(thd->get_examined_row_count() >=thd->variables.min_examined_row_limit) 标明SQL的扫描数据行数不小于参数min_examined_row_limit 的值,默许为0
-
-
log_throttle_qni.log(thd, warn_no_index) 标明用来计算该条未运用索引的SQL是否需要写入到slow log,计算需要运用到参数log_throttle_queries_not_using_indexes , 该参数标明答应每分钟写入到slow log中的未运用索引的SQL的数量,默许值为0,标明不约束
依照线上装备
-
log_throttle_queries_not_using_indexes = 0
-
log_queries_not_using_indexes = OFF
-
log_slow_admin_statements = OFF
-
min_examined_row_limit = 0
-
slow_query_log = ON
-
long_query_time = 1.000000
那么在GreatSQL-8.0.26中,是否写入到慢日志中,取决于thd->server_status & SERVER_QUERY_WAS_SLOW
,即SQL履行总耗时-SQL锁等候耗时>1秒
那么在GreatSQL-8.0.32中,是否写入到慢日志中,取决于thd->server_status & SERVER_QUERY_WAS_SLOW
,即SQL履行总耗时>1秒
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级使用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,能够作为MySQL或Percona Server的可选替换,用于线上出产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖主张反馈: greatsql.cn/thread-54-1…
社区博客有奖征稿详情: greatsql.cn/thread-100-…
(对文章有疑问或许有独到见解都能够去社区官网提出或共享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:添加GreatSQL社区助手(微信号:wanlidbc
)好友,待社区助手拉您进群。