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
155
|
--source include/have_optimizer_trace.inc
--source include/have_64bit.inc
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
--source include/no_protocol.inc
--source include/not_hypergraph.inc # Does not output the same optimizer trace.
SET optimizer_trace_max_mem_size=1048576; # 1MB
SET optimizer_trace="enabled=on,one_line=off";
SET end_markers_in_json="on";
CREATE TABLE tmp (
pk INT PRIMARY KEY AUTO_INCREMENT,
col1 CHAR (1)
);
INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
CREATE TABLE t1 (
uniq VARCHAR(10),
col1 VARCHAR(10),
col2 VARCHAR(1024)
) CHARSET utf8mb4;
INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
--source include/turn_off_only_full_group_by.inc
# Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause.
SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
SELECT * FROM information_schema.OPTIMIZER_TRACE;
# Uses @internal_tmp_mem_storage_engine temporary table
--sorted_result
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
SELECT * FROM information_schema.OPTIMIZER_TRACE;
select @@tmp_table_size;
SET @old_size= @@tmp_table_size;
SET SESSION tmp_table_size= 1024;
SET SESSION internal_tmp_mem_storage_engine='memory';
INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
# Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION internal_tmp_mem_storage_engine=default;
SET GLOBAL tmp_table_size= @old_size;
# Temp tables for I_S tables. Uses HEAP temporary table.
# Converts it to MyISAM packed record format due to heap size limitation
SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE innodb_buffer_page INNODB_BUFFER_PAGE
SELECT * FROM information_schema.OPTIMIZER_TRACE;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
DROP TABLE t1, tmp;
--echo #
--echo # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES
--echo # ON DISK
--echo #
CREATE TABLE t1 (
c1 INT AUTO_INCREMENT PRIMARY KEY,
c2 VARCHAR(250)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t1(c2) VALUES ('b'),('b');
INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
SET @@max_heap_table_size=1;
SET @@group_concat_max_len= 500;
SET SESSION internal_tmp_mem_storage_engine='memory';
# This query creates 4 tmp tables, 3 of them aren''t packed without this
# fix.
# Purpose Format without fix / with fix
# union result fixed packed
# derived tbl result fixed packed
# join materialization fixed packed
# group_concat internal tbl packed packed (not shown in the opt trace)
--sorted_result
SELECT c1,GROUP_CONCAT(c2) cc FROM
(SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
GROUP BY c1
ORDER BY cc;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
--replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
SELECT * FROM information_schema.OPTIMIZER_TRACE;
DROP TABLE t1;
--echo #
# Clean up.
SET SESSION internal_tmp_mem_storage_engine=default;
--echo #
--echo # Bug #30773218 ASSERTION IN SQL_TMP_TABLE.CC::TRACE_TMP_TABLE
--echo #
CREATE TABLE t1(name VARCHAR(64), type TEXT);
INSERT INTO t1(name, type) VALUES
('t1', 'table'),
('t2', 'table'),
('t3', 'table'),
('t4', 'table'),
('t5', 'table'),
('t6', 'table'),
('t7', 'table'),
('t8', 'table'),
('t9', 'table'),
('t10', 'table'),
('t11', 'table');
CREATE TABLE t2(name VARCHAR(64), data TEXT);
INSERT INTO t2(name, data) VALUES
('t1', 'data'),
('t2', 'data'),
('t3', 'data'),
('t4', 'data'),
('t5', 'data'),
('t6', 'data'),
('t7', 'data'),
('t8', 'data');
# We need this combination of settings in order to force a second execution
# of the function trace_tmp_table. During join execution, we get
# "join_execution":
# "subselect_execution":
# "materialize":
# "converting_tmp_table_to_ondisk":
# "cause": "memory_table_size_exceeded",
# "tmp_table_info":
# At this point we do have a QEP_TAB, but no corresponding JOIN_TAB.
# If we use semijoin, no temporary table is instantiated.
SET SESSION internal_tmp_mem_storage_engine=MEMORY;
SET @@session.optimizer_trace='enabled=on';
SET @@session.tmp_table_size = 1024;
SET @@optimizer_switch='semijoin=off';
SELECT name, data FROM t2 WHERE name IN
( SELECT name FROM t1 WHERE type='table');
DROP TABLE t1, t2;
SET SESSION internal_tmp_mem_storage_engine=default;
SET @@session.optimizer_trace='enabled=default';
SET @@session.tmp_table_size = default;
SET @@optimizer_switch='semijoin=default';
|
