パフォーマンスを追求するためのSQLite設定

スポンサーリンク

私はVisualStudio2013 C#からデータベースをなるべく高速に使うことを目的としてサーベイをしてきました。

結論からいうと、MySQLやSQL ServerよりSQLiteが別格で高速です。SQLiteの欠点はセキュリティ機能がないこと。つまりログイン機能がありません。複数のアクセス主体に対して権限付与やアクセス制御をする用途ではSQLiteは不向きです。

ですが私のように、時系列データを大量につっこんで金融分析をやっている人からするとそんなアクセス制御は不要です。とにかくパフォーマンスが重要なのです。ここでいうパフォーマンスとは実行速度。速くリードして速くライトする、これが求められています。そういった目的ならSQLiteがベストです。

.NET Frameworkからアクセスするのは簡単です。あとDataTableクラスやDataGridViewと紐付けて更新するのはやめましょう。えらい遅くて全然処理が完了しなくなります。しっかりトランザクションの開始と終了を明示して、replace文を都度発行してまとめて更新しましょう。selectしてデータがあるか確認してから更新では遅いです。ユニーク制約を設定した上で、replace文で更新しましょう。

まずWALモードはやめましょう。ログ先行書込みはDB本体に加えてwalファイルとshmファイルができるため、DBのサイズがでかくなってくるとこれらのファイルサイズも爆発的に大きくなってきます。そうすると特にHDDにdbファイルをおいている場合は、DBへのアクセス速度が極端に落ちます。

WALモードに設定した当初は速いです。つまりまだWALファイルが小さい間は速いのです。WALモードは、walファイルに変更後の新しいデータを書いていって、古いデータはdbファイル本体に残したままにします。だからwalファイルが小さい間は速いですが、大きくなってくると遅くなります。

あとMemoryモードもやめましょう。Memoryモードはアプリケーションが落ちると、ほぼ確実にdbファイルが壊れます。一応復旧できるのですが、とても面倒です。DBファイルのサイズが大きくなればなるほど復旧に時間がかかります。10回くらい復旧経験して面倒になってMemoryモードはやめました。

おすすめはSync=OFF,Journal=Persistモードです。まずSyncをOFFにすると同期を取りません。同期を取らないというのは何かというと、OSにDBへのIOを依頼したら、その返答を待たないということです。つまりOSがDBにデータ書込完了をするのを待たないということ。待たないようにすると、これがものすごく速くなります。

この同期オフの欠点は、OSが落ちたときにDBが壊れるということです。OSが落ちることなんてそうそう無いので、私は同期はオフにしています。

そしてJournalモード。デフォルトはDeleteモードですが、Deleteというのは変更後のものをdbファイルに書いて、変更前のをjournalファイルに移し替える方法です。そして書込に成功したら、もうjournalファイルはいらないので消します。この消すのが遅いのです。そこで、Truncateモードというのがあります。これはjournalファイルの中身を全てゼロにします。ファイルを消すよりゼロクリアの方が速いからです。そして更に速いものにPersistモードがあります。これは無効フラグを立てることでjournalファイルはもう不要、ということを教えてあげるものです。これはたった1ビット立てるだけなのでとても高速です。

まとめると、SyncをOFFにしてPersistモードにする。これが一番だと私は結論づけています。さらにSSDにdbファイルを置くべきです。私はHDDにおいてやっていましたが、10GBを超えるとえらいパフォーマンスが落ちます。

ですがSSDだとそうはなりません。SSD上のSQLiteはほぼ無敵レベルに高速です。

スポンサーリンク