ファイル倉庫系DBをSQLiteでなんとかできるか
MySQLのDBAをしていると、MySQLを「SQLでアクセス可能なファイル倉庫」として使う人たちに出会うことがある。
設定は全部デフォ。メモリはデータ量以上に積んでるので基本すべてメモリにキャッシュされている状態で試した。 サーバはVMと、、、割りと雑な検証です。。。
※ データにも依存するが、InnoDBで圧縮すればだいたい半分ぐらいになることが多い。
彼らの要件はだいたいこんな感じ・・・
- SQLを喋れる「何か」である必要があるが、MySQLである必要はない
- DBサーバに入れておけば、可用性やバックアップ面が担保されているので楽チンだからDBに入れる
- DBサーバを自分たちで運用したくない
- 処理はアドホックに行われ、雑なSQLが流れる、多くは解析系
- ログやバイナリを雑に保存する。容量が増えていく
ファイル倉庫系は「そこそこ」動いてれば良いので問題が出ても根本対策がされず、だましだまし運用を続ける、「癌化」しやすい注意案件である。
Excel 以上、MySQL 未満 がマッチしているような案件…SQLite が使えるかも？と思ったので検証してみる。
SQLite は今や Mac OSやiOSに同梱されており、実績は十分である。Percona LIVEのkeynote のスライドを見るとSQLiteはぜんぜん「Lite」ではない高機能なプロダクトであることがわかる。
SQLiteのデータベースは単一ファイル。レプリカを作りたければ、他のサーバにデータファイルをコピーするだけで良い。
冗長化やバックアップも(そこそこのレベルで良いのであれば)非常にお手軽である。
検証環境
SQLite はローカルで更新・参照して計測した、MySQL はDBサーバとクライアントは分離する。設定は全部デフォ。メモリはデータ量以上に積んでるので基本すべてメモリにキャッシュされている状態で試した。 サーバはVMと、、、割りと雑な検証です。。。
$ rpm -qa |egrep 'sqlite|mysql-community-server'
sqlite-3.7.17-8.el7.x86_64
mysql-community-server-5.7.20-1.el7.x86_64
容量比較
sysbench を利用して500万行のテーブルを作る。
$ sudo ls -alh /var/lib/mysql/sbtest
total 1.2G
drwxr-x--- 2 mysql mysql 55 Dec 9 15:26 .
drwxr-x--x 7 mysql mysql 4.0K Dec 9 15:22 ..
-rw-r----- 1 mysql mysql 65 Dec 9 15:22 db.opt
-rw-r----- 1 mysql mysql 8.5K Dec 9 15:26 sbtest1.frm
-rw-r----- 1 mysql mysql 1.2G Dec 9 15:26 sbtest1.ibd
$ ls -alh tmp.db
-rw-r--r-- 1 user user 1.1G Dec 9 15:54 tmp.db※ データにも依存するが、InnoDBで圧縮すればだいたい半分ぐらいになることが多い。
インポートの時間
ほとんど差がなかった・・・！ ネットワーク経由でデータを送るMySQLのほうが多少遅くなると思ったが、そうでもないようだ。
mysql> LOAD DATA LOCAL INFILE 'tmp2.csv' INTO TABLE sbtest1 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 5000000 rows affected (2 min 28.06 sec)
Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0$ time sqlite3 tmp.db < imp.sql
real 2m28.788s
user 0m53.754s
sys 0m19.732sエクスポートの時間
SQLiteのほうが3割ぐらい速い。
mysql> SELECT * FROM sbtest1 INTO OUTFILE '/var/lib/mysql-files/out.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
Query OK, 5000000 rows affected (14.96 sec)sqlite> .mode csv
sqlite> .output result.csv
sqlite> .timer on
sqlite> SELECT * FROM sbtest1;
CPU Time: user 7.159407 sys 1.355699mysql> SELECT * FROM sbtest1 INTO OUTFILE '/var/lib/mysql-files/aout.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
Query OK, 5000000 rows affected (16.35 sec)まとめ
ファイル倉庫系のワークロードでSQLiteが劣っているようなことはなさそう。
Excel以上、MySQL未満を必要とするユーザに提案してみたい。
