MySQLにWEBアプリのログを保存しているケースの8割くらいが幸せになる方法

2019/03/30 PHPerKaigi 2019

Transcript

1. MySQLにWEBアプリのログを保存 しているケースの8割くらいが幸せに なる方法 略してMySQLが幸せになる方法 2019/03/30 yoku0825 PHPerKaigi 2019
2. MySQLにWEBアプリのログを保存しているケースの8割 くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服する方法を考えて幸せ になりたい 1/87
3. あっすいませ んPHPの話出 てきません 2/87
4. Definition & Classification WEBアプリのログ？ WEBサーバーのアクセスログ…はないと思う ‐ アプリケーションのエラーログやデバッグログ…もないと思う ‐ エンドユーザーの挙動を表現するログ 集計されて何かに利用されるもの 何かの証憑として保管され続けなければいけないもの ‐ 分類は独断と偏見によるものです、環境によって他にもある はず 3/87
5. TL;DR そのログは本当にRDBMSに保管 し続ける のが幸せなのか を見つめる 保管からしばらくはRDBMSがそれなりに適していると思う いつ、どんなタイミングで使われるのかを整理して、 RDBMSの 外に逃がす という視点を持つと8割がた幸せにな れます 4/87
6. Definition & Classification ログレコード INSERT されて以降 UPDATE されることがないレコードでRDBMS的な制約が 要らない ホットログ ログレコード のうち、オンライントラフィックで参照されるもの コールドログ ホットログ でない ログレコード (オンラインで参照されないもの) ログテーブル ログレコード だけで満たされたテーブル 分類は独断と偏見によるものです、環境によって他にもある はず 5/87
7. はい 6/87
8. ログテーブル 作ったことが ない人？ 7/87
9. ログテーブ ル 嫌いな 人？ 8/87
10. ログテーブ ル 大好きな 人？ 9/87
11. MySQLにWEBアプリのログを保存しているケースの8割 くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服する方法を考えて幸せ になりたい 10/87
12. MySQLにWEBアプリのログを保存しているケースの8割 くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服する方法を考えて 幸 せになりたい 11/87
13. Let’s think 12/87
14. ＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 実はPHPerではない ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 13/87
15. Definition & Classification d 使う? 14/87
16. ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる トランザクションの保護が受けられる 15/87
17. ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と 親和性が高い = 開発しやすい 一覧表示 集計 細かい条件で検索したり、秒単位で指定して削除ができたり 細かいことを考えずに永続化して一元管理できるデータストアといえばこれ ‐ RDBMSがそもそも備えている検索のための機能が使える インデックス, バッファプールによるI/O効率の向上 パラレルスキャン (ただし現在は SELECT COUNT(*) に限る…) 全文検索 (∩ﾟдﾟ)ｱｰｱｰ ‐ トランザクションの保護が受けられる 16/87
18. ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と 親和性が高い = 開発しやすい 一覧表示 集計 細かい条件で検索したり、秒単位で指定して削除ができたり 細かいことを考えずに永続化して一元管理できるデータストアといえばこれ ‐ RDBMSがそもそも備えている検索のための機能が使える インデックス, バッファプールによるI/O効率の向上 パラレルスキャン (ただし現在は SELECT COUNT(*) に限る…) 全文検索 (∩ﾟдﾟ)ｱｰｱｰ ‐ トランザクションの保護が受けられる 17/87
19. ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる トランザクションの保護が受けられる 書き込み失敗を検出できる 「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応 ‐ 書き込みに成功した以上は必ず ログレコード が残っていることを保 証できる ‐ 一貫性のある読み取りを勝手に実装してくれている ログローテーションをしなくても 「バッチ処理中に新たなログが追加された ら？」「書き込み途中のログを読みこんでしまったら？」 なんてことを考えずに 上から下まで読める ‐ 18/87
20. ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる トランザクションの保護が受けられる 書き込み失敗を検出できる 「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応 ‐ 書き込みに成功した以上は必ず ログレコード が残っていることを保 証できる ‐ 一貫性のある読み取りを勝手に実装してくれている ログローテーションをしなくても 「バッチ処理中に新たなログが追加された ら？」「書き込み途中のログを読みこんでしまったら？」 なんてことを考えずに 上から下まで読める ‐ 19/87
21. ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが 決まってしまう トランザクションの保護を受けるための仕組みが重厚すぎる 20/87
22. ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが 決まってしまう 往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」 という訳にはいかない ‐ 銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど… ‐ トランザクションの保護を受けるための仕組みが重厚 21/87
23. ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが 決まってしまう 往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」 という訳にはいかない ‐ 銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど… ‐ トランザクションの保護を受けるための仕組みが重厚 22/87
24. ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが 決まってしまう トランザクションの保護を受けるための仕組みが重厚すぎる ログレコード そのものの他にREDOログもUNDOログも要るしイン デックスはソート済みのデータの複製だし効率的にデータ構造を維持 するためにページには空き領域を残しておかないといけないし… ‐ ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー バーヘッドは大きい ‐ 23/87
25. ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが 決まってしまう トランザクションの保護を受けるための仕組みが重厚すぎる ログレコード そのものの他にREDOログもUNDOログも要るしイン デックスはソート済みのデータの複製だし効率的にデータ構造を維持 するためにページには空き領域を残しておかないといけないし… ‐ ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー バーヘッドは大きい ‐ 24/87
26. メリットとデメリットの整理 ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる ‐ トランザクションの保護が受けられる ‐ ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まって しまう ‐ トランザクションの保護を受けるための仕組みが重厚すぎる ‐ 25/87
27. MySQLにWEBアプリのログを保存しているケースの8割 くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服 する方法を考えて幸 せになりたい 26/87
28. ( ﾟдﾟ) 27/87
29. メリットとデメリットの整理 ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる ‐ トランザクションの保護が受けられる ‐ ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まって しまう ‐ トランザクションの保護を受けるための仕組みが重厚すぎる ‐ 28/87
30. メリットとデメリットの整理 ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる ‐ トランザクションの保護が受けられる ‐ ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まって しまう ‐ トランザクションの保護を受けるための仕組みが重厚すぎる ‐ 29/87
31. ( ﾟдﾟ) 30/87
32. メリットの方 に括弧書きし てみよう 31/87
33. ログをRDBMS（主語が大きい）に保管するメリット ( SQLでアクセスしたい時に ) SQLでアクセスできる ( トランザクションの保護を受けたい時に )トランザクショ ンの保護が受けられる 32/87
34. ログをRDBMS（主語が大きい）に保管するメリット ( SQLでアクセスしたい時に ) SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と 親和性が高い = 開発しやすい 一覧表示 しなくていい時 集計 しなくていい時 細かい条件で検索したり、秒単位で指定して削除ができたり しなくていい時 細かいことを考えずに永続化して一元管理できるデータストアといえばこれ 他に もっとログ用に望ましいデータストアがある時 ‐ RDBMSがそもそも備えている検索のための機能が使える インデックス, バッファプールによるI/O効率の向上 を期待しなくていい時 パラレルスキャン (ただし現在は `SELECT COUNT(*)` に限る…) 全文検索 ‐ (トランザクションの保護を受けたい時に)トランザクション の保護が受けられる 33/87
35. ログをRDBMS（主語が大きい）に保管するメリット (SQLでアクセスしたい時に) SQLでアクセスできる ( トランザクションの保護を受けたい時に )トランザクショ ンの保護が受けられる 書き込み失敗を検出 しなくていい時 「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応 しなくて いい時 ‐ 書き込みに成功した以上は必ず ログレコード が残っていることを保 証でき なくてもいい時 ‐ 一貫性のある読み取りを勝手に実装してくれている ことがなくても いい時 ログローテーションをしていれば 「バッチ処理中に新たなログが追加された ら？」「書き込み途中のログを読みこんでしまったら？」 なんてことを考えずに 上から下まで読めたりするんでは？ ‐ 34/87
36. Definition & Classification (again) ログレコード INSERT されて以降 UPDATE されることがないレコードでRDBMS的な制約が 要らない ホットログ ログレコード のうち、オンライントラフィックで参照されるもの コールドログ ホットログ でない ログレコード (オンラインで参照されないもの) ログテーブル ログレコード だけで満たされたテーブル 分類は独断と偏見によるものです、環境によって他にもある はず 35/87
37. ホットログ 「 ログレコード のうち、オンライントラフィックで参照さ れるもの」と定義すると SQLでアクセスするメリットは美味しい 自動でLRUによるキャッシュがかかるのも美味しい ‐ 必ずしもトランザクションで保護される必要はあるのか？ UPDATE が走らないのであれば、一度Diskに書き込み終えたらもうク ラッシュセーフと考えられるのでは？ ‐ スイッチしてから読めば、読み取り一貫性のケアは要らないのでは？ 念のため読み取りロックフリーならなお良し？ ‐ 36/87
38. ( ﾟдﾟ)ﾊｯ! 37/87
39. ( ﾟдﾟ) ま いあいさｍ 38/87
40. （運用が複雑化 するのでオスス メしません） 39/87
41. コールドログ 「 ログレコード のうち、オンライントラフィックで参照さ れないもの」と定義すると SQLは要らない？ オンラインからは参照されないにして集計処理的なものは走る？ 頻度、要求レスポンスタイムとのと相談 ‐ トランザクションの保護は要らなさそう 更新もされない、リアルタイムな参照もされない ‐ 読み取りの競合もずらそうと思えばずらせるんじゃ？ ‐ 十分長い期間保管されることを考えるとむしろ容量効率を優 先した方がいいはず ログをサマライズした結果を長期保存することで容量をコン パクトにすることもできる 40/87
42. ( ﾟдﾟ)ﾊｯ! 41/87
43. ( ﾟдﾟ) 圧縮済 まいあいさｍ 42/87
44. （運用が超複雑 化するのでオス スメしません） 43/87
45. どんなものが良いの？ SQLアクセス トランザクション 合いそうなデータスト ア not a log o o RDBMS Hot log o x 時系列DB? Treasure Data? Cold Log x x HDFSとか? MySQLだけが守備範囲の俺には答えは出ていません 44/87
46. ただし データストアを変えるタイミングでは永続性は上位レイヤー でのサポートが必要 エクスポートは正しい選択肢だと思うけれど、エクスポート処理が確 実に行われたことはちゃんと担保してやらないといけない ‐ SELECT してエクスポートして書き出した内容が正しいことを検証し てから DELETE なり DROP なり エクスポート先がクラッシュアンセーフならば、書き出しに失敗したらデータの フェッチからやり直す ‐ 45/87
47. レコードがホットログに変わるタイミングでテーブルを移 してInnoDB圧縮に変える 「n日間は更新があるかも知れないけど、それ以降変更され なくなる」ようなものはありませんか？ RDBMS的な制約がないことが前提ではある（テーブルを分けるとナ チュラルキーが死ぬ） ‐ 退会会員のデータとか、無効判定された売買のログとかそうなるかも ‐ mysql57 10> CREATE TABLE t1_archive LIKE t1; Query OK, 0 rows affected (0.01 sec) mysql57 10> ALTER TABLE t1_archive ROW_FORMAT= Compressed; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 46/87
48. レコードがホットログに変わるタイミングでテーブルを移 してInnoDB圧縮に変える サロゲートキーがあれば、なんと ORDER BY <unique_key> LIMIT .. でフェッチの負荷を抑えつつゆっくりアーカイブ できる mysql57 10> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql57 10> INSERT INTO t1_archive SELECT * FROM t1 ORDER BY num LIMIT 2; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql57 10> DELETE FROM t1 ORDER BY num LIMIT 2; Query OK, 2 rows affected (0.00 sec) mysql57 10> COMMIT; Query OK, 0 rows affected (0.02 sec) 47/87
49. ORDER BY .. LIMIT .. が使えない時、使ってはいけない時 ユニークキー以外での ORDER BY .. LIMIT .. は非決定性ク エリーになるので、 INSERT と DELETE で同じ行が選ばれる とは限らない サロゲートキーがある前提でのテクニック ‐ ユニークキーの古い順、以外に WEHRE でフィルターをかける 場合、オプティマイザーが変なインデックスを選んでしまわ ないかどうかに注意 InnoDBはインデックスを使ってロックをかけるので、変にカーディ ナリティーが低いインデックスを使ってロックされてしまうと悲惨 ‐ 48/87
50. MySQLにWEBアプリのログを保存しているケースの8割 くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服する方法を考えて 幸 せになりたい 49/87
51. 月並みで すが 50/87
52. 用法・用量を守っ て楽しいデータス トアライフを！ 51/87
53. 　 52/87
54. (ﾟдﾟ ) 53/87
55. ( ﾟдﾟ ) 54/87
56. ( ﾟдﾟ) 55/87
57. ( ﾟдﾟ) とはいえ yoku0825は MySQLに入れるん でしょ？ 56/87
58. 入れます 57/87
59. MySQLにWEBアプリのログを保存しているケースの 0.0001割くらいが幸せになる方法 あなたがログをINSERTしたのは、この金のMySQLです か？ 銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではな いんですが、それなりのデメリットがあってメリットもあり ます メリットを残しつつデメリットを克服する方法を考えて 幸 せになりたい マイエスキューエルにデータが入る ⇒ 幸せ！！ ‐ 58/87
60. MySQLにWEBアプリのログを保存しているケースの 0.0001割くらいが幸せになる方法 SQLアクセス トランザクション 合いそうな データスト ア MySQL not a log o o 無圧縮InnoDB, TokuDB, MyRocks Hot log o x MyISAM?, TokuDB, MyRocks, 圧縮 InnoDB? Cold Log x x MyISAM, TokuDB, MyRocks, MCS, mysqldump 59/87
61. こんなテーブルがあるじゃろ？ $ ll -h t1.ibd -rw-r-----. 1 mysql mysql 4.1G Mar 29 09:33 t1.ibd mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`digest`), KEY `ipaddr` (`ipaddr`), KEY `url_string` (`url_string`), KEY `dt` (`dt`,`ipaddr`), KEY `dt_2` (`dt`,`url_string`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 60/87
62. こんなテーブルがあるじゃろ？ $ ll -h t1.ibd -rw-r-----. 1 mysql mysql 4.1G Mar 29 09:33 t1.ibd mysql> SHOW TABLE STATUS LIKE 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 9929360 Avg_row_length: 134 Data_length: 1332723712 Max_data_length: 0 Index_length: 2905538560 Data_free: 2097152 Auto_increment: NULL Create_time: 2019-03-29 09:33:06 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) 61/87
63. 金銀銅松竹梅のMySQL 方式 サイズ percentage InnoDB(ナチュラルキー) 4.1G 113% InnoDB(サロゲートキー) 3.6G 100% InnoDB(サロゲートキー) 圧縮 1.9G 53% MyISAM(サロゲートキー) 2.3G 64% MyISAM(インデックス抜 き) 1.0G 28% MyISAM圧縮 1.8G 50% MyISAM圧縮(インデックス 抜き) 502M 14% 62/87
64. 金銀銅松竹梅のMySQL 方式 サイズ percentage InnoDB(サロゲートキー) 3.6G 100% MyRocks(lz4圧縮) 2.3G 64% TokuDB(zlib圧縮) 1.2G 33% MariaDB Column Store(イ ンデックス抜き) 1.3G 36% TSVエクスポート 1.1G 31% TSV圧縮 452M 13% 63/87
65. InnoDBの場合はサロゲートキーを使うと容量効率が良い InnoDBのセカンダリーインデックスのリーフに格納されるのは プライマリーキーの値 KEY (suit, number) -> PRIMARY KEY(id) ‐ root club spade 2 3 2 13 20 18 45 77 64/87
66. InnoDBの場合はサロゲートキーを使うと容量効率が良い プライマリーキーの長さの差 * セカンダリーインデックスの数 * インデックスのリーフの数 ≒ 削減容量 varchar(32) から bigint に変えると… ‐ bigint から int に変えると… ‐ 件数がかさんでくるとじわじわ効いてくる ‐ root club spade 2 3 2 13 20 18 45 77 65/87
67. InnoDBの場合はサロゲートキーを使うと容量効率が良い もともとPRIMARY KEYだったものはUNIQUE KEYとして追 加して「意味的なプライマリーキー」を保つ mysql> ALTER TABLE t1 DROP PRIMARY KEY, ADD seq BIGINT PRIMARY KEY auto_increment FIRST, ADD UNIQUE KEY( digest); Query OK, 0 rows affected (3 min 46.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `url_string` (`url_string`), KEY `dt` (`dt`,`ipaddr`), KEY `dt_2` (`dt`,`url_string`) ) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 66/87
68. InnoDBの場合はサロゲートキーを使うと容量効率が良い 500MB, 13%くらいの節約 $ ll -h t1.ibd -rw-r-----. 1 mysql mysql 3.6G Mar 29 09:55 t1.ibd mysql> SHOW TABLE STATUS LIKE 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 9923680 Avg_row_length: 144 Data_length: 1432338432 Max_data_length: 0 Index_length: 2283716608 Data_free: 2097152 Auto_increment: 10001061 Create_time: 2019-03-29 09:55:10 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 67/87
69. みんなだいすき マイア・イサム トランザクションの機構を持たないぶん軽量 ただしキーの長さにInnoDBより強い制約がある ‐ mysql> CREATE TABLE t1_myisam LIKE t1; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t1_myisam Engine= MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes mysql> ALTER TABLE t1_myisam DROP KEY url_string, ADD KEY (url_st ring(250)), DROP KEY dt_2, ADD KEY (dt, url_string(248)), Engine= MyISAM; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 68/87
70. みんなだいすき マイア・イサム インデックスを保持したままでも2.3GBくらいまで減らせる インデックスをDROPしてしまえばMYIファイルがほぼ空になるので 1.0GBくらいまで減る ‐ MySQL 5.7とそれ以前であれば、 .frm, .MYD, .MYI の3ファイルをポ ンと置くだけで他のMySQLでも読み出せるのがポータビリティー高 い ‐ $ ll -h t1_myisam.MY* -rw-r-----. 1 mysql mysql 982M Mar 29 10:00 t1_myisam.MYD -rw-r-----. 1 mysql mysql 1.3G Mar 29 10:04 t1_myisam.MYI mysql> SHOW CREATE TABLE t1_myisam\G *************************** 1. row *************************** Table: t1_myisam Create Table: CREATE TABLE `t1_myisam` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `dt` (`dt`,`ipaddr`), KEY `url_string` (`url_string`(250)), KEY `dt_2` (`dt`,`url_string`(248)) ) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 69/87
71. みんなだいすき マイア・イサム圧縮 シェルの支援が必要 && 書き込みができなくなるのでロー テーションは必須… インデックス込み1.8GB、インデックス抜き500MB…:(；ﾞﾟ’ωﾟ’): ‐ $ myisampack t1_myisam $ myisamchk -rq t1_myisam $ ll -h t1_myisam.MY* -rw-r-----. 1 mysql mysql 502M Mar 29 10:00 t1_myisam.MYD -rw-r-----. 1 mysql mysql 1.3G Mar 29 10:16 t1_myisam.MYI mysql> SHOW CREATE TABLE t1_myisam\G *************************** 1. row *************************** Table: t1_myisam Create Table: CREATE TABLE `t1_myisam` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `dt` (`dt`,`ipaddr`), KEY `url_string` (`url_string`(250)), KEY `dt_2` (`dt`,`url_string`(248)) ) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 70/87
72. しかもMyISAMって雑にテーブル結合できるんだぜ… 圧縮、無圧縮は統一されてないとダメ myisamchkで .MYI ファイルは再作成しないとダメ インデックスはぎ取っておけば、これは一瞬で終わるしな… ‐ $ cat t1_myisam.MYD >> mukashi_no_t1.MYD $ myisamchk -rq mukashi_no_t1 $ rm t1_myisam.MYD 71/87
73. さすが変態 (c) @tmtms 72/87
74. ベタにInnoDB圧縮 ただしInnoDB圧縮はメモリー効率が悪い 圧縮後のページと、解凍済みのページをそれぞれバッファプールに読 み込む ‐ .ibdファイルに書き戻す時に再圧縮がかかるのでダーティーページの 書き出し時にCPU時間を使う ‐ ミスヒットのコストが高いので、オンライントラフィックと 混ぜるな危険 73/87
75. ベタにInnoDB圧縮 mysql> ALTER TABLE t1 ROW_FORMAT= Compressed; $ ll -h t1.ibd -rw-r-----. 1 mysql mysql 1.9G Mar 29 10:30 t1.ibd mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `url_string` (`url_string`), KEY `dt` (`dt`,`ipaddr`), KEY `dt_2` (`dt`,`url_string`) ) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESS ED 1 row in set (0.01 sec) 74/87
76. MyRocksストレージエンジン RocksDBをバックエンドに持つストレージエンジン デフォルトはLZ4圧縮、zstdにするともうちょっと稼げる ‐ Percona ServerやMariaDB, Facebook MySQLでどうぞ Variable_name: rocksdb_default_cf_options Value: compression=kLZ4Compression;bottommost_compression=kLZ4Compression $ du -sh .rocksdb/ 2.1G .rocksdb/ mysql> SHOW CREATE TABLE t1_rocksdb\G *************************** 1. row *************************** Table: t1_rocksdb Create Table: CREATE TABLE `t1_rocksdb` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `dt` (`dt`,`ipaddr`), KEY `url_string` (`url_string`(191)), KEY `dt_2` (`dt`,`url_string`(190)) ) ENGINE=ROCKSDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 75/87
77. TokuDBストレージエンジン Fractal Tree Indexなる面白い構造のストレージエンジン デフォルトはzlib圧縮 ‐ 同じくPercona ServerやMariaDBでどうぞ ただしPercona Server 8.0では非推奨になった… ‐ Variable_name: tokudb_row_format Value: tokudb_zlib $ du d1/*.tokudb | awk '{i += $1}END{print i}' 1231264 mysql> SHOW CREATE TABLE t1_tokudb\G *************************** 1. row *************************** Table: t1_tokudb Create Table: CREATE TABLE `t1_tokudb` ( `seq` bigint(20) NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY `ipaddr` (`ipaddr`), KEY `url_string` (`url_string`), KEY `dt` (`dt`,`ipaddr`), KEY `dt_2` (`dt`,`url_string`) ) ENGINE=TokuDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 76/87
78. 飛び道具のMariaDB Column Store Columnstoreストレージエンジンは一切合切インデックスが 貼れない（けど、カラムストアだから全カラムにインデック スが貼ってあるばりのパフォーマンスは出る） 使い心地はInfiniDBとほぼ同じ ‐ $ du -sh /usr/local/mariadb/columnstore ## Before 4.2G /usr/local/mariadb/columnstore $ du -sh /usr/local/mariadb/columnstore ## After 5.5G /usr/local/mariadb/columnstore MariaDB [d1]> SHOW CREATE TABLE t1_mcs\G *************************** 1. row *************************** Table: t1_mcs Create Table: CREATE TABLE `t1_mcs` ( `seq` bigint(20) NOT NULL, `dt` datetime NOT NULL, `ipaddr` varchar(17) NOT NULL, `url_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL ) ENGINE=Columnstore DEFAULT CHARSET=latin1 1 row in set (0.000 sec) 77/87
79. シンプルにTSVでエクスポート FILE権限がない場合はシンプルにスクリプトやバッチでエク スポートするとよろし 当然インデックスはなくなる && SQLアクセスもできないけれど ‐ 圧縮がよく効くので長期保存に向いている ‐ mysql> SELECT * FROM t1 INTO OUTFILE '/var/lib/mysql-files/t1.tsv'; Query OK, 10000000 rows affected (17.44 sec) $ ll -h /var/lib/mysql-files/t1.tsv -rw-rw-rw-. 1 mysql mysql 1.1G Mar 29 10:18 /var/lib/mysql-files/t1.tsv $ gzip /var/lib/mysql-files/t1.tsv $ ll -h /var/lib/mysql-files/t1.tsv.gz -rw-rw-rw-. 1 mysql mysql 452M Mar 29 10:18 /var/lib/mysql-files/t1.tsv.gz $ zstd /var/lib/mysql-files/t1.tsv $ ll -h /var/lib/mysql-files/t1.tsv.zst -rw-rw-rw-. 1 mysql mysql 445M Mar 29 10:18 /var/lib/mysql-files/t1.tsv.zst 78/87
80. シンプルにTSVでエクスポート mysqldump には最初からTSVでエクスポートするためのオプ ションがついている $ mysqldump --tab=/path/to/output/directory --where="created_at < '2019-03-30'" d1 t1 $ ll /tmp/t1.* -rw-r--r-- 1 yoku0825 yoku0825 1469 Mar 30 11:05 /tmp/t1.sql -rw-rw-rw- 1 yoku0825 yoku0825 107 Mar 30 11:05 /tmp/t1.txt 79/87
81. 戻す前提でmysqldumpでテキスト化という手もある そのままパースするんじゃなくて「読みたい時にMySQLに 戻す」と割り切るなら mysqldump の --skip-extended- insert もいい $ mysqldump --no-create-info --skip-extended-insert --where="created_ at BETWEEN '2019/02/01' AND '2019/02/28'" d1 t1 > /path/to/d1_t1.sql $ cat /path/to/d1_t1.sql .. LOCK TABLES `t1` WRITE; /*!40000 ALTER TABLE `t1` DISABLE KEYS */; INSERT INTO `t1` VALUES (1,'one','2019-03-30 10:53:10'); INSERT INTO `t1` VALUES (2,'two','2019-03-30 10:53:10'); INSERT INTO `t1` VALUES (3,'three','2019-03-30 10:53:10'); INSERT INTO `t1` VALUES (4,'four','2019-03-30 10:53:21'); /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; 80/87
82. まとめ SQLアクセス トランザクション 合いそうな データスト ア MySQL not a log o o 無圧縮InnoDB, TokuDB, MyRocks Hot log o x MyISAM?, TokuDB, MyRocks, 圧縮 InnoDB? Cold Log x x MyISAM, TokuDB, MyRocks, MCS, mysqldump 81/87
83. まとめ 方式 サイズ percentage InnoDB(ナチュラルキー) 4.1G 113% InnoDB(サロゲートキー) 3.6G 100% InnoDB(サロゲートキー) 圧縮 1.9G 53% MyISAM(サロゲートキー) 2.3G 64% MyISAM(インデックス抜 き) 1.0G 28% MyISAM圧縮 1.8G 50% MyISAM圧縮(インデックス 抜き) 502M 14% 82/87
84. まとめ 方式 サイズ percentage InnoDB(サロゲートキー) 3.6G 100% MyRocks(lz4圧縮) 2.3G 64% TokuDB(zlib圧縮) 1.2G 33% MariaDB Column Store(イ ンデックス抜き) 1.3G 36% TSVエクスポート 1.1G 31% TSV圧縮 452M 13% 83/87
85. まとめ InnoDBは プライマリーキーの長さの差 * セカンダリーイ ンデックスの数 * インデックスのリーフの数 ≒ 削減容量 サロゲートキーがあれば SELECT .. ORDER BY <unique_key> LIMIT .. FOR UPDATE でロックを取って書き 出して DELETE が負荷少な目でできる MyISAM、こういう用途には結構優秀 Percona ServerやMariaDBであればMyRocks, TokuDBもそ こそこ優秀 84/87
86. というのは全て MySQLerがやればいい のであって、PHPerは 素直にテキストにダンプ するのがいいと思うの 85/87
87. 用法・用量を守っ て楽しいMySQL ライフを！ 86/87
88. Any Questions and/or Suggestions? 87/87