Q&A
インデックス付与状況も質問文中に記載してください。
PostgreSQLのDBで運用しているシステムで、特定のファイルのアクセスだけ極端に遅くなります。
select * from tbl where tbl_seri='1';
のような処理が、最初は3ms程度ですが、12時間運用すると400msほどになります。
(上記のtbl_seriはシリアル型のカラムです。それ以外のindexはありません)
遅くなるのは特定の1テーブルのみでそれ以外のテーブルの速度は1ms以下です。
3日ほど運用すると数分反応しなくなるので、現在は1日1回ダンプ→リストアでデータベースを再構築しています。
再構築直後から次第に遅くなっていきます。
原因と対策を教えていただけますでしょうか。
試したこと
VACUUM tbl; -->結果×
VACUUM; -->結果×
select * from tbl limit 1; -->1ms以下
select * from tbl limit 1; -->だんだん遅くなる
試したこと【2】
ご指摘いただいた内容について実施しました(結果：ほぼ解決)
以下、EXPLAIN SELECT * FROM tbl WHERE tbl_seri=1; の結果と処理時間
試す前の状態
Seq Scan on tbl (cost=0.00..71755.30 rows=3142 width=506) 350ms
↓
VACUUM tbl;実施後
Seq Scan on tbl (cost=0.00..64434.24 rows=33 width=506) 320ms
↓
VACUUM FULL;実施後
Seq Scan on tbl (cost=0.00..310.24 rows=33 width=506) 2.9ms
↓
REINDEX TABLE tbl;実施後
Seq Scan on tbl (cost=0.00..310.24 rows=33 width=506) 2.8ms
↓
VACUUM ANALYZE;実施後
Seq Scan on tbl (cost=0.00..535.24 rows=1 width=264) 3.3ms
明日、VACUUM FULL;なしを試してみようと思います。
補足情報
PostgreSQL 8.1.23
システムへのアクセス
1日 5000件くらい
うち該当テーブルへのアクセス
select 1日500件くらい
insert,update 1日1件くらい
該当テーブルはWindowsのCSVデータをLinuxのPostgreSQLのEUCのテーブルにインポートしており、最初にリストアしたときは文字化けでエラーになったので、文字化けしているデータを削除しています。
回答2件
0
ベストアンサー
投稿2018/10/24 03:27
編集2018/10/24 03:49
総合スコア25419
横から失礼、ANALYZEかVACUUM ANALYZEで良くないですか？ https://www.postgresql.jp/document/8.1/html/populate.html#POPULATE-ANALYZE の受け売りですけど。
こっちのドキュメント https://www.postgresql.jp/document/8.1/html/maintenance.html で VACUUM FULL 言及ありました。こっちの方が良さそうに思えました。
はい。FULLは不要タプルの削除の為で、リストア後の状態と同様にするためのものです。
多分一番更新頻度の高いテーブルである事が予想されるので、ANALAYZEやREINDEXだけで効果が出るか疑わしかったので。
sazi様、ありがとうございます。
稼働中のシステムですので、今夜にでもVACUUM FULL、REINDEX試してみます。
ほぼ同じ環境の検証機あるのですが、そちらでは再現できないので。
バージョンアップも検討してますが、原因究明しないと納得してくれないので、頑張ります。
発生している環境のバックアップをリストアしても、リストア自体がVACUUM FULLが不要な状況を作るので、発生している環境でしか確認できない事象です。
教えていただいた内容を実施し、結果を追記しました。
sazi様のご指摘通り、VACUUM FULLでリストアと同じ結果になりました。VACUUM ANALYZE;でrowsが1になったのでこれが正常と言うことですね。
ただ、VACUUM FULL;は定期的に実施ない方がイイという記述があったので、明日はVACUUM FULL;なしを試してみようと思います。意味ないでしょうか？
VACUUM FULLを実施した環境では意味が無いでしょう。
再現した時に試すのは、実施しておいた方がいいでしょうね。
ですよね。1日で再現するので今夜もう一度確認します。
再現したので、VACUUM ,REINDEX , VACUUM ANALYZEのみ(VACUUM FULLなし)を試してみましたが、改善されませんでした。VACUUM FULLが必要なようです。
ほぼSELECTしかされないテーブルがこのように遅くなるのは疑問ですが、不具合を解消させるメンテナンス方法が分かったのでクローズとさせていただきます。
ありがとうございました。
https://www.postgresql.jp/document/8.1/html/planner-stats.html でいう ALTER TABLE SET STATISTICSコマンド で改善できないかなぁ。（ただの思いつき。）
>ほぼSELECTしかされないテーブルがこのように遅くなる
見落としてました。一日１件程度でパフォーマンスが劣化するというのは、Postgres以外のところに原因があるかもしれませんね。ストレージを最適化（断片化の解消）してみるとか、不良セクタが無いかなどのヘルスチェックなども行って見て下さい。
m6u様
今夜、ALTER TABLE SET STATISTICSを試してみます。
sazi様
特定のテーブルだけなのでPostgresだけを疑ってましたが、それ以外もチェックしてみます。
今夜の時点でのEXPLAIN SELECT * FROM tbl WHERE tbl_seri=1; の結果と処理時間
Seq Scan on tbl (cost=0.00..47093.24 rows=1 width=264)
※先日教えいただいた処理でrows=1はキープできてます。
どのカラムを変更すればいいか分からなかったのでとりあえずserial型のtbl_seriを変更
ALTER TABLE tbl ALTER COLUMN tbl_seri SET STATISTICS 100;
↓
ANALYZEやVACUUM ANALYZEは効果ありませんでした。
他のカラムに設定すべきないのでしょうか？
ん？今気付きましたが、同じ条件でROWが変化するのはおかしいですね。
なんか情報が壊れていたりしているような気も。
一旦該当のテーブルをDROPして、VACUUM FULL (テーブル名を指定せず全てを圧縮）した後に、当該テーブルのリストアを行ってみるとどうでしょうか。
若しくは、DBを削除してリストアしてみるとか
VACUUM FULLした後にリストアしてみました。
「試したこと【2】」の後、リストア前まではrows=1をキープしていたのですが、リストア後からrowsの値が増加する現象が再発してしまいました。
VACUUM FULL→rows=33になる・・・前回と同じ
VACUUM ANALYZE→rows=1になる・・・前回と同じ
恐らくここからはrows=1を維持してくれると思いますが、costが増加し続けます。
actual timeも変化していますか？また、actual timeの方のrowはどうでしょうか？
actual timeが小さい値なら実害はないはずなので。
EXPLAN ANALYZEはやってなかったのでactual timeが変化するか様子を見ることにします。
\timingで測定してたのですが、1日で3→350msまで増加します。
結果からだけ見ると、追加または更新が1件とありますけど、見た目上がその状況ということで、無条件の更新若しくは（delete+insert)が頻繁に行われている場合のように見えます。
8.3からは不要なタプルを確認できるのですが、8.1ではできないので、logでupdate等が発行されていないか確認することになるでしょう。
プログラムの変更で発行回数を減らすことができない場合は、auto vacumeの頻度を変えれば改善する可能性はあるとは思います。
以下を参考にして下さい。
https://www.postgresql.jp/document/8.1/html/maintenance.html#AUTOVACUUM
Apacheのログで関連していると思われるプログラムへのアクセスを拾ったつもりだったのですが、Postgresのログから再調査してみることにします。
0
テンプレ回答で申し訳ないけど、
性能に関するヒント
にある事項を理解した上で、
どういう対策をとっているのかを質問文中に記載してほしい。
投稿2018/10/24 03:29
退会済みユーザー
総合スコア0
PostgreSQLに限らず、プログラミング全般において公式またはそれに準ずるドキュメントに辿り着けない方（やケース）って結構いそう（ありそう）なので、こういった回答好きです。
m6u様、リンク情報ありがとうございます。
一部試してる部分もあるのですが、しっかり理解できていないままだったので、整理してから追記します。
ppn様、お恥ずかしい<(_ _)>
いえ、恥ずかしながら私もよくそういうことあります（読みづらいor理解しづらいので避けがちというのもありますが(^^; 一方で、平易に書いてあるものもあってものすごく有益だったりもしますが。
EXPLAINとかANALYZEとか、意味を理解しないまま実施していましたが、実行結果をEXPLAINで確認することで動きがなんとなく理解できました。ありがとうございます。
あなたの回答
tips
プレビュー
