SQLiteとMySQLにおける一括更新と逐次更新でパフォーマンス比較をしてみる
2年くらい前だったでしょうか。SQLiteとMySQLの更新速度の比較をしているブログを拝見し、圧倒的にSQLiteが早かったことから、ほぉ~それは興味深い、と実際にこの1年ちょっとの間、自分の運営サイトでもSQLiteを採用して使ってきました。
その間、SQLite特有のDatabase is lockedに何度も悩まされ、ようやく先日の記事「SQLiteのDatabase is lockedで悩む人に見て欲しいロックの話 」で一応の解決を見ました。
環境構築が恐ろしく簡単なSQLiteでここまでのことが出来るのはすごいなぁと感心する半面、今後もSQLiteを使い続けるか、という点ではちょっと悩ましくも感じています。特に同時更新が発生する状況ではロックの範囲が広い(※SQLiteではDB全体にロックがかかる)と同時アクセスしている他のユーザーへの影響が気になります。
そこであらためて、自分でも簡単なパフォーマンステストをしてみることにしました。あまり本格的なものではないので鵜呑みにはしないで欲しいですが、先に結論から書くと一括更新ではSQLiteのほうが早く、ロックが発生するような並列更新ではMySQLのほうが圧倒的に早いという結果になりました。
テスト環境
ハードウェアは先日投稿した「SQLでマスタテーブルと結合したテーブルから最新1件ずつ表示する例と速度テスト」で使ったテスト環境と同じです。
機種名 | ACEPC GK3 |
CPU | Intel Celeron J4125 |
メモリ | 8GB |
ストレージ | SSD 256GB |
ACEPCはもう販売していないようですが、似たようなのだとこのへん↓
https://amzn.to/3oX8aM1OS | Windows 10 Pro |
HTTPサーバー | nginx 1.21.6 |
PHP | 7.4.27 |
SQLite | 3.31.1 |
mariadb | 10.3.34 |
昨日投稿したばかりですが、「nginxでphpファイルがダウンロードされるときはブラウザキャッシュもチェック」のとおり、つまらない原因で半日つぶしつつ、それぞれ最新版にアップデートしました。
テスト用のテーブル
SQLite/MySQL共通のテーブル構造。
page_seq | PRIMARY KEY | INTEGER | ページ番号 |
cnt | INTEGER | PV |
今回のテストではアクセス解析のようなものを想定しているため、ページ番号となるpage_seq、そのページが開かれた回数を数えるcntで構成しています。
SQLiteの速度テスト
一括更新
<?php
$cfg['max_page'] = 10; //ページ数
$cfg['max_pv'] = 100; //最大PV
//SQLite
$cfg['PDO_DSN'] = 'sqlite:test.db';
$cfg['INSERT_QUERY'] = 'INSERT INTO count_page (page_seq, cnt) VALUES (?, 1) ON CONFLICT(page_seq) DO UPDATE SET cnt=cnt+1';
set_exception_handler('myExceptionHandler');
$db = new PDO($cfg['PDO_DSN'], '', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_TIMEOUT => 120));
$t1 = microtime(true);
$db->query("BEGIN IMMEDIATE");
$st = $db->prepare($cfg['INSERT_QUERY']);
for ($i = 1; $i <= $cfg['max_pv']; $i++) {
$page_seq = mt_rand(1, $cfg['max_page']); //ランダムなページ番号を取得
$st->execute(array($page_seq)); //そのページ番号のPVをカウントアップ
}
$db->query("COMMIT");
$t = microtime(true) - $t1.' sec.';
file_put_contents('count_page.log', date('Y-m-d H:i:s').' OK:'.$t."\n", FILE_APPEND | LOCK_EX);
function myExceptionHandler($e)
{
file_put_contents('count_page.log', date('Y-m-d H:i:s').' NG:'.$e."\n", FILE_APPEND | LOCK_EX);
exit();
}
?>
まずは一括更新のテストですが、並列更新でもBEGIN~COMMITの位置が違うだけでほぼ同じコードを使います。
最初は100万件くらいの更新をしていたのですが、この後の並列更新で恐ろしく時間がかかったため、ページ数10ページ、最大PV数100というショボい回数になっています。件数が増えても線形に秒数が伸びるだけなので何卒ご了承ください。
結果
0.024757862091064秒
処理全体をBEGIN~COMMITで囲っているため、ジャーナルファイルが作成されるのも1回だけであり、さすがに早いです。
UPSERTに関する余談
もしかしたら見慣れない人がいるかもなのでSQL文について補足しておきますが、下記のSQL文はいわゆるUPSERT(あったら更新なければ挿入する処理)です。
INSERT INTO count_page (page_seq, cnt) VALUES (?, 1) ON CONFLICT(page_seq) DO UPDATE SET cnt=cnt+1
ページ番号(page_seq)をランダムに決めて、そのPV(cnt)をカウントアップするため、愚直に書くなら「ページ番号でSELECT」→「レコードが存在しなかったらINSERT」OR「レコードが存在したらUPDATE」という処理をします。
しかしそれでは読み取り操作(SELECT)と書き込み操作(INSERT/UPDATE)の2回分、SQL側に命令を送ることになり、無駄が多いため各データベースシステムではUPSERT(UPDATEとINSERTを合わせた造語)用の仕組みが用意されていたりします。
これがSQL文の標準にあると良いのですが、残念ながら各システムごとに書き方が異なり、当然のようにSQLiteとMySQLでも書き方が異なるため、変数にセットしています。
逐次更新
ほぼ同じコードでBEGIN~COMMITの位置を変えただけなので前後半は省略します。
<?php
//~前半省略~
for ($i = 1; $i <= $cfg['max_pv']; $i++) {
$db->query("BEGIN IMMEDIATE");
$page_seq = mt_rand(1, $cfg['max_page']); //ランダムなページ番号を取得
$st->execute(array($page_seq)); //そのページ番号のPVをカウントアップ
$db->query('COMMIT');
}
//~後半省略~
?>
当たり前ですが、アクセス解析に用いるのであればアクセスが発生するたびにBEGIN~COMMITで更新を完結させなければならないので、こちらがより本番に近い形です。
また、トランザクションの開始がBEGIN IMMEDIATEになっているのはこうしておかないと、この後実施する複数プロセスでの並列更新時にDatabase is lockedが発生するから、です。以前の記事で書いたとおり、IMMEDIATEやEXCLUSIVEを付けないとSQLiteは設定したタイムアウト値を無視しちゃうので。
結果
1.4919309616089秒
だいぶ遅くなりました。SQLiteはトランザクション中にジャーナルファイルを作って、コミット時にそのジャーナルファイルを本体へ反映させる、という仕組み上、BEGIN~COMMITが頻繁に発生すると途端に遅くなる性質があります。
MySQLの速度テスト
一括更新
<?php
$cfg['max_page'] = 10; //ページ数
$cfg['max_pv'] = 100; //最大PV
//MySQL
$cfg['PDO_DSN'] = 'mysql:host=localhost;dbname=[MySQLのデータベース名];';
$cfg['PDO_USER'] = '[MySQLのユーザー名]';
$cfg['PDO_PASS'] = '[MySQLのパスワード]';
$cfg['INSERT_QUERY'] = 'INSERT INTO count_page (page_seq, cnt) VALUES (?, 1) ON DUPLICATE KEY UPDATE cnt=cnt+1';
set_exception_handler('myExceptionHandler');
$db = new PDO($cfg['PDO_DSN'], '', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_TIMEOUT => 120));
$t1 = microtime(true);
$db->query("BEGIN");
for ($i = 1; $i <= $cfg['max_pv']; $i++) {
$page_seq = mt_rand(1, $cfg['max_page']); //ランダムなページ番号を取得
$st->execute(array($page_seq)); //そのページ番号のPVをカウントアップ
}
$db->query("COMMIT");
$t = microtime(true) - $t1.' sec.';
file_put_contents('count_page.log', date('Y-m-d H:i:s').' OK:'.$t."\n", FILE_APPEND | LOCK_EX);
function myExceptionHandler($e)
{
file_put_contents('count_page.log', date('Y-m-d H:i:s').' NG:'.$e."\n", FILE_APPEND | LOCK_EX);
exit();
}
?>
SQLiteのときとコードはほぼ同じで、違いはDBへの接続情報とINSERT文くらいです。
SQLiteのUPSERTは INSERT ほにゃらら ON CONFLICT [キー] DO UPDATE ほにゃららですが、MySQLでは INSERT ほにゃらら ON DUPLICATE KEY UPDATE ほにゃらら と書きます。
うちの運営サイトは趣味でやっているだけあって、気分でデータベースシステムを変更するので、このへんのSQL方言を書き換えるのがちょっと手間です。
結果
0.37655901908875秒。
ドン引きするほど遅くてビビりました。SQLiteが0.024757862091064秒のところ、MySQLで0.37655901908875秒。桁数多くてワケわからんかもですが、1000倍のデータなら24秒と376秒です。
なんだこれは、と思って再テストしたらMySQLで0.022398948669434秒が出ました。SQLiteより早い。なんでや。キャッシュか。
その後mariadbを落としたり、PC再起動して試したところ、どうやら1回目だけMySQLのほうが遅いっぽいことがわかりました。2回目以降はMySQLのほうが早い。SQL文のコンパイルとかしてるのかもしれんなぁ。
逐次更新
こちらもSQLiteとほぼ同じコードですが、MySQLにBEGIN IMMEDIATE構文は存在しないためBEGINに変更しています。
<?php
//~前半省略~
for ($i = 1; $i <= $cfg['max_pv']; $i++) {
$db->query("BEGIN");
$page_seq = mt_rand(1, $cfg['max_page']); //ランダムなページ番号を取得
$st->execute(array($page_seq)); //そのページ番号のPVをカウントアップ
$db->query('COMMIT');
}
//~後半省略~
?>
結果
0.38997292518616秒。
はやっ
キャッシュが効いたりしてそうだったので、mariadbを再起動してからテストしてみましたが、逐次更新でも0.38秒です。一括更新時でも0.37秒(2回目以降は0.02秒)だったわけで、MySQLにおいては一括更新でも逐次更新でもあまり差がないことになります。
複数プロセスの並列更新ではどうなるのか
一番大事なのはここです。Webサイトという不特定多数のユーザーが同時に並列にアクセスしてくる状況でどのくらいのパフォーマンスが出るのか、それが大事。
start /B php test.php start /B php test.php start /B php test.php ...
今回もWindows上でのテストなのでstartコマンドの出番です。上記のように /B オプションを付けて、10行(=10プロセス)と30行(=30プロセス)のパターンでテストしてみました。
並列処理のテスト結果
並列実行されるため、一番時間のかかったプロセスを列挙しています。
SQLite | 10プロセス | 21.216676950455秒 |
MySQL | 10プロセス | 0.94095396995544秒 |
SQLite | 30プロセス | 66.291305780411秒 |
MySQL | 30プロセス | 1.8404700756073秒 |
うわぁ… 想像以上にSQLiteが遅せぇ…。
SQLite版の一番早いプロセスは1.5秒で更新が終わっていますが、一番遅いのは66秒もかかっています。
本来、1.5秒×30プロセスで、約45秒で終わるはずですが、ロック待ちの処理に余分な時間がかかってそうですねぇ。
一方でMySQLはどのプロセスも1.2~1.8秒くらいの間で、綺麗に並列実行されていることがわかりました。
まとめ
MySQLよりもSQLiteのほうが更新処理が早いらしいけれど、運営サイトで1年以上使ってきて、ロックが発生する状況の待ち時間が無視できないレベルに思えたので実際にテストしてみました。
単純すぎるテーブル構造とクエリーですし、キャッシュやSQL文のコンパイル状況もどうなっているかわからないため、検証というには到底足りないテストですが、少なくとも並列処理が多い状況下ではMySQLを使うべき理由がわかった気がします。
そんなの言われるまでもねぇ~、って感じだとは思うんですけども、以前このブログでも書いたとおり、世界的にWordPressの利用率が高く、共用レンタルサーバーではMySQLの処理がいっぱいいっぱいになっているところもあるんですね。
実際、今回のようなアクセス解析みたいなコードをMySQLで作り、レンタルサーバー上で実行したら、最大接続数がオーバーしてMySQLへ接続できなくなりました。
SQLiteは並列処理に弱くても同時接続数オーバーでのエラーはありません。なので、月並みではありますが、作るシステムや要件によってデータベースシステムを適切に選ぶことが大事そうです。