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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
#
# Test temptable_use_mmap
#
# Restart the server to clean up any prior allocations
let $restart_parameters=;
--source include/restart_mysqld.inc
SELECT @@global.temptable_use_mmap;
--error ER_GLOBAL_VARIABLE
SET @@session.temptable_use_mmap=false;
--error ER_WRONG_VALUE_FOR_VAR
SET @@global.temptable_use_mmap=NULL;
--echo # Test with temptable_use_mmap set to false
--echo # to stop overflowing temptable to disk
SET @@global.temptable_use_mmap=false;
SELECT @@global.temptable_use_mmap;
# Ensure there are no existing allocations
SELECT count_alloc > 0
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk';
CREATE TABLE t (c VARCHAR(128));
INSERT INTO t VALUES
(REPEAT('a', 128)),
(REPEAT('b', 128)),
(REPEAT('c', 128)),
(REPEAT('d', 128));
ANALYZE TABLE t;
SET GLOBAL temptable_max_ram = 2097152;
-- disable_result_log
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
-- enable_result_log
SET GLOBAL temptable_max_ram = default;
# There should be no allocations on the physical disk
SELECT count_alloc > 0
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk';
DROP TABLE t;
--echo # Test with temptable_use_mmap set to true
--echo # to enable overflowing temptable to disk
SET @@global.temptable_use_mmap = true;
SELECT @@global.temptable_use_mmap;
#
# Test TempTable overflow to disk
#
CREATE TABLE t (c LONGBLOB); # Forces use of temporary table in filesort.
INSERT INTO t VALUES
(REPEAT('a', 128)),
(REPEAT('b', 128)),
(REPEAT('c', 128)),
(REPEAT('d', 128));
ANALYZE TABLE t;
SET GLOBAL temptable_max_ram = 2097152;
-- disable_result_log
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
-- enable_result_log
SET GLOBAL temptable_max_ram = default;
SET GLOBAL temptable_use_mmap = default;
SELECT @@global.temptable_use_mmap;
# Just make sure some disk pages were allocated, the exact number of bytes
# and pages is irrelevant for this test.
SELECT count_alloc > 0
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk';
DROP TABLE t;
#
# Test TempTable by using a query which will exhaust both RAM and MMAP limits.
#
CREATE TABLE t (c LONGBLOB); # Forces use of temporary table in filesort.
INSERT INTO t VALUES
(REPEAT('a', 128)),
(REPEAT('b', 128)),
(REPEAT('c', 128)),
(REPEAT('d', 128));
ANALYZE TABLE t;
SET GLOBAL temptable_max_ram = 2*1024*1024;
SET GLOBAL temptable_max_mmap = 4*1024*1024;
-- disable_result_log
SELECT * FROM
t AS t1,
t AS t2,
t AS t3,
t AS t4,
t AS t5,
t AS t6
ORDER BY 1
LIMIT 2;
-- enable_result_log
# We need to make sure we have exhausted the RAM limit
# Greater-or-equal must be used as memory for shared-block is not accounted for. After fixing
# Bug #29890126 TEMPTABLE ALLOCATOR DOESN'T TRACK RAM-CONSUMPTION FOR SHARED-BLOCK we can
# switch to using equal-operator (as we do for MMAP-ed allocations down below)
SELECT sum_number_of_bytes_alloc >= 2*1024*1024
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_ram';
# We need to make sure we have exhausted the MMAP limit
SELECT sum_number_of_bytes_alloc = 4*1024*1024 + 64
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name = 'memory/temptable/physical_disk';
DROP TABLE t;
SET GLOBAL temptable_max_ram = default;
SET GLOBAL temptable_max_mmap = default;
SET GLOBAL temptable_use_mmap = default;
|
