PostgreSQLのデータが肥大化
zabbix 3.0、PostgreSQL 9.2.23 を使用しています。
alerts テーブルのデータ量が肥大化し続けています。
データの保持日数は「ヒストリ：5d」 「トレンド： 5d」です。 5dが経過しましたが、減りませんでした。
システム要件ですぐに削除できる方法を探しています。なおできれば直近のものは残しておきたいと思っています。
過去のフォーラムをいろいろ確認しましたが、下記(1) のURLでは削除したいデータを削除できるまで時間がかかるため、検討しない方向です。
(2) のURLにあるテーブルの内容をエクスポートして、テーブルを削除、再作成して、テーブルの内容をインポート
だと元に戻ってしまい、データ量は減らないように思われますが、どうでしょうか。
(1) http://www.zabbix.jp/node/943
(2) http://www.zabbix.jp/node/2946
------- -------
時間があるならZabbixを止めた状態で…
1)history_uintテーブルのみエクスポート
2)history_uintをdropテーブルしてcreateテーブル
3)history_uintテーブルのみインポート
------- -------
また、DBは事前にパーティショニングされていないので、パーティショニング機能を利用できない状況です。
DBを直接削除するのはよくないのは知っていますが、 下記の方法で問題ないでしょうか。
1.設定をエクスポート
2.Zabbix Server を停止
3.alerts テーブルについて、レコードを truncate
4.Zabbix Server を起動、設定をインポート
fripper - 投稿数: 495
１．肥大化しているテーブルの特定について
＞alerts テーブルのデータ量が肥大化
PostgreSQLをご利用とのことですが‥
MySQLでInnoDBを利用して、innodb_file_per_table を有効にしている場合と違い
単にファイルサイズとファイル名からではテーブル名まで特定できないかと思いますが
こちらについては
https://gist.github.com/Epictetus/3386858
この記事にあるような手順で、テーブルとデータファイルの関係性を確認された‥ということで
よろしいでしょうか？
２．alertsテーブルについて
アイテムとして収集した生の値が格納される history 系や、収集値の傾向を集約した trends 系のテーブルと違い
alerts は「通知の履歴」に相当するものです
アイテムから集めた値が historyへ格納され‥
トリガーによって条件判定された結果‥
障害・正常などの状態の変化が発生し、それがeventsとなったうえで‥
イベントによって、アクションが引き起こされて
アクションによって、メールの送信など、何らかの通知動作を行ったときに
「alertsにレコードが追加されます」
各アイテムにおけるヒストリ・トレンドの保存期間とは関係なく
WebUIで「管理→一般設定→データの保存期間」から
イベントとアラート の保存期間を短くすることで
過去の不要なデータを削除することが可能です
３．alertsテーブルのデータファイルサイズの解放・縮小について
1. の項目で挙げたような手順で、テーブルとデータファイルの対応関係を
把握されている、という前提で‥
以下の PostgreSQL 公式マニュアルにもあるとおり
https://www.postgresql.jp/document/9.2/html/routine-vacuuming.html
23.1.2. ディスク容量の復旧
delete されたレコードのデータ領域も、すぐに実データファイルから解放されて
ファイルサイズが小さくなるようなことはありません
標準のVACUUM処理を実施しても、ファイル末尾の空きブロックだけが解放されるのみです
そのため、VACUUM FULLが必要となるかと思います
Zabbixの場合はデータが追記されて古いレコードが削除されるような動作のため
標準のVACUUM処理を実施しても、ほとんど空きが増えることはないと思います‥
＃標準のVACUUM処理実行後には、「未使用」としてマークされたファイル内の領域が
＃再利用されるようになるので肥大化のペースは少しマシになるとは思います
少し古い記事ですが‥
http://www.1x1.jp/blog/2007/11/postgresql_no_vacuum_full.html
このようなアプローチで、別テーブルを作って、必要なデータだけを移植して‥と
いう方法を思いつくかと思いますが
Zabbix の alerts テーブルについては、非常に難しいと思います
先に挙げたように、alerts は events や actions など、他テーブルと密接に結びついており
一方でデータが削除されると、他方の関連するデータも削除されるような形で
「ON DELETE CASCADE;」なDBテーブル間の制約が指定されているので
片方のテーブルだけ無理やりに「別テーブルへ移植して、オリジナルを捨ててリネーム‥」と
いった方策が簡単には実施できないためです
負荷的には難しいのだろうとは思いますが、「保存期間」の設定変更で不要データを削除し
VACUUM FULLで、空き容量の確保をなさるアプローチが、一番簡単、安全かと思います
fripper - 投稿数: 495
補足‥というか、私自身が試してみたことがないので、なんとも言えないのですが‥
create tablespace 文を使って、ディスク上の別領域に新たなテーブルスペースを用意したうえで
alter table alerts SET TABLESPACE yyy のようにして、一時的に別テーブルスペースへデータファイルを再構築・移動させ
再度 alter table alerts SET TABLESPACE xxx のようにして、再度もとの領域へ戻す‥とすれば
ファイルレベルでのデータ構造が再構成されるので、空き領域が回収され、ファイルサイズが縮むかもしれません‥
hellozabbix - 投稿数: 21
コメントありがとうございます。
説明不足で申し訳ありません。
肥大化し続けているテーブルは alerts テーブル以外にも、events テーブルと history_log テーブルが肥大化しています。
現状 alerts テーブルが一番肥大化しており、約9千万レコードあります。
最終的に alerts テーブル、events テーブル、history_log テーブルとも不要なデータを削除したいと思っています。
「管理→一般設定→データの保存期間」からイベントとアラートの保存期間を短くするとありますが、
現状、一番古いデータは2018年4月のもので、2018年8月のデータを削除したい場合は、
「HousekeepingFrequency=1」のため、削除対象まで時間がかかりますよね。
alerts テーブルについて、直接レコードを truncate することが難しいで、
VACUUM FULL する必要があることについて、承知しました。
events テーブルと history_log テーブルも同様でしょうか。
補足について、確認してみます。
質問文にあった下記の内容ですが、いかがでしょうか。分かりましたら、お願い致します。
>(2) のURLにあるテーブルの内容をエクスポートして、テーブルを削除、再作成して、テーブルの内容をインポート
>だと元に戻ってしまい、データ量は減らないように思われますが、どうでしょうか。
>
>(2) http://www.zabbix.jp/node/2946
>------- -------
>時間があるならZabbixを止めた状態で…
>1)history_uintテーブルのみエクスポート
>2)history_uintをdropテーブルしてcreateテーブル
>3)history_uintテーブルのみインポート
>------- -------
fripper - 投稿数: 495
先の私のコメントにて触れていたのは、DB側がサーバ上に保存するDBファイルのサイズに
あたる観点での話を中心にしていました
単に「DELETE」しても、レコード数・データ量は減りますが、実ファイル上ではディスク領域は
解放されず、空き容量が増えることはないですよ‥といった観点でした
挙げておられた掲示板の記事において、
＞エクスポート→テーブル作り直し→インポート
といった手順が挙げられていた件については‥
MySQLの場合に、DELETE文の実行速度が非常に遅いというMySQL固有の事情があるのと
当該掲示板のスレ主様が、ディスク領域の解放を求めておられた、という点にありました
単にHouseKeeperの処理や、DELETEの実行にて過去データを削除しても
ディスク領域は解放されませんが、DELETE後にエクスポート→インポートすることで
DBファイルが再構成されるので、無効データが入っている領域の分、ファイルサイズを
小さくすることができます、という理屈です
これにあたる処理をPostgreSQLに自動でさせるのがVACUUM FULLなので
VACUUM FULLすれば、ファイルサイズを少しは小さくすることができる‥という観点で
先の私のコメント、という次第です
さて
＞肥大化し続けているテーブルは alerts テーブル以外にも、events テーブルと history_log テーブルが肥大化しています。
＞現状 alerts テーブルが一番肥大化しており、約9千万レコードあります。
＞最終的に alerts テーブル、events テーブル、history_log テーブルとも不要なデータを削除したいと思っています。
ファイルサイズというよりも、DB内のレコード数が多くなってしまっている点を憂慮されていたのですね
早とちりでした
＞「HousekeepingFrequency=1」のため、削除対象まで時間がかかりますよね
なんにせよ、大量にデータがたまってしまった状況においては、ZABBIXのHouseKeeper機能によって
DELETEされる量と処理速度にはある程度の限界はありますので、一気に大量の削除をしたいのであれば
DBを直接操作するほかないかと思います
単にレコード数を減らすのを主目的に、DBからDELETEを実行するぶんには
alerts / events / history_log 等、特に問題ないと思います
先の書き込みに挙げたように、「ON DELETE CASCADE;」なDBテーブル間の制約が指定されているので
ZABBIXが動作するうえで問題になってしまうような不整合にならず
他テーブルからも不要になったデータが併せて削除されるためです
hellozabbix - 投稿数: 21
コメントありがとうございます。
>MySQLの場合に、DELETE文の実行速度が非常に遅いというMySQL固有の事情があるのと
>当該掲示板のスレ主様が、ディスク領域の解放を求めておられた、という点にありました
>単にHouseKeeperの処理や、DELETEの実行にて過去データを削除しても
>ディスク領域は解放されませんが、DELETE後にエクスポート→インポートすることで
>DBファイルが再構成されるので、無効データが入っている領域の分、ファイルサイズを
>小さくすることができます、という理屈です
>
>これにあたる処理をPostgreSQLに自動でさせるのがVACUUM FULLなので
>VACUUM FULLすれば、ファイルサイズを少しは小さくすることができる‥という観点で
>先の私のコメント、という次第です
提示した URL に記載されている「エクスポート→テーブル作り直し→インポート」は、
DELETE 後に実行するということでしたか。よく確認できていませんでした。。
MySQL と PostgreSQL の違いについても勉強不足のままでの質問、すみませんでした。
PostgreSQL にて、DELETE しないままで「エクスポート→テーブル作り直し→インポート」
を実行しても何も変わらないですね。
これにあたる処理は VACUUM FULL について、承知しました。
>単にレコード数を減らすのを主目的に、DBからDELETEを実行するぶんには
>alerts / events / history_log 等、特に問題ないと思います
こちらについては、承知しました。
根本的な問題ですが、「HousekeepingFrequency=1」、「MaxHousekeeperDelete=0」
と設定していますが、肥大化ついて考えられる原因は、下記以外何が考えられますでしょうか。
・削除動作が失敗
・キューに溜まっている
fripper - 投稿数: 495
HouseKeeperの機能については、コメントに記載いただいている zabbix_server.confの設定項目
HousekeepingFrequency、MaxHousekeeperDelete がベースとなる設定で
現状記載いただいている設定値ですと、１時間に１度、HouseKeeper処理が実行され
１回あたりの削除量には上限なし、となっています
削除対象の項目および期間は、コメントにて触れた「管理→一般設定…」内の各項目で
詳細設定するので、こちらの設定値もご確認ください
HouseKeeperが動作する際、zabbix_server のログに、以下のような出力があるはずですので
出力内容の確認をお願いします
＞12553:20171220:001036.110 executing housekeeper
＞12553:20171220:001036.113 housekeeper [deleted 0 hist/trends, 0 items, 0 events, 0 sessions, 0 alarms, 0 audit items in 0.002681 sec, idle for 1 hour(s)]
hellozabbix - 投稿数: 21
コメントありがとうございます。
削除対象の項目および期間の設定とログを確認します。
下記について、再度確認させてください。
>DELETE後にエクスポート→インポートすることで
>DBファイルが再構成されるので、無効データが入っている領域の分、ファイルサイズを
>小さくすることができます
PostgreSQL においても、「DBを直接DELETE、テーブルの内容をエクスポートして、テーブルを削除、再作成して、テーブルの内容をインポート」
することでファイルサイズは小さくできますでしょうか。
なお、これにあたる処理は VACUUM FULL という認識でよろしいでしょうか。
fripper - 投稿数: 495
先のコメントでも触れたとおりです
ZABBIXのHouseKeeper処理は、不要となる古いDBレコードをDELETEするのみです
PostgreSQLだけでなくMySQLにも共通する点として、たとえDB上でDELETE操作が実行されても
実OS上のデータファイル内容としては、DELETEされたデータが格納されていた場所が「未使用」として
マークされるだけで、実OS上の実データファイルサイズが縮むような動作にはなっていない
という各DBソフトの仕様です‥
特にZABBIXの場合は、どんどん新しいレコードが追加される中で古いレコードがDELETEされるので
データファイル内は「歯抜け」のような形で「未使用」が挟まるような形になるので
ファイルサイズが縮むような動作になりにくい‥という、「使い方」に関する事情も、影響を及ぼしています
DBデータファイル上の「歯抜け」領域を再整頓するためのアプローチのひとつが
「DELETE後にエクスポート→インポート」です
DB的にみると、全削除したうえで有効なデータのみを再登録するわけですので
「歯抜け」部分が無くなる、すなわちOS上のファイルサイズが減る、ということです
「歯抜け」部分が殆ど無いような場合には、ほとんど効果はありません
PostgreSQLの場合に、この「歯抜けを再整頓」するためのアプローチが「VACUUM FULL」というワケです
VACUUMについてはこちらを参照してください
https://www.postgresql.jp/document/9.4/html/sql-vacuum.html
本コメントで「歯抜け」と表現しているものが、PostgreSQLマニュアルにおける「不要タプル」にあたると
感じて頂ければよいかと思います
hellozabbix - 投稿数: 21
コメントありがとうございます。
「DELETE後にエクスポート→インポート」のDELETEというのは、HouseKeeperの処理によって削除されることを指していますね。
てっきり「DB上でDELETE操作」だと思っていました。。
それとも「DELETE後にエクスポート→インポート」のDELETEは、HouseKeeperの処理とDB上でDELETE操作、両方を指していますでしょうか。
t-kubo - 投稿数: 11
当方PostgreSQL少しをかじっておりました...
マニュアルにも記載があるので繰り返しとなりますが
補足としてVACUUM FULLの使用上注意が2点ございます。
①実施中対象テーブルにACCESS EXCLUSIVE LOCKが掛かる
②OSのディスクに対象テーブルと同等の空きが必要となる
また、定期メンテナンスとして「VACUUM FULL」を取り入れることは公式にも推奨しておりません。
ZABBIX/PostgreSQLのversionにもよりますが、基本的にPostgreSQLは
VACUUM FULLを定期的に実施しなくとも、自動VACUUMする仕組みがあります。
ただし、特殊な場合においては手動VACUUMが必要な状態になってしまう場合がありますが、
基本「VACUUM FULL」は必要ない想定の仕様です。
実施する場合は上記注意点を考慮に入れZABBIXサーバ（もしくはそのDBサーバ）の
メンテ作業を行っていただければと存じます。
また、9千万程あるということでしたので、VACUUMM FULLにかかる時間もかなりの時間と推測されます。
停止を検討されていて少しでも時間を短縮したい場合は、pg_dump/truncate/pg_restoreの方が
時間が短縮される可能性があります。
ただし、こちらも結局はデータを退避→メンテ→データ戻しと変わらないので
OSの領域が必要なことには変わりありません。
遅いレスポンスですが少しでもお役にたてれば。
失礼いたしました。
hellozabbix - 投稿数: 21
t-kubo様、コメントありがとうございます。
大変参考になりました。ありがとうございます。
VACUUMM FULL に時間がかかることについて、承知しました。
「pg_dump/truncate/pg_restore」 について、下記の URL は「DBエクスポート→DB作り直し→DBインポート」
と記載されていますが、実際はやっていることは「pg_dump/truncate/pg_restore」と同じですよね。
DBエクスポートの方法の参考情報はありますでしょうか。
>(2) http://www.zabbix.jp/node/2946
>------- -------
>時間があるならZabbixを止めた状態で…
>1)history_uintテーブルのみエクスポート
>2)history_uintをdropテーブルしてcreateテーブル
>3)history_uintテーブルのみインポート
>------- -------
t-kubo - 投稿数: 11
hellozabbix様
細かい内容になってしまい申し訳ありませんが。
＞「pg_dump/truncate/pg_restore」 について、下記の URL は「DBエクスポート→DB作り直し→DBインポート」
＞と記載されていますが、実際はやっていることは「pg_dump/truncate/pg_restore」と同じですよね。
私の想定している作業では
①pg_dumpでは特定のテーブルのみdump取得
②特定テーブルのみtruncate（データのみ切り捨て）
③pg_restore -aでデータのみリストア
こうすることで、TABLEのCREATE、INDEXの貼り直しを短縮しています。
件数が多くなるにつれて特にINDEX作成に時間がかかるため、データの出し入れだけにしています。
trancateすることでVACUUMと同じ効果も得られます。（データ整然INSERT）
zabbixデータベースの全てのTABLEのDDLを網羅しているわけではないので申し訳ないのですが、
もし対象のTABLEにINDEXが無ければ、作業時間はほぼ同じになるかと思います。
また、pg_dumpオプションの-Fcを使用することでpostgresqlツールの圧縮が使用できますので
作業領域（dumpファイルサイズ）は圧縮できるかと思います。（1/5～1/10くらい？）
お使いのversionが9.2ということですので、9.2のマニュアルURLを記載します。
https://www.postgresql.jp/document/9.2/html/app-pgdump.html
https://www.postgresql.jp/document/9.2/html/app-pgrestore.html
fripper - 投稿数: 495
>hellozabbix 様
＞「DELETE後にエクスポート→インポート」のDELETEというのは、HouseKeeperの処理によって削除されることを指していますね。
＞てっきり「DB上でDELETE操作」だと思っていました。。
＞それとも「DELETE後にエクスポート→インポート」のDELETEは、HouseKeeperの処理とDB上でDELETE操作、両方を指していますでしょうか。
特に区別したつもりはありません
HouseKeeper処理が問題なく動作しているようであれば、そちらに任せてしまうのがベストだとは思いますが
対象が多すぎて時間が掛かる、等であれば、手動でDBから直接DELETEする方法もアリだと思います
>t-kubo 様
フォローありがとうございます
VACUUM処理時に排他ロックが掛かり、処理中は更新不能となることや、ディスク容量に関する注意事項等
重要な確認ポイントまでコメント内で網羅できていませんでした。ありがとうございます
hellozabbix - 投稿数: 21
fripper 様、t-kubo 様
コメントありがとうございます。
引き続き質問させてください。
>＞「HousekeepingFrequency=1」のため、削除対象まで時間がかかりますよね
>なんにせよ、大量にデータがたまってしまった状況においては、ZABBIXのHouseKeeper機能によって
>DELETEされる量と処理速度にはある程度の限界はありますので、一気に大量の削除をしたいのであれば
>DBを直接操作するほかないかと思います
>
>単にレコード数を減らすのを主目的に、DBからDELETEを実行するぶんには
>alerts / events / history_log 等、特に問題ないと思います
こちらの環境では、上記テーブルは外部キー制約が多く設定していて、
レコード数を手動でDBから削除(truncate)するなら、DBの全テーブルに対して削除
する必要があるという認識は合っていますか。
また、こちらの環境では、現状「MaxHousekeeperDelete=0」と設定しているにもかかわらず肥大化になっています。
設定値が0に設定したのは問題かもしれませんが、もしMaxHousekeeperDeleteを最大値1000000に変更して、
レコードを大量に削除は可能でしょうか。
(0と1000000のどっちに設定したほうがより大量に削除できるかを知りたいです。)
MaxHousekeeperDeleteの設定値(0,1000000)によって、削除対象レコードの範囲などの制限
はありますでしょうか。
fripper - 投稿数: 495
＞hellozabbix 様
以前のコメント
http://www.zabbix.jp/node/4513#comment-13344
にて、設定ファイルの設定内容のほか
WebUIからの保存期間に関する設定値、および
ログの出力状況をご確認ください、とお願いしております
ログに出力があるにもかかわらず、データが消えていないのか
ログに出力すらないのか‥
確認をお願いします
DB直接操作については‥
正直なところ、DB操作に関する話題を中心に
DB破損や、意図していないデータの損失等があっても責任を
取ることはできないため、「これをやっても大丈夫」とは断言できません
検証用環境でやってみて動作はどうだったのか、など、
ご自分でもお確かめになったうえで、本番環境に対して実施する等をお願いします
t-kubo - 投稿数: 11
>hellozabbix様
すみません、私のpg_restoreの参考情報が混乱させてしまいましたが。。。
fripper様の言われている通り、DB直接操作（主にデータに対する操作）は
フリーツールやスクリプトなどを含めて、DBA観点から見ても非推奨となります。
もし実装をご検討の場合は、検証環境などで十分な検討・検証・確認を行った上での
実装を強く推奨いたします。
当初のご質問内容と論点が外れてきていると思いますので、そもそもの問題として
肥大化してしまった特定のテーブルサイズを安全に縮小する方法でしたら
①該当のテーブルを特定
②zabbix-serverサービスの停止 ★
③VACUUM FULL スキーマ名.テーブル名; コマンドの実施
④zabbix-serverサービスの起動
の手順を推奨いたします。
時間はかかると思いますが、おそらく安全にメンテすることができます。