MySQL InnoDBの領域最適化

この記事はMySQL Advent Calendar 2019の25日目の記事です。

MySQLのInnoDBの領域最適化について最近確認し直したことをまとめたいと思います.

本エントリの内容

本エントリでは下記の２点をランダムInsertにより格納効率が悪くなったデータを最適化するサンプルにそって確認します.

• ページ分割が発生しディスク上の格納効率が悪くなった際の次の確認方法

  • 特定テーブルスペースのディスク上の配置
  • 特定テーブルのbuffer_pool上のデータ格納状況

• innodb_fill_factorの設定による動作

物理配置の詳細については本エントリではとりあげませんので下記を参照してください.
https://blog.jcole.us/innodb/

検証条件

• MySQL: 5.7.28
• テストデータ: 主キーがランダム,セカンダリインデックスとして追記(created), ランダム(id)の列をそれぞれ用意

create table users(
    uuid varchar(36) NOT NULL PRIMARY KEY,
    id varchar(36),
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (id),
    INDEX (created)
);

テストデータ作成は下記にて

for i in {1..100000}; do mysql -u root -e "INSERT INTO test.users set uuid=uuid(), id=uuid();"; done

• その他: innodb_buffer_pool_instances: 1 (単純化のため)

最適化の動作確認

次の順に操作を行い最適化/断片化の様子を確認します.

• ランダムInsert
• mysqldump
• optimize

ランダムInsert

MySQLが苦手とされるランダムInsertを領域管理の面から確認して行きたいと思います.
ランダムInsert後の初期状態のディスク容量およびbuffer poolの状態を確認します.

$ sudo ls -lah /var/lib/mysql/test/users.ibd
-rw-r-----. 1 mysql mysql 44M 12月 23 16:52 /var/lib/mysql/test/users.ibd

mysql> select * from sys.schema_table_statistics_with_buffer where table_name='users'\G
*************************** 1. row ***************************
              table_schema: test
                table_name: users
              rows_fetched: 0
             fetch_latency: 0 ps
             rows_inserted: 100000
            insert_latency: 3.05 s
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 14
                   io_read: 1.35 KiB
           io_read_latency: 20.28 us
         io_write_requests: 2407
                  io_write: 37.27 MiB
          io_write_latency: 26.62 ms
          io_misc_requests: 510
           io_misc_latency: 680.80 ms
   innodb_buffer_allocated: 29.03 MiB
        innodb_buffer_data: 21.38 MiB
        innodb_buffer_free: 7.65 MiB
       innodb_buffer_pages: 1858
innodb_buffer_pages_hashed: 289
   innodb_buffer_pages_old: 0
 innodb_buffer_rows_cached: 100637
1 row in set (0.04 sec)

※クエリは一定の負荷が発生します.

続いて、Jeremy Cole氏のinnodb_rubyを利用してibdファイルの物理配置を確認します.
uuidを代表とするランダムinsertではページ分割の発生によりfree_spaceが増え、データの充填率が低くなりますがこれを実際に確認します.

充填率/レイアウトの確認にはデータが少量の際は、 space-extents-illustrate 量が多い場合や実際の充填率分布を細かくみたい場合は、space-index-pages-free-plot が有用です.

$ innodb_space -f ../users.ibd space-extents-illustrate

指定のテーブルスペースに対する物理配置を表しています.
Index 59が主キーのuuid, Index 69がid, Index 61がcreatedで、ランダム挿入となっている主キーおよびcreatedに対して隙間がある様子が観測できます.
一方でcreatedは追記となるためこちらは断片化せず容量が増加します.

If the insertions into an index are always ascending and records are deleted only from the end, the InnoDB filespace management algorithm guarantees that fragmentation in the index does not occur.

同一行に連続して配置されて、領域確保の単位であるエクステントが専有エクステントとして各セグメント毎にエクステントで領域確保される様子が合わせて確認できます.

$ innodb_space -f ../users.ibd space-index-pages-free-plot

横軸はページ数,縦軸は空きスペースを示します。innodb_page_sizeが16Kですので、この例では主キーに対して最大約50%程度の空きスペースが発生していることが読み取れます.
ページ分割が期待通りに発生しています.

mysqldumpでデータを入れ直す

--order-by-primary を付与してmysqldumpしたものを入れ直し、同様にデータの最適化状態を確認します.

$ sudo ls -lah /var/lib/mysql/test/users.ibd
-rw-r-----. 1 mysql mysql 32M 12月 23 23:44 /var/lib/mysql/test/users.ibd

mysql> select * from sys.schema_table_statistics_with_buffer where table_name='users'\G
*************************** 1. row ***************************
              table_schema: test
                table_name: users
              rows_fetched: 0
             fetch_latency: 0 ps
             rows_inserted: 100000
            insert_latency: 901.03 ms
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 19
                   io_read: 1.81 KiB
           io_read_latency: 16.79 us
         io_write_requests: 1480
                  io_write: 22.79 MiB
          io_write_latency: 15.63 ms
          io_misc_requests: 132
           io_misc_latency: 224.42 ms
   innodb_buffer_allocated: 22.52 MiB
        innodb_buffer_data: 21.35 MiB
        innodb_buffer_free: 1.17 MiB
       innodb_buffer_pages: 1441
innodb_buffer_pages_hashed: 290
   innodb_buffer_pages_old: 0
 innodb_buffer_rows_cached: 100479
1 row in set (0.04 sec)

ディスク上の領域が最適化され、合わせて次のようにbuffer_pool上の領域が削減されています. buffer pool上の最適化によりいままでbuffer_pool上にのらなかった他のデータをキャッシュヒットさせることが可能となり場合によっては性能上大きな改善が期待できます.

• innodb_buffer_pages: 1858 > 1441
• innodb_buffer_allocated: 29.03 MiB > 22.52 MiB

$ innodb_space -f ../users.ibd space-extents-illustrate

$ innodb_space -f ../users.ibd space-index-pages-free-plot

こちらのグラフを見ると主キーについては1/16の空きスペースを計画的に確保しているのに対し、セカンダリインデックスは同じ空きスペースは確保せず隙間を空けずに最適化されていることが観測できます.
これは下記のデフォルトのinnodb_fill_factor 0 の動作と整合性があります

An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.

短期的には容量が節約されますがセカンダリインデックスに空きスペースがないため断片化が発生しやすい状態となります.

optimizeを実施

MySQL 5.6.17以降は通常またはpartitioningされたInnoDBについてはonlineでoptimizeが実行できます.

mysql>  alter table users ENGINE=innodb;
Query OK, 0 rows affected (2.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from sys.schema_table_statistics_with_buffer where table_name='users'\G
*************************** 1. row ***************************
              table_schema: test
                table_name: users
              rows_fetched: 100000
             fetch_latency: 13.99 ms
             rows_inserted: 100000
            insert_latency: 901.03 ms
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 64
                   io_read: 6.09 KiB
           io_read_latency: 64.88 us
         io_write_requests: 1467
                  io_write: 22.58 MiB
          io_write_latency: 17.58 ms
          io_misc_requests: 142
           io_misc_latency: 173.55 ms
   innodb_buffer_allocated: 22.45 MiB
        innodb_buffer_data: 21.36 MiB
        innodb_buffer_free: 1.09 MiB
       innodb_buffer_pages: 1437
innodb_buffer_pages_hashed: 0
   innodb_buffer_pages_old: 0
 innodb_buffer_rows_cached: 100573
1 row in set (0.04 sec)

各セグメントが連続領域として配置されセグメントの末尾に空きスペースが配置されます.

innodb_fill_factorの影響を確認する

無駄な容量は減らしたいものの最適化後に急に容量が増えない方が望ましいケースがあるかもしれません. これを制御するパラメータの動作を確認します.

mysql> show variables like 'innodb_fill_factor';
mysql> set global innodb_fill_factor=95;
mysql>  alter table users ENGINE=innodb;
Query OK, 0 rows affected (2.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

この状態で先程のspace-index-pages-free-plotを確認します.

セカンダリインデックスの空きスペースもコントロールされました.

An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.

この例だと現実的には追記のみ発生し空きスペースが必要ないcreatedカラムにも一定の空きスペースが確保されることになるため全体でのトレードオフを考慮して運用する必要がありそうです.

まとめ

ランダム挿入されたデータがoptimizeによってディスク上のスペースおよびbuffer_pool上のサイズが最適化されることを確認しました
バッファプール上のサイズは、sys.schema_table_statistics_with_bufferで、ディスク上でデータがどのように分割されているかはinnodb_spaceのspace-extents-illustrate や space-index-pages-free-plot で確認しました.

セカンダリインデックスがランダム挿入の場合と追記の場合のデータの充填の様子を確認しました.

本記事では扱わなかったデータが削除大量にされているときはもちろん、ランダム挿入されるインデックスが多い場合、特に最適化をしたときの効果が期待されます.

innodb_fill_factorの動作: デフォルトでクラスタインデックスのみ1/16の空きを確保、その他の値に設定した際はセカンダリインデックスも含め空きスペースを作る挙動を確認しました.

参考

関連するページヘのリンクを記載します.

• 領域最適化

  • https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
  • https://dev.mysql.com/doc/refman/5.7/en/sorted-index-builds.html
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html
  • http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0035
  • https://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

• 領域管理

  • https://mysqlserverteam.com/innodb-tablespace-space-management/
  • https://www.percona.com/blog/2017/04/10/innodb-page-merging-and-page-splitting/
  • https://blog.jcole.us/innodb/
  • https://dev.mysql.com/worklog/task/?id=6747

• uuid on MySQL (uuidの容量効率や性能をあげる議論など):

  • https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/
  • https://www.percona.com/blog/2015/04/03/illustrating-primary-key-models-in-innodb-and-their-impact-on-disk-usage/
  • https://www.percona.com/blog/2017/05/03/uuid-generated-columns/
  • https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

• その他

  • https://www.percona.com/doc/percona-xtradb-cluster/LATEST/diagnostics/innodb_fragmentation_count.html
  • https://planet.mysql.com/entry/?id=2058688