SREの林 aka もりはやです。
先日オンプレで稼働していたとあるPostgreSQLのDBを、Amazon Aurora PostgreSQL（以後はAurora）に移行を行ったところ CREATE INDEX が以下のエラーで作成できない問題に苦労させられたため、解決策と合わせて公開します。
これがベストな対応かは自信がないため、より良い方法をご存知の方はお気軽に教えてください。
以下はDBのリストア中に CREATE INDEX で発生したエラーです。
pg_restore: error: could not execute query: ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp19647.47.fileset/1.2": No space left on device CONTEXT: parallel worker Command was: CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
TL;DR
シンプルに経緯と課題と解決策を述べると以下です。
- 経緯
- オンプレのPostgreSQL13を利用するZabbix Serverをマイグレーションしたかった
Zabbixシステムを丸ごと移行するためOSとミドルを以下の様にアップグレード
- OS: CentOS ? -> Ubuntu 22:04 *1
- AP: Zabbix Server 5.0 -> 6.0
- DB: PostgreSQL 13 -> 15
移行手段は
pg_dump -Fc <DB>で取得したダンプファイルをpsqlでAuroraへ流し込んだ
- 課題
- psql実施中にエラー
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp19647.26.fileset/0.2": No space left on device - 原因はどうやら
CREATE INDEX中に使われるのが2つあるAuroraのボリュームのうちの、自動拡張されるクラスターボリュームではなく、一時ストレージと呼ばれる領域が溢れている - PostgreSQLの仕様で、大規模テーブルのINDEX作成時には一時領域を利用しているようだ*2
- psql実施中にエラー
- 対策
- 対策として以下の2つを行なって無事に
CREATE INDEXを行えた- DBインスタンスクラスのスケールアップ
- psqlで
CREATE INDEXを実行するセッションでSET work_mem = 'xxxGB';※maintenance_work_mem が適切だった
- 対策として以下の2つを行なって無事に
詳細
以下からは詳細になります。
経緯
当社はモニタリングをDatadogを主体に利用していますが、ネットワーク機器や一部のオンプレミス機器に対してはZabbixを活用してきました。
そのZabbixが動作するOSのバージョンが年季が入っており、かつ稼働環境も当社のメイン基盤であるAWSではないことから、AWSへの移行を開始しました。*3
構成はシンプルで、Zabbix Serverおよび、DBのPostgreSQLもVMで動作していました。
オンプレPostgreSQLからのバックアップはS3に日次で取得されている
Zabbix DBであるPostgreSQLのデータは、バックアップを目的として日次で pg_dump によって取得し、S3へアップロードを行っています。
以下はPostgreSQLのバックアップシェルから主要コマンド部分を抜粋したものです。
pg_dump で出力したDBを gzip で固めて、それを s3 でアップロードするだけのシンプルなスクリプトです。*4
# Data dump $ pg_dump -Fc zabbix | gzip > /mnt/backup/pg_dump.gz;date # Upload data to S3 $ aws s3 rm s3://hogehoge-backet/pg_dump.gz && \ $ aws s3 cp /mnt/backup/pg_dump.gz s3://hogehoge-backet/
Auroraへのリストア
新しい環境となるAWS上では、DBはAuroraを選択しています。
リストアにあたっては上述した様にZabbixのDBデータがS3に毎日アップロードされている状態でしたので、それを適当なEC2から psql を用いて流し込むだけの想定でした。
シンプルなバックアップ＆リストアになりますが、これにはZabbixを利用するネットワーク担当の同僚氏から「ダンプからリストアの間に欠損するデータについては気にしなくて良いよ」とコメントをいただいていためです。これは大変ありがたい話で、RPOを短くするならストリーミングなどの手段を取る必要が出てくるため移行のハードルが下がったと感じています。*5
具体的には以下様なコマンドでAuroraへリストアを行いました。
$ aws s3 cp s3://hogehoge-backet/pg_dump.gz ./ ; echo $? $ time gunzip -c pg_dump.gz | pg_restore -c -d zabbix -h aws-aurora-oisixradaichi-zabbix.example.com -U zabbix
課題
DBリストア中にエラーが発生、ただし初回はスルーした
上記のリストアコマンドは当初は順調に進んでいる様に見えました。 コマンド実行の直後はオンプレからAuroraへ移行する時特有のPublic系テーブルのエラーが多発しましたが、これはRDSやAuroraの仕様と判断して無視しました。
数十時間をかけてリストアが終わり、発生したエラーは以下のみでした。 後ほどこのエラーがZabbixの動作に致命的なボトルネック要因となるのですが、このタイミングではPublic系テーブルの大量エラーを見送った直後でもあり「とりあえず動けばまあ大丈夫だろ」と判断して仮リリースを行いました。
pg_restore: error: could not execute query: ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp19647.26.fileset/0.2": No space left on device
Command was: ALTER TABLE ONLY public.trends_uint
ADD CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock);
pg_restore: error: could not execute query: ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp19647.47.fileset/1.2": No space left on device
CONTEXT: parallel worker
Command was: CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
pg_restore: error: could not execute query: ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp19647.51.fileset/0.2": No space left on device
Command was: CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock);
pg_restore: warning: errors ignored on restore: xxx
こうして上述のエラーはでましたが、OS、APであるZabbix Server、DBであるPostgreSQLのバージョンをそれぞれ上げ、環境を大きくオンプレからAWSへ移行しても、Zabbix Serverは綺麗に上がってきました。この結果はZabbix Server内部でのバージョンマイグレーションプロセスがしっかり作られているためと認識しておりZabbix開発関係者を大きくリスペクトします。
仮リリース中に画面が重くなり504が発生
DBリストア後、オンプレZabbixと並行する形でAWS上に移行したZabbixの運用を開始しました。ネットワーク担当の同僚氏にチェックをお願いしたところ、当初は良さそうでしたが途中からどうにも画面が重く、場合によっては504が多発する様な事態となりました。
ボトルネックの特定
当初Zabbix Server側のキャッシュチューニングを行いましたが効果はありませんでした。
DatadogやCloudWatchを含め調査したところ、Performance InsightでDBがボトネックとなっていることが見えました。
圧倒的な IO:DataFileRead (水色) の多発です。
加えて Top SQL には以下のクエリが表示されていました。
select clock,ns,value from history_uint where itemid=? and clock>?
ここまで見れば一目瞭然で、リストア時に無視したINDEXのエラーが原因と判断できました。INDEXの無い history_uint テーブルにREADアクセスが走り、詰まっていたのです。
どうしてCREATE INDEXのエラーが発生したのか
INDEXが無いことがボトルネックと特定できましたので、改めて手動で CREATE INDEX を実行しましたが、同じエラーが発生しました。
> CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock); ERROR: could not write to file "base/pgsql_tmp/pgsql_tmpxxxx.xx.fileset/0.x": No space left on device
この時の私の認識では、ストレージが自動拡張するAuroraでどうして No space left on device のようなストレージのエラーが出るのか不思議でした。
原因はどうやら CREATE INDEX 中に使われるAuroraのディスク領域について、2つあるAuroraのボリュームのうちの、自動拡張されるクラスターボリュームではなく、ローカルストレージと呼ばれる領域が溢れているとわかりました。
AWSドキュメント - Amazon Aurora ストレージと信頼性 より「Aurora は、非永続的な一時ファイル用に、分離したローカルストレージを使用します。これには、クエリ処理中の大きなデータセットのソートや、インデックスの作成などの目的に使用するファイルが含まれます。」
上の引用の様に、PostgreSQLでは大規模テーブルのINDEX作成時には一時ファイルを作成し、それはローカルストレージを利用しているようです。*6
実際にCloudWatchのグラフでも、ローカルストレージが CREATE INDEX 実行後に減り続けて枯渇し、エラー後に急激に回復している様子が見えました。
対策
対策1: Auroraのローカルストレージ（一時領域）はスケールアップで増える
AWSドキュメント - Amazon Aurora PostgreSQL の管理 - Aurora PostgreSQL 用の一時ストレージの制限 にあるように、Auroraのローカルストレージはインスタンスのサイズによって変動します。
例を挙げると以下の様に倍々で増えていきます。
| DB instance class | Maximum temporary storage available (GiB) |
|---|---|
| db.r7g.large | 32 |
| db.r7g.xlarge | 63 |
| db.r7g.2xlarge | 126 |
| db.r7g.4xlarge | 252 |
これを受けて CREATE INDEX が失敗した場合はスケールアップをすることでエラーを回避することにしました。
対策2: セッションで SET work_memによる作業メモリの拡大（本来適切なのはmaintenance_work_mem）
（2024-02-01追記）本記事の公開当初は work_mem のチューニングを対策として述べておりましたが、それは不適切でした。正しくは maintenance_work_mem のチューニングが CREATE IDNEX の一時ファイルに対しては有効です。謹んで訂正します。
----ここからの work_mem についての記事は公開当初の内容です----
加えてPostgreSQLにはセッション単位で作業メモリを変更することができる work_mem というパラメタがあります。
PostgreSQL 15.4文書 - 第20章 サーバの設定 - 20.4. 資源の消費 より引用
work_mem (integer) 一時ディスクファイルに書き込むようになる前に、問い合わせ操作（たとえば並べ替えとハッシュテーブル操作）が使用する基本的な最大のメモリ容量を指定します。
具体的には psql で接続後、以下を実施しました。
zabbix=> SET work_mem = 'xxxGB'; SET zabbix=> zabbix=> show work_mem; work_mem ---------- xxxGB (1 row)
----ここまでの work_mem についての記事は公開当初の内容です----
（2024-02-01追記）上記の様に本記事の公開当初は work_mem のチューニングを対策として述べておりましたが、それは不適切でした。正しくは maintenance_work_mem のチューニングが CREATE IDNEX の一時ファイルに対しては有効です。謹んで訂正します。
PostgreSQL 15.4文書 - 第20章 サーバの設定 - 20.4. 資源の消費 より引用
maintenance_work_mem (integer) VACUUM、CREATE INDEX、およびALTER TABLE ADD FOREIGN KEYの様な保守操作で使用されるメモリの最大容量を指定します。 この値が単位なしで指定された場合は、キロバイト単位であるとみなします。 デフォルト値は64メガバイト（64MB）です。 1つのデータベースセッションでは、一度に1つしか上記操作はできませんし、通常インストレーションでこうした操作が同時に非常に多く発生することはありませんので、これをwork_memよりもかなり多めの値にしても安全です。 大きい値を設定することでvacuum処理と、ダンプしたデータベースのリストア性能が向上します。
あとはもうトライ＆エラー
ここからは泥臭くてダサい話になります。
こうして対策はわかりましたが、その時の私はINDEXを作成するためにどの程度の一時領域が必要になるかがわからず、とりあえずワンサイズあげればいけるだろうと甘い見込みでリトライを行いました。
結果として以下の"FreeLocalStorage"のグラフがさす様に、何度も失敗してはスケールアップを繰り返してしまいました。
流れとしては以下の3つをリトライのたびに繰り返したのです。
- グラフのメトリクスは
CREATE INDEX実行開始で消費され右肩下がり減っていきます。 - そして枯渇してエラーとなり急げきに上にあがります。
- その後Auroraをスケールアップすることで約2倍の空き容量になります。
ここまで説明すれば、読まれた方には半分涙目になりながら CREATE INDEX と SET work_mem (本来適切なのは maintenance_work_mem )とAuroraスケールアップをするSREの姿がグラフから浮かんでくるでしょうか...。*7
無事にINDEXを作成でき、Zabbixは安定稼働中
こうして、5回目のトライでなんとか CREATE INDEX に成功した後は、コストの観点からすぐにAuroraのインスタンスを適正サイズに変更しました。
改めて謝罪と共にネットワーク担当の同僚氏に引渡しを行い、彼からは見違える様にサクサクで安定稼動していると評価いただいています。
諸々のバージョンを上げているため、今後運用しながら細かなトラブルシュートは発生しそうですが、それはまた別の機会でナレッジが公開されるかもしれません。
まとめと今後への反省
以上で無事にZabbix Serverを移行できましたが、落ち着いてからいくつかの反省点があるため自戒とともに記載します。
Auroraのインスタンスのチョイスはもっと熟慮すべきだった
後から気づいたことで、当初はコストと性能のバランスから db.r6g 系を採用していましたが、2023年11月ごろにリリースされていたOptimized Readsな db.r6gd を採用していれば起きない問題だった可能性があります。
NVMEベースの一時ストレージを有する Optimized Reads については、上述した一時ストレージ制限のリストにも記載がなく、もしかするとクラスターボリュームと同じく可変スケールなのかもしれませんが、これは未確認な情報です。
テーブルサイズからある程度のIndexサイズの見積もりができたはず
既存のZabbixのDBは稼動しているため、そちらのテーブルとIndexのサイズを確認することで、どの程度の一時領域が必要になるか見積もれた可能性があります。事前の見積もりを正しく行えば5回もトライせずに少ないリトライで解決できたかもしれません。
work_memではなくmaintenance_work_memをチューニングすべきだった
（2024-02-01追記）
上の記述についても追記訂正をしましたが、 CREATE INDEX への対策としては work_mem ではなく maintenance_work_mem が適切でした...
トライアンドエラー時にも、AWSやPostgreSQLのドキュメントをちゃんと読めば辿り着けていたはずですが、目についた work_mem を盲信してしまっていました。大反省ですが、もう忘れることはないでしょう。
PostgreSQL 15.4文書 - 第20章 サーバの設定 - 20.4. 資源の消費 より引用
maintenance_work_mem (integer) VACUUM、CREATE INDEX、およびALTER TABLE ADD FOREIGN KEYの様な保守操作で使用されるメモリの最大容量を指定します。 この値が単位なしで指定された場合は、キロバイト単位であるとみなします。 デフォルト値は64メガバイト（64MB）です。 1つのデータベースセッションでは、一度に1つしか上記操作はできませんし、通常インストレーションでこうした操作が同時に非常に多く発生することはありませんので、これをwork_memよりもかなり多めの値にしても安全です。 大きい値を設定することでvacuum処理と、ダンプしたデータベースのリストア性能が向上します。
最後に
時間も気力も消費しましたが、結果として無事に移行できたのは大変よかったです。加えてトライアンドエラー中に目を通した多くのドキュメントやブログやAIによる回答は、自分の知見として蓄えることができたと言えます。
システムの移行は骨の折れる作業ですが、誰かがやらなければならない重要な作業です。同じ様な立場の皆さんにエールと一緒にがんばろうぜの念を送って本記事の結びとします。
