MySQLの容量とか圧縮まわり

Slide 1

Slide 1 text

MySQLの容量とか圧縮まわり 2019/09/13 MySQL Casual Talks vol.12

Slide 2

Slide 2 text

2 アンケート

Slide 3

Slide 3 text

3 どれぐらいのサイズを超えると⾝構える︖

Slide 4

Slide 4 text

アンケート 1.５０GB 2.１００GB 3.３００GB 4.５００GB 4

Slide 5

Slide 5 text

⾃⼰紹介 5 • 三⾕智史（Twitter: @mita2） • MySQL DBA @ どっかのポータルサイト • どきどきブログを書いてます http://mita2db.blogspot.com/

Slide 6

Slide 6 text

お品書き 1. 論理サイズと物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 6

Slide 7

Slide 7 text

お品書き 1. 論理サイズと物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 7

Slide 8

Slide 8 text

論理サイズと物理サイズ説明論理サイズ保存されているデータの量（SHOW TABLE STATUSで出る容量）物理サイズデータファイルのサイズ 8

Slide 9

Slide 9 text

9 論理サイズ ︕＝物理サイズになるときがある

Slide 10

Slide 10 text

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)

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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)

Slide 13

Slide 13 text

2. 統計情報 • サンプリングをもとに全体量を推定 • → 誤差 • サンプリングするページ数 • innodb_stats_persistent_sample_pages をベースに計算 13 https://dev.mysql.com/doc/refman/5.6/ja/innodb-analyze-table-complexity.html より

Slide 14

Slide 14 text

2. 統計情報 • 更新タイミングのラグ • InnoDB では 10%データが変更されたら⾃動更新 • innodb_stats_auto_recalc はデフォルトON • ⼿動で更新するには ANALYZE TABLE 14

Slide 15

Slide 15 text

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)

Slide 16

Slide 16 text

information_schema_stats_expiry • information_schema_stats_expiry • テーブル統計をキャッシュを保持しておく期間 • デフォルトは１⽇（86400秒） • キャッシュがexpireしたら、InnoDBに「最新の統計情報」を取りに⾏く

Slide 17

Slide 17 text

information_schema_stats_expiry • どういう効果を意図したパラメータなんでしょう・・・ • ⾃分は 0 にします 17

Slide 18

Slide 18 text

お品書き 1. 論理サイズと物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 18

Slide 19

Slide 19 text

容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 19

Slide 20

Slide 20 text

容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 20

Slide 21

Slide 21 text

お掃除 • 不要なテーブル、レコード • PKで⼩分けにして消し込み • ⼤きなロックの回避 • レプリケーション遅延の回避 21 mysql> SELECT id FROM mytable WHERE created_at < ‘2019-08-30’; mysql> DELETE FROM mytable WHERE id IN (…)

Slide 22

Slide 22 text

Slide 23

Slide 23 text

お掃除 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

Slide 24

Slide 24 text

お掃除 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`

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 26

Slide 27

Slide 27 text

圧縮 • InnoDB Table Compression • InnoDB Page Compression 27 CREATE TABLE 〜 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=N CREATE TABLE 〜 COMPRESSION=“zlib/lz4/none”

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

InnoDB Table Compression • ブロックサイズはKEY_BLOCK_SIZEの値に • 16K、8K、4K、2K をサポート • ⼩さくなったブロックに圧縮したデータを保存 29 ⾏⾏⾏圧縮解凍⾮圧縮 16K BLOCK 圧縮 8K BLOCK

Slide 30

Slide 30 text

InnoDB Table Compression • ブロック数が減ることはない 30 ⾏⾏⾏⾏⾏⾏⾏⾏⾏⾏⾏⾏間違い→ こうなる→

Slide 31

Slide 31 text

InnoDB Table Compression • 圧縮率が⾼いデータ • ⼩さなKEY_BLOCK_SIZEを選択 31 ⾏⾏⾏⾏⾏⾏圧縮 4K BLOCK ⾮圧縮 16K BLOCK

Slide 32

Slide 32 text

圧縮 • InnoDB Table Compression • InnoDB Page Compression 32 CREATE TABLE 〜 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=N CREATE TABLE 〜 COMPRESSION=“zlib/lz4/none”

Slide 33

Slide 33 text

InnoDB Page Compression • ファイルシステムのHole Punchingの仕組みを利⽤ 33 ⾏(4K) ⾏(4K) ⾏(4K) 2K 2K 2K 2K ⾏(4K) 4K 4K 4K 4K 4K 4K 圧縮 InnoDB File System

Slide 34

Slide 34 text

InnoDB Page Compression • ⾒かけ上のサイズ • 実際使っている容量 34 $ du --apparent-size * 11264 no_compression.ibd 12288 page_compression.ibd $ du * 11264 no_compression.ibd 8760 page_compression.ibd

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 36

Slide 37

Slide 37 text

主キーと容量⼩さな主キー 37 CREATE TABLE `sbtest1` ( ìd` 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 (ìd`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB CREATE TABLE `sbtest2` ( ìd` 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 (ìd`,`k`,`c`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB ⼤きな主キー

Slide 38

Slide 38 text

主キーと容量 496M 38 CREATE TABLE `sbtest1` ( ìd` 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 (ìd`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB CREATE TABLE `sbtest2` ( ìd` 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 (ìd`,`k`,`c`), KEY `k_1` (`k`), KEY `c_1` (`c`), KEY `pad_1` (`pad`) ) ENGINE=InnoDB 784M

Slide 39

Slide 39 text

主キーと容量 • InnoDBのセカンダリインデックスは主キーを含む • ⼤きな主キー x たくさんのインデックスは避ける 39 http://nippondanji.blogspot.com/2010/10/innodb.html より

Slide 40

Slide 40 text

容量が⾜らなくなったときに確認すること 1. お掃除 2. 圧縮 3. 巨⼤な主キー 4. 断⽚化の解消 40

Slide 41

Slide 41 text

断⽚化の解消 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

Slide 42

Slide 42 text

断⽚化の解消 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

Slide 43

Slide 43 text

断⽚化の解消 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

Slide 44

Slide 44 text

断⽚化の解消 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

Slide 45

Slide 45 text

DELETE⽅法の違い・Data_free にカウントされる • Data_free にカウントされない 45 mysql> DELETE FROM sbtest1 WHERE id < 500000; mysql> SHOW TABLE STATUS ¥G Data_free: 116391936 # 116M mysql> DELETE FROM sbtest1 WHERE (id % 70 <= 35); mysql> SHOW TABLE STATUS ¥G Name: sbtest1 Data_free: 37748736 # 37M!?

Slide 46

Slide 46 text

背景 • http://nippondanji.blogspot.com/2010/09/innodb.html 46 InnoDBのテーブルスペース内では、データは16KBのページ単位で管理されており、さらに64 個の連続するページから成るエクステントにグループ化されている。InnoDBテーブルからデータを削除すると、対応するBツリーインデックスが縮⼩される。これによって、他のテーブルに対して空き領域が再利⽤できるかどうかは、削除のパターンがテーブルスペースの個々のページやエクステントを解放するかどうかによる。エクステントが解放された場合には、そのエクステントは他のテーブルによって再利⽤されるが、個々のページやページの⼀部だけが開放されただけの場合には他のテーブルによって再利⽤されることはない。つまり、Data_freeは使⽤されていないエクステントのサイズであり、ページ単位で⾒ると空き領域がある場合があるということだ。しかしながら、領域を再利⽤できるかどうかはエクステント全体が空いているかどうかで決まるため、Data_freeはほぼ正確な空き領域を⽰していると⾔える。

Slide 47

Slide 47 text

innodb_space 47

Slide 48

Slide 48 text

断⽚化の解消 48 雑にデータを消してると、思ったより断⽚化してるかも

Slide 49

Slide 49 text

49 アンケート結果開票

Slide 50

Slide 50 text

アンケート 50

Slide 51

Slide 51 text

お品書き 1. 論理サイズと物理サイズ 2. 容量が⾜らなくなったら確認すること 3. お知らせ 51

Slide 52

Slide 52 text

お知らせ • MySQL Casual Slack • https://mysql-casual- slackin.herokuapp.com • dbtech showcase 2019 • ９⽉25 〜 27 @ 秋葉原 • https://www.db-tech- showcase.com/dbts/tokyo 52

Slide 53

Slide 53 text

53 まとめ

Slide 54

Slide 54 text

まとめ 54 MySQLは⽤法・⽤量を守ってお使いください MySQL 8.0 で information_schema_stats_expiry が出たよ

Slide 55

Slide 55 text

55 Enjoy MySQL