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

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

MySQLにWEBアプリのログを保存しているケースの8割くらいが幸せになる方法あなたがログをINSERTしたのは、この金のMySQLですか？銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではないんですが、それなりのデメリットがあってメリットもありますメリットを残しつつデメリットを克服する方法を考えて幸せになりたい
1/87

あっすいませんPHPの話出てきません 2/87

Definition & Classification WEBアプリのログ？ WEBサーバーのアクセスログ…はないと思う ‐ アプリケーションのエラーログやデバッグログ…もないと思う ‐ エンドユーザーの挙動を表現するログ集計されて何かに利用されるもの
何かの証憑として保管され続けなければいけないもの ‐ 分類は独断と偏見によるものです、環境によって他にもあるはず 3/87

TL;DR そのログは本当にRDBMSに保管し続けるのが幸せなのかを見つめる保管からしばらくはRDBMSがそれなりに適していると思ういつ、どんなタイミングで使われるのかを整理して、 RDBMSの外に逃がすという視点を持つと8割がた幸せにな
れます 4/87

Definition & Classification ログレコード INSERT されて以降 UPDATE されることがないレコードでRDBMS的な制約が要らないホットログ
ログレコードのうち、オンライントラフィックで参照されるものコールドログホットログでないログレコード (オンラインで参照されないもの) ログテーブルログレコードだけで満たされたテーブル分類は独断と偏見によるものです、環境によって他にもあるはず 5/87

はい 6/87

ログテーブル作ったことがない人？ 7/87

ログテーブル嫌いな人？ 8/87

ログテーブル大好きな人？ 9/87

MySQLにWEBアプリのログを保存しているケースの8割くらいが幸せになる方法あなたがログをINSERTしたのは、この金のMySQLですか？銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではないんですが、それなりのデメリットがあってメリットもありますメリットを残しつつデメリットを克服する方法を考えて幸せになりたい
10/87

MySQLにWEBアプリのログを保存しているケースの8割くらいが幸せになる方法あなたがログをINSERTしたのは、この金のMySQLですか？銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではないんですが、それなりのデメリットがあってメリットもありますメリットを残しつつデメリットを克服する方法を考えて幸
せになりたい 11/87

Let’s think 12/87

＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 実はPHPerではない ‐
生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 13/87

Definition & Classification d 使う? 14/87

ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできるトランザクションの保護が受けられる 15/87

ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と親和性が高い = 開発しやすい一覧表示集計
細かい条件で検索したり、秒単位で指定して削除ができたり細かいことを考えずに永続化して一元管理できるデータストアといえばこれ ‐ RDBMSがそもそも備えている検索のための機能が使えるインデックス, バッファプールによるI/O効率の向上パラレルスキャン (ただし現在は SELECT COUNT(*) に限る…) 全文検索 (∩ﾟдﾟ)ｱｰｱｰ ‐ トランザクションの保護が受けられる 16/87

ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と親和性が高い = 開発しやすい一覧表示集計
細かい条件で検索したり、秒単位で指定して削除ができたり細かいことを考えずに永続化して一元管理できるデータストアといえばこれ ‐ RDBMSがそもそも備えている検索のための機能が使えるインデックス, バッファプールによるI/O効率の向上パラレルスキャン (ただし現在は SELECT COUNT(*) に限る…) 全文検索 (∩ﾟдﾟ)ｱｰｱｰ ‐ トランザクションの保護が受けられる 17/87

ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできるトランザクションの保護が受けられる書き込み失敗を検出できる「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応 ‐ 書き込みに成功した以上は必ずログレコードが残っていることを保証できる
‐ 一貫性のある読み取りを勝手に実装してくれているログローテーションをしなくても「バッチ処理中に新たなログが追加されたら？」「書き込み途中のログを読みこんでしまったら？」なんてことを考えずに上から下まで読める ‐ 18/87

ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできるトランザクションの保護が受けられる書き込み失敗を検出できる「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応 ‐ 書き込みに成功した以上は必ずログレコードが残っていることを保証できる
‐ 一貫性のある読み取りを勝手に実装してくれているログローテーションをしなくても「バッチ処理中に新たなログが追加されたら？」「書き込み途中のログを読みこんでしまったら？」なんてことを考えずに上から下まで読める ‐ 19/87

ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまうトランザクションの保護を受けるための仕組みが重厚すぎる 20/87

ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまう往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」という訳にはいかない ‐ 銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど… ‐ トランザクションの保護を受けるための仕組みが重厚 21/87

ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまう往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」という訳にはいかない ‐ 銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど… ‐ トランザクションの保護を受けるための仕組みが重厚 22/87

ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまうトランザクションの保護を受けるための仕組みが重厚すぎるログレコードそのものの他にREDOログもUNDOログも要るしインデックスはソート済みのデータの複製だし効率的にデータ構造を維持するためにページには空き領域を残しておかないといけないし… ‐ ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー
バーヘッドは大きい ‐ 23/87

ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまうトランザクションの保護を受けるための仕組みが重厚すぎるログレコードそのものの他にREDOログもUNDOログも要るしインデックスはソート済みのデータの複製だし効率的にデータ構造を維持するためにページには空き領域を残しておかないといけないし… ‐ ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー
バーヘッドは大きい ‐ 24/87

メリットとデメリットの整理ログをRDBMS（主語が大きい）に保管するメリット SQLでアクセスできる ‐ トランザクションの保護が受けられる ‐ ログをRDBMS（主語が大きい）に保管するデメリット SQLでアクセスするために(RDBMSごとに)フォーマットが決まってしまう ‐
トランザクションの保護を受けるための仕組みが重厚すぎる ‐ 25/87

( ﾟдﾟ) 27/87

( ﾟдﾟ) 30/87

メリットの方に括弧書きしてみよう 31/87

ログをRDBMS（主語が大きい）に保管するメリット ( SQLでアクセスしたい時に ) SQLでアクセスできる ( トランザクションの保護を受けたい時に )トランザクションの保護が受けられる 32/87

ログをRDBMS（主語が大きい）に保管するメリット ( SQLでアクセスしたい時に ) SQLでアクセスできる WEBアプリケーション本体 (がRDBMSを使っているなら) の実装と親和性が高い =
開発しやすい一覧表示しなくていい時集計しなくていい時細かい条件で検索したり、秒単位で指定して削除ができたりしなくていい時細かいことを考えずに永続化して一元管理できるデータストアといえばこれ他にもっとログ用に望ましいデータストアがある時 ‐ RDBMSがそもそも備えている検索のための機能が使えるインデックス, バッファプールによるI/O効率の向上を期待しなくていい時パラレルスキャン (ただし現在は `SELECT COUNT(*)` に限る…) 全文検索 ‐ (トランザクションの保護を受けたい時に)トランザクションの保護が受けられる 33/87

ログをRDBMS（主語が大きい）に保管するメリット (SQLでアクセスしたい時に) SQLでアクセスできる ( トランザクションの保護を受けたい時に )トランザクションの保護が受けられる書き込み失敗を検出しなくていい時「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応
しなくていい時 ‐ 書き込みに成功した以上は必ずログレコードが残っていることを保証できなくてもいい時 ‐ 一貫性のある読み取りを勝手に実装してくれていることがなくてもいい時ログローテーションをしていれば「バッチ処理中に新たなログが追加されたら？」「書き込み途中のログを読みこんでしまったら？」なんてことを考えずに上から下まで読めたりするんでは？ ‐ 34/87

Definition & Classification (again) ログレコード INSERT されて以降 UPDATE されることがないレコードでRDBMS的な制約が要らない
ホットログログレコードのうち、オンライントラフィックで参照されるものコールドログホットログでないログレコード (オンラインで参照されないもの) ログテーブルログレコードだけで満たされたテーブル分類は独断と偏見によるものです、環境によって他にもあるはず 35/87

ホットログ「ログレコードのうち、オンライントラフィックで参照されるもの」と定義すると SQLでアクセスするメリットは美味しい自動でLRUによるキャッシュがかかるのも美味しい ‐ 必ずしもトランザクションで保護される必要はあるのか？ UPDATE
が走らないのであれば、一度Diskに書き込み終えたらもうクラッシュセーフと考えられるのでは？ ‐ スイッチしてから読めば、読み取り一貫性のケアは要らないのでは？念のため読み取りロックフリーならなお良し？ ‐ 36/87

( ﾟдﾟ)ﾊｯ! 37/87

( ﾟдﾟ) まいあいさｍ 38/87

（運用が複雑化するのでオススメしません） 39/87

コールドログ「ログレコードのうち、オンライントラフィックで参照されないもの」と定義すると SQLは要らない？オンラインからは参照されないにして集計処理的なものは走る？頻度、要求レスポンスタイムとのと相談 ‐ トランザクションの保護は要らなさそう
更新もされない、リアルタイムな参照もされない ‐ 読み取りの競合もずらそうと思えばずらせるんじゃ？ ‐ 十分長い期間保管されることを考えるとむしろ容量効率を優先した方がいいはずログをサマライズした結果を長期保存することで容量をコンパクトにすることもできる 40/87

( ﾟдﾟ)ﾊｯ! 41/87

( ﾟдﾟ) 圧縮済まいあいさｍ 42/87

（運用が超複雑化するのでオススメしません） 43/87

どんなものが良いの？ SQLアクセストランザクション合いそうなデータストア not a log o o
RDBMS Hot log o x 時系列DB? Treasure Data? Cold Log x x HDFSとか? MySQLだけが守備範囲の俺には答えは出ていません 44/87

ただしデータストアを変えるタイミングでは永続性は上位レイヤーでのサポートが必要エクスポートは正しい選択肢だと思うけれど、エクスポート処理が確実に行われたことはちゃんと担保してやらないといけない ‐ SELECT してエクスポートして書き出した内容が正しいことを検証してから DELETE
なり DROP なりエクスポート先がクラッシュアンセーフならば、書き出しに失敗したらデータのフェッチからやり直す ‐ 45/87

レコードがホットログに変わるタイミングでテーブルを移して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

レコードがホットログに変わるタイミングでテーブルを移して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

ORDER BY .. LIMIT .. が使えない時、使ってはいけない時ユニークキー以外での ORDER BY ..
LIMIT .. は非決定性クエリーになるので、 INSERT と DELETE で同じ行が選ばれるとは限らないサロゲートキーがある前提でのテクニック ‐ ユニークキーの古い順、以外に WEHRE でフィルターをかける場合、オプティマイザーが変なインデックスを選んでしまわないかどうかに注意 InnoDBはインデックスを使ってロックをかけるので、変にカーディナリティーが低いインデックスを使ってロックされてしまうと悲惨 ‐ 48/87

月並みですが 50/87

用法・用量を守って楽しいデータストアライフを！ 51/87

　 52/87

(ﾟдﾟ ) 53/87

( ﾟдﾟ ) 54/87

( ﾟдﾟ) 55/87

( ﾟдﾟ) とはいえ yoku0825は MySQLに入れるんでしょ？ 56/87

入れます 57/87

MySQLにWEBアプリのログを保存しているケースの 0.0001割くらいが幸せになる方法あなたがログをINSERTしたのは、この金のMySQLですか？銀のMySQLですか？ MySQLというかRDBMSにログを記録するのは絶対悪ではないんですが、それなりのデメリットがあってメリットもありますメリットを残しつつデメリットを克服する方法を考えて幸
せになりたいマイエスキューエルにデータが入る ⇒ 幸せ！！ ‐ 58/87

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

こんなテーブルがあるじゃろ？ $ 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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`digest`), KEY ìpaddr` (ìpaddr`), KEY ùrl_string` (ùrl_string`), KEY `dt` (`dt`,ìpaddr`), KEY `dt_2` (`dt`,ùrl_string`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 60/87

こんなテーブルがあるじゃろ？ $ 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

金銀銅松竹梅の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

金銀銅松竹梅の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

InnoDBの場合はサロゲートキーを使うと容量効率が良い InnoDBのセカンダリーインデックスのリーフに格納されるのはプライマリーキーの値 KEY (suit, number) -> PRIMARY KEY(id) ‐
root club spade 2 3 2 13 20 18 45 77 64/87

InnoDBの場合はサロゲートキーを使うと容量効率が良いプライマリーキーの長さの差 * セカンダリーインデックスの数 * インデックスのリーフの数 ≒ 削減容量 varchar(32) から
bigint に変えると… ‐ bigint から int に変えると… ‐ 件数がかさんでくるとじわじわ効いてくる ‐ root club spade 2 3 2 13 20 18 45 77 65/87

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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY ùrl_string` (ùrl_string`), KEY `dt` (`dt`,ìpaddr`), KEY `dt_2` (`dt`,ùrl_string`) ) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 66/87

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

みんなだいすきマイア・イサムトランザクションの機構を持たないぶん軽量ただしキーの長さに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

みんなだいすきマイア・イサムインデックスを保持したままでも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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY `dt` (`dt`,ìpaddr`), KEY ùrl_string` (ùrl_string`(250)), KEY `dt_2` (`dt`,ùrl_string`(248)) ) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 69/87

みんなだいすきマイア・イサム圧縮シェルの支援が必要 && 書き込みができなくなるのでローテーションは必須… インデックス込み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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY `dt` (`dt`,ìpaddr`), KEY ùrl_string` (ùrl_string`(250)), KEY `dt_2` (`dt`,ùrl_string`(248)) ) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 70/87

しかもMyISAMって雑にテーブル結合できるんだぜ… 圧縮、無圧縮は統一されてないとダメ myisamchkで .MYI ファイルは再作成しないとダメインデックスはぎ取っておけば、これは一瞬で終わるしな… ‐ $ cat t1_myisam.MYD
>> mukashi_no_t1.MYD $ myisamchk -rq mukashi_no_t1 $ rm t1_myisam.MYD 71/87

さすが変態 (c) @tmtms 72/87

ベタにInnoDB圧縮ただしInnoDB圧縮はメモリー効率が悪い圧縮後のページと、解凍済みのページをそれぞれバッファプールに読み込む ‐ .ibdファイルに書き戻す時に再圧縮がかかるのでダーティーページの書き出し時にCPU時間を使う ‐ ミスヒットのコストが高いので、オンライントラフィックと混ぜるな危険
73/87

ベタに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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY ùrl_string` (ùrl_string`), KEY `dt` (`dt`,ìpaddr`), KEY `dt_2` (`dt`,ùrl_string`) ) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESS ED 1 row in set (0.01 sec) 74/87

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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY `dt` (`dt`,ìpaddr`), KEY ùrl_string` (ùrl_string`(191)), KEY `dt_2` (`dt`,ùrl_string`(190)) ) ENGINE=ROCKSDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 75/87

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, ìpaddr` varchar(17) NOT NULL, ùrl_string` varchar(255) NOT NULL, `digest` varchar(32) NOT NULL, `number` bigint(20) unsigned NOT NULL, PRIMARY KEY (`seq`), UNIQUE KEY `digest` (`digest`), KEY ìpaddr` (ìpaddr`), KEY ùrl_string` (ùrl_string`), KEY `dt` (`dt`,ìpaddr`), KEY `dt_2` (`dt`,ùrl_string`) ) ENGINE=TokuDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 76/87

飛び道具の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

シンプルに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

シンプルに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

戻す前提で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

まとめ 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

まとめ方式サイズ 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

まとめ方式サイズ 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

まとめ InnoDBはプライマリーキーの長さの差 * セカンダリーインデックスの数 * インデックスのリーフの数 ≒ 削減容量
サロゲートキーがあれば SELECT .. ORDER BY <unique_key> LIMIT .. FOR UPDATE でロックを取って書き出して DELETE が負荷少な目でできる MyISAM、こういう用途には結構優秀 Percona ServerやMariaDBであればMyRocks, TokuDBもそこそこ優秀 84/87

というのは全て MySQLerがやればいいのであって、PHPerは素直にテキストにダンプするのがいいと思うの 85/87

用法・用量を守って楽しいMySQL ライフを！ 86/87

Any Questions and/or Suggestions? 87/87

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

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

More Decks by yoku0825

Other Decks in Technology

Featured

Transcript