MySQL の InnoDB テーブルの断片化の影響を調べてみた

準備

• テーブルを作成して1千万件データをロードする

$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=10000000 \
 --mysql-host=aurora01.******.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=****** \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare

• テーブルの件数と空き領域を確認する

mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   3145728 |    9867937 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

• 断片化させるプロシージャを作成する

mysql> delimiter //
mysql> create procedure fragment_sbtest1(in x int, in y int)
begin
 while x < y do
  delete from mydb.sbtest1 where id = x;
  set x = x + 2;
 end while;
end
//
mysql> delimiter ;

クエリ実行時間を測定する

1千万件

• 実行時間は1分15秒程度

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 15.50 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 17.92 sec)

5百万件（断片化状態）

• データを歯抜けに削除する

mysql> call fragment_sbtest1(1, 10000000);

• テーブルの件数と空き領域を確認する

mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   7340032 |    4922775 |
+--------------+------------+-----------+------------+
1 row in set (0.02 sec)

• 実行時間は1分10秒程度でほぼ変化なし。

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 8.51 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 12.69 sec)

5百万件（断片化後）

• 断片化を解消する

mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (34.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

• テーブルの件数と空き領域を確認する

mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   4194304 |    5154227 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

• 実行時間は35秒程度と半分程度に短縮。

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (35.31 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (34.81 sec)

環境

"select count(pad) from sbtest1" で実行時間を測定したインスタンスは以下の通り。性能差を測定するためミニマムな環境にした。

• db.t2.small
• query_cache_type: 0
• innodb_buffer_pool_size: 67108864