Debian Codegraph @

mysql-test/suite/innodb/r/create_index_excess_memory.result

raw
1 # Create Table
2 CREATE TABLE t1 (
3 auditid VARCHAR(45) NOT NULL,
4 ip VARCHAR(39) NOT NULL DEFAULT '',
5 PRIMARY KEY (auditid)
6 ) ENGINE=INNODB;
7 # Insert Values
8 INSERT INTO t1 VALUES
9 ('abcdefghi', '1.1.1.1'),
10 ('fghi', '192.168.1.1'),
11 ('jklmnop', '2.2.2.2'),
12 ('qrstu', '255.255.255.255'),
13 ('uvwxyzabcde', '2001:db8::1');
14 # Create function for getting memory usage from perf schema
15 CREATE FUNCTION get_my_std_memory_usage() RETURNS INT READS SQL DATA
16 RETURN (
17 SELECT SUM_NUMBER_OF_BYTES_ALLOC
18 FROM performance_schema.memory_summary_global_by_event_name
19 WHERE EVENT_NAME = 'memory/innodb/std'
20 );
21 # Test 1. Check memory usage during create index
22 # Set the limit for memory usage
23 # Set Buffer Size
24 SET innodb_ddl_buffer_size = (4*1024*1024);
25 # Number of bytes allocated before create index
26 SET @before_memory = get_my_std_memory_usage();
27 # Create index
28 CREATE INDEX i1 ON t1(ip);
29 # Number of bytes allocated after create index
30 SET @after_memory = get_my_std_memory_usage();
31 # Bytes allocated in the process
32 SET @memory_diff = @after_memory - @before_memory;
33 include/assert.inc [Assert that memory usage is less than 4MB]
34 # Drop index
35 DROP INDEX i1 ON t1;
36 # Set Buffer Size
37 SET innodb_ddl_buffer_size = (8*1024*1024);
38 # Number of bytes allocated before create index
39 SET @before_memory = get_my_std_memory_usage();
40 # Create index
41 CREATE INDEX i1 ON t1(ip);
42 # Number of bytes allocated after create index
43 SET @after_memory = get_my_std_memory_usage();
44 # Bytes allocated in the process
45 SET @memory_diff = @after_memory - @before_memory;
46 include/assert.inc [Assert that memory usage is less than 4MB]
47 # Drop index
48 DROP INDEX i1 ON t1;
49 # Set Buffer Size
50 SET innodb_ddl_buffer_size = (16*1024*1024);
51 # Number of bytes allocated before create index
52 SET @before_memory = get_my_std_memory_usage();
53 # Create index
54 CREATE INDEX i1 ON t1(ip);
55 # Number of bytes allocated after create index
56 SET @after_memory = get_my_std_memory_usage();
57 # Bytes allocated in the process
58 SET @memory_diff = @after_memory - @before_memory;
59 include/assert.inc [Assert that memory usage is less than 4MB]
60 # Drop index
61 DROP INDEX i1 ON t1;
62 # Set Buffer Size
63 SET innodb_ddl_buffer_size = (32*1024*1024);
64 # Number of bytes allocated before create index
65 SET @before_memory = get_my_std_memory_usage();
66 # Create index
67 CREATE INDEX i1 ON t1(ip);
68 # Number of bytes allocated after create index
69 SET @after_memory = get_my_std_memory_usage();
70 # Bytes allocated in the process
71 SET @memory_diff = @after_memory - @before_memory;
72 include/assert.inc [Assert that memory usage is less than 4MB]
73 # Drop index
74 DROP INDEX i1 ON t1;
75 # Set Buffer Size
76 SET innodb_ddl_buffer_size = (64*1024*1024);
77 # Number of bytes allocated before create index
78 SET @before_memory = get_my_std_memory_usage();
79 # Create index
80 CREATE INDEX i1 ON t1(ip);
81 # Number of bytes allocated after create index
82 SET @after_memory = get_my_std_memory_usage();
83 # Bytes allocated in the process
84 SET @memory_diff = @after_memory - @before_memory;
85 include/assert.inc [Assert that memory usage is less than 4MB]
86 # Drop index
87 DROP INDEX i1 ON t1;
88 # Set Buffer Size
89 SET innodb_ddl_buffer_size = (128*1024*1024);
90 # Number of bytes allocated before create index
91 SET @before_memory = get_my_std_memory_usage();
92 # Create index
93 CREATE INDEX i1 ON t1(ip);
94 # Number of bytes allocated after create index
95 SET @after_memory = get_my_std_memory_usage();
96 # Bytes allocated in the process
97 SET @memory_diff = @after_memory - @before_memory;
98 include/assert.inc [Assert that memory usage is less than 4MB]
99 # Drop index
100 DROP INDEX i1 ON t1;
101 # Test 2. Test memory usage with minumum value of innodb_ddl_buffer_size
102 # and maximum value of --innodb_parallel_read_threads
103 SET innodb_ddl_buffer_size = 65536;
104 SET innodb_parallel_read_threads = 256;
105 # Number of bytes allocated before create index
106 SET @before_memory = get_my_std_memory_usage();
107 # Create index
108 CREATE INDEX i1 ON t1(ip);
109 # Number of bytes allocated after create index
110 SET @after_memory = get_my_std_memory_usage();
111 # Bytes allocated in the process
112 SET @memory_diff = @after_memory - @before_memory;
113 include/assert.inc [Assert that memory usage is less than 4MB]
114 # Drop index
115 DROP INDEX i1 ON t1;
116 # Test 3. Test memory usage with maximum value of innodb_ddl_buffer_size
117 # and minimum value of --innodb_parallel_read_threads
118 SET innodb_ddl_buffer_size = 4294967295;
119 SET innodb_parallel_read_threads = 1;
120 # Number of bytes allocated before create index
121 SET @before_memory = get_my_std_memory_usage();
122 # Create index
123 CREATE INDEX i1 ON t1(ip);
124 # Number of bytes allocated after create index
125 SET @after_memory = get_my_std_memory_usage();
126 # Bytes allocated in the process
127 SET @memory_diff = @after_memory - @before_memory;
128 include/assert.inc [Assert that memory usage is less than 4MB]
129 # Drop index
130 DROP INDEX i1 ON t1;
131 # Test 4. Test DB_OVERFLOW by setting tuple vector size to 2
132 # Set Buffer Size
133 SET innodb_ddl_buffer_size = (4*1024*1024);
134 SET innodb_parallel_read_threads = default;
135 # Create index
136 SET DEBUG = '+d, ddl_buf_add_two';
137 CREATE INDEX i1 ON t1(ip);
138 # The warning "InnoDB rebuilding table to add column FTS_DOC_ID" is expected
139 CREATE FULLTEXT INDEX i2 ON t1(ip);
140 Warnings:
141 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
142 SET DEBUG = '-d, ddl_buf_add_two';
143 # Drop index
144 DROP INDEX i1 ON t1;
145 DROP INDEX i2 ON t1;
146 # Cleanup
147 DROP TABLE t1;
148 DROP FUNCTION get_my_std_memory_usage;
149 # Restart server to set parameters to default
150 # restart