高負荷時のPostgreSQLのボトルネックの除去

こんにちは。現在、OTUSは新しいPostgreSQLコースストリームへの登録を受け付けています。この点で、私たちは伝統的にあなたのためにこのトピックに関する有用な資料の翻訳を用意してきました。

---

PeterZaitsevによるMySQLPerformance Bottlenecksに関する記事を基に、PostgreSQLについて少しお話ししたいと思います。



ORMフレームワークは、最近PostgreSQLで動作するためによく使用されます。通常は正常に機能しますが、時間の経過とともに負荷が増加し、データベースサーバーを調整する必要があります。PostgreSQLと同じくらい信頼性がありますが、トラフィックが増えると速度が低下する可能性があります。



パフォーマンスのボトルネックを解消する方法はたくさんありますが、この記事では以下に焦点を当てます。

• サーバーパラメータ
• 接続管理
• 自動真空設定
• 追加の自動真空設定
• フローティングテーブル（膨張）
• データのホットスポット
• アプリケーションサーバー
• レプリケーション
• サーバー環境

「カテゴリ」と「潜在的な影響」について

「複雑さ」とは、提案されたソリューションの実装がいかに簡単かを指します。また、「潜在的な影響」は、システムパフォーマンスの改善の程度を示します。ただし、システムの古さ、種類、技術的負債などによります。複雑さと影響を正確に説明することは問題になる可能性があります。結局のところ、困難な状況では、最終的な選択は常にあなた次第です。



カテゴリ：

• 複雑
  
  
  • 低
  • 平均
  • 高い
  • 低中高
• 潜在的な影響
  
  
  
  
  • 低
  • 平均
  • 高い
  • 低中高

サーバーパラメータ

難易度：低い。

潜在的な影響：高い。



少し前までは、現在のバージョンのpostgresをi386で実行できることがありました。その後、デフォルト設定が変更されましたが、使用するリソースが最小になるように構成されています。



これらの設定は非常に簡単に変更でき、通常は初期インストール時に構成されます。これらのパラメータの値が正しくないと、CPUとI / Oの使用率が高くなる可能性があります：

• パラメータeffective_cache_size〜50〜75％
• パラメータshared_buffers〜1 / 4-1 / 3RAMの量
• パラメータwork_mem〜 10メガバイト

Effective_cache_sizeの推奨値は、一般的ですが、「top」（free + cached）を参照すると、より正確に計算できます。shared_buffers



の値を計算することは、興味深いパズルです。これは2つの角度から見ることができます。データベースが小さい場合は、データベース全体をRAMに収めるのに十分な大きさのshared_buffersの値を設定できます。一方、頻繁に使用するテーブルとインデックスのみをメモリにロードするように構成できます（80/20を思い出してください）。）。以前は、値をRAMの量の1/3に設定することが推奨されていましたが、時間の経過とともに、メモリの量が増えるにつれて、値は1/4に減少しました。割り当てられるメモリが少ない場合、I / Oとプロセッサの負荷が増加します。プロセッサとI / O負荷のプラトーに達すると、メモリ割り当てが多すぎることが示されます。







考慮すべきもう1つの要素は、OSキャッシュです。十分なRAMが与えられると、Linuxはテーブルとインデックスをメモリにキャッシュし、構成方法によっては、PostgreSQLがRAMではなくディスクからデータを読み取っていると信じ込ませる可能性があります。同じページがpostgresバッファとOSキャッシュの両方にあり、これがshared_buffersをあまり大きくしない理由の1つです。pg_buffercache拡張機能の使用キャッシュの使用状況をリアルタイムで確認できます。work_mem



パラメーターは、ソート操作に使用されるメモリーの量を指定します。この値の設定が低すぎると、ディスク上の一時ファイルを使用してソートが実行されるため、パフォーマンスが低下することが保証されます。一方、大きな値を設定してもパフォーマンスには影響しませんが、接続数が多いとRAMが不足するリスクがあります。すべてのリクエストとセッションで使用されるメモリを分析することで、必要な値を計算できます。 EXPLAIN ANALYZEを使用すると、ソート操作がどのように実行されるかを確認し、セッションの値を変更することで、ディスクへのフラッシュがいつ開始されるかを判別できます。ベンチマークを 使用することもできます







システム。

接続管理

難易度：低い。

潜在的な影響：低-中-高高



負荷は通常、単位時間あたりのクライアントセッションの増加に関連しています。それらの数が多すぎると、プロセスがブロックされたり、遅延が発生したり、エラーが発生したりする可能性があります。



簡単な解決策は、同時接続の最大数を増やすことです。

# postgresql.conf: default is set to 100<br />max_connections

ただし、より効率的なアプローチは接続プーリングです。多くの解決策がありますが、最も人気のあるのはpgbouncerです。PgBouncerは、次の3つのモードのいずれかを使用して接続を管理できます。

• (session pooling). . , . , . .
• (transaction pooling). . PgBouncer , , .
• (statement pooling). . . , .

Secure Socket Layer（SSL）にも注意を払う必要があります。有効にすると、接続はデフォルトでSSLを使用します。これにより、暗号化されていない接続と比較してプロセッサの負荷が増加します。通常のクライアントの場合、SSL（pg_hba.conf）を使用せずにホストベースの認証を構成し、管理タスクまたはストリーミングレプリケーションにSSLを使用できます。

自動真空設定

難易度：中。

潜在的な影響：低-中。



マルチバージョン同時実行制御は、PostgreSQLをそのような人気のあるデータベースソリューションにする基本原則の1つです。ただし、厄介な問題の1つは、変更または削除されたレコードごとに、未使用のコピーが作成され、最終的には破棄する必要があることです。自動真空プロセスが正しく構成されていないと、パフォーマンスが低下する可能性があります。さらに、サーバーの負荷が高いほど、問題が明らかになります。



自動バキュームデーモンを制御するには、次のパラメータを使用します。

• autovacuum_max_workers. ( ). , . . . .
• maintenance_work_mem. , . , . , .
• autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.

RAMとCPUの過負荷に注意してください。最初に設定された値が高いほど、システムの負荷が増加したときにリソースが枯渇するリスクが高くなります。設定が高すぎると、特定の負荷レベルを超えるとパフォーマンスが大幅に低下する可能性があります。work_memの



計算と同様に、この値は算術的に計算するか、ベンチマークして最適な値を取得できます。

追加の自動真空設定

難易度：高い。

潜在的な影響：高い。



この方法は複雑であるため、システムパフォーマンスがすでにホストの物理的限界に近づいており、これが実際に問題になっている場合にのみ使用する必要があります。



ランタイム自動バキュームオプションはで構成されpostgresql.confます。残念ながら、高負荷システムで機能する万能のソリューションはありません。



テーブルのストレージオプション。多くの場合、データベースでは、負荷のかなりの部分が少数のテーブルにのみかかります。テーブルの自動真空設定をカスタマイズすることは、システムに大きな影響を与える可能性のあるVACUUMを手動で開始する必要をなくすための優れた方法です。次のコマンド



を使用してテーブルをカスタマイズできます。

ALTER TABLE .. SET STORAGE_PARAMETER

フローティングテーブル（膨張）

難易度：低い。

潜在的な影響：中〜高。



時間の経過とともに、テーブルの過度の肥大化による不適切なクリーンアップポリシーにより、システムパフォーマンスが低下する可能性があります。したがって、autovacuumデーモンを設定して手動でVACUUMを起動しても、問題は解決しません。このような場合、pg_repack拡張機能が役に立ちます。pg_repack



拡張機能を使用して、本番環境でテーブルとインデックスを再構築および再編成できます。

データのホットスポット

難易度：高い。

潜在的な影響：低-中-高。MySQLと



同様に、PostgreSQLはホットスポットを取り除くためにデータストリームに依存しており、システムのアーキテクチャを変更することさえあります。 まず、次の点に注意する必要があります。

• インデックス。検索する列にインデックスがあることを確認してください。システムカタログとビューを使用して、クエリがインデックスを使用していることを監視および確認できます。pg_stat_statementおよびpgbadger拡張機能を使用して、クエリのパフォーマンスを分析します。
• ヒープのみのタプル（HOT）。インデックスが多すぎる可能性があります。未使用のインデックスを削除することで、潜在的な肥大化を減らし、テーブルサイズを減らすことができます。
• . , , . , , , . , . , , .
• . postgres. , .
• . , . . , !

難易度：低い。

潜在的な影響：高い。



同じホストでアプリケーション（PHP、Java、Python）とpostgresを実行することは避けてください。これらの言語のアプリケーション、特にガベージコレクターは大量のRAMを消費する可能性があるため、リソースをめぐってデータベースシステムとの競合が発生し、全体的なパフォーマンスが低下するため、注意してください。

レプリケーション

難易度：低い。

潜在的な影響：高い。



同期および非同期レプリケーション。postgresの最近のバージョンは、同期モードと非同期モードの両方で論理レプリケーションとストリーミングレプリケーションをサポートしています。デフォルトのレプリケーションモードは非同期ですが、特に遅延が大きいネットワークでは、同期レプリケーションを使用することの影響を考慮する必要があります。

サーバー環境

最後になりましたが、これはホスト容量の単純な増加です。各リソースがPostgreSQLのパフォーマンスにどのように影響するかを見てみましょう。

• . , . . , , -.
• . , , . .
• . .
  
  
  • -, ,
• .
  
  
  
  
  • . , .
  • .
  • . .
  • WAL-, , , . , (log shipping) , , .

: