PHP用のSQLiteのチューニング

この記事では、PHPプロジェクトでのSQLiteの使用を評価し、マルチスレッド作業の機能と作業を高速化する方法について説明します。 「デザート用」-他のDBMS（MySQL、PosgreSQL、MS SQL、MongoDB）との小さな比較。

開始する

少し前まで、自分のプロジェクトのプロモーションコードのサービスを自分で作りたかったのです。 プラットフォームの選択において、私は長い間疑いませんでした。 PHP環境がデプロイされたサーバーがあるので、サービス自体はPHPで安全に作成できます（特に慣れているため）。 DBMSとして、SQLiteを試してみたかった（ただし、MySQLとPostgreSQLはサーバーにデプロイされています）。 その理由は、「戦闘」環境への転送（フォルダーのコピーのみ）、「裸」サーバーへの展開が簡単だからです（PHP以外は必要ありません）。 さらに、バックアップを簡素化したかった（ディスクスナップショット+結果の「スライス」の単純なコピーを使用する予定だった）。

ただし、使用する前に、このソリューションの適用性とパフォーマンスを確認する必要があります。

鉄

テストは2つのオブジェクトで行われました。

• ワークステーション。 Intel i5。 12GB RAM。 SSD + HDD（WDブラック）。 Windows8。PHPを実行するためにOpenServerによって使用されます。
• Selectelのクラウド。 Debian 6.サービスがあります。 デフォルト構成でLAMPをインストールしました。 メモリ消費を削減するために、Apacheがさらに削減しない限り。

予備評価

最初のパフォーマンス評価のために、簡単なスクリプトが作成されました。

<?php //   function initDB() { $guid = ''; //      if (function_exists('com_create_guid') === true) { $guid = trim(com_create_guid(), '{}'); } else { $guid = sprintf('%04X%04X-%04X-%04X-%04X-%04X%04X%04X', mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(16384, 20479), mt_rand(32768, 49151), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535)); } //     $file = "_$guid.db"; //   ( ) $db = new SQLite3($file); //   $db->exec('CREATE TABLE foo (i INT, i2 INT)'); return $db; } //    function testDB($db, $count) { for ($i=0; $i<$count; $i++) testIteration($db, $i); } // 1   function testIteration($db, $iteration) { $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } // ----------     ---------- //    $COUNT = 1000.0; if(isset($_REQUEST['COUNT'])) $COUNT = $_REQUEST['COUNT']; //   $db = initDB(); //    $start = microtime(true); //   testDB($db, $COUNT); //   $time = microtime(true) - $start; //   echo "$COUNT inserts per " . number_format($time, 2) . "sec. " . number_format($COUNT / $time, 2) . " operation per second"; ?> 

テーブルに1000レコードを順番に挿入します。 費やした時間を測定します。 1秒あたりの操作数を計算します。
原則として、大まかな評価のために、それ以上は必要ありません。
トランザクションは特に使用していません。 各操作は、独自のトランザクションで実行されます。 この動作モードは、マルチスレッドモードでのベースの実際の使用に近いものです。

そして今、結果：
SSDについては、1秒あたり365操作のスコアを獲得しました。 悪くない。
HDDの場合、13の操作のみを受け取りました。 悲しいです しかし、サーバーは何と言いますか？
そして、 サーバーは満足しています。 210操作。
一般に、サーバーのパフォーマンス評価はかなりまともです。 SQLiteを使用してPHPのテストを続行できます。 ただちに、サーバーには高速ファイルサブシステム（SSD）を使用する必要があることに注意してください。
私たちの場合、私が言ったように、クラウドはSelectelで使用されています。 SSDは、ディスクとクラウドマシン間のキャッシュとして機能します（いずれにせよ、悪名高いamaraoの話から理解したように）。

マルチスレッド操作

ここで、ベースがマルチスレッドモードで動作できるかどうかの質問に答えてみましょう。
SQLiteに関する一連のすばらしい記事を読む： habrahabr.ru/post/149356
また、マルチスレッドモードでは、WALログを有効にする必要があることがわかります。 データベースの初期化に行を追加します（開いた後）：

 $db->exec('PRAGMA journal_mode=WAL;'); 

そしてすぐにテストを実行します：
SSD： 1秒あたり2000トランザクション。 インジケーターは5倍以上成長しました！
HDD： 42操作。 3倍の成長。
サーバー： 1000操作。 成長はほぼ5倍です。

非常に予期しない結果。 ログモードを簡単に変更すると、5倍に増加します。
次のステップは、マルチスレッド用にスクリプトを書き直すことです。
複数のスレッド（サイトの実際の操作により近い）でPHPスクリプトを数回呼び出すことにより、マルチスレッドを実現します。 これを行うには、ユーティリティAB（Apache Benchmark）を使用します。

 ab -n 1000 -c 10 "http://localhost:81/test/benc_sqlite.php" 

結果のスクリプトテキスト：

 <?php function openDB() { //     $file = "_TEMP.db"; //   ( ) $db = new SQLite3($file); //   $db->busyTimeout(5000); $db->exec('PRAGMA journal_mode=WAL;'); return $db; } //   function initDB() { $db = openDB(); //   $db->exec('CREATE TABLE foo (i INT, i2 INT)'); return $db; } //    function testDB($db, $count) { return testIteration($db, mt_rand(0, 1000)); } // 1   function testIteration($db, $iteration) { return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } // ----------     ---------- //   if(isset($_REQUEST['init'])) $db = initDB(); else $db = openDB(); //   if(testDB($db, 1)) echo 'OK'; else echo 'FAIL'; ?> 

挿入速度の測定はスクリプトから削除されています。ABはこれを提供します。 現在、初期化は1回のみ実行されます。 1つのデータベースで作業が進行中です。
さらに、インストール済み

 $db->busyTimeout(5000); 

これは、マルチスレッド記録を試みるときに、プロセスがそのキューをしばらく待機し（5秒に設定）、「SQLITE_BUSY」という事実にすぐに落ちないようにするために必要です。

結果：
SSD：毎秒970回の操作。 十分すぎるほど。
HDD： 35操作。
サーバー：ローカルマシンからテストする場合は90操作、サーバーからABを起動する場合は210操作。 SSDでの実行との非常に大きな違い。 どうやら、これはシステムの構成の違いによるものです（Apacheサーバーでは、可能な限り最小化されています）。
さらに、ローカルインジケーターが非常に大きく変動し、サーバーの曇りが影響することに注意する必要があります。 私からサーバーまでは約5千キロメートル。
いずれにせよ、1秒間に最低90回の操作を行ったとしても、私の意見ではこれで十分です。 たとえば、Habrの平均ビュー数は1秒あたり30回です（もちろん、午後は強いピークがありますが、Habrの前に成長および成長しています）。

将来的には、ローカルテスト中にSSDおよびHDDのインジケータが取得されます。 そして、サーバーのために-リモートマシンから。
サーバーとSSDのテストは、10スレッドで1000リクエストを実行することにより行われます。 HDDの場合-10スレッドで100リクエスト。

合併症

テストでは、1つの単純なINSERT要求があったことを思い出させてください。 原則として、それに基づいて1つの操作を実行するサービスがあると便利です。 しかし、現実とはかけ離れています。 最大3つのクエリ（1つのSELECT、1つのUPDATE、1つのINSERT）を複雑にします。
サマリー関数testIteration：

  function testIteration($db, $iteration) { $rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration); if($rez > 0) { $db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration); } return $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); } 

SELECTクエリがなくても実行できることは明らかですが、それでもテストがあり、最適化の競合はありません。

指標：
SSD： 420リクエストから。 実行ごとに、レートは低下します。
HDD： 20件のリクエスト（およびクラッシュ）から。
サーバー： 65リクエスト（およびクラッシュ）から。

DB最適化

パフォーマンスの低下が気に入らなかった。 さて、この問題を修正してみましょう。 具体的には、インデックスを追加します。 1つのフィールドのみを検索するため、インデックスに追加します。

初期化で、次の行を追加します

 $db->exec('CREATE INDEX "idx_foo" ON "foo" ("i");'); 

結果：
SSD： 671要求。 とても良い。
HDD： 25〜12リクエスト。 問題が何であるかはわかりませんが、結果は指標ではありません。 おそらく、サーバーとSSDは10スレッドで1000リクエスト、HDD-10スレッドで100リクエストをテストしているという事実が原因です。 桁違いにデータが少なくなります。 しかし、そのような広がりは影響を受けてはいけません。
サーバー： 60リクエスト。

まあ、結果は私にとって非常に楽しいです。 しかし、すでに2つの基本変更操作があります。トランザクションはどこにありますか？ 追加してください：

  function testIteration($db, $iteration) { $rez = $db->querySingle('SELECT count(*) FROM foo WHERE i='.$iteration); $db->exec('BEGIN;'); if($rez > 0) { $db->exec('UPDATE foo SET i2 = i2+1 where i='.$iteration); } $rez = $db->exec('INSERT INTO foo VALUES ('.$iteration.', 1)'); $db->exec('COMMIT;'); return $rez; } 

そして再び結果：
SSD： 720リクエストから。
HDD： 30リクエストから。 そして今回は、インジケーターは非常に安定しています。
サーバー： 70リクエストから。

データベース障害の最適化

Apache Benchmarkは、クエリの実行に加えて、リクエストの成功を監視します。 応答の長さが取得され、元の応答よりも長いまたは短い応答はすべてエラーと見なされます。 興味深いのは、同様の問題をテストすると、長い間現れ始めたことです。 1000リクエストにつき1ダースだけにしますが、それでもです。
ログでは、メッセージ「SQLite3 :: exec（）：database is locked」。 奇妙な、WALモード、トランザクション、および「データベースはロックされています」。 ソリューションは、SQLiteメーリングリストで見つかりました： http : //www.mail-archive.com/sqlite-users@sqlite.org/msg05525.html
マルチスレッドモードでは、「BEGIN」ではなく「BEGIN IMMEDIATE」または「BEGIN EXCLUSIVE」を使用する必要があります。 マルチスレッド作業用の特別なトランザクション。 「BEGIN EXCLUSIVE」モードでもこの問題が発生することがありましたが、「BEGIN IMMEDIATE」モードでは完全に解決されました。

そして今、最終テスト：
SSD： 700リクエストから。
HDD： 30リクエストから。 さらに、インジケーターは非常に安定しています。
サーバー： 53リクエストから。 奇妙なことに、私は約70を期待していました。しかし、これは雲であり、安定性については語れません。

ちなみに、リクエスト数のインジケータは時間とともに少し低下します。 クリーンベースでは、SSDは1000件のリクエストを発行します。 「ダーティ」-最大350。同時に、約50,000エントリがデータベースに蓄積されています。

マルチスレッド（1つのSELECT、1つのUPDATE、1つのINSERTクエリ+トランザクション+インデックス）を複雑にしたため、シングルスレッドテストが複雑な場合、SSDで毎秒1,500回の反復が発生することを付け加える価値があります。 努力すべきところがあります。 同時に、データベースが大きくなると、パフォーマンスはマルチスレッドモードの場合よりも約2倍遅くなります。
テストを繰り返しても、マルチスレッドと比較したSQLiteのシングルスレッドモードの重要な利点は明らかになりませんでした。

原則として、テストはかなり良い結果を示しました。 それを場所と矛盾し、物議をかもし、十分に学ばないようにします。 しかし、それでも個人的には、SQLiteとPHPの束のパフォーマンス評価はかなり示されています。

他のDBMSとの比較

私たちが見たSQLite。 しかし、これらの条件で他のDBMSはどのように動作しますか？
このテストは、ローカルマシンおよびSSDでのみ実行されます。 その理由は、サーバーよりもローカルマシンの方がDBMSが多く、このモードはサーバーの操作により近いためです。
手元には：SQLite（v3.7.7.1）、PostgreSQL（v9.2.1）、MS SQL Server（v11.0.3000.0（2012、Developer Edition））、MySQL（v5.5.28）、MongoDB（v2.4.4） 。
PHPは、SQLiteおよびMongoDBの標準ドライバーを使用しました。 PostgreSQL、MySQL、MS SQLはPDOを介して機能しました。
さらに、テストをわずかに変更することにしました。10,000リクエストが10スレッドで実行されます。 実験の純度のため、ベースは毎回空でした。
データベースの初期化は手動モードで行われます。 初期化には、フィールドごとにテーブルとインデックスを作成することが含まれます。



結果：
SQLite： 993クエリ/秒。 期待される結果です。
PostgreSQL： 1秒あたり178,202クエリ。 かなり小さい。 どうやら、問題はデフォルト設定にあります。
MS SQL： 1400クエリ/秒。 悪くない、非常に悪い。
MySQL： 1490クエリ/秒。 ここにデフォルト設定があります:)。 最初は1秒あたり9件のリクエストがあったことに注意してください。 localhostへの呼び出しを127.0.0.1に置き換えると役に立ちました。 私の知る限り、システム内のアクティブなIPv6に接続されています。
MongoDB： 1秒あたり3173クエリ。 素晴らしい結果。 NoSQLにはもっと期待していましたが。

率直に言って、比較は最も正直ではありません。 テストはデフォルト設定で行われました。 DBMS設定を調整するために必要な管理スキルがありません。
MongoDBは主に速度指向です。 SQLIteは信頼性に重​​点を置いています。 また、デフォルト構成のPostgeSQLは、最小のリソース消費に焦点を合わせています。 おそらくこれが失敗の理由です。

---

UPD： PostgreSQLは再接続を好まないと「大きな秘密」で教えてくれました。 そのため、DBMSごとにシングルスレッドテストを実施しました。
このテストでは、シングルプレスモードでの動作時間を測定します。 10,000操作、クリーンなデータベース。 トランザクションでの1 SELECT、1 UPDATE、1 INSERTの形式の操作。 データベースに接続せずに、スクリプトでの速度の測定（最初のテストのように）。
結果は次のとおりです。
PostgreSQL： 1403。7倍の成長。 これは、PostgreSQLの再接続に対する嫌悪を裏付けています。
PostgreSQL（fsync = off）： 1766。デフォルトモードに比べて4分の1増加。 マルチスレッドモードでは、増加は表示されませんでした。
SQLite： 938。ストレンジ、1500年以前。 おそらくテストに間違いがありました。
MS SQL： 1993。成長はありますが、重要ではありません。 どこかで40％（1.4倍の成長）。
MySQL： 2514。成長は1.7倍です。
MongoDB： 7603。成長はほぼ2.5倍です。

---

結果は非常に興味深いものです。 そのため、サーバー上のさまざまなDBMSのパフォーマンスを比較することにしました。 Debian 6が使用されていることを思い出させてくださいサーバーには、SQLite（v3.7.3）、PostgreSQL（v9.0.4）、MySQL（v5.1.66）があります。 すべてがデフォルト設定になっています。 ABはサーバー上でローカルに起動しました。 10スレッドで10,000リクエスト。
結果：
SQLite： 1秒あたり174クエリ。
PostgreSQL： 1秒あたり104クエリ。
MySQL： 1秒あたり167クエリ。

ご覧のとおり、ローカルマシンほど大きなギャップはありません。 ただし、サーバーがSSDを使用することを忘れないでください（「純粋な」形式ではなく、クラウドストレージの要素として）。

まとめ

その結果、PHPとSQLiteの組み合わせは、負荷の少ないプロジェクトに対して非常に機能的であると言えます。 いくつかの簡単なルールに従うだけで十分です。

1. WALロギングモードを使用します。
2. ビジータイムアウトの設定を忘れないでください。
3. レコードはトランザクションで結合されます。 そして単純ではなく、「すぐに開始」。

プロジェクトを「小さな」プロジェクトに帰することができない場合は、データベースにSSDを使用することが役立つ場合があります。

Hi-Loadについて話す場合、これを行う人は私のアドバイスなしで何をすべきかを知っています。

SQLiteを他のDBMSと比較すると、デフォルト設定で同等のパフォーマンスが得られます。 とにかく、SSDドライブを使用する場合。
同時に、セットアップと管理の容易さ（何も構成および管理する必要はありません）、別のサーバーへの転送の容易さ（単純なコピーと貼り付け）がSQLiteにいくつかの利点をもたらします。 主に開発者向け。 PHPでSQLiteを使用するには、PHP自体のみが必要です。