DDL操作を実行してApsaraDB RDS for PostgreSQLインスタンスにフィールドを追加する - ApsaraDB RDS

このトピックでは、ApsaraDB RDS for PostgreSQLインスタンスのDDL操作の自動再試行とロック待機タイムアウト期間を設定する方法について説明します。この構成は、フィールドの追加に使用されるDDLステートメントを実行するときにロックによって発生するブロックを解決し、ワークロードへの影響を軽減するのに役立ちます。

背景情報

ほとんどの場合、デフォルト値を持たないフィールドを数秒以内にRDSインスタンスに追加できます。 RDSインスタンスがPostgreSQL 11以降を実行している場合、計算されていないデフォルト値を持つフィールドを数秒以内に追加することもできます。詳細については、「PostgreSQLドキュメント」をご参照ください。

フィールドの追加に使用されるDDLステートメントを数秒以内に実行できます。ただし、DDLステートメントの実行中にロックによるブロックが発生する場合があります。この問題は、自動真空または長時間のトランザクションによって引き起こされるテーブルロックの競合が原因で発生します。 ApsaraDB RDS for PostgreSQLでは、フィールドの追加に使用されるDDLステートメントの実行に必要なテーブルロックは排他ロックです。テーブルロックは他の読み取りおよび書き込み要求を中断し、DDLステートメントが正常に実行されるまで、要求はテーブルロックの待機キューに入ります。

ソリューション

解決策 1

トランザクションレベルのロック待機タイムアウト期間を設定します。ロック待機がタイムアウトすると、DDL操作は自動的にキャンセルされます。例：

BEGIN;

SET LOCAL lock_timeout = 500;  -- Set the lock wait timeout period to 500 milliseconds.
ALTER TABLE <Table name> ADD COLUMN <Column name> VARCHAR;  -- Add a field.

COMMIT; -- Commit the transaction.

説明

ビジネス要件に基づいて、トランザクションのロック待機タイムアウト期間を示すlock_timeoutを指定する必要があります。パラメーターを大きな値に設定すると、ターゲットテーブルの1秒あたりの読み取り /書き込みトランザクション (TPS) が大きく影響します。詳細については、「ストレステストの結果」をご参照ください。

解決策 2

トランザクションレベルのロック待機タイムアウト期間を設定し、操作が成功するまでシステムが自動的かつ定期的にDDL操作を実行できるようにします。例：

DO $$
DECLARE
    msg text;
BEGIN
    LOOP
        BEGIN
            PERFORM pg_sleep(1);
            SET LOCAL lock_timeout = 500;
            ALTER TABLE <Table name> ADD COLUMN <Column name> VARCHAR;  -- Add a field.
            EXIT;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT;
            RAISE NOTICE 'failed, error: %s', msg;
        END;
    END LOOP;   
    RAISE NOTICE 'success!';
END;
$$;

(オプション) DDL操作をブロックする接続を終了する

重要

この操作を実行すると、関連する接続が切断されます。作業は慎重に行ってください。

現在の接続のプロセスID (PID) を照会します。
```
SELECT pg_backend_pid();
```
DDL操作を実行して、テーブルにフィールドを追加します。
```
ALTER TABLE <Table name> ADD COLUMN <Field name> VARCHAR; 
```
DDL操作をブロックするプロセスを照会します。
```
SELECT pg_blocking_pids(<The PID obtained in step 1>);
```
DDL操作をブロックするプロセスを手動で終了します。
- 実行中のプロセスをキャンセルします。
```
SELECT pg_cancel_backend(<The PID obtained in step 3>);
```
- プロセスを強制終了します。
```
SELECT pg_terminate_backend(<The PID obtained in step 3>);
```

説明

自動真空プロセスの終了に失敗する可能性があります。

ストレステストの結果

sysbenchは、テストテーブルで読み取り専用ストレステスト (oltp_read_only) を実行するために使用されます。テスト期間は300秒です。実行時間の長いトランザクションがテストテーブルで実行されます。テスト中にフィールドを追加するために使用されるDDLステートメントを実行すると、テスト結果は、ロック待機タイムアウト期間が設定されているかどうかによって異なります。

lock_timeoutを指定せず、DDLステートメントを実行してフィールドを追加するときにロックが原因でブロックが発生した場合、RDSインスタンスの読み書きTPSは0になります。
lock_timeoutを指定し、DDLステートメントを実行してフィールドを追加するときにロックが原因でブロックが発生した場合、RDSインスタンスの読み書きTPSはわずかに影響を受けます。 lock_timeoutを大きな値に設定すると、RDSインスタンスの読み取り /書き込みTPSに大きな影響があります。

次の図は、TPSトレンドチャートとテスト中の各ステージでの操作を示しています。

番号	API 操作	説明
1	sysbenchを使用して、テストテーブルで読み取り専用ストレステスト (oltp_read_only) を実行し、テストテーブルで長時間実行されるトランザクションを実行します。	なし
2	フィールドの追加に使用されるDDLステートメントを約30秒間実行します。	RDSインスタンスのTPSは、DDLステートメントの実行中に0に減少します。
3	lock_timeoutを100ミリ秒に設定した後、フィールドの追加に使用されるDDLステートメントを約30秒間実行します。詳細については、「ソリューション2」をご参照ください。	RDSインスタンスのTPSは、DDLステートメントの実行中にわずかに影響を受けます。
4	lock_timeoutを500ミリ秒に設定した後、フィールドの追加に使用されるDDLステートメントを約30秒間実行します。詳細については、「ソリューション2」をご参照ください。	RDSインスタンスのTPSは、DDLステートメントの実行中に大きな影響を受けます。
5	長時間のトランザクションを終了します。詳細については、「 (オプション) DDL操作をブロックする接続を終了する」をご参照ください。	フィールドの追加に使用されるDDLステートメントは、迅速かつ正常に実行されます。

ApsaraDB RDS:DDL操作を実行してApsaraDB RDS for PostgreSQLインスタンスにフィールドを追加する