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

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

ログレコード
INSERT されて以降 UPDATE されることがないレコードでRDBMS的な制約が
要らない
ホットログ
ログレコードのうち、オンライントラフィックで参照されるもの
コールドログ
ホットログでないログレコード (オンラインで参照されないもの)
ログテーブル
ログレコードだけで満たされたテーブル
はず
5/87

View Slide

はい
6/87

View Slide

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

View Slide

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

View Slide

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

View Slide

ます
メリットを残しつつデメリットを克服する方法を考えて幸せ
になりたい
10/87

View Slide

ます
メリットを残しつつデメリットを克服する方法を考えて幸
せになりたい
11/87

View Slide

Let’s
think
12/87

View Slide

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

View Slide

d
使う?
14/87

View Slide

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

View Slide

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

View Slide

一覧表示
集計
細かい条件で検索したり、秒単位で指定して削除ができたり
細かいことを考えずに永続化して一元管理できるデータストアといえばこれ
‐
インデックス, バッファプールによるI/O効率の向上
パラレルスキャン (ただし現在は SELECT COUNT(*) に限る…)
全文検索 (∩ﾟдﾟ)ｱｰｱｰ
‐
17/87

View Slide

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

View Slide

書き込み失敗を検出できる
「ログが残らないくらいなら処理を中断させる」なんて世界線にも対応
‐
証できる
‐
一貫性のある読み取りを勝手に実装してくれている
ログローテーションをしなくても「バッチ処理中に新たなログが追加された
上から下まで読める
‐
19/87

View Slide

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

View Slide

往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」
という訳にはいかない
‐
銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど…
‐
トランザクションの保護を受けるための仕組みが重厚
21/87

View Slide

往々にして「DBサーバーからファイルを2～3個コピーしてPON!!」
という訳にはいかない
‐
銀の弾丸なETLなソリューションと仲良くなれればいいんだけれど…
‐
トランザクションの保護を受けるための仕組みが重厚
22/87

View Slide

ログレコードそのものの他にREDOログもUNDOログも要るしイン
デックスはソート済みのデータの複製だし効率的にデータ構造を維持
するためにページには空き領域を残しておかないといけないし…
‐
ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー
バーヘッドは大きい
‐
23/87

View Slide

ログレコードそのものの他にREDOログもUNDOログも要るしイン
デックスはソート済みのデータの複製だし効率的にデータ構造を維持
するためにページには空き領域を残しておかないといけないし…
‐
ただの書き込み、ただの読み込みよりも性能的にも容量的にもオー
バーヘッドは大きい
‐
24/87

View Slide

メリットとデメリットの整理
‐
‐
SQLでアクセスするために(RDBMSごとに)フォーマットが決まって
しまう
‐
‐
25/87

View Slide

ます
せになりたい
26/87

View Slide

( ﾟдﾟ)
27/87

View Slide

‐
‐
しまう
‐
‐
28/87

View Slide

‐
‐
しまう
‐
‐
29/87

View Slide

( ﾟдﾟ)
30/87

View Slide

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

View Slide

( SQLでアクセスしたい時に ) SQLでアクセスできる
( トランザクションの保護を受けたい時に )トランザクショ
ンの保護が受けられる
32/87

View Slide

( SQLでアクセスしたい時に ) SQLでアクセスできる
一覧表示しなくていい時
集計しなくていい時
細かい条件で検索したり、秒単位で指定して削除ができたりしなくていい時
細かいことを考えずに永続化して一元管理できるデータストアといえばこれ他に
もっとログ用に望ましいデータストアがある時
‐
インデックス, バッファプールによるI/O効率の向上を期待しなくていい時
パラレルスキャン (ただし現在は `SELECT COUNT(*)` に限る…)
全文検索
‐
(トランザクションの保護を受けたい時に)トランザクション
の保護が受けられる
33/87

View Slide

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

View Slide

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

View Slide

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

View Slide

( ﾟдﾟ)ﾊｯ!
37/87

View Slide

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

View Slide

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

View Slide

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

View Slide

( ﾟдﾟ)ﾊｯ!
41/87

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

レコードがホットログに変わるタイミングでテーブルを移
して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;
Records: 0 Duplicates: 0 Warnings: 0
46/87

View Slide

レコードがホットログに変わるタイミングでテーブルを移
してInnoDB圧縮に変える
サロゲートキーがあれば、なんと ORDER BY
LIMIT .. でフェッチの負荷を抑えつつゆっくりアーカイブ
できる
mysql57 10> BEGIN;
mysql57 10> INSERT INTO t1_archive SELECT * FROM t1 ORDER BY num LIMIT 2;
mysql57 10> DELETE FROM t1 ORDER BY num LIMIT 2;
mysql57 10> COMMIT;
47/87

View Slide

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

View Slide

ます
せになりたい
49/87

View Slide

月並みで
すが
50/87

View Slide

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

View Slide

　
52/87

View Slide

(ﾟдﾟ )
53/87

View Slide

( ﾟдﾟ )
54/87

View Slide

( ﾟдﾟ)
55/87

View Slide

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

View Slide

入れます
57/87

View Slide

MySQLにWEBアプリのログを保存しているケースの
0.0001割くらいが幸せになる方法
ます
せになりたい
マイエスキューエルにデータが入る ⇒ 幸せ！！
‐
58/87

View Slide

MySQLにWEBアプリのログを保存しているケースの
0.0001割くらいが幸せになる方法
ア 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

View Slide

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

View Slide

こんなテーブルがあるじゃろ？
$ ll -h 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:
61/87

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

もともと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)
*************************** 1. row ***************************
Table: t1
`seq` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`seq`),
UNIQUE KEY `digest` (`digest`),
) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4
66/87

View Slide

500MB, 13%くらいの節約
$ ll -h 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:
67/87

View Slide

みんなだいすきマイア・イサム
トランザクションの機構を持たないぶん軽量
ただしキーの長さにInnoDBより強い制約がある
‐
mysql> CREATE TABLE t1_myisam LIKE t1;
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;
68/87

View Slide

みんなだいすきマイア・イサム
インデックスを保持したままでも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` (
KEY ùrl_string` (ùrl_string`(250)),
KEY `dt_2` (`dt`,ùrl_string`(248))
) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4
69/87

View Slide

みんなだいすきマイア・イサム圧縮
シェルの支援が必要 && 書き込みができなくなるのでロー
テーションは必須…
インデックス込み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` (
) ENGINE=MyISAM AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4
70/87

View Slide

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

View Slide

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

View Slide

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

View Slide

ベタにInnoDB圧縮
mysql> ALTER TABLE t1 ROW_FORMAT= Compressed;
$ ll -h t1.ibd
*************************** 1. row ***************************
Table: t1
) ENGINE=InnoDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESS
ED
74/87

View Slide

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` (
) ENGINE=ROCKSDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4
75/87

View Slide

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` (
) ENGINE=TokuDB AUTO_INCREMENT=10001061 DEFAULT CHARSET=utf8mb4
76/87

View Slide

飛び道具の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,
`number` bigint(20) unsigned NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1
77/87

View Slide

シンプルにTSVでエクスポート
FILE権限がない場合はシンプルにスクリプトやバッチでエク
スポートするとよろし
当然インデックスはなくなる && SQLアクセスもできないけれど
‐
圧縮がよく効くので長期保存に向いている
‐
mysql> SELECT * FROM t1 INTO OUTFILE '/var/lib/mysql-files/t1.tsv';
$ 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

View Slide

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

View Slide

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

View Slide

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

View Slide

まとめ
InnoDB(ナチュラルキー) 4.1G 113%
InnoDB(サロゲートキー)
圧縮
1.9G 53%
MyISAM(サロゲートキー) 2.3G 64%
MyISAM(インデックス抜
き)
1.0G 28%
MyISAM圧縮 1.8G 50%
MyISAM圧縮(インデックス
抜き)
502M 14%
82/87

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

Any Questions
and/or
Suggestions?
87/87

View Slide

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

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

yoku0825
PRO

More Decks by yoku0825

Other Decks in Technology

Featured

Transcript

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

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

yoku0825 PRO

More Decks by yoku0825

Other Decks in Technology

Featured

Transcript

yoku0825
PRO