1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
set @@log_slow_verbosity="";
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
select @@log_slow_rate_limit;
@@log_slow_rate_limit
1
select @@log_slow_verbosity;
@@log_slow_verbosity
show variables like "log_slow%";
Variable_name Value
log_slow_admin_statements ON
log_slow_disabled_statements sp
log_slow_filter admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit 1
log_slow_slave_statements ON
log_slow_verbosity
set @org_slow_query_log= @@global.slow_query_log;
set @@log_slow_filter= "filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk,admin";
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
set @@log_slow_filter="admin,admin";
select @@log_slow_filter;
@@log_slow_filter
admin
set @@log_slow_filter=7;
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk
set @@log_slow_filter= "filesort,impossible,impossible2,admin";
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of 'impossible'
set @@log_slow_filter= "filesort, admin";
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of ' admin'
set @@log_slow_filter= 1<<31;
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of '2147483648'
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk
set @@log_slow_verbosity= "query_plan,innodb";
select @@log_slow_verbosity;
@@log_slow_verbosity
innodb,query_plan
set @@log_slow_verbosity=1;
select @@log_slow_verbosity;
@@log_slow_verbosity
innodb
show fields from mysql.slow_log;
Field Type Null Key Default Extra
start_time timestamp(6) NO current_timestamp(6) on update current_timestamp(6)
user_host mediumtext NO NULL
query_time time(6) NO NULL
lock_time time(6) NO NULL
rows_sent int(11) NO NULL
rows_examined int(11) NO NULL
db varchar(512) NO NULL
last_insert_id int(11) NO NULL
insert_id int(11) NO NULL
server_id int(10) unsigned NO NULL
sql_text mediumtext NO NULL
thread_id bigint(21) unsigned NO NULL
rows_affected int(11) NO NULL
flush slow logs;
set long_query_time=0.1;
set log_slow_filter='';
set slow_query_log=1;
set global log_output='TABLE';
select sleep(0.5);
sleep(0.5)
0
select count(*) FROM mysql.slow_log;
count(*)
1
set @@long_query_time=default;
set @@slow_query_log=default;
set @@log_slow_filter=default;
set @@log_slow_verbosity=default;
set global log_output= default;
truncate mysql.slow_log;
#
# MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off
#
SET SESSION slow_query_log=OFF;
SET GLOBAL slow_query_log=OFF;
SET long_query_time=0.1;
# Although this query is disallowed by slow_query_log, it should still increment Slow_queries
SELECT VARIABLE_VALUE INTO @global_slow_queries
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='SLOW_QUERIES';
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE
VARIABLE_NAME='SLOW_QUERIES';
Slow_queries_increment
1
# Although this query is disallowed by log_slow_filter, it should still increment Slow_queries
SET log_slow_filter=filesort;
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT VARIABLE_VALUE INTO @global_slow_queries
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='SLOW_QUERIES';
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE
VARIABLE_NAME='SLOW_QUERIES';
Slow_queries_increment
1
SET log_slow_filter=DEFAULT;
SET @@long_query_time=default;
SET GLOBAL slow_query_log= @org_slow_query_log;
#
# MDEV-21187: log_slow_filter="" logs queries not using indexes
#
flush status;
create table t (id int);
insert into t values (1),(4);
set log_slow_filter='';
select * from t;
id
1
4
show session status like 'Slow_queries';
Variable_name Value
Slow_queries 0
drop table t;
#
# End of 10.3 tests
#
|
