Amazon RDS for PostgreSQLにおける自動バキュームのケーススタディ

PostgreSQLデータベースにおいて、自動バキューム処理(autovacuum)は複数の重要なメンテナンス操作を実行します。周回を防止するためにトランザクションIDをフリーズすることに加えて、デッドタプルを削除し空きスペースを回復させます。書き込み回数の多いデータベースの場合は、自動バキュームを頻繁に実行するようにチューニングすることをお勧めします。そうすることで、テーブルやインデックスを膨らませるデッドタプルの蓄積を避けることができます。

この記事では、デッドタプルが蓄積される状況でどのように自動バキューム処理を監視し、チューニングするかを実際に示すために、ケーススタディを用いてご説明します。

デッドタプルとは何か？

PostgreSQL は同時実行される読み込みや書き込みを支援するために MVCC (Multiversion Concurrency Controll / 多版型同時実行制御) を使用します。行が更新された時、タプル(新たなバージョンの行)が作成され、表に新たに挿入されます。古いバージョンの行はデッドタプルとして参照されますが、物理的には削除されず、その後に実行されるトランザクションからは不可視としてマークされます。

すべての行が複数の異なるバージョンを持つ可能性があることから、設定されたトランザクション隔離レベルに基づいてそのタプルがトランザクションやクエリから可視かどうかを判断するのを助けるために、PostgreSQL はタプルの内部に可視性に関する情報を格納しています。もしデッドタプルがすべてのトランザクションから不可視であれば、バキュームは将来再利用可能な領域としてマークすることでそのタプルを削除することができます。PostgreSQLのドキュメントには、領域回復のためのバキュームについての優れた説明がありますので参照してください。

デッドタプル削除については2つの重要な側面があります。デッドタプルは利用可能な領域を減少させてしまうだけではなく、データベースのパフォーマンスを悪化させる原因となることがあります。表が大量のデッドタプルを抱えると、実際に必要なサイズよりもずいぶんと大きくなってしまいます。これをテーブルの膨張と呼びます。膨張したテーブルに対しての逐次スキャンはより多くのページを読み込む必要があり、追加のI/Oとより長い時間を費やします。索引には行の可視性についての情報が格納されておらず、デッドタプルへのポイントが格納されがちなため、やはり膨張してしまいます。膨張した索引はより多くの不必要なI/Oフェッチを引き起こし、索引の参照やスキャンを遅くします。

自動バキュームがデッドタプルを削除しなかった場合

大量の書き込み処理を行うデータベースでは、デッドタプルの成長率は高くなってしまいます。加えて、Amazon Relational Database Service (Amazon RDS) for PostgreSQLのデフォルトの設定では、自動バキュームが十分なスピードでそれらを綺麗にすることを難しくしています。

次のAmazon CloudWatchのグラフはこの問題について私が遭遇した例を示します。またこのグラフは私がチューニングした経緯も見せてくれています。

最近、私は1セットの OLTP (online transaction processing / オンライントランザクション処理) データベースについて Oracle から Amazon RDS for PostgreSQL バージョン 9.6.3 に移行しました。Oracle における通常の IOPS (input/output operations per second / 秒間入出力回数) 幅と、Q4 のピーク時期に2倍と予想したキャパシティプランニングに基づき、私は Amazon RDS for PostgreSQL インスタンスに対して 30,000 プロビジョニング IOPS を設定しました。移行の後の最初の数週間の間、特に負荷の上昇がない状況にも関わらず、いくつかのデータベースで最大 25,000 IOPS となるI/Oの急激な上昇を経験しました。

IOPS の急激な上昇が発生した時、私は多くのI/Oを消費しているデータベースセッションを探しました。そこで自動バキュームセッションについて2つの問題に気付いたのです。

問題1: デフォルトの3つの自動バキュームセッションは、表のバキュームのために長時間処理を行わなければなりませんでした。

SQL

-- autovacuumセッションを確認するためのSQL

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, state, query
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY xact_start;

datname | usename  |  pid  |    xact_runtime        | state  |                    query        
--------+----------+-------+------------------------+--------+-----------------------------------------------------
oltp101 | rdsadmin | 50723 | 2 days 16:03:48.447991 | active | autovacuum: VACUUM sh.table1 (to prevent wraparound)
oltp101 | rdsadmin |  8112 | 15:35:30.003172        | active | autovacuum: VACUUM sh.table2
oltp101 | rdsadmin | 64109 | 14:34:23.605948        | active | autovacuum: VACUUM ANALYZE sh.table3

Amazon RDS for PostgreSQL バージョン 9.6.3 のデフォルト設定では、autovacuum_vacuum_scale_factorが 0.1 となっています。これはタプル全体の10パーセント以上がデッドタプルとなった時に当該表がバキュームすべきものとして認識されることを意味します。IOPSの急激な上昇が発生する前には、この閾値に達しているテーブルは存在せず、自動バキュームは開始されていませんでした。しかし、たくさんの表が大きくなり、数億行に達しました。いくつかの表でこの10パーセントの閾値に達した時には、それらの表のタプルは既に数百万行に肥大化していたのです。自動バキュームセッションはそれらの処理のために大量のI/Oと時間を費やすことになりました。

デフォルト設定ではさらに autovacuum_max_workersを 3 としています。これは与えられた時間の中で、自動バキュームセッションが 3並列で実行できる、つまりは3つの表のバキュームが並列で実行できることを意味しています。これら3つの自動バキュームセッションがすべて占有された時、他の表はデッドタプルが成長し続ける中、バキュームの順番が回ってくるのを待つしかありませんでした。不健全なサイクルに陥ってしまったのです。

問題2: 自動バキュームセッションを最も長く実行していた表では、その表にクエリを発行してスタック状態になっている、idle in transactionである別のセッションを見つけました。

SQL

-- 特定の表に対してクエリを発行しているセッションを確認するSQL

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, state, query
FROM pg_stat_activity 
WHERE query LIKE '%table1%'
ORDER BY xact_start;

datname | usename |  pid  |      xact_runtime      |        state        |                 query             
--------+---------+-------+------------------------+---------------------+--------------------------------------
oltp101 | user1   | 36637 | 2 days 22:25:22.838865 | idle in transaction | select * from table1 where column1=$1

セッションはトランザクション開始時にidle in transactionとしてマークされますが、COMMITかROLLBACKを発行するまでは終了しません。PostgreSQL　においては、すべてのSQL分はトランザクション境界内で実行され、トランザクションは特に明示的な合図もなく開始されます。私のアプリケーションの1つは autocommit を無効化してSELECT文を実行していました – これはトランザクションが暗黙的に開始されることを意味します。しかし、アプリケーションはそれを認識せず、SELECT文の後に明示的なCOMMITまたはROLLBACKを発行していませんでした。結果として、データベース側には不完全なトランザクションが取り残されました。自動バキュームが関係する表のデッドタプルを削除しようとしても、引き続き有効なトランザクションが存在することに気づき、削除することはできませんでした。自動バキュームは基本的にブロックされ続けてしまったのです。

これら2つの問題により、自動バキュームセッションは要求に応えることができませんでした。先に示した表統計が示すように、多くの表が膨張してしまい、かつデッドタプルは急激に増えてしまいました。これがIOPSの急激な上昇の根本的な原因となっていました。

膨張を緩和するために、私はバキュームを停止し、pg_repackを使用する方法も試しました。自動バキュームが期待通りに機能しない場合には、これら手動での手順は役に立つものです。

SQL

-- デッドタプルの数に関する表統計情報を確認するためのSQL

SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%",
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date, 
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM pg_stat_all_tables 
ORDER BY last_autovacuum;

relname | n_live_tup | n_dead_tup | ratio% |   autovacuum_date   |  autoanalyze_date
--------+------------+------------+--------+--------+---------------------+------------
table1  |  450398643 |  459406616 |    102 | 2017-08-30 09:10:08 | 2017-08-30 09:11:33
table2  |  332046816 | 1919230596 |    578 | 2017-08-30 14:25:29 | 2017-08-30 07:51:07
table3  |  729910818 | 4642232802 |    636 | 2017-08-30 19:29:25 | 2017-08-30 11:56:36

デッドタプルを削除するための自動バキュームチューニング

Amazon RDS のドキュメントはこのチューニングのための良いスタート地点を提供してくれています。自由に変更可能な多くのパラメータ提供されています。いくつかは Amazon RDS インスタンスに影響を与えることなく動的に変更ができます。いくつかはデータベース単位または表単位のどちらかで設定されます。私のチューニングはパラメータに焦点を当てており、前節までで示した2つの問題を解決するのに役立ちました。

問題1では、自動バキュームの開始が遅れてしまったという理由で、私の表に対してautovacuum_vacuum_scale_factorのデフォルト値が高すぎる値であることを示しました。このパラメータのデフォルト値は Amazon RDS for PostgreSQL バージョン 9.5 以前では 0.2 です。このパラメータは、どの表をバキューム対象なのかを決定する、デフォルト値が50のautovacuum_vacuum_thresholdと組み合わせて使います。

次の式は表に対して自動バキュームを行うかどうかの閾値を計算します。

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * タプル数

自動バキュームをより頻繁に実行させるためのトリガーとして、私はautovacuum_vacuum_scale_factorに 0.02 若しくは 0.01 などの小さな値を用いることを勧めます。このパラメータにより小さな値を設定すると、自動バキュームは実行の度により少ない数のデッドタプルを処理することになります。大きな表に対しては、これは自動バキュームがより少ないI/Oで、より短い時間で終了することができることを意味します。ただこれには、自動バキュームが不必要に頻繁に実行され、オーバーヘッドが発生する懸念があります。もしあなたが様々な大きさの表、異なる書き込みパターンの表を管理しているのであれば、このパラメータをデータベースレベルで単一の値にするのではなく、表のレベルに応じて異なる値にされることを推奨します。

問題1は、3つの自動バキュームセッションが同時実行しても自動バキュームの閾値に達したすべての表を処理しきれなかったことも示しています。そうするとautovacuum_max_workersを増やしたくなります。しかし、それぞれの自動バキュームセッションはそれなりの量のメモリとCPU、IOPSを消費します。自動バキュームセッションは、十分なシステムリソースの割り当てがあれば、最適な方法でジョブを実行できるようになります。一方では、パフォーマンスへの影響を予測できるように、システムリソースの消費量に制限を設ける必要があります。

バランスを取るには、もしautovacuum_max_workersを増加させることを考えるのであれば、次の2つのパラメータセットを確認し、それに応じて調整されることをお勧めします。

maintenance_work_memとautovacuum_work_mem：これら2つのパラメータはそれぞれの自動バキュームセッションが使用できる最大メモリ量を設定します。autovacuum_work_memが設定されていない場合には、maintenance_work_memと同じ値がデフォルトで設定されます。Amazon RDSの文書ではmaintenance_work_memについて詳しく述べられています。デフォルトでは、両方のパラメータは Amazon RDSインスタンスタイプに応じて次の数式で同一の値として定義されます:GREATEST({DBInstanceClassMemory/63963136*1024},65536) 私の経験ではこれは十分良い値です。
autovacuum_vacuum_cost_limitとautovacuum_vacuum_cost_delay: PostgreSQLは自動バキュームセッションがI/Oに与える影響を計算するためにコストベースのアルゴリズムを使用します。異なるI/O操作は異なるコストとして割り当てられます。自動バキュームセッションが開始された時、追跡されたそのI/O操作とコストが加算されていきます。トータルコストがautovacuum_vacuum_cost_limit/autovacuum_max_workersで計算される閾値を超えた時、自動バキュームセッションはautovacuum_vacuum_cost_delayで定められる時間、処理を中断します。この中断の目的は自動バキュームセッションのI/O使用量を削減するように調整することです。autovacuum_vacuum_cost_limitのデフォルト値は200であり、すべての自動バキュームセッションで均等に分散されることを考えると保守的であると言えます。私としてはautovacuum_max_workersを増やすのであればこの値は増やすことを推奨します。他の方法としては、中断時間を短くし、自動バキュームをより頻繁に実行させるためにautovacuum_vacuum_cost_delayをデフォルトの20ミリ秒から10ミリ秒かそれ以下に減少させることです。

問題2に立ち戻りましょう。長時間実行しているトランザクションまたはクエリは、デッドタプルを保持したまま自動バキュームによるデッドタプルの削除をブロックしたため、自動バキュームプロセスを妨害していました。statement_timeoutを設定して、SQLステートメントの最大許容時間を制限することをお勧めします。

PostgreSQL 9.6からは、idle_in_transaction_session_timeoutによっても一定時間後にステータスがtransaction in idleのセッションを終了することができます。これらのタイムアウト・パラメータの設定においては、優先度のバランスをとるために、データベースで最長のトランザクションとクエリを実行し、どの程度の時間がかかるのかを考慮しました。

自動バキュームがブロックされたり、肥大化したりする可能性のある別のシチュエーションとしては、Amazon RDS リード・レプリカを持つデータベースにおいてです。詳細は、PostgreSQLのドキュメントの「問い合わせコンフリクトの処理」を参照してください。

自動バキュームのモニタリングとチューニング結果の測定

パラメータ変更の後には、CloudWatchメトリックを用いて全体のシステムリソースを監視し、自動バキュームが並列で実行された時にも許容可能な範囲で収まっていることを確認することをお勧めします。先に示したCloudWatchのグラフで示されるように、autovacuum_max_workersを8、autovacuum_vacuum_cost_limitを4800に増加させた後、読み込みIOPSは10000 – 私が使っているAmazon RDSインスタンスタイプのキャパシティの1/3 – に上昇しました。

自動バキュームセッションを監視するのに、最も信頼性の高い方法はautovacuum のログ記録を使用することです。デッドタプルの数や自動バキュームの日付など、pg_stat_all_tablesビューにはいくつかの自動バキュームに関する情報が存在しているかもしれません。ただし、それらの表統計は見積もりに基づいており、場合によってはデータが入力されないことがあります。ログ記録では、実行時間、継続時間、システムリソースの使用状況、デッドタプルの削除の進行状況など、各自動バキュームセッションに関する詳細メッセージを提供することができます。トラブルシューティングの目的で異なるログレベルを設定することもできます。

以下は、rds.force_autovacuum_logging_levelを ‘log’ に設定し、log_autovacuum_min_durationを1000に設定した後のPostgreSQLエラーログの抜粋です。

SQL

2017-09-11 14:35:28 UTC::@:[46017]:LOG: automatic vacuum of table "oltp101.sh.table10": index scans: 1
	pages: 0 removed, 747397 remain, 0 skipped due to pins, 138 skipped frozen
	tuples: 48089 removed, 69738579 remain, 573231 are dead but not yet removable
	buffer usage: 756502 hits, 549470 misses, 117812 dirtied
	avg read rate: 16.254 MB/s, avg write rate: 3.485 MB/s
	system usage: CPU 1.36s/27.45u sec elapsed 264.09 sec

PostgreSQLのエラーログから自動バキュームメッセージを抽出するAPIを使用すると便利だとわかりました：

SQL

export region=us-east-1
export db_name=oltp101
export schema_name=sh
export table_name=table1
export hours_to_check=24

/apollo/env/AmazonAwsCli/bin/aws rds describe-db-log-files --region ${region} --db-instance-identifier ${db_name} --output text | sort -k2 -n | tail -${hours_to_check} | awk -F' ' '{print $3}' | while read i;
do
/apollo/env/AmazonAwsCli/bin/aws rds download-db-log-file-portion --region ${region} --db-instance-identifier ${db_name} --log-file-name ${i} --output text | grep -A 5 "automatic vacuum of table \"${db_name}.${schema_name}.${table_name}\""
done

まとめ

この記事では、大量の書き込み操作を行うOLTPデータベースの自動バキューム処理をチューニングしたケーススタディを紹介しました。今回の例では、自動バキュームでデッドタプルを迅速にクリーンアップできない場合、膨張が発生し、データベースのパフォーマンスの問題が発生することがわかりました。また、パラメータを調整し自動バキュームを監視する方法も学習しました。

この記事では、PostgreSQL用のAmazon RDSの自動バキュームについてより深く理解していただき、あなたのデータベース管理者ライフの助けになれば幸いです。詳細については、PostgreSQLのマニュアルとAmazon RDSのマニュアルを参照してください。

ご不明な点がございましたら、お気軽にコメントをお寄せください。

著者について

Lei Zeng は Amazon のシニアデータベースエンジニアです。

翻訳はソリューションアーキテクトの五十嵐が行いました。原文は A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL です。

Cookie の設定を選択する

Amazon Web Services ブログ