すべてのプロダクト
Search

このプロダクト

    ApsaraDB RDS:開発とO&Mの推奨事項

    ドキュメントセンター

    ApsaraDB RDS:開発とO&Mの推奨事項

    最終更新日:Jan 17, 2024

    このトピックでは、ApsaraDB RDS for PostgreSQLの開発およびO&Mに関する推奨事項について説明します。 推奨事項は、ApsaraDB RDS for PostgreSQLインスタンスのコンプライアンス、安定性、およびパフォーマンスの向上に役立ちます。

    接続プール

    • SQL文をPreparedStatementオブジェクトに格納することを推奨します。 これにより、ハード解析が不要になり、CPUリソースの消費量が削減され、RDSインスタンスのパフォーマンスが向上します。

    • アイドル接続を減らすことを推奨します。 これにより、メモリ使用量が削減され、GetSnapshotData() の効率が向上し、システムパフォーマンスが向上します。

    • アプリケーションの接続プール機能を有効にして、リソースが短期間の接続によって消費されるのを防ぎ、パフォーマンスの低下を防ぐことをお勧めします。 アプリケーションが接続プール機能をサポートしていない場合は、アプリケーションとRDSインスタンス間の接続プールを設定することを推奨します。 たとえば、PgBouncerまたはPgpool-IIを接続プールとして使用できます。

    • 接続プールには、次のパラメーターを設定することを推奨します。

      • minimumIdle: 接続プール内のアイドル接続の最小数を指定します。 アイドル接続を減らすために、このパラメーターを1に設定することを推奨します。

        説明

        maxIdleパラメーターは、ほとんどの接続プールの設定から削除されます。 maxIdleパラメーターが使用可能な場合は、このパラメーターを1に設定する必要があります。

      • maxLifetime: 接続プール内の各接続の最大有効期間 (TTL) を指定します。 このパラメーターを60分に設定することを推奨します。 これにより、RelCacheへの頻繁な接続が原因で発生するメモリ不足 (OOM) エラーの可能性を減らすことができます。

      • maximumPoolSize: 各接続プールで許可される接続の最大数を指定します。 このパラメーターを15に設定することを推奨します。 最大15の接続をサポートする接続プールは、ほとんどのビジネスシナリオに適しています。 接続プールにキャッシュされた接続の数が少なく、RDSインスタンスが接続からのワークロードのみを処理する場合、データベースクライアントでmaximumPoolSizeパラメーターを15を超える値に設定できます。

      説明

      接続プールには次の設定を使用することを推奨します。

      • Java環境で推奨される接続プールであるHikariCP接続プールには、次の設定を使用することを推奨します。 

        minimumIdle=1、maximumPoolSize=15、idleTimeout=600000 (10分) 、maxLifetime=3600000 (60分)

      • GO環境で推奨される接続プールであるGORM接続プールには、次の設定を使用することを推奨します。 

        sqlDB.SetMaxIdleConns(1), sqlDB.SetMaxOpenConns(15), sqlDB.SetConnMaxLifetime(time.Hour)

      • Java環境で使用されるDruid接続プールには、次の設定を使用することを推奨します。

        initialSize=1、minIdle=1、maxIdle=1、maxActive=15、testOnBorrow=false、testOnReturn=false、testWhileIdle=true、minEvictableIdleTimeMillis=600000 (10分) 、maxEvictableIdleTimeMillis=900000 (15分) 、timeBetweenEvictionRunsMillis=60000 (1)=6 (1秒6000) 。

      上記の設定には、PreparedStatementオブジェクトは含まれません。 ビジネス要件に基づいてPreparedStatementオブジェクトを設定する必要があります。

    パフォーマンスと安定性

    • ApsaraDB RDS for PostgreSQLでは、単一のデータベースが基になるファイルシステムのフォルダーに対応します。 データベース内のテーブル、パーティション、およびインデックスは、フォルダ内のファイルに対応しています。 フォルダ内のファイル数が20万を超えると、ディスク容量が使い果たされたことを示すエラーが報告される場合があります。 ビジネス要件に基づいて、データベースを分割するか、テーブルファイルをマージすることをお勧めします。

    • CREATE INDEX CONCURRENTLYステートメントを実行して、オンラインワークロードのインデックスを作成することを推奨します。 これは、インデックスが作成されるテーブルに対する他のセッションのINSERT、UPDATE、およびDELETE DML操作には影響しません。

    • REINDEX CONCURRENTLYステートメントを実行して、PostgreSQL 12以降を実行するRDSインスタンスのインデックスを再作成することを推奨します。 PostgreSQL 11以前を実行するRDSインスタンスの場合、元のインデックスを削除する前に、CONCURRENTLYを使用してインデックスを作成することを推奨します。

    • 一時テーブルを頻繁に作成または削除しないでください。 これにより、システムテーブルリソースの消費が削減されます。 ON COMMIT DROPを使用するときは注意してください。 ほとんどの場合、一時テーブルを作成する代わりにWITH句を使用できます。

    • PostgreSQL 13では、パーティションテーブル、GROUP BY句のHashAggregate操作、および並列クエリの機能が改善されています。 RDSインスタンスのメジャーエンジンバージョンをPostgreSQL 13にアップグレードすることを推奨します。 詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのメジャーエンジンバージョンのアップグレード」をご参照ください。

    • カーソル機能を使用しなくなった場合は、この機能を無効にすることを推奨します。

    • RDSインスタンスのパフォーマンスを向上させるために、テーブルに対してDELETEステートメントではなくTRUNCATEステートメントを実行することを推奨します。

    • PostgreSQLは、DDLトランザクションの実行とロールバックをサポートしています。 DDLステートメントをトランザクションにカプセル化することを推奨します。 これにより、ビジネス要件に基づいてDDLステートメントをロールバックできます。 DDLステートメントを適切な長さのトランザクションにカプセル化する必要があることに注意してください。 トランザクションが長い場合、これらのトランザクションによってアクセスされているオブジェクトに対する読み取り動作は、長期間にわたってブロックされる可能性がある。

    • 大量のデータをRDSインスタンスに書き込む場合は、COPYまたはINSERT INTO table VALUES () 、() 、...(); ステートメントを実行して、書き込み速度を向上させることを推奨します。

    マイナーエンジン版

    • レプリケーションスロット機能を使用する場合は、RDSインスタンスのマイナーエンジンバージョンを20201230以降に更新することを推奨します。 このようにして、論理レプリケーションスロットフェールオーバー機能を有効にし、最大レプリケーションスロット待ち時間メトリックのアラートルールを設定して、論理サブスクリプションが遅延または中断されるのを防ぐことができます。 論理サブスクリプションが遅延または中断されると、論理レプリケーションスロットが失われ、ライトアヘッドロギング (WAL) レコードが蓄積されます。 詳細については、「Logical Replication Slot Failover」および「ApsaraDB RDS For PostgreSQLインスタンスのアラートルールの管理」をご参照ください。

    • 監査ログ機能またはパフォーマンスインサイト機能を有効にする場合は、RDSインスタンスのマイナーエンジンバージョンを20211031以降に更新することを推奨します。 log_statementをallに設定すると、この機能を有効にします。

      説明

      log_statementがallに設定されている場合、アクティブな接続が50を超えるシナリオでRDSインスタンスのパフォーマンスが約4倍向上します。 これにより、RDSインスタンスのCPU使用率の大幅な増加も防ぎます。

    モニタリングとアラート

    • RDSインスタンスのInitiative Alertをオンにして、モニタリングおよびアラート機能によって提供されるデフォルトのアラートルールを有効にすることを推奨します。 詳細については、「アラートの管理」をご参照ください。

    • しきい値を指定して、ビジネス要件に基づいてメモリ使用量アラートをトリガーできます。 しきい値を85% 〜95% の値に設定することを推奨します。

    トラブルシューティング

    最も多くのリソースを消費するSQL文を特定する方法の詳細については、「最も多くのリソース消費を持つSQL文の検索」をご参照ください。

    デザイン

    権限

    • アクセス許可をスキーマまたはロールごとに管理し、最小特権 (PoLP) の原則に従って、RDSインスタンスに次の2つのロールを作成することを推奨します。1つのロールは読み取りおよび書き込み権限を持ち、1つのロールは読み取り権限のみを持ちます。 詳細については、「ApsaraDB RDS For PostgeSQLインスタンスの権限の管理」をご参照ください。

    • アプリケーション層で読み書き分離を有効にする場合は、PoLPに従い、読み取り専用データベースクライアントに読み取り専用ロールを使用することをお勧めします。

    テーブル

    • RDSインスタンスのスキーマのフィールドに定義されているデータ型は、アプリケーションで定義されているデータ型と同じである必要があります。 さらに、すべてのテーブルのフィールドをチェックするために同じルールを使用する必要があります。 これにより、エラーを防ぎ、インデックスを使用できることを確認できます。

    • 履歴データを定期的に削除する場合は、年または月ごとにテーブルを分割することをお勧めします。 また、パーティション分割後に取得した子テーブルに対してDROPまたはTRUNCATEステートメントを実行して、データを削除することをお勧めします。 データを削除するために、子テーブルに対してDELETEステートメントを実行しないことを推奨します。

    • テーブルを頻繁に更新する場合は、テーブルのFILLFACTORを85に設定して、テーブルの作成時に1ページあたりのストレージの15% を予約することを推奨します。 予約されたストレージは、テーブル内のホットデータを更新するために使用されます。 

      CREATE TABLE test123(id int、info text) WITH(FILLFACTOR=85);

    • 一時テーブルの名前がtmp_ で始まることを確認することを推奨します。 また、子テーブルの名前が、子テーブルの親テーブルのパーティション分割に基づくルールで終わるようにする必要があります。 たとえば、年ごとに分割された親テーブルの名前がtblの場合、親テーブルの子テーブルの名前はtbl_2016とtbl_2017になります。

    インデックス

    • Bツリーインデックスには、合計サイズが最大2,000バイトのフィールドを含めることができます。 フィールドの合計サイズが2,000バイトを超える場合、新しいインデックスが必要になります。 ハッシュインデックスなどの関数インデックスを作成することを推奨します。 関数インデックスを作成しない場合は、データにインデックスを作成する前に、アナライザーを使用してデータを分析することをお勧めします。

    • ストリーミングデータ、時間フィールド、および自動インクリメントフィールドなどのデータは、線形順序で記憶され得る。 ほとんどの場合、範囲クエリはこれらのタイプのデータをクエリするために実行されます。 BRINインデックスを作成して、インデックスあたりのサイズを小さくし、データ挿入を高速化することを推奨します。 

      CREATE INDEX idx ON tbl using BRIN(id);

    • 大量のデータをスキャンして分析する場合を除き、テーブル全体のスキャンは実行しないことをお勧めします。 ApsaraDB RDS for PostgreSQLは、ほとんどのデータ型のインデックスをサポートしています。

      B-tree、Hash、GIN、GiST、SP-GiST、BRIN、RUM、Bloom、PASEのタイプのインデックスがサポートされています。 これらの種類のインデックスのうち、RUN、Bloom、PASEは拡張インデックスです。

    • プライマリキーインデックスの名前はpk_ で始まり、一意のインデックスの名前はuk_ で始まり、通常のインデックスの名前はidx_ で始まるようにすることをお勧めします。

    データ型と文字セット

    • 書き込むデータに適したデータ型を選択することを推奨します。 数値データを記述する場合、または記述するデータをツリー構造に格納できる場合は、文字列データ型を選択しないことをお勧めします。

      適切なデータ型は、クエリ効率を向上させる。

      ApsaraDB RDS for PostgreSQLは、数値、浮動小数点、通貨、文字列、文字、バイナリ、日付 /時刻、ブール、列挙型、ジオメトリ、ネットワークアドレス、ビット文字列、テキスト検索、UUID、XML、JSON、配列、コンポジット、範囲、オブジェクト識別子、行番号、ラージオブジェクト、ltree構造、データキューブ、地理、H-Store、pg_trgmモジュール、postGIS、およびHyperLogLog。 PostGISには、ポイント、線分、サーフェス、パス、緯度、経度、ラスタ、トポロジなどのデータ型が含まれます。 HyperLogLogは、調整可能な精度で個別の値をカウントするために使用される固定サイズのセット状のデータ構造です。

    • LC_COLLATEをUTF8ではなくCに設定することを推奨します。 UTF8文字セットの照合は、C文字セットの照合よりも劣っています。 さらに、UTF8文字セットの照合順序を使用する場合は、LIKEクエリをサポートするインデックスにtext_pattern_ops演算子クラスを指定する必要があります。

    ストアドプロシージャ

    • ビジネスロジックが長い場合は、アプリケーションとRDSインスタンス間のやり取りの数を減らすことを推奨します。 PL/pgSQLに基づくストアドプロシージャや組み込み関数などのストアドプロシージャの使用を推奨します。 PL/pgSQLは、PostgreSQLでサポートされ、複雑なビジネスロジックを処理するために使用される手続き型プログラミング言語です。 PostgreSQLは、分析関数、集計関数、ウィンドウ関数、数学関数、幾何学関数の一般的な関数と複雑な関数をサポートしています。

    データクエリ

    • COUNT(*) をCOUNT(column_name) またはCOUNT(constants) に置き換えないことを推奨します。 COUNT(*) は、行数をカウントするSQL-92に定義されている標準関数です。 COUNT(*) は、実際の行数を計算するときにNULL値をカウントしますが、COUNT(column_name) はNULL値をカウントしません。

    • COUNT(DISTINCT) を使用する場合は、指定する複数の列の名前を括弧 () で囲む必要があります。 例: COUNT( (col1,col2,col3) ) 。 COUNT(DISTINCT) は、すべてのNULL値をカウントします。 したがって、COUNT(DISTINCT) は、COUNT(*) と同じ結果を生成する。

    • SELECT * FROMは使用しないことを推奨します。 ワイルドカード (*) を必要なフィールドの配列に置き換えます。 このようにして、ApsaraDB RDSは指定したフィールドのみを返し、必要のないフィールドは返しません。

    • ApsaraDB RDSが大量のデータをデータベースクライアントに返さないようにすることを推奨します。ただし、抽出、変換、および読み込み (ETL) 操作を除きます。 クエリに対して返されるデータ量が異常に多い場合は、クエリの実行プランが最適かどうかを確認します。

    • 範囲クエリを実行する場合は、クエリのパフォーマンスを向上させるために、rangeデータ型とGiSTインデックスを使用することを推奨します。

    • アプリケーションが多数の結果が返されるクエリを頻繁に開始する場合は、そのようなクエリのすべての結果を結果セットに集約することをお勧めします。 たとえば、クエリに対して返される結果の数が100に達した場合、クエリの100結果を結果セットに集約することを推奨します。 また、アプリケーションがIDで設定された結果に頻繁にアクセスする場合は、IDで結果を定期的に集計することをお勧めします。 返される結果の数が少ない場合は、応答時間が短いことを示します。

    インスタンス管理

    • RDSインスタンスのSQL Explorerおよび監査機能を有効にすることを推奨します。 この機能を使用すると、RDSインスタンスで実行されるSQLステートメントに関する情報を照会およびエクスポートできます。 この情報は、SQL文が実行されるデータベース、SQL文の状態、およびSQL文の実行期間を含む。 この機能を使用して、SQL文の正常性の診断、パフォーマンスの問題のトラブルシューティング、およびビジネストラフィックの分析を行うことができます。 詳細については、「SQLエクスプローラーと監査機能の使用」をご参照ください。

    • Alibaba Cloudアカウント内のアクティビティを監視および記録する場合は、ActionTrailを使用することを推奨します。 監視および記録できるアクティビティには、Alibaba cloud管理コンソール、API、および開発者ツールを使用したクラウド製品およびサービスへのアクセスおよび使用が含まれます。 ActionTrail は、対象となる操作をイベントとして記録します。 ActionTrailコンソールからイベントをダウンロードするか、イベントをLog Service LogstoreまたはObject Storage Service (OSS) バケットに配信するようにActionTrailを設定できます。 その後、イベントに基づいて、アクション分析、セキュリティ分析、リソース変更追跡、コンプライアンス監査などの操作を実行できます。 詳細については、「ActionTrailとは」をご参照ください。

    • DDL操作は、実行する前にレビューする必要があります。 ピーク時間外にDDL操作を実行してください。

    • データを削除または変更するために実行されるトランザクションをコミットする前に、SELECTステートメントを実行してトランザクションを確認することを推奨します。 このようにして、誤った操作を防ぐことができます。 ビジネスロジックに基づいて1行のみを更新する場合は、LIMIT 1を追加します。

    • 特定のオブジェクトに対してロックを取得する可能性のあるDDL操作またはその他の同様の操作を実行する場合は、ロック待機メカニズムを設定して、これらの操作がロックされたオブジェクトに対するクエリをブロックしないようにすることをお勧めします。 そのような動作は、VACUUM FULLおよびCREATE INDEXを含む。 

      始める;
SET local lock_timeout = '10s';
-- DDLクエリ;
終了;

    • EXPLAIN ANALYZEステートメントを実行して、クエリの実行計画を表示できます。 EXPLAIN ANALYZEステートメントとEXPLAINステートメントは同様に機能します。 ただし、EXPLAIN ANALYZEステートメントにはデータの変更が含まれる場合があります。 クエリの実行プランにDML UPDATE、INSERT、DELETEなどのデータ変更の原因となる操作が含まれている場合は、トランザクションでEXPLAIN ANALYZEステートメントを実行し、ステートメントの実行後にトランザクションをロールバックする必要があります。 

      始める;
EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>;
ロールバック;

    • 大量のデータを削除または更新する場合は、データをバッチに分割し、各バッチのデータを独立したトランザクションで削除または更新することをお勧めします。 1つのトランザクションですべてのデータを削除または更新しないことを推奨します。 1つのトランザクションですべてのデータを削除または更新すると、大量の迷惑データが生成されます。