mita2 database life

主にMySQLに関するメモです

ファイル倉庫系DBをSQLiteでなんとかできるか

MySQLのDBAをしていると、MySQLを「SQLでアクセス可能なファイル倉庫」として使う人たちに出会うことがある。

彼らの要件はだいたいこんな感じ・・・

  • 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
こいつを、SELECT OUT INTO でCSVとして出力→sqliteでロードした。 
$ ls -alh tmp.db
-rw-r--r--  1 user user 1.1G Dec  9 15:54 tmp.db
どっちも1Gちょい。容量はあまり変わらない。ただし、MySQL (InnoDB) は圧縮が利用できるので、MySQLで圧縮するほうが物理容量面では有利である。
※ データにも依存するが、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.355699
ちなみにMySQLでローカルでやった場合もあまり時間は変わらなかった。 
mysql>  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未満を必要とするユーザに提案してみたい。