[已解决] 慢查询日志每天暴涨，如何解决？

138 1 2024-9-4 15:40

greatsql的docker版本8.0.32-25，操作系统是麒麟V10

docker容器里面的my.cnf文件中的相关配置：

"log_slow_admin_statements"       "ON"
"log_slow_extra"       "ON"
"log_slow_filter"       ""
"log_slow_rate_limit"       "1"
"log_slow_rate_type"       "session"
"log_slow_replica_statements"       "ON"
"log_slow_slave_statements"       "ON"
"log_slow_sp_statements"       "ON"
"log_slow_verbosity"       ""
"log_queries_not_using_indexes"       "ON"
"slow_query_log"       "ON"
"slow_query_log_always_write_time"       "10.000000"
"slow_query_log_file"       "/data/GreatSQL/slow.log"
"slow_query_log_use_global_control"       ""

在mysql目录下修改long_query_time
"long_query_time"       "10.000000"

但是日志还是不停记录，slow.log日志如下：

SET timestamp=1725430343;
select count(nodealarmc0_.id) as col_0_0_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (1=2 or nodealarmc0_.node_id=9083 or nodealarmc0_.topology_location=9083) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960;
# Time: 2024-09-04T14:12:28.287719+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1397
# Query_time: 0.000714  Lock_time: 0.000003 Rows_sent: 0  Rows_examined: 115 Thread_id: 1397 Errno: 0 Killed: 0 Bytes_received: 1659 Bytes_sent: 2162 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:28.287005+08:00 End: 2024-09-04T14:12:28.287719+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430348;
select nodealarmc0_.id as id1_15_, nodealarmc0_.created_date as created_2_15_, nodealarmc0_.last_modified_date as last_mod3_15_, nodealarmc0_.created_by as created_4_15_, nodealarmc0_.last_modified_by as last_mod5_15_, nodealarmc0_.comments as comments6_15_, nodealarmc0_.name as name7_15_, nodealarmc0_.status_id as status_i8_15_, nodealarmc0_.type_id as type_id9_15_, nodealarmc0_.alarm_code_id as alarm_c21_15_, nodealarmc0_.alarm_severity_id as alarm_s10_15_, nodealarmc0_.confirm_time as confirm11_15_, nodealarmc0_.confirm_user_id as confirm22_15_, nodealarmc0_.eliminated_time as elimina12_15_, nodealarmc0_.eliminated_user_id as elimina23_15_, nodealarmc0_.history_id as history13_15_, nodealarmc0_.node_id as node_id24_15_, nodealarmc0_.occurred_time as occurre14_15_, nodealarmc0_.occurred_time_latest as occurre15_15_, nodealarmc0_.repeat_count as repeat_16_15_, nodealarmc0_.resource_location as resourc17_15_, nodealarmc0_.root_alarm as root_al18_15_, nodealarmc0_.topology_location as topolog19_15_, nodealarmc0_.value as value20_15_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (nodealarmc0_.resource_location in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or nodealarmc0_.node_id in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or 1=2) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960 order by nodealarmc0_.occurred_time desc limit 99999;
# Time: 2024-09-04T14:12:28.288318+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1397
# Query_time: 0.000405  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 115 Thread_id: 1397 Errno: 0 Killed: 0 Bytes_received: 600 Bytes_sent: 56 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:28.287913+08:00 End: 2024-09-04T14:12:28.288318+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430348;
select count(nodealarmc0_.id) as col_0_0_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (nodealarmc0_.resource_location in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or nodealarmc0_.node_id in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or 1=2) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960;
# Time: 2024-09-04T14:12:29.277207+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1394
# Query_time: 0.000666  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 115 Thread_id: 1394 Errno: 0 Killed: 0 Bytes_received: 1355 Bytes_sent: 2162 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:29.276541+08:00 End: 2024-09-04T14:12:29.277207+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430349;
select nodealarmc0_.id as id1_15_, nodealarmc0_.created_date as created_2_15_, nodealarmc0_.last_modified_date as last_mod3_15_, nodealarmc0_.created_by as created_4_15_, nodealarmc0_.last_modified_by as last_mod5_15_, nodealarmc0_.comments as comments6_15_, nodealarmc0_.name as name7_15_, nodealarmc0_.status_id as status_i8_15_, nodealarmc0_.type_id as type_id9_15_, nodealarmc0_.alarm_code_id as alarm_c21_15_, nodealarmc0_.alarm_severity_id as alarm_s10_15_, nodealarmc0_.confirm_time as confirm11_15_, nodealarmc0_.confirm_user_id as confirm22_15_, nodealarmc0_.eliminated_time as elimina12_15_, nodealarmc0_.eliminated_user_id as elimina23_15_, nodealarmc0_.history_id as history13_15_, nodealarmc0_.node_id as node_id24_15_, nodealarmc0_.occurred_time as occurre14_15_, nodealarmc0_.occurred_time_latest as occurre15_15_, nodealarmc0_.repeat_count as repeat_16_15_, nodealarmc0_.resource_location as resourc17_15_, nodealarmc0_.root_alarm as root_al18_15_, nodealarmc0_.topology_location as topolog19_15_, nodealarmc0_.value as value20_15_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (1=2 or nodealarmc0_.node_id=9083 or nodealarmc0_.topology_location=9083) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960 order by nodealarmc0_.occurred_time desc limit 99999;
# Time: 2024-09-04T14:12:29.277747+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1394
# Query_time: 0.000353  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 115 Thread_id: 1394 Errno: 0 Killed: 0 Bytes_received: 296 Bytes_sent: 56 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:29.277394+08:00 End: 2024-09-04T14:12:29.277747+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430349;
select count(nodealarmc0_.id) as col_0_0_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (1=2 or nodealarmc0_.node_id=9083 or nodealarmc0_.topology_location=9083) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960;
# Time: 2024-09-04T14:12:34.284448+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1394
# Query_time: 0.000702  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 115 Thread_id: 1394 Errno: 0 Killed: 0 Bytes_received: 1659 Bytes_sent: 2162 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:34.283746+08:00 End: 2024-09-04T14:12:34.284448+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430354;
select nodealarmc0_.id as id1_15_, nodealarmc0_.created_date as created_2_15_, nodealarmc0_.last_modified_date as last_mod3_15_, nodealarmc0_.created_by as created_4_15_, nodealarmc0_.last_modified_by as last_mod5_15_, nodealarmc0_.comments as comments6_15_, nodealarmc0_.name as name7_15_, nodealarmc0_.status_id as status_i8_15_, nodealarmc0_.type_id as type_id9_15_, nodealarmc0_.alarm_code_id as alarm_c21_15_, nodealarmc0_.alarm_severity_id as alarm_s10_15_, nodealarmc0_.confirm_time as confirm11_15_, nodealarmc0_.confirm_user_id as confirm22_15_, nodealarmc0_.eliminated_time as elimina12_15_, nodealarmc0_.eliminated_user_id as elimina23_15_, nodealarmc0_.history_id as history13_15_, nodealarmc0_.node_id as node_id24_15_, nodealarmc0_.occurred_time as occurre14_15_, nodealarmc0_.occurred_time_latest as occurre15_15_, nodealarmc0_.repeat_count as repeat_16_15_, nodealarmc0_.resource_location as resourc17_15_, nodealarmc0_.root_alarm as root_al18_15_, nodealarmc0_.topology_location as topolog19_15_, nodealarmc0_.value as value20_15_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (nodealarmc0_.resource_location in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or nodealarmc0_.node_id in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or 1=2) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960 order by nodealarmc0_.occurred_time desc limit 99999;
# Time: 2024-09-04T14:12:34.285038+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1394
# Query_time: 0.000404  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 115 Thread_id: 1394 Errno: 0 Killed: 0 Bytes_received: 600 Bytes_sent: 56 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:34.284634+08:00 End: 2024-09-04T14:12:34.285038+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430354;
select count(nodealarmc0_.id) as col_0_0_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (nodealarmc0_.resource_location in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or nodealarmc0_.node_id in (9060 , 9061 , 9062 , 9063 , 9064 , 9065 , 9066 , 9067 , 9068 , 9069 , 9070 , 9071 , 9072 , 9073 , 9074 , 9075 , 9076 , 9077 , 9078 , 9079 , 9080 , 9081) or 1=2) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960;
# Time: 2024-09-04T14:12:35.274008+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1408
# Query_time: 0.000641  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 115 Thread_id: 1408 Errno: 0 Killed: 0 Bytes_received: 1355 Bytes_sent: 2162 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:35.273367+08:00 End: 2024-09-04T14:12:35.274008+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430355;
select nodealarmc0_.id as id1_15_, nodealarmc0_.created_date as created_2_15_, nodealarmc0_.last_modified_date as last_mod3_15_, nodealarmc0_.created_by as created_4_15_, nodealarmc0_.last_modified_by as last_mod5_15_, nodealarmc0_.comments as comments6_15_, nodealarmc0_.name as name7_15_, nodealarmc0_.status_id as status_i8_15_, nodealarmc0_.type_id as type_id9_15_, nodealarmc0_.alarm_code_id as alarm_c21_15_, nodealarmc0_.alarm_severity_id as alarm_s10_15_, nodealarmc0_.confirm_time as confirm11_15_, nodealarmc0_.confirm_user_id as confirm22_15_, nodealarmc0_.eliminated_time as elimina12_15_, nodealarmc0_.eliminated_user_id as elimina23_15_, nodealarmc0_.history_id as history13_15_, nodealarmc0_.node_id as node_id24_15_, nodealarmc0_.occurred_time as occurre14_15_, nodealarmc0_.occurred_time_latest as occurre15_15_, nodealarmc0_.repeat_count as repeat_16_15_, nodealarmc0_.resource_location as resourc17_15_, nodealarmc0_.root_alarm as root_al18_15_, nodealarmc0_.topology_location as topolog19_15_, nodealarmc0_.value as value20_15_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (1=2 or nodealarmc0_.node_id=9083 or nodealarmc0_.topology_location=9083) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960 order by nodealarmc0_.occurred_time desc limit 99999;
# Time: 2024-09-04T14:12:35.274510+08:00
# User@Host: root[root] @  [127.0.0.1]  Id:  1408
# Query_time: 0.000344  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 115 Thread_id: 1408 Errno: 0 Killed: 0 Bytes_received: 296 Bytes_sent: 56 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 116 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2024-09-04T14:12:35.274166+08:00 End: 2024-09-04T14:12:35.274510+08:00 Schema: oscape_db Rows_affected: 0
SET timestamp=1725430355;
select count(nodealarmc0_.id) as col_0_0_ from node_alarm_current nodealarmc0_ cross join node node1_ where nodealarmc0_.node_id=node1_.id and (1=2 or nodealarmc0_.node_id=9083 or nodealarmc0_.topology_location=9083) and (nodealarmc0_.status_id not in  (960 , 966)) and node1_.status_id<>960;
# Time: 2024-09-04T14:12:40.275371+08:00

请问如何修改能够生效？

GreatSQL, 慢sql

全部回复(1)

只看楼主

KAiTO 2024-9-4 15:54:23

参数中有一个 log_queries_not_using_indexes ，表示作用是将所有未能使用索引的查询请求也判定为慢查询，可能是没有创建索引，也可能是用不上合适的索引 [1]，就算SQL没达到 long_query_time 设置的参数，也会记录再慢查询中。可能是这个参数设置的ON导致的记录。

要验证也很简单，把慢查询日志中的SQL语句，放到数据库中，查看执行计划 explain ，没有显示使用到索引，那就基本确定了