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

0deae06ab5d86b39feeec2e23a30b88a?s=47 yoku0825
March 30, 2019

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

2019/03/30 PHPerKaigi 2019

0deae06ab5d86b39feeec2e23a30b88a?s=128

yoku0825

March 30, 2019
Tweet

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. ( ゚д゚) ま いあいさm 38/87

  40. (運用が複雑化 するのでオスス メしません) 39/87

  41. コールドログ 「 ログレコード のうち、オンライントラフィックで参照さ れないもの」と定義すると SQLは要らない? オンラインからは参照されないにして集計処理的なものは走る? 頻度、要求レスポンスタイムとのと相談 ‐ トランザクションの保護は要らなさそう

    更新もされない、リアルタイムな参照もされない ‐ 読み取りの競合もずらそうと思えばずらせるんじゃ? ‐ 十分長い期間保管されることを考えるとむしろ容量効率を優 先した方がいいはず ログをサマライズした結果を長期保存することで容量をコン パクトにすることもできる 40/87
  42. ( ゚д゚)ハッ! 41/87

  43. ( ゚д゚) 圧縮済 まいあいさm 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