· 8 years ago · Jan 11, 2018, 09:56 AM
1mysql> select version();
2+-----------+
3| version() |
4+-----------+
5| 5.6.38 |
6+-----------+
71 row in set (0.00 sec)
8
9mysql> set sql_mode='';
10Query OK, 0 rows affected (0.00 sec)
11
12mysql> drop table if exists t;
13Query OK, 0 rows affected (0.79 sec)
14
15mysql> create table t (
16 -> f int unsigned not null auto_increment,
17 -> a varchar(200),
18 -> b varchar(200),
19 -> c varchar(200),
20 -> primary key (f)
21 -> ) engine=innodb default charset=latin1 ;
22Query OK, 0 rows affected (0.05 sec)
23
24mysql>
25mysql> -- we insert some random data
26mysql> insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
27Query OK, 5 rows affected (0.00 sec)
28Records: 5 Duplicates: 0 Warnings: 0
29
30mysql> insert into t(a,b,c) select concat(rand(),uuid()),concat(rand(),uuid()),concat(rand(),uuid()) from t a,t b,t c,t d,t e,t f,t g,t h,t i,t j;
31Query OK, 9765625 rows affected (3 min 10.28 sec)
32Records: 9765625 Duplicates: 0 Warnings: 0
33
34mysql> analyze table t;
35+--------+---------+----------+----------+
36| Table | Op | Msg_type | Msg_text |
37+--------+---------+----------+----------+
38| test.t | analyze | status | OK |
39+--------+---------+----------+----------+
401 row in set (0.36 sec)
41
42mysql> show table status like 't'\G
43*************************** 1. row ***************************
44 Name: t
45 Engine: InnoDB
46 Version: 10
47 Row_format: Compact
48 Rows: 9643609
49 Avg_row_length: 207
50 Data_length: 2002780160
51Max_data_length: 0
52 Index_length: 0
53 Data_free: 5242880
54 Auto_increment: 9830256
55 Create_time: 2018-01-11 09:48:25
56 Update_time: NULL
57 Check_time: NULL
58 Collation: latin1_swedish_ci
59 Checksum: NULL
60 Create_options:
61 Comment:
621 row in set (0.01 sec)
63
64mysql> select count(*) from t;
65+----------+
66| count(*) |
67+----------+
68| 9765630 |
69+----------+
701 row in set (22.24 sec)
71
72mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
73+-----------+--------------------------+-------------+--------------+
74| Data_free | Data_length+Index_length | Data_length | Index_length |
75+-----------+--------------------------+-------------+--------------+
76| 5242880 | 2002780160 | 2002780160 | 0 |
77+-----------+--------------------------+-------------+--------------+
781 row in set (0.00 sec)
79
80mysql>
81mysql> -- now we create some holes in table by deleting 90% of the rows.
82mysql> delete from t where b not like '0.1%';
83Query OK, 8790601 rows affected (3 min 41.98 sec)
84
85mysql> select sleep(600); -- wait a long time for purge/page cleaner....
86+------------+
87| sleep(600) |
88+------------+
89| 0 |
90+------------+
911 row in set (10 min 0.00 sec)
92
93mysql> analyze table t;
94+--------+---------+----------+----------+
95| Table | Op | Msg_type | Msg_text |
96+--------+---------+----------+----------+
97| test.t | analyze | status | OK |
98+--------+---------+----------+----------+
991 row in set (7.51 sec)
100
101mysql> show table status like 't'\G
102*************************** 1. row ***************************
103 Name: t
104 Engine: InnoDB
105 Version: 10
106 Row_format: Compact
107 Rows: 971592
108 Avg_row_length: 2060
109 Data_length: 2001862656
110Max_data_length: 0
111 Index_length: 0
112 Data_free: 5242880
113 Auto_increment: 9830256
114 Create_time: 2018-01-11 09:48:25
115 Update_time: NULL
116 Check_time: NULL
117 Collation: latin1_swedish_ci
118 Checksum: NULL
119 Create_options:
120 Comment:
1211 row in set (0.00 sec)
122
123mysql> select count(*) from t;
124+----------+
125| count(*) |
126+----------+
127| 975029 |
128+----------+
1291 row in set (22.07 sec)
130
131mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
132+-----------+--------------------------+-------------+--------------+
133| Data_free | Data_length+Index_length | Data_length | Index_length |
134+-----------+--------------------------+-------------+--------------+
135| 5242880 | 2001862656 | 2001862656 | 0 |
136+-----------+--------------------------+-------------+--------------+
1371 row in set (0.00 sec)
138
139mysql>
140mysql> -- try multiple analyze tables'.
141mysql> analyze table t;
142+--------+---------+----------+----------+
143| Table | Op | Msg_type | Msg_text |
144+--------+---------+----------+----------+
145| test.t | analyze | status | OK |
146+--------+---------+----------+----------+
1471 row in set (0.03 sec)
148
149mysql> analyze table t;
150+--------+---------+----------+----------+
151| Table | Op | Msg_type | Msg_text |
152+--------+---------+----------+----------+
153| test.t | analyze | status | OK |
154+--------+---------+----------+----------+
1551 row in set (0.04 sec)
156
157mysql> analyze table t;
158+--------+---------+----------+----------+
159| Table | Op | Msg_type | Msg_text |
160+--------+---------+----------+----------+
161| test.t | analyze | status | OK |
162+--------+---------+----------+----------+
1631 row in set (0.04 sec)
164
165mysql> show table status like 't'\G
166*************************** 1. row ***************************
167 Name: t
168 Engine: InnoDB
169 Version: 10
170 Row_format: Compact
171 Rows: 952849
172 Avg_row_length: 2100
173 Data_length: 2001862656
174Max_data_length: 0
175 Index_length: 0
176 Data_free: 5242880
177 Auto_increment: 9830256
178 Create_time: 2018-01-11 09:48:25
179 Update_time: NULL
180 Check_time: NULL
181 Collation: latin1_swedish_ci
182 Checksum: NULL
183 Create_options:
184 Comment:
1851 row in set (0.00 sec)
186
187mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
188+-----------+--------------------------+-------------+--------------+
189| Data_free | Data_length+Index_length | Data_length | Index_length |
190+-----------+--------------------------+-------------+--------------+
191| 5242880 | 2001862656 | 2001862656 | 0 |
192+-----------+--------------------------+-------------+--------------+
1931 row in set (0.00 sec)
194
195mysql>
196mysql> -- rebuild table using copy + blocking dml method (makes smaller ibd size)
197mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
198Query OK, 975029 rows affected (10.94 sec)
199Records: 975029 Duplicates: 0 Warnings: 0
200
201mysql> analyze table t;
202+--------+---------+----------+----------+
203| Table | Op | Msg_type | Msg_text |
204+--------+---------+----------+----------+
205| test.t | analyze | status | OK |
206+--------+---------+----------+----------+
2071 row in set (0.08 sec)
208
209mysql> show table status like 't'\G
210*************************** 1. row ***************************
211 Name: t
212 Engine: InnoDB
213 Version: 10
214 Row_format: Compact
215 Rows: 962931
216 Avg_row_length: 207
217 Data_length: 199966720
218Max_data_length: 0
219 Index_length: 0
220 Data_free: 4194304
221 Auto_increment: 9830256
222 Create_time: 2018-01-11 09:48:25
223 Update_time: NULL
224 Check_time: NULL
225 Collation: latin1_swedish_ci
226 Checksum: NULL
227 Create_options:
228 Comment:
2291 row in set (0.00 sec)
230
231mysql> select count(*) from t;
232+----------+
233| count(*) |
234+----------+
235| 975029 |
236+----------+
2371 row in set (0.23 sec)
238
239mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
240+-----------+--------------------------+-------------+--------------+
241| Data_free | Data_length+Index_length | Data_length | Index_length |
242+-----------+--------------------------+-------------+--------------+
243| 4194304 | 199966720 | 199966720 | 0 |
244+-----------+--------------------------+-------------+--------------+
2451 row in set (0.00 sec)
246
247mysql>
248mysql>
249
250
251
252
253mysql> select version();
254+-----------+
255| version() |
256+-----------+
257| 5.7.20 |
258+-----------+
2591 row in set (0.00 sec)
260
261mysql> set sql_mode='';
262Query OK, 0 rows affected, 1 warning (0.00 sec)
263
264mysql> drop table if exists t;
265Query OK, 0 rows affected (0.09 sec)
266
267mysql> create table t (
268 -> f int unsigned not null auto_increment,
269 -> a varchar(200),
270 -> b varchar(200),
271 -> c varchar(200),
272 -> primary key (f)
273 -> ) engine=innodb row_format=compact default charset=latin1 ;
274Query OK, 0 rows affected (0.01 sec)
275
276mysql>
277mysql> -- we insert some random data
278mysql> insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
279Query OK, 5 rows affected (0.02 sec)
280Records: 5 Duplicates: 0 Warnings: 0
281
282mysql> insert into t(a,b,c) select concat(rand(),uuid()),concat(rand(),uuid()),concat(rand(),uuid()) from t a,t b,t c,t d,t e,t f,t g,t h,t i,t j;
283Query OK, 9765625 rows affected (8 min 36.85 sec)
284Records: 9765625 Duplicates: 0 Warnings: 0
285
286mysql> analyze table t;
287+--------+---------+----------+----------+
288| Table | Op | Msg_type | Msg_text |
289+--------+---------+----------+----------+
290| test.t | analyze | status | OK |
291+--------+---------+----------+----------+
2921 row in set (0.53 sec)
293
294mysql> show table status like 't'\G
295*************************** 1. row ***************************
296 Name: t
297 Engine: InnoDB
298 Version: 10
299 Row_format: Compact
300 Rows: 9643609
301 Avg_row_length: 207
302 Data_length: 2002780160
303Max_data_length: 0
304 Index_length: 0
305 Data_free: 5242880
306 Auto_increment: 9830256
307 Create_time: 2018-01-09 14:56:19
308 Update_time: 2018-01-11 11:30:01
309 Check_time: NULL
310 Collation: latin1_swedish_ci
311 Checksum: NULL
312 Create_options: row_format=COMPACT
313 Comment:
3141 row in set (0.00 sec)
315
316mysql> select count(*) from t;
317+----------+
318| count(*) |
319+----------+
320| 9765630 |
321+----------+
3221 row in set (24.05 sec)
323
324mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
325+-----------+--------------------------+-------------+--------------+
326| Data_free | Data_length+Index_length | Data_length | Index_length |
327+-----------+--------------------------+-------------+--------------+
328| 5242880 | 2002780160 | 2002780160 | 0 |
329+-----------+--------------------------+-------------+--------------+
3301 row in set (0.00 sec)
331
332mysql>
333mysql> -- now we create some holes in table by deleting 90% of the rows.
334mysql> delete from t where b not like '0.1%';
335Query OK, 8789111 rows affected (2 min 33.37 sec)
336
337mysql> select sleep(600); -- wait a long time for purge/page cleaner....
338+------------+
339| sleep(600) |
340+------------+
341| 0 |
342+------------+
3431 row in set (10 min 0.00 sec)
344
345mysql> analyze table t;
346+--------+---------+----------+----------+
347| Table | Op | Msg_type | Msg_text |
348+--------+---------+----------+----------+
349| test.t | analyze | status | OK |
350+--------+---------+----------+----------+
3511 row in set (10.90 sec)
352
353mysql> show table status like 't'\G
354*************************** 1. row ***************************
355 Name: t
356 Engine: InnoDB
357 Version: 10
358 Row_format: Compact
359 Rows: 956198
360 Avg_row_length: 2093
361 Data_length: 2001895424
362Max_data_length: 0
363 Index_length: 0
364 Data_free: 5242880
365 Auto_increment: 9830256
366 Create_time: 2018-01-09 14:56:19
367 Update_time: 2018-01-11 11:33:00
368 Check_time: NULL
369 Collation: latin1_swedish_ci
370 Checksum: NULL
371 Create_options: row_format=COMPACT
372 Comment:
3731 row in set (0.00 sec)
374
375mysql> select count(*) from t;
376+----------+
377| count(*) |
378+----------+
379| 976519 |
380+----------+
3811 row in set (19.65 sec)
382
383mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
384+-----------+--------------------------+-------------+--------------+
385| Data_free | Data_length+Index_length | Data_length | Index_length |
386+-----------+--------------------------+-------------+--------------+
387| 5242880 | 2001895424 | 2001895424 | 0 |
388+-----------+--------------------------+-------------+--------------+
3891 row in set (0.00 sec)
390
391mysql>
392mysql> -- try multiple analyze tables'.
393mysql> analyze table t;
394+--------+---------+----------+----------+
395| Table | Op | Msg_type | Msg_text |
396+--------+---------+----------+----------+
397| test.t | analyze | status | OK |
398+--------+---------+----------+----------+
3991 row in set (0.05 sec)
400
401mysql> analyze table t;
402+--------+---------+----------+----------+
403| Table | Op | Msg_type | Msg_text |
404+--------+---------+----------+----------+
405| test.t | analyze | status | OK |
406+--------+---------+----------+----------+
4071 row in set (0.05 sec)
408
409mysql> analyze table t;
410+--------+---------+----------+----------+
411| Table | Op | Msg_type | Msg_text |
412+--------+---------+----------+----------+
413| test.t | analyze | status | OK |
414+--------+---------+----------+----------+
4151 row in set (0.06 sec)
416
417mysql> show table status like 't'\G
418*************************** 1. row ***************************
419 Name: t
420 Engine: InnoDB
421 Version: 10
422 Row_format: Compact
423 Rows: 990175
424 Avg_row_length: 2021
425 Data_length: 2001895424
426Max_data_length: 0
427 Index_length: 0
428 Data_free: 5242880
429 Auto_increment: 9830256
430 Create_time: 2018-01-09 14:56:19
431 Update_time: 2018-01-11 11:33:00
432 Check_time: NULL
433 Collation: latin1_swedish_ci
434 Checksum: NULL
435 Create_options: row_format=COMPACT
436 Comment:
4371 row in set (0.00 sec)
438
439mysql> select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
440+-----------+--------------------------+-------------+--------------+
441| Data_free | Data_length+Index_length | Data_length | Index_length |
442+-----------+--------------------------+-------------+--------------+
443| 5242880 | 2001895424 | 2001895424 | 0 |
444+-----------+--------------------------+-------------+--------------+
4451 row in set (0.00 sec)
446
447mysql>