SQLiteかPostgreSQLか？複雑です！
読む所要時間: 19 分
June 27, 2022
執筆者
レビュー担当者
Twilioではブログを非常に重要視しています。ブログでどのようなコンテンツが効果的で、どのようなコンテンツがそうでないかを理解するために、筆者、チーム、製品、公開日など、各記事で保持しているメタデータと、Google Analyticsからのトラフィック情報を組み合わせたダッシュボードを用意しています。ユーザーは、さまざまな方法でデータをフィルタリングしたりグループ化したりしながら、チャートや表をインタラクティブにリクエストできます。
私はこのダッシュボードをサポートするデータベースにSQLiteを選びました。これは、このシステムを構築した2021年初めには、チームメイトや私がブログの改善に使用できる小さくてニッチなアプリケーションと考えられるものに最適な選択だと思えました。しかし、ほぼ1年半後には、このアプリケーションはTwilioブログとSendGridブログで1日8000件近くのトラフィックを追跡し、個々のトラフィック記録は1日約650万件に達し、ユーザーベースは200人以上の従業員に成長しました。
あるとき、結果を出すのに数秒かかるクエリーがあることに気づきました。PostgreSQLのような堅牢なデータベースなら、より優れたパフォーマンスを提供できるのではと思い始めました。私はパフォーマンスベンチマークが好きではないということを公言していたため、オンラインで比較して調べたいという衝動に耐え、その代わりに、このアプリケーションの特定のユースケースについて、これら2つのデータベースのパフォーマンスを正確に測定するための一連の実験に着手しました。以下は、私の努力、テストの結果（驚くべき展開も！）、私の分析と最終的な決定の詳細ですが、予想以上に複雑なものでした。
PostgreSQLへのアプリケーションの移植
最初のステップは、アプリケーションを簡単に再設定できるようにして、SQLiteとPostgreSQLのいずれも使えるようにすることでした。これにより、両者をすぐに比較できるからです。そこで、MacのノートパソコンにPostgreSQLをインストールして、開発で使えるようにしました。
PostgreSQLと互換性があるようにコードを調整するのは比較的簡単でした。このアプリケーションはPython用のORMライブラリであるSQLAlchemyを使用しているため、開発者はほとんどデータベースに依存しないPythonの構造体を使用してスキーマやクエリーを作成することができます。データベース接続URLを設定可能にすることで、postgresql://とSQLite://のURLを切り替えられるようにしました。これらのデータベースの違いはSQLAlchemyにより内部的に処理されているため、Pythonコードへの変更は必要ありませんでした。SQLAlchemyは、新しいPostgreSQLデータベースにもすべての表とインデックスを作成しました。これは、SQLiteでもまったく同じでした。
アプリケーションがPostgreSQLと完全に連携できるようにするために、現在SQLiteデータベースに保存されているデータも移行する必要がありました。私は、pgloaderという、いくつかのソースからPostgreSQLデータベースにデータをインポートする、かなり完成度の高いオープンソーススクリプトを使用しました。幸いにも、SQLiteはサポートされているデータソースの1つです。
pgloaderでデータをインポートする最も簡単な方法は、ソースや宛先、オプションを定義する構成ファイルを作成することです。私が作成したものは以下のとおりです。
私が使用した唯一のオプションはdata onlyで、これは、pgloaderに、表やインデックスを作成せずに、ソースデータベースからデスティネーションデータベースにデータを移動するよう指示するものです。これは重要なことです。なぜなら、データベースのスキーマをできるだけSQLiteのものに近づけたかったからで、そのための最良の方法は、SQLAlchemyにすべてのテーブルとインデックスを作成させることだからです。
データ移行を実行するために、pgloaderは構成ファイルの名前を唯一の引数として起動されます。
数回試行した後、インポートプロセスは滞りなく動作するようになりました。最初に失敗した試行では、データベース設計に深刻な問題を抱えている可能性があることが通知されました。ただ、SQLiteでは、そのことを報告したり気にしたりすることはありませんでした。
- 記事のスラッグに対し、私は256の長さの
VARCHARカラムを定義していました。スラッグが最大値を超えていた4つの記事で、データ移行に失敗しました。SQLiteは特異なタイピングシステムを備えており、長いスラッグも気にせず、不平も言わず保存しました。PostgreSQLでは、カラムタイプが厳格に適用されるため、これら4つの投稿を移行することができませんでした。解決策はslugカラムを512文字に拡大して再試行することでした。 - また、タイピングに関連して、テーブルの1つには当初整数の主キーがありましたが、ある時点でこれらのキーをUUID文字列に変更することにしました。SQLiteでこの変更を実施した際、テーブルを手動で移行しましたが、このテーブルの主キー列を
integer（整数）として定義したままにしていたことに気付きませんでした。もう一度言いますが、SQLiteはこのカラムに私のUUIDを問題なく格納し、インデックスまで作成しましたが、これらをPostgreSQLに行わせるには、スキーマを修正し、これらのキーを文字列として適切に宣言する必要がありました。
この時点で、比較的少ない労力で、設定ファイルのデータベースURLを編集してPythonサーバーを再起動するだけで、同等のSQLiteデータベースとPostgreSQLデータベースを切り替えることができるようになっていました。
開発用のノートパソコンでいくつかのクエリーを手動でテストしたところ、PostgreSQLデータベースではすべてが高速であると感じました。これは、心強い第一印象でした。
パフォーマンステストの作成
次のタスクは、自動化可能で繰り返し可能なテストを作成することでした。これにより、両方のデータベースでシステムをテストして、どちらが優れているかを判断できるようになります。簡単でしょう？
残念ながら、このシステムを使用するすべての方法をカバーするテストの設計は非常に困難です。ユーザーが多くの異なるクエリーを作成できるように設計したため、可能性の数がとても多いのです。たとえばユーザーは、数日、数週間、あるいは数年にわたる期間のトラフィック統計を取得し、その結果を、記事や筆者、チーム、製品、コンピュータ言語、執筆言語、公開日別に分類できます。また、トラフィック結果を発信国別に分割することもできます。記事を取得するときに、ユーザーは記事のすべての翻訳をオリジナルの記事とグループ化して表示することを選択できます。すべてのクエリーについて、ユーザーは、筆者、チーム、製品、コンピュータ言語、執筆言語、公開日の任意の組み合わせにより、指定されたデータセットのセクションをフィルタリングで取り込んだり取り除いたりすることができます。こうしたすべてのことを、TwilioブログまたはSendGridブログ、または両方のブログで実行できます。
本筋から逸れないよう、私は最も一般的なユースケースに集中することにしました。そこで、それらを表すAPIリクエストのリストを作成しました。以下がそのリストです。
これらのURLには、追加のクエリー文字列引数を指定する必要があります。start引数とend引数は、要求される期間を指定するため、すべてのクエリーで必要です。これが応答時間に大きな影響を与えると判明したので、計画では期間の長さを変えてみることにしました。
フィルタの実装に使用できるクエリー文字列引数は多数あります。上記のURLに入れたblog引数は、トラフィックが最も多いTwilioブログを選択します。私は他のフィルターでテストを複雑にしないことにしました。使用状況ログを見てみると、ユーザーからのほとんどのクエリーには何も含まれていないことに気付いたからです。
このリストをもとに、これらのクエリーを実行し、各クエリーがどれだけの時間を費やしたかをresults辞書に記録する、次の短いPython関数を作成しました。
test()関数はrequestsリストのコピーを作成し、ランダムに並べ替えてから、curlをサブプロセスとして使用してリクエストを実行します。Python標準ライブラリのtimeit()関数を使用して、各クエリーが応答を返すのにかかる時間を測定し、対応するURL配下でresults辞書にこの測定値を追加します。
リストをランダム化する理由は、この関数の複数のインスタンスを並行して実行し、同時クライアントをシミュレートするためです。関数にランダムな順序でクエリーを実行させることは便利です。これにより、データベースは、同じクエリーのインスタンスを複数受け取るのではなく、いつでもさまざまなクエリーを実行できるようになります。
この関数はURLのドメイン部分をserver引数として受け取ります。私はこれを簡単に設定できるようにして、開発システムと運用システムを切り替えることができるようにしたいと考えていました。APIキーもこの関数に渡されるため、リクエストを認証できます。最後に、必要なstartとendクエリー文字列引数が渡され、リクエストを行う前にURLに追加されます。
results辞書は、requestsリストのURLをキーとして使用します。各キーに対し、1つの値だけでなく、結果の配列を格納します。こうすることで、クエリーごとに複数の実行を記録し、平均値を得ることができるからです。
テストスクリプトを完成させるために、次のようにrun_test()関数とコマンドラインパーサーを追加しました。
このrun_test()関数はnum_threads引数に応じて1つ以上のスレッドを起動します。すべてのスレッドは、test()関数を並列に実行するように構成されています。これにより、複数の同時クライアントからの負荷がシミュレートされ、それぞれがURLのリストを独自のランダムな順序で実行し、予測不可能な状態を作り出します。
すべてのスレッドが終了した後、run_test()は各リクエストURLの平均リクエスト時間と、すべてのクエリーの合計の平均を表示します。これは、私が分析に使用することにしたメトリックです。
コマンドライン引数を使用すると、サーバールート、APIキー、開始日と終了日をクエリーに渡し、同時実行ができます。これらのコントロールにより、さまざまなシナリオをテストすることができるのです。
これでテストスクリプトの準備ができました。メトリックをいくつか確認してみましょう！
開発システムでのテスト
私が作業している開発システムはMacのノートパソコンで、6つのハイパースレッディングされたコアと16GBのRAMを搭載しています。このダッシュボードの本番環境は、1台のvCPUと2GBのRAMを搭載したLinode仮想サーバーです。
ベンチマークの過去の経験から、私は高速システムの結果が必ずしも遅いシステムと同じではないことを知っています。そのため、私の最終目標は、本番システムをテストし、そのプラットフォームで得られた結果に基づいて判断することです。
しかし、それを行う前に、テストスクリプトが正しく動作していることを確認するための方法として、ノートパソコンで最初の「練習」テストを一通り行いたいと考えました。また、この2つのデータベースがかなり強力なプラットフォームでどのように動作するかを知りたいと考えていたため、これを実行するのは当然のことでした。
使用することにしたテスト方法は次のとおりです。1週間、1か月、1四半期、1年の期間に2つのデータベースで実行されているシステムをテストします。どのクエリーも開始日は2021年1月1日です。また、同時クライアント数1、2、4でテストを繰り返します。個々のテストごとにスクリプトを3回実行し、3回のうちで最良の実行を記録します。使用するメトリックは、すべてのクエリーの合計平均です。
このプランでは、最終的には24のデータポイント（2つのデータベース x 4つのクエリー期間 x 3つの同時実行レベル）を使いました。次の表は、1つのクライアントでPostgreSQL（青）とSQLite（赤）の応答時間を示しています。
これによると、PostgreSQLがより速く結果を返し、特に短いクエリーには効率的であることは明らかです。PostgreSQLの応答時間は、クエリー期間が長くなるにつれて直線的に増加または減少しますが、SQLiteでは最初の3つのテストでほぼ一定の応答時間であり、最後のテストでは大きな影響が出ました。
以下は、同時クライアント数が2と4の場合の同じテストのグラフです。
同時実行数を変えても、ほとんどのテストで応答時間に大きな影響がないように見えることは興味深いと思いました。おそらく、このノートパソコンに複数のコアが搭載されているからでしょう。唯一差があるのは1年のクエリーで、PostgreSQLがSQLiteよりも比例的に多くの影響を受けているように見えます。
これを視覚化するために、PostgreSQLテストの応答時間を示すグラフを以下に示します。各クエリー期間の3つの同時処理レベルを比較しています。
これを見ると、クエリーの行数が少ないか中程度の場合、最大4つのクライアントを同時実行しても応答時間に大きな影響が出ないことが分かります。しかし、含まれる行数が増えるにつれて、同時実行数の増加により速度が低下し始めます。
SQLiteの場合の同じグラフを次に示します。
これは、SQLiteがこのシステム上のPostgreSQLよりもはるかに優れた同時負荷を処理できることを示しており、本当に驚きです。もちろん、応答時間はこちらの方が長いのですが、同時実行がこれらの応答時間に与える影響が少ないという事実は非常に興味深いものです。
本番プラットフォームでも同様の結果が示されるのでしょうか？私の予想では、自分のノートパソコンの6CPUと比べて、シングル1CPUでは同時実行性が結果に大きく影響する要因になると思います。
本番システムでのテスト
十分にテストされたベンチマークスクリプトを使用して、本番システムのクローンで新しいLinodeを起動し、SQLiteデータベースをPostgreSQLに移行するためのすべての手順を繰り返しました。また、設定ファイルを編集してサービスを再起動すれば、データベースを簡単に切り替えられることを再確認しました。
このテスト作業では、自分のノートパソコンでテストスクリプトを実行することにしました。そのため、インターネット経由のトラフィック、暗号化、プロキシ設定に関わる遅延がすべて結果に含まれるはずです。サービスと同じホスト上でテストを実行し、追加コストを回避する選択もできましたが、結局のところ私の目標はユーザーに対しサービスのパフォーマンスを向上させることです。ユーザーは、こうした無関係に見えるすべてのレイヤーをくぐり抜ける以外に道はないのです。
以下は同時実行数を1にした場合に得られたグラフです。
これはまったく予想外の展開です。線が交差しているのです！
PostgreSQLは、短いクエリーと中程度の長さのクエリーではSQLiteよりも優れていますが、1年という長いクエリーでは問題が多く、SQLiteのほぼ2倍の時間がかかります。これはまぐれだろうとお考えの方のために、テストを何回か繰り返し、この結果が実際に正しいことを確認しました。
以下は、本番サーバーでクライアント数が2と4の場合のグラフです。
どの同時実行レベルでも曲線の形状はほぼ同じですが、Y軸のスケールを見ただけで、このプラットフォームでは、応答時間が私のノートパソコンよりも同時処理による影響を大きく受けることが分かります。同時実行クライアント数を4にした1年のクエリーでは、応答時間は非常に遅く、PostgreSQLは約14秒、SQLiteは約8秒でした。
PostgreSQLとSQLiteは同じように同時実行による影響を受けるのでしょうか？それを探るため、別々に見てみましょう。次のグラフは、PostgreSQLが同時クライアント数1、2、4で実行した4つの期間での結果を示しています。
これを見ると、このデータベースではサイズの大きなクエリーの制御が非常に難しい一方、小・中規模のサイズのクエリーの応答時間は比較的安定していることがわかります。
以下はSQLiteの同じグラフです。
これはまた異なるイメージではないでしょうか？シングルCPUの場合、SQLiteでの同時実行は、小・中規模のクエリーでも役割を果たします。応答時間は直線的には増加しませんが、小規模のテストでも常に増加しています。
こうした違いをより分かりやすく視覚化するために、別のグラフセットを作成しました。テストした各クエリー期間について、各同時実行ポイントで2つのデータベースを比較したのが次のグラフです。
これらのグラフは、PostgreSQLがSQLiteよりも小・中規模のクエリーを処理する能力が高いことを明確に示しています。この時点までは、同時実行の増加が応答時間に与える影響は非常に小さく、これは素晴らしいことです。一方、SQLiteは、すべてのクエリーサイズにおいて同時実行数による影響を直接受けます。また、小・中規模のクエリーではパフォーマンスが低下しますが、長いクエリーではPostgreSQLを大きく上回る結果が出ます。
パフォーマンスの微調整
これまではデータベースの設定については気にせず、デフォルトの設定で十分だと考えていました。SQLiteの場合設定することはあまり多くないのですが、PostgreSQLには操作すべきチューニングレバーがいくつかあります。
メモリの使用状況に関連する設定オプションを確認したところ、work_memというオプションを見つけました。これは一時ディスクファイルへのデータの保存を開始する前に、各クエリーが使用できるメモリ容量を設定するものです。このオプションのデフォルトは4MBです。私はこれを16MBに変更しました。
週、月、四半期のテストでは、以前と同様の応答時間でした。しかし、この変更により長いクエリーの結果が改善されました。1年間のPostgreSQLテストを再度実行した後の、クライアント数1、2、4でのグラフを以下に示します。
この変更により線が交差することはなくなり、PostgreSQLは、長いクエリーの場合でもSQLiteよりも速く返すようになり始めました。1年間のクエリー、クライアント数が4という負荷の高いテストでは、メモリ調整されたデータベースサーバーにより応答時間が14秒から6秒に短縮されました。これらのクエリーは、私が望むよりもまだ遅いものですが、ローエンドプラットフォームで実行していることを考えると、妥当だと思います。
まとめ
お察しの通り、この結果を受けて私はPostgreSQLに切り替えることにしました。本番システムをアップグレードした後、数人のユーザーにパフォーマンスについてそれとなく尋ねてみたところ、システムは以前よりも明らかに高速で応答していると全員が考えており、これで私のミッションは達成されました。
この記事から何か1つ得ていただくものがあるとすれば、価値あるベンチマークは、独自のプラットフォーム、独自のスタック、独自のデータ、独自のソフトウェアで実行されるものだけである、ということです。さらにその場合でも、最高のパフォーマンスを得るには、カスタムの最適化を追加しなければならないことがあります。
パフォーマンスをさらに向上させるという点では、このテスト演習では、次の2つの可能性を残しています。
- 予算があれば、CPUを追加することで、より長いテストの結果を改善できる可能性があります。CPUを4台搭載した専用サーバーに切り替えるとかなりの違いが出て、私のノートパソコンで測定した応答時間に近づくはずです。
- 問題に追加資金を投じることなく改善するなら、主な改善領域は長いクエリーにあります。データベースが日次データに加えて事前計算された月次合計を保存していれば、さらに単純化できるかもしれません。そうすれば、1年間のトラフィックを、1記事につき365行ではなく、12行から取得できます。クエリーが月の境界に整合していない場合は、削減分がより複雑なロジックにより相殺されることもありますが、全体的には長いクエリー時間を短縮できるはずです。
この記事で新しいアイデアをいくつか見つけていただければ幸いです。ご自身のプロジェクトをベンチマークし、最適化することをお勧めします。
Miguel Grinbergは、Twilioのテクニカルコンテンツ担当プリンシパルソフトウェアエンジニアです。このブログでは皆様のプロジェクトを紹介します。ぜひ、mgrinberg [at] twilio [dot] comまでご連絡ください。
