[解決済み】非常に大きなデータベースファイルを持つsqliteのパフォーマンス特性は何ですか？[クローズド]。

質問

2020年アップデート , 質問を投稿してから約11年が経過し、その後閉鎖されたため、新しい回答ができなくなりました。

ここに書かれていることはほとんどすべて時代遅れです。昔々、sqliteはメモリ容量に制限があったり、ストレージが2GB（32ビット）だったり、よくある数字だったのですが...まあ、それは昔の話です。

公式の制限事項が記載されています . 実質的には、ストレージが利用可能な限り、sqliteは動作すると思われます。 . メモリより大きなデータセットでもうまくいく、もともとメモリが薄かった頃に作られたもので、当初から非常に重要なポイントでした。

100GBのデータを保存しても全く問題ありません。TBを保存しても問題ないでしょうが、最終的にはSQLiteがその仕事に最適なツールであるかどうかを疑う必要があり、おそらく本格的なデータベースからの機能（リモートクライアント、同時書き込み、読み取り専用レプリカ、シャーディングなど）を求めることになるでしょう。

オリジナルです。

sqliteは、サポートされている場合でも、非常に大きなデータベースファイルではうまく動作しないことを知っています（以前は、sqliteのウェブサイトに、1GB以上のファイルサイズが必要な場合は、エンタープライズrdbmsの使用を検討した方がよいというコメントがありました）。今はもう見当たりませんが、sqliteの古いバージョンに関係しているのかもしれません）。

しかし、私の目的のためには、他の解決策を検討する前に、実際にどの程度悪いのかを把握したいのです。

2GB以降の数ギガバイト台のsqliteデータファイルのことです。 どなたか経験のある方はいらっしゃいますか？何かヒントやアイディアがあれば教えてください。

解決方法は？

そこで、sqliteで非常に大きなファイルについていくつかのテストを行い、（少なくとも私の特定のアプリケーションについては）いくつかの結論に達しました。

テストでは、1つのテーブル、または複数のテーブルを含む1つのsqliteファイルを使用しました。各テーブルは約8列で、ほとんどが整数、そして4つのインデックスを持ちます。

sqliteファイルが50GB程度になるまで十分なデータを挿入することでした。

単一テーブル

テーブルが1つだけのsqliteファイルに複数行を挿入しようとしました。ファイルが約7GB（行数については特定できなくてすみません）のとき、挿入にあまりにも時間がかかりすぎました。このテストでは、すべてのデータを挿入するのに24時間ほどかかると見積もっていたのですが、48時間経っても完了しませんでした。

このことから、単一の非常に大きなsqliteテーブルでは、挿入やおそらく他の操作で問題が発生するという結論に達しました。

テーブルが大きくなればなるほど、すべてのインデックスの挿入と更新に時間がかかるのは当然でしょう。

複数のテーブル

次に、データを時間ごとに複数のテーブルに分割し、1日に1テーブルずつにしてみました。元の1テーブルのデータは、〜700テーブルに分割されました。

この設定では、挿入に問題はなく、毎日新しいテーブルが作成されるので、時間が進むにつれて時間がかかることはありませんでした。

真空の問題

i_like_caffeineが指摘したように、VACUUMコマンドはsqliteファイルが大きくなればなるほど問題になります。挿入/削除が多くなると、ディスク上のファイルの断片化がひどくなるので、定期的にVACUUMしてファイルを最適化し、ファイル容量を回復するのが目的です。

しかし ドキュメント バキュームを行うには、データベースの完全なコピーが作成され、完了するまでに非常に長い時間がかかります。そのため、データベースが小さければ小さいほど、この操作は速く終わります。

結論

私の特定のアプリケーションでは、バキュームパフォーマンスと挿入/削除速度の両方を最適化するために、おそらくデータを複数のdbファイルに分割して、1日1回使用することになると思います。

そのため、クエリーは複雑になりますが、私にとっては、これだけのデータをインデックス化できることは、価値のあるトレードオフです。さらに、1日分のデータを削除するために、dbファイル全体を削除することもできます（私のアプリケーションではよくある操作です）。

ファイルごとのテーブルサイズも監視して、いつ速度が問題になるかを確認する必要がありそうですね。

の他にインクリメンタルバキューム方式がないようなのが残念です。 自動バキューム . 私のバキュームの目的はファイルのデフラグなので（ファイル容量は大したことない）、自動バキュームではできないので使えません。実際、ドキュメントによると、断片化を悪化させる可能性があるので、定期的にファイルを完全にバキュームすることに頼るしかないのです。