· 8 years ago · Jan 09, 2018, 12:12 PM
1mysql> set sql_mode='';
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> drop table if exists t;
5Query OK, 0 rows affected (0.89 sec)
6
7mysql> create table `t` (
8 -> `a` varchar(100) not null default '',
9 -> `b` varchar(50) not null default '',
10 -> `c` varchar(5000) default null,
11 -> `d` timestamp null default current_timestamp on update current_timestamp,
12 -> `e` timestamp null default current_timestamp,
13 -> `f` bigint(25) not null auto_increment,
14 -> primary key (`f`)
15 -> ) engine=innodb default charset=latin1 ;
16Query OK, 0 rows affected (0.01 sec)
17
18mysql>
19mysql>
20mysql> -- we insert some random data
21mysql> insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
22Query OK, 5 rows affected (0.00 sec)
23Records: 5 Duplicates: 0 Warnings: 0
24
25mysql> 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;
26Query OK, 9765625 rows affected, 65535 warnings (4 min 12.36 sec)
27Records: 9765625 Duplicates: 0 Warnings: 9764876
28
29mysql> analyze table t;
30+--------+---------+----------+----------+
31| Table | Op | Msg_type | Msg_text |
32+--------+---------+----------+----------+
33| test.t | analyze | status | OK |
34+--------+---------+----------+----------+
351 row in set (0.07 sec)
36
37mysql> show table status like 't'\G
38*************************** 1. row ***************************
39 Name: t
40 Engine: InnoDB
41 Version: 10
42 Row_format: Dynamic
43 Rows: 9638852
44 Avg_row_length: 215
45 Data_length: 2081423360
46Max_data_length: 0
47 Index_length: 0
48 Data_free: 5242880
49 Auto_increment: 9830256
50 Create_time: 2018-01-09 13:41:19
51 Update_time: 2018-01-09 13:53:06
52 Check_time: NULL
53 Collation: latin1_swedish_ci
54 Checksum: NULL
55 Create_options:
56 Comment:
571 row in set (0.00 sec)
58
59mysql> select count(*) from t;
60+----------+
61| count(*) |
62+----------+
63| 9765630 |
64+----------+
651 row in set (2.42 sec)
66
67mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
68+--------------------------+-------------+--------------+
69| data_length+index_length | data_length | index_length |
70+--------------------------+-------------+--------------+
71| 2081423360 | 2081423360 | 0 |
72+--------------------------+-------------+--------------+
731 row in set (0.00 sec)
74
75mysql>
76mysql>
77mysql> -- now we create some fragmentation by updating and deleting rows.
78mysql> update t set c = repeat('a',floor(100*rand())) where b like '0.1%';
79Query OK, 975722 rows affected (40.49 sec)
80Rows matched: 975722 Changed: 975722 Warnings: 0
81
82mysql> update t set c = repeat('b',floor(500*rand())) where b like '0.5%';
83Query OK, 976602 rows affected (4 min 7.30 sec)
84Rows matched: 976602 Changed: 976602 Warnings: 0
85
86mysql> delete from t where b like '0.7%';
87Query OK, 975635 rows affected (26.86 sec)
88
89mysql> analyze table t;
90+--------+---------+----------+----------+
91| Table | Op | Msg_type | Msg_text |
92+--------+---------+----------+----------+
93| test.t | analyze | status | OK |
94+--------+---------+----------+----------+
951 row in set (0.00 sec)
96
97mysql> show table status like 't'\G
98*************************** 1. row ***************************
99 Name: t
100 Engine: InnoDB
101 Version: 10
102 Row_format: Dynamic
103 Rows: 9762250
104 Avg_row_length: 414
105 Data_length: 4050632704
106Max_data_length: 0
107 Index_length: 0
108 Data_free: 4194304
109 Auto_increment: 9830256
110 Create_time: 2018-01-09 13:41:19
111 Update_time: 2018-01-09 13:58:23
112 Check_time: NULL
113 Collation: latin1_swedish_ci
114 Checksum: NULL
115 Create_options:
116 Comment:
1171 row in set (0.00 sec)
118
119mysql> select count(*) from t;
120+----------+
121| count(*) |
122+----------+
123| 8789995 |
124+----------+
1251 row in set (2.72 sec)
126
127mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
128+--------------------------+-------------+--------------+
129| data_length+index_length | data_length | index_length |
130+--------------------------+-------------+--------------+
131| 4050632704 | 4050632704 | 0 |
132+--------------------------+-------------+--------------+
1331 row in set (0.00 sec)
134
135mysql>
136mysql>
137mysql> -- now we rebuild the table in an attempt to defragment (I).
138mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
139Query OK, 0 rows affected (10 min 13.34 sec)
140Records: 0 Duplicates: 0 Warnings: 0
141
142mysql> analyze table t;
143+--------+---------+----------+----------+
144| Table | Op | Msg_type | Msg_text |
145+--------+---------+----------+----------+
146| test.t | analyze | status | OK |
147+--------+---------+----------+----------+
1481 row in set (0.11 sec)
149
150mysql> show table status like 't'\G
151*************************** 1. row ***************************
152 Name: t
153 Engine: InnoDB
154 Version: 10
155 Row_format: Dynamic
156 Rows: 8588647
157 Avg_row_length: 277
158 Data_length: 2383396864
159Max_data_length: 0
160 Index_length: 0
161 Data_free: 5242880
162 Auto_increment: 9830256
163 Create_time: 2018-01-09 13:58:26
164 Update_time: NULL
165 Check_time: NULL
166 Collation: latin1_swedish_ci
167 Checksum: NULL
168 Create_options:
169 Comment:
1701 row in set (0.00 sec)
171
172mysql> select count(*) from t;
173+----------+
174| count(*) |
175+----------+
176| 8789995 |
177+----------+
1781 row in set (1.89 sec)
179
180mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
181+--------------------------+-------------+--------------+
182| data_length+index_length | data_length | index_length |
183+--------------------------+-------------+--------------+
184| 2383396864 | 2383396864 | 0 |
185+--------------------------+-------------+--------------+
1861 row in set (0.00 sec)
187
188mysql>
189mysql>
190mysql> -- now we rebuild the table in an attempt to defragment (II).
191mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
192Query OK, 8789995 rows affected (2 min 9.64 sec)
193Records: 8789995 Duplicates: 0 Warnings: 0
194
195mysql> analyze table t;
196+--------+---------+----------+----------+
197| Table | Op | Msg_type | Msg_text |
198+--------+---------+----------+----------+
199| test.t | analyze | status | OK |
200+--------+---------+----------+----------+
2011 row in set (0.03 sec)
202
203mysql> show table status like 't'\G
204*************************** 1. row ***************************
205 Name: t
206 Engine: InnoDB
207 Version: 10
208 Row_format: Dynamic
209 Rows: 8601352
210 Avg_row_length: 242
211 Data_length: 2084569088
212Max_data_length: 0
213 Index_length: 0
214 Data_free: 6291456
215 Auto_increment: 9830256
216 Create_time: 2018-01-09 13:58:26
217 Update_time: 2018-01-09 14:10:51
218 Check_time: NULL
219 Collation: latin1_swedish_ci
220 Checksum: NULL
221 Create_options:
222 Comment:
2231 row in set (0.00 sec)
224
225mysql> select count(*) from t;
226+----------+
227| count(*) |
228+----------+
229| 8789995 |
230+----------+
2311 row in set (1.79 sec)
232
233mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
234+--------------------------+-------------+--------------+
235| data_length+index_length | data_length | index_length |
236+--------------------------+-------------+--------------+
237| 2084569088 | 2084569088 | 0 |
238+--------------------------+-------------+--------------+
2391 row in set (0.00 sec)
240
241mysql>