MySQLの容量とか圧縮まわり

Transcript

1. MySQLの容量とか圧縮まわり 2019/09/13 MySQL Casual Talks vol.12
2. 2 アンケート
3. 3 どれぐらいのサイズを 超えると⾝構える︖
4. アンケート 1.５０GB 2.１００GB 3.３００GB 4.５００GB 4
5. ⾃⼰紹介 5 • 三⾕ 智史（Twitter: @mita2） • MySQL DBA @ どっかのポータルサイト • どきどきブログを書いてます http://mita2db.blogspot.com/
6. お品書き 1. 論理サイズ と 物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 6
7. お品書き 1. 論理サイズ と 物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 7
8. 論理サイズ と 物理サイズ 説明 論理サイズ 保存されているデータの量 （SHOW TABLE STATUSで出る容量） 物理サイズ データファイルのサイズ 8
9. 9 論理サイズ ︕＝ 物理サイズ になるときがある
10. 1. 断⽚化 • 物理ファイルは（⾃動的に）縮まらない 10 # ls -alh sbtest/sbtest1.ibd -rw-r----- mysql mysql 240M Aug 28 sbtest/sbtest1.ibd mysql> DELETE FROM sbtest.sbtest1; Query OK, 1000000 rows affected (11.74 sec)
11. 1. 断⽚化 • テーブルをリビルドすると解放される 11 $ ls -alh sbtest/sbtest1.ibd -rw-r----- mysql mysql 128K Aug 28 sbtest/sbtest1.ibd mysql> ALTER TABLE sbtest.sbtest1 ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
12. 2. 論理サイズは統計情報に基づく • 論理サイズ • SHOW TABLE STATUS • information_schema.TABLES 12 mysql> SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sbtest1'; +------------+-------------+--------------+-----------+ | TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE | +------------+-------------+--------------+-----------+ | sbtest1 | 225132544 | 16269312 | 4194304 | +------------+-------------+--------------+-----------+ 1 row in set (0.00 sec)
13. 2. 統計情報 • サンプリングをもとに全体量を推定 • → 誤差 • サンプリングするページ数 • innodb_stats_persistent_sample_pages をベースに計算 13 https://dev.mysql.com/doc/refman/5.6/ja/innodb-analyze-table-complexity.html より
14. 2. 統計情報 • 更新タイミングのラグ • InnoDB では 10%データが変更されたら⾃動更新 • innodb_stats_auto_recalc はデフォルトON • ⼿動で更新するには ANALYZE TABLE 14
15. MySQL 8から⼊った、余計な︖ヤーツ 15 mysql> DELETE FROM sbtest.sbtest1 ; Query OK, 1000000 rows affected (9.53 sec) mysql> SELECT TABLE_ROWS FROM information_schema.tables WHERE table_name = 'sbtest1'; +------------+ | TABLE_ROWS | +------------+ | 986400 | +------------+ 1 row in set (0.01 sec)
16. information_schema_stats_expiry • information_schema_stats_expiry • テーブル統計をキャッシュを保持しておく期間 • デフォルトは１⽇（86400秒） • キャッシュがexpireしたら、InnoDBに 「最新の統計情報」を取りに⾏く
17. information_schema_stats_expiry • どういう効果を意図したパラメータなんでしょう・・・ • ⾃分は 0 にします 17
18. お品書き 1. 論理サイズ と 物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 18
19. 容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 19
20. 容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 20
21. お掃除 • 不要なテーブル、レコード • PKで⼩分けにして消し込み • ⼤きなロックの回避 • レプリケーション遅延の回避 21 mysql> SELECT id FROM mytable WHERE created_at < ‘2019-08-30’; mysql> DELETE FROM mytable WHERE id IN (…)
22. お掃除 • 過去に⼀度も使われてないインデックス 22 mysql> SELECT * FROM sys.schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | sbtest | sbtest1 | k_1 | | sbtest | sbtest2 | k_2 | | sbtest | sbtest3 | k_3 | +---------------+-------------+------------+ 6 rows in set (0.02 sec)
23. お掃除 23 • 冗⻑なインデックス CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `k_c_1` (`k`,`c`) ) ENGINE=InnoDB
24. お掃除 24 mysql> SELECT * FROM sys.schema_redundant_indexes ¥G *************************** 1. row *************************** table_schema: sbtest table_name: sbtest1 redundant_index_name: k_1 redundant_index_columns: k redundant_index_non_unique: 1 dominant_index_name: k_c_1 dominant_index_columns: k,c dominant_index_non_unique: 1 subpart_exists: 0 sql_drop_index: ALTER TABLE `sbtest`.`sbtest1` DROP INDEX `k_1`
25. INVISIBLE INDEX • MySQL 8.0 の新機能 • INVISIBLEにしたインデックスは使われなくなる • DROPする前の保険 • INVISIBLE 状態でもインデックスの更新は継続 • 問題があれば、VISIBLEに戻すだけ 25 mysql> ALTER TABLE t ALTER INDEX idx_c1 [INVISIBLE|VISIBLE]; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
26. 容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 26
27. 圧縮 • InnoDB Table Compression • InnoDB Page Compression 27 CREATE TABLE 〜 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=N CREATE TABLE 〜 COMPRESSION=“zlib/lz4/none”
28. InnoDB Table Compression • 以下の資料が詳しいです • SH2さんのブログ https://sh2.hatenablog.jp/entries/2009/07/05 https://sh2.hatenablog.jp/entries/2009/06/28 • 瀬島さんの資料 https://www.slideshare.net/takanorisejima/innodb- table-compression 28
29. InnoDB Table Compression • ブロックサイズはKEY_BLOCK_SIZEの値に • 16K、8K、4K、2K をサポート • ⼩さくなったブロックに圧縮したデータを保存 29 ⾏ ⾏ ⾏ 圧縮 解凍 ⾮圧縮 16K BLOCK 圧縮 8K BLOCK
30. InnoDB Table Compression • ブロック数が減ることはない 30 ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ 間違い→ こうなる→
31. InnoDB Table Compression • 圧縮率が⾼いデータ • ⼩さなKEY_BLOCK_SIZEを選択 31 ⾏ ⾏ ⾏ ⾏ ⾏ ⾏ 圧縮 4K BLOCK ⾮圧縮 16K BLOCK
32. 圧縮 • InnoDB Table Compression • InnoDB Page Compression 32 CREATE TABLE 〜 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=N CREATE TABLE 〜 COMPRESSION=“zlib/lz4/none”
33. InnoDB Page Compression • ファイルシステムのHole Punchingの仕組みを利⽤ 33 ⾏(4K) ⾏(4K) ⾏(4K) 2K 2K 2K 2K ⾏(4K) 4K 4K 4K 4K 4K 4K 圧縮 InnoDB File System
34. InnoDB Page Compression • ⾒かけ上のサイズ • 実際使っている容量 34 $ du --apparent-size * 11264 no_compression.ibd 12288 page_compression.ibd $ du * 11264 no_compression.ibd 8760 page_compression.ibd
35. InnoDB Page Compression • cpするとhole punchingされないファイルになってしまう 35 $ cp page_compression.ibd page_compression.copied $ du * 11216 page_compression.copied 7364 page_compression.ibd $ md5sum page_compression.copied page_compression.ibd 736477eee71dfc51e6f217945c5a6ed449c6 page_compression.copied 736577eee71dfc51e6f217945c5a6ed449c6 page_compression.ibd
36. 容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 36
37. 主キーと容量 ⼩さな主キー 37 CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB CREATE TABLE `sbtest2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`,`k`,`c`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB ⼤きな主キー
38. 主キーと容量 496M 38 CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB CREATE TABLE `sbtest2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`,`k`,`c`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB 784M
39. 主キーと容量 • InnoDBのセカンダリインデックスは主キーを含む • ⼤きな主キー x たくさんのインデックス は避ける 39 http://nippondanji.blogspot.com/2010/10/innodb.html より
40. 容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 40
41. 断⽚化の解消 41 もともと１００万⾏だったテーブルのうち５０万⾏を消した mysql> SHOW TABLE STATUS ¥G *************************** 1. row *************************** Name: sbtest1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 493200 # 50万⾏ Avg_row_length: 295 Data_length: 145883136 Max_data_length: 0 Index_length: 16138240 Data_free: 116391936 # 116M
42. 断⽚化の解消 42 $ sudo ls -alh /var/lib/mysql/sbtest total 273M drwxr-x--- 2 mysql mysql 25 Sep 12 21:41 . drwxr-x--x 7 mysql mysql 4.0K Sep 12 21:39 .. -rw-r----- 1 mysql mysql 272M Sep 12 21:44 sbtest1.ibd $ sudo ls -alh /var/lib/mysql/sbtest total 141M drwxr-x--- 2 mysql mysql 25 Sep 12 21:44 . drwxr-x--x 7 mysql mysql 4.0K Sep 12 21:39 .. -rw-r----- 1 mysql mysql 140M Sep 12 21:44 sbtest1.ibd
43. 断⽚化の解消 43 さっきとは違うDELETE⽂で５０万⾏ 消しました。 mysql> SHOW TABLE STATUS ¥G *************************** 1. row *************************** Name: sbtest1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 491145 # 50万⾏ Avg_row_length: 457 Data_length: 224788480 Max_data_length: 0 Index_length: 16138240 Data_free: 37748736 # 37M
44. 断⽚化の解消 44 $ sudo ls -alh /var/lib/mysql/sbtest total 273M drwxr-x--- 2 mysql mysql 25 Sep 12 21:41 . drwxr-x--x 7 mysql mysql 4.0K Sep 12 21:39 .. -rw-r----- 1 mysql mysql 272M Sep 12 21:44 sbtest1.ibd $ sudo ls -alh /var/lib/mysql/sbtest total 141M drwxr-x--- 2 mysql mysql 25 Sep 12 21:49 . drwxr-x--x 7 mysql mysql 4.0K Sep 12 21:46 .. -rw-r----- 1 mysql mysql 140M Sep 12 21:49 sbtest1.ibd
45. DELETE⽅法の違い ・Data_free にカウントされる • Data_free にカウントされない 45 mysql> DELETE FROM sbtest1 WHERE id < 500000; mysql> SHOW TABLE STATUS ¥G <snip> Data_free: 116391936 # 116M mysql> DELETE FROM sbtest1 WHERE (id % 70 <= 35); mysql> SHOW TABLE STATUS ¥G Name: sbtest1 <snip> Data_free: 37748736 # 37M!?
46. 背景 • http://nippondanji.blogspot.com/2010/09/innodb.html 46 InnoDBのテーブルスペース内では、データは16KBのページ単位で管理されており、さらに64 個の連続するページから成るエクステントにグループ化されている。InnoDBテーブルからデー タを削除すると、対応するBツリーインデックスが縮⼩される。これによって、他のテーブルに 対して空き領域が再利⽤できるかどうかは、削除のパターンがテーブルスペースの個々のページ やエクステントを解放するかどうかによる。エクステントが解放された場合には、そのエクステ ントは他のテーブルによって再利⽤されるが、個々のページやページの⼀部だけが開放されただ けの場合には他のテーブルによって再利⽤されることはない。つまり、Data_freeは使⽤され ていないエクステントのサイズであり、ページ単位で⾒ると空き領域がある場合があるというこ とだ。しかしながら、領域を再利⽤できるかどうかはエクステント全体が空いているかどうかで 決まるため、Data_freeはほぼ正確な空き領域を⽰していると⾔える。
47. innodb_space 47
48. 断⽚化の解消 48 雑にデータを消してると、思ったより断⽚化してるかも
49. 49 アンケート結果 開票
50. アンケート 50
51. お品書き 1. 論理サイズ と 物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 51
52. お知らせ • MySQL Casual Slack • https://mysql-casual- slackin.herokuapp.com • dbtech showcase 2019 • ９⽉25 〜 27 @ 秋葉原 • https://www.db-tech- showcase.com/dbts/tokyo 52
53. 53 まとめ
54. まとめ 54 MySQLは⽤法・⽤量を守ってお使いください MySQL 8.0 で information_schema_stats_expiry が出たよ
55. 55 Enjoy MySQL