「MySQL」と「PostgreSQL」のテーブルとインデックスのメンテナンス手法を比較する
第10回の今回は、「MySQL」と「PostgreSQL」について、不要領域の回収・統計情報の更新・物理的な再編成というテーブル・インデックスおけるメンテナンスの考え方と手法を両製品の違いから整理して解説します。
6:30
はじめに
この連載では、オープンソースのリレーショナル・データベース(RDB)の「MySQL」と「PostgreSQL」を使いこなすヒントを、両製品の共通点と違いを確認しながら解説していきます。
現在、それぞれの製品をベースとしたクラウド・データベースは複数存在しています。この連載では製品の比較を多く行いますが、製品機能や仕様の優劣についてを論ずる目的のものではないことはあらかじめご了承ください。
第8回と第9回では、データベースのリソース、SQL文の性能、ロック、レプリケーションなどの監視について解説しました。第10回の今回は、監視によって把握した変化に応じて、テーブルやインデックスを適切な状態に維持するためのメンテナンスを取り上げます。
不要領域の回収、統計情報の更新、物理的な再編成では、目的や実行時の影響が異なります。自動処理に任せられる範囲と、手動実行や設定変更を検討する条件を、MySQLとPostgreSQLの共通点と違いから整理します。MySQLについては標準のストレージエンジンであるInnoDBを対象とします。
日常のメンテナンスと必要時の再編成を区別する
データベースのメンテナンスには、不要になった情報を回収して領域を再利用する処理、SQL文の実行計画を選択するための統計情報の更新、テーブルやインデックスの再編成などがあります。このうち、自動的に実行される処理もあれば、運用担当者が対象や実行するタイミングを判断するものもあります。
例えば、大量のデータを削除した場合、アプリケーションから見える行数は減っても、データファイルのサイズが同じ割合で小さくなるとは限りません。削除されたデータの領域をテーブル内で再利用できることと、ファイルを縮小してOSへ空き容量を返却することは異なる処理だからです。翌日以降のデータ追加で同程度の領域を再び利用するのであれば、ファイルを縮小せずに内部で再利用することも合理的です。
表1:メンテナンスの目的と実施判断
| 目的 | 主な処理 | 実施を判断する観点 |
| 不要になった情報の回収 | InnoDBのパージ、PostgreSQLのVACUUM | 自動処理が更新量に追いついているか、回収を妨げる要因がないか |
| 実行計画のための統計情報更新 | MySQLのANALYZE TABLE、 PostgreSQLのANALYZE | 大量ロードやデータ分布の変化に統計情報が対応しているか |
| 物理的な領域の再編成 | MySQLのOPTIMIZE TABLE、 PostgreSQLのVACUUM FULLやREINDEX | 領域やI/Oの問題を改善する必要があり、実行中の負荷やロックを許容できるか |
自動処理があるからと言って何も確認しなくてよいわけではありませんが、すべてのテーブルに重い再編成処理を定期実行することも適切とは限りません。通常のメンテナンスで安定した状態を維持し、それでは解消できない問題に対して、対象を絞った対応を検討するという順序で考えます。
処理そのものの必要性と、運用担当者による手動実行の必要性は区別して考えます。例えば、PostgreSQLでは継続的なVACUUMが必要ですが、Autovacuumが適切に処理していれば担当者が毎日手動で実行する必要はありません。以降では、手動実行や設定変更を判断するきっかけと、その必要性をあわせて説明します。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/innodb-multi-versioning.html
・https://dev.mysql.com/doc/refman/26.7/en/optimize-table.html
・https://www.postgresql.jp/document/18/html/routine-vacuuming.html
MySQLのメンテナンス
不要になった行やUNDO情報をパージする
InnoDBストレージエンジンのトランザクション管理は、MVCC(Multi-Version Concurrency Control)のために更新前の行を再構成する情報をUNDOログに保持します。更新処理をコミットしても、別のトランザクションが過去の状態を参照する可能性がある間は、その情報を破棄できません。また、DELETEで削除された行も、直ちに物理的に取り除かれるわけではありません。
不要になったUNDO情報や削除済みのレコードを整理する処理がInnoDBにおけるパージです。InnoDBではバックグラウンドで実行されるため、通常の運用で利用者がVACUUMに相当するSQL文を定期実行する必要はありません。ただし、これは不要領域の回収という目的に関する比較であり、PostgreSQLのVACUUMと内部処理や役割がすべて同じという意味ではありません。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/innodb-multi-versioning.html
・https://dev.mysql.com/doc/refman/26.7/en/innodb-purge-configuration.html
長時間継続するトランザクションが古い状態を参照し続けると、過去の状態を再現する情報を保持する必要があり、パージが進まず、UNDO領域の増加につながることがあります。更新を行わない読み取り専用の処理でも、過去のスナップショットを保持する場合は影響する点に注意が必要です。
UNDO領域の使用量が増え続ける場合や、更新量が落ちた後も不要な履歴が減らない場合には、パージの進行を調査します。SHOW ENGINE INNODB STATUSのTRANSACTIONSセクションにあるHistory list lengthは、その判断に利用できる情報です。ただし、この値は不要になった行数やUNDO領域のバイト数そのものではありません。値の増加が継続しているかを確認し、第9回で紹介したINFORMATION_SCHEMA.INNODB_TRXなどから、長時間継続しているトランザクションと関連付けて調査します。
History list lengthについて、どのシステムでも共通して設定変更が必要となる数値はありません。同程度の更新負荷で、業務処理の応答時間とUNDO領域の使用量が安定していた期間に記録した値の範囲と比較します。その上で、値の増加が続く時間、更新処理が落ち着いた後の減少傾向、UNDO領域の使用量を組み合わせて判断します。自動処理で安定していれば、設定変更は不要です。
まず、アプリケーションがトランザクションを終了し忘れていないか、長時間の参照処理や大量更新がないかを確認します。回収が妨げられている場合、パージ用のスレッド数の上限であるinnodb_purge_threadsを増やすだけでは解消できません。続いて、阻害要因がないのに処理が追いつかない場合は、更新対象の分散状況とCPUやI/Oの余力を確認します。複数のテーブルへ更新が分散し、リソースにも余力がある場合に上限の増加を検討します。
少数のテーブルへ更新が集中する場合は、スレッドを増やすと競合が増える可能性もあります。InnoDBは設定された上限の範囲で実際に利用するパージ・スレッド数を調整するため、この値の変更は通常時の必須作業ではなく、処理の遅れとリソースの余力を確認した場合の調整となります。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/innodb-purge-configuration.html
統計情報を更新する
MySQLのオプティマイザは、テーブルの行数やインデックスの値の分布などの統計情報を使い、SQL文の実行計画を選択します。データを大量にロードした場合やデータの分布が大きく変わった場合には、統計情報が実際の状態を十分に反映していないことが適切な実行計画を選べない原因になる場合があります。
統計情報を永続化するinnodb_stats_persistentと、自動再計算を制御するinnodb_stats_auto_recalcがデフォルトで有効です。永続化された統計情報の自動再計算はテーブルの行の10%を超える変更などを契機にバックグラウンドで実行されますが、変更直後に同期して完了するものではありません。大量ロードの直後に業務処理を開始する場合など、必要な時点までに統計情報を更新したいときはANALYZE TABLEを明示的に実行する方法があります。
この10%は自動再計算の契機であり、超えたら手動実行が必須になる閾値ではありません。手動実行を検討するのは、大量ロード直後に新しい統計情報が必要な場合、自動再計算を無効にしている状態でインデックス列のデータを大きく変更した場合、データ変更後に実行計画が変わり応答時間が悪化した場合などです。自動更新で十分な精度と性能を維持できていれば一律の定期実行は必須ではなく、永続化や自動再計算の設定をあえて変更する必要もありません。
ANALYZE TABLEは統計情報を更新する処理であり、テーブルを再構築してファイルを縮小する処理ではありません。また、InnoDBでは解析中に読み取りロックを取得するため、更新処理への影響を考慮して実行するタイミングを決める必要があります。
実行後は完了メッセージの確認だけでなく、第9回で紹介したEXPLAINやSQL文の性能統計を使って対象のSQL文が期待する実行計画や応答時間になっているかも確認します。統計情報を更新すれば、どのSQL文も必ず高速になるというものではありません。更新前後を比較できるよう、主要なSQL文の実行計画を記録しておくことも有効です。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/innodb-persistent-stats.html
・https://dev.mysql.com/doc/refman/26.7/en/analyze-table.html
OPTIMIZE TABLEによる再編成を判断する
MySQLのOPTIMIZE TABLEは、テーブルとインデックスの物理的な格納状態を再編成するためのSQL文です。通常のInnoDBテーブルではALTER TABLE ... FORCEに対応するテーブルの再構築が行われます。パージや統計情報の更新だけを目的とする処理とは異なり、テーブル全体に対する処理が発生することを理解しておく必要があります。
テーブルごとに独立した.ibdファイルを持つfile-per-tableの表領域では、大量削除などで生じた未使用領域を再編成してOSへ返却できます。一方、システム表領域や一般表領域のように複数のテーブルを格納できる共有の表領域では、内部に空きができることと表領域ファイル自体が縮小することを区別しなければなりません。現在のinnodb_file_per_tableの値だけではなく、対象テーブルが実際にどの表領域に格納されているかを確認します。
一般的なInnoDBテーブルではオンラインDDLが利用され、処理の多くを通常の読み書きと並行して実行できます。ただし、準備や完了の段階では排他的なロックが必要です。長いトランザクションがある場合にはロックの取得待ちが発生する可能性があり、「オンラインだからアプリケーションへの影響がない」という意味ではありません。FULLTEXTインデックスを持つテーブルなどではオンラインDDLを利用できない条件もあります。
さらに、再構築中は一時的なソート・ファイルや中間テーブル用の領域が必要になります。中間テーブルには元のテーブルと同程度の領域が必要になる場合があるため、データディレクトリだけでなく一時ディレクトリ側の空き容量も確認します。空き容量がほとんどない状態で容量削減を目的に実行すると、処理に必要な領域を確保できないことがあります。
大量削除後も同じテーブルにデータが追加され続け、内部の空き領域を再利用できているのであれば、直ちに再編成する必要はありません。データ保持期間の変更により今後の格納量が大きく減る場合など、物理的に容量を返却する効果と、実行中の負荷やロックの影響を比較して判断します。
「データを何%以上削除したら必ず実行する」という共通の基準はなく、OPTIMIZE TABLEは条件に応じて選ぶ処理です。SQL文の応答時間が悪化した場合も、まず第9回の監視情報から実行計画やロック待ちを調べ、再編成で解決できる問題かを確認します。また、innodb_file_per_tableの変更だけで既存テーブルの格納先が切り替わるわけではないため、既存ファイルを縮小する目的でこの設定だけを変更することは適切ではありません。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/optimize-table.html
・https://dev.mysql.com/doc/refman/26.7/en/innodb-file-per-table-tablespaces.html
・https://dev.mysql.com/doc/refman/26.7/en/innodb-online-ddl-space-requirements.html
PostgreSQLのメンテナンス
通常のVACUUMを基本とする
第8回では、PostgreSQLの不要な行バージョンの回収と、Autovacuumの監視方法を紹介しました。ここでは、回収処理をどのように維持し、手動処理を補うかという観点で整理します。
PostgreSQLでは、UPDATEやDELETEで不要になった行バージョンを通常のVACUUMで回収し、テーブルやインデックス内の領域を再利用できるようにします。通常はAutovacuumが自動的に処理するため、まず自動処理により不要領域の増加を抑えられているかを確認します。VACUUMは可視性マップの保守や古いトランザクションIDの「凍結」にも関係するため、単にデータファイルの空きを増やすためだけの処理ではありません。
PostgreSQLでの「凍結」とは、十分古い行バージョンの挿入トランザクションIDを、常に過去のものとして扱えるようにする処理です。トランザクションIDが周回しても行の新旧を誤って判定しないために必要であり、行データの更新を禁止するという意味ではありません。第8回でも周回防止の必要性に触れましたが、この処理の進行もメンテナンスで確認する対象となります。
大量の更新や削除を行った後、次の業務処理を始めるまでに不要領域を回収しておきたい場合や、自動処理の開始を待てない場合には、対象を指定して手動実行を検討します。Autovacuumで十分に回収できていれば、更新や削除のたびに手動実行する必要はありません。以下の例では通常のVACUUMと、後述する統計情報の更新をあわせて実行します。
VACUUM (VERBOSE, ANALYZE) public.orders;VERBOSEは処理の詳細なメッセージを出力するオプションで、手動で回収結果を調べたい場合に利用します。ANALYZEはオプティマイザ向けの統計情報を収集する指定で、不要領域の回収と統計情報の更新を同時に行いたい場合に付けます。通常のVACUUMだけでは、列の値の分布などを収集するANALYZEの代わりにはなりません。また、VACUUMはトランザクション・ブロック内では実行できないため、BEGINとCOMMITで囲んだ処理や、利用ツールの自動的なトランザクション制御に注意します。
通常のVACUUMは、原則としてSELECT、INSERT、UPDATE、DELETEと並行して実行できますが、I/O負荷は発生します。テーブル末尾の空きページを切り詰める際には排他的なロックが必要となる場合もあるため、ロックや負荷が一切発生しない処理ではありません。処理の進捗は第8回で紹介したpg_stat_progress_vacuumから確認できます。
【参考URL】
・https://www.postgresql.jp/document/18/html/routine-vacuuming.html
・https://www.postgresql.jp/document/18/html/sql-vacuum.html
Autovacuumの実行条件と処理能力を調整する
Autovacuumの調整では、処理の開始条件、処理能力、回収を妨げる要因を分けて確認します。自動処理で領域と性能が安定していれば、設定変更は必須ではありません。
表2は自動VACUUMやANALYZEの実行対象を決める件数の閾値と、その計算例です。計算例は推定100万行(N=1,000,000)、全行凍結済みのページがない状態(U=1)を前提とし、各パラメータにはPostgreSQL 18のデフォルト値を使用しています。
- N: システムカタログ
pg_classのreltuples列に格納された有効行数の推定値。この値はVACUUMやANALYZEなどで更新され、全行を数えた正確な件数ではない - U: 全ページに対する「全行が凍結済みとは記録されていないページ」の割合
表2:Autovacuumの主な実行条件と閾値の例
| 処理のきっかけ | 閾値の計算式 | 閾値の例 |
| 更新・削除による不要な行バージョンの増加 | min(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × N) | 200,050件 |
| 前回のVACUUM以降の挿入 | autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × N × U | 201,000件 |
| 前回のANALYZE以降の挿入・更新・削除 | autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × N | 100,050件 |
各推定件数が閾値を超えると処理対象になりますが、即時の開始や手動実行を必須とする値ではありません。検査タイミングやワーカーの空き状況、統計情報の反映にも左右されます。
PostgreSQL 18のautovacuum_vacuum_max_thresholdが-1なら上限を適用せず、autovacuum_vacuum_insert_thresholdが-1なら挿入件数による条件を無効にします。挿入だけのテーブルでも可視性マップの更新や凍結が必要です。
設定変更は以下の傾向がある場合に検討します。確保すべき空きディスク容量は運用側で決める基準であり、PostgreSQL共通の容量上限ではありません。
表3:Autovacuumの状態に応じた調整の方向
| 確認された傾向 | 検討する調整 |
| 自動VACUUMが始まるまでに不要領域が増え、運用で確保したい空きディスク容量を維持できない | 固定件数のautovacuum_vacuum_thresholdや、推定行数に掛ける割合のautovacuum_vacuum_scale_factorを下げ、早めに回収を開始。上限のautovacuum_vacuum_max_thresholdも確認 |
| 自動ANALYZEが始まるまでにデータ分布が変わり、古い統計情報によって不適切な実行計画が選ばれる | 固定件数のautovacuum_analyze_thresholdや、推定行数に掛ける割合のautovacuum_analyze_scale_factorを下げ、より少ない変更件数で解析対象になるよう調整 |
| 複数テーブルが対象となり、ワーカー待ちが続く | autovacuum_max_workersの増加を検討。autovacuum_worker_slotsの上限も確認 |
| VACUUMによる回収が更新・削除による不要領域の増加に追いつかず、I/Oに余力がある | autovacuum_vacuum_cost_limitによる実効的なコスト上限を増やす、またはautovacuum_vacuum_cost_delayで指定する休止時間を短くし、処理を進める |
| VACUUMと業務処理がI/Oを競合し、業務の応答時間が悪化する | コスト上限を下げる、または休止時間を長くして、VACUUMのI/O負荷を抑える |
ワーカー増加は複数テーブルの並行処理を増やす調整です。コスト上限は休止までに許容するI/O操作の累積コストを表します。autovacuum_vacuum_cost_limitが-1なら通常のVACUUM用の上限を引き継ぎます。変更後はI/Oと業務の応答時間を確認します。
長時間トランザクションやhot_standby_feedbackの影響で回収が妨げられる場合は設定変更だけでは解消できないため、第9回の監視情報から原因を調べます。
凍結の実行条件にはテーブル内の凍結されていないトランザクションIDの下限値と現在のIDの差をトランザクション数で表すage(relfrozenxid)も使われます。autovacuum_freeze_max_ageに達する段階では、周回防止の処理が強制されます。
周回防止のVACUUMが完了せず、age(relfrozenxid)の値が増え続ける場合は、処理の進捗と阻害要因を調査します。must be vacuumed withinを含む周回警告が出た場合は速やかに阻害要因を解消し、通常のVACUUMを進める必要があります。警告を待つ運用やAutovacuumの恒常的な無効化は避けるべきです。
【参考URL】
・https://www.postgresql.jp/document/18/html/runtime-config-vacuum.html
・https://www.postgresql.jp/document/18/html/catalog-pg-class.html
・https://www.postgresql.jp/document/18/html/routine-vacuuming.html
・https://www.postgresql.jp/document/18/html/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
ANALYZEで実行計画のための統計情報を更新する
PostgreSQLでも、行数や列の値の分布などの統計情報が実行計画の選択に利用されます。通常はAutovacuumが統計情報の自動収集も行いますが、大量ロードの直後など、業務処理を始める前に更新しておきたい場合にはANALYZEを明示的に実行できます。
手動実行を検討するきっかけには、データの大量追加・削除後にSQL文の応答時間が悪化した場合や、実行計画の推定行数が実際の行数と大きく異なる場合もあります。こうした差は調査の手掛かりであり、統計情報だけが原因とは限らないため、更新後も改善しなければ索引設計やSQL文の条件などを調べます。通常テーブルで自動解析が十分に機能していれば、一律の手動実行は必須ではありません。
この処理は不要な行バージョンを回収するVACUUMとは目的が異なるため、統計情報だけを更新する場合にはANALYZEを選択できます。通常の読み書きと並行して実行できますが、データの読み取りによる負荷が生じることには注意が必要です。
一方、Autovacuumはパーティション親テーブルのANALYZEを自動実行しません。個々のパーティションで自動解析が行われていても、親テーブルの統計情報は別に更新する必要があります。パーティションの追加・削除や子テーブルへの大量ロードなど、構成やデータ分布を大きく変える操作の後を実施のきっかけとして親テーブルの統計情報を利用するSQL文に合わせて手動ANALYZEを計画します。更新後はMySQLと同様に、主要なSQL文の実行計画と応答時間を確認します。
【参考URL】
・https://www.postgresql.jp/document/18/html/sql-analyze.html
・https://www.postgresql.jp/document/18/html/routine-vacuuming.html#VACUUM-FOR-STATISTICS
VACUUM FULLとREINDEXを使い分ける
通常のVACUUMが内部領域を再利用するのに対し、VACUUM FULLはテーブルを新しいファイルへ書き直し、REINDEXはインデックスを再構築します。選択条件と影響は表4の通りです。
表4:再編成処理の選択条件と影響
| 処理 | 実施を検討するきっかけ | 通常の読み書きへの影響 |
VACUUM FULL | 大量削除後に今後のデータ量も減り、テーブル本体の物理容量を返却したい | テーブルを書き直し、ACCESS EXCLUSIVEロックで読み書きをブロック |
REINDEX | 有効なデータ量に対してインデックスが肥大化し、領域やI/Oの改善が見込める | 通常は書き込みをブロックし、読み取りも待たされる場合がある。対応するインデックスではCONCURRENTLYで通常の読み書きを継続可能 |
いずれも一律の定期実行は必須ではなく、追加領域が必要です。内部の空きを再利用しデータ量も元に戻るなら、原則としてVACUUM FULLは不要で、周回警告への対処にも使用しません。Autovacuumが自動実行することもありません。
インデックスでは、削除後に各ページへ少量のキーが残る場合などに再構築が有効です。単なるデータ増加と区別し、容量、読み取り量、応答時間から判断します。読み書きを継続する場合の例は以下の通りです。
REINDEX INDEX CONCURRENTLY public.orders_created_at_idx;CONCURRENTLYでは通常のREINDEXより処理時間や作業量が増えることがあり、トランザクション・ブロック内では実行できません。対象インデックスの利用条件も事前に確認します。
失敗後にINVALIDが表示された場合は後処理が必要です。同時再構築で残った無効なインデックスの名前に_ccnewと_ccoldのどちらの接尾辞が付いているかを確認します。前者は新規作成途中のインデックスで原因解消と削除後に再試行し、後者は切り替え後に残った不要な旧インデックスを削除します。接尾辞には番号が付く場合もあるため名前だけで機械的に削除せず、インデックスの状態を確認して対応します。
【参考URL】
・https://www.postgresql.jp/document/18/html/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY
・https://www.postgresql.jp/document/18/html/sql-vacuum.html
・https://www.postgresql.jp/document/18/html/sql-reindex.html
・https://www.postgresql.jp/document/18/html/routine-reindex.html
本番環境での実施計画と結果の確認
メンテナンスを実行する際は、実行コマンドだけでなく、実行する条件と完了と判断する条件を決めておくことが重要です。以下は本番環境で実施する際に確認しておきたい項目です。
- 対象と目的を特定する: 統計情報の更新、不要領域の再利用、OSへの容量返却など、解決したい問題を明確化
- 実行前の状態を記録する: 対象の容量、空き容量、主要SQL文の実行計画と応答時間、レプリケーション遅延を確認
- 所要時間と影響を検証する: 本番に近いデータ量や更新量で、ロック、I/O負荷、一時領域、ログの増加を確認
- 実行と中断の条件を決める: 対象を絞り、負荷の低い時間帯に順番に実行。許容する待機時間やレプリケーション遅延、業務への影響も事前に決定
- 実行後の状態を確認する: 処理の完了、容量や実行計画の変化、主要SQL文の性能、レプリケーション先の追従まで確認
特に再編成では、実行した処理を中断すれば直ちに元の負荷へ戻るとは限りません。中断後の後処理やロックの解放まで含め、実際のコマンドとバージョンで挙動を検証します。第6回で整理したバックアップと復旧手順も、実施前に確認しておく必要があります。
レプリケーション構成では、処理の影響をソースやプライマリだけで判断しないことも重要です。MySQLのANALYZE TABLEとOPTIMIZE TABLEは、デフォルトでバイナリログに記録され、レプリカでも実行されます。一方、PostgreSQLの物理レプリケーションでは、プライマリでのメンテナンスにより発生したWALをスタンバイが適用するため、処理の転送・適用負荷にも注意します。両製品とも、実行したサーバーで処理が終了した後もレプリケーション先の負荷や遅延が収まるまで確認します。
【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/analyze-table.html
・https://dev.mysql.com/doc/refman/26.7/en/optimize-table.html
・https://www.postgresql.jp/document/18/html/warm-standby.html
MySQLとPostgreSQLのメンテナンスの比較
両製品に共通するのは、自動処理が正常に進む条件を維持し、手動処理は目的と影響を確認して実施することです。一方で、不要領域の回収方法や再編成中のロックには違いがあります。SQL文の名前だけを対応付けるのではなく、どの情報を整理し、どの領域を再利用し、何を待たせるのかという観点で比較すると、運用時の判断につながります。
表5:MySQLとPostgreSQLの主なメンテナンスの比較
| 観点 | MySQL(InnoDB) | PostgreSQL |
| 不要になった情報の回収 | バックグラウンドのパージ | 通常はAutovacuumによるVACUUM |
| 回収を妨げる主な要因の例 | 古い読み取りビューを保持するトランザクション | 古いスナップショットを保持するトランザクションなど |
| 統計情報の自動更新 | InnoDBの統計情報の自動再計算 | Autovacuumによる自動ANALYZE |
| 統計情報の手動更新 | ANALYZE TABLE | ANALYZEまたはVACUUMのANALYZEオプション |
| 物理的な領域の再編成 | OPTIMIZE TABLE。OSへの容量返却は表領域の種類に注意 | VACUUM FULLによるテーブルの書き直し、REINDEXによるインデックス再構築 |
| 再編成中の通常処理 | オンラインDDLの利用条件と準備・完了時のロックに注意 | VACUUM FULLは読み書きをブロック。対応するREINDEXではCONCURRENTLYを利用可能 |
まとめ
今回は、MySQLとPostgreSQLのテーブルとインデックスを適切な状態に維持するためのメンテナンスを解説しました。不要領域の回収、統計情報の更新、物理的な再編成は、それぞれ目的と影響が異なります。監視によって変化を把握し、自動処理の調整、対象を絞った手動処理、必要時の再編成を使い分けることが重要です。
メンテナンスは、実行したサーバーで処理が終了すれば完了というものではありません。アプリケーションの性能やレプリケーション先への影響まで確認することが、安定した本番運用につながります。
次回は、ログの保持と容量管理に加え、ログ管理ツールとの連携による収集・保管・分析の運用について解説します。
この記事をシェアしてください
