質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

2回答

4626閲覧

PostgreSQLで特定のテーブルだけ処理が遅くなる

turkey

総合スコア15

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

0クリップ

投稿2018/10/24 03:11

編集2018/10/24 12:33

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のテーブルにインポートしており、最初にリストアしたときは文字化けでエラーになったので、文字化けしているデータを削除しています。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/10/24 03:27

インデックス付与状況も質問文中に記載してください。
turkey

2018/10/24 04:38

ありがとうございます。文中に追記しました。初投稿ですので修正編集の仕方違ったら指摘して下さい。
guest

回答2

0

ベストアンサー

先ずは、そのテーブルに対して明示的に、VACUUM FULL を試して下さい。
次にREINDEXを行って下さい。
最後に、そのテーブルにVACUUM ANALYZE
※どのタイミングで効果があるかをそれぞれのコマンドの後で確認して下さい。

結構古いバージョンですね。バージョンアップを検討された方がいいと思います。

投稿2018/10/24 03:27

編集2018/10/24 03:49
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2018/10/24 03:51 編集

はい。FULLは不要タプルの削除の為で、リストア後の状態と同様にするためのものです。 多分一番更新頻度の高いテーブルである事が予想されるので、ANALAYZEやREINDEXだけで効果が出るか疑わしかったので。
turkey

2018/10/24 04:51

sazi様、ありがとうございます。 稼働中のシステムですので、今夜にでもVACUUM FULL、REINDEX試してみます。 ほぼ同じ環境の検証機あるのですが、そちらでは再現できないので。 バージョンアップも検討してますが、原因究明しないと納得してくれないので、頑張ります。
sazi

2018/10/24 05:05 編集

発生している環境のバックアップをリストアしても、リストア自体がVACUUM FULLが不要な状況を作るので、発生している環境でしか確認できない事象です。
turkey

2018/10/24 12:38

教えていただいた内容を実施し、結果を追記しました。 sazi様のご指摘通り、VACUUM FULLでリストアと同じ結果になりました。VACUUM ANALYZE;でrowsが1になったのでこれが正常と言うことですね。 ただ、VACUUM FULL;は定期的に実施ない方がイイという記述があったので、明日はVACUUM FULL;なしを試してみようと思います。意味ないでしょうか?
sazi

2018/10/24 16:00

VACUUM FULLを実施した環境では意味が無いでしょう。 再現した時に試すのは、実施しておいた方がいいでしょうね。
turkey

2018/10/24 23:00

ですよね。1日で再現するので今夜もう一度確認します。
turkey

2018/10/25 11:14

再現したので、VACUUM ,REINDEX , VACUUM ANALYZEのみ(VACUUM FULLなし)を試してみましたが、改善されませんでした。VACUUM FULLが必要なようです。 ほぼSELECTしかされないテーブルがこのように遅くなるのは疑問ですが、不具合を解消させるメンテナンス方法が分かったのでクローズとさせていただきます。 ありがとうございました。
sazi

2018/10/25 12:42

>ほぼSELECTしかされないテーブルがこのように遅くなる 見落としてました。一日1件程度でパフォーマンスが劣化するというのは、Postgres以外のところに原因があるかもしれませんね。ストレージを最適化(断片化の解消)してみるとか、不良セクタが無いかなどのヘルスチェックなども行って見て下さい。
turkey

2018/10/25 23:09

m6u様 今夜、ALTER TABLE SET STATISTICSを試してみます。
turkey

2018/10/25 23:11

sazi様 特定のテーブルだけなのでPostgresだけを疑ってましたが、それ以外もチェックしてみます。
turkey

2018/10/26 10:58

今夜の時点での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は効果ありませんでした。 他のカラムに設定すべきないのでしょうか?
sazi

2018/10/26 11:47

ん?今気付きましたが、同じ条件でROWが変化するのはおかしいですね。 なんか情報が壊れていたりしているような気も。 一旦該当のテーブルをDROPして、VACUUM FULL (テーブル名を指定せず全てを圧縮)した後に、当該テーブルのリストアを行ってみるとどうでしょうか。 若しくは、DBを削除してリストアしてみるとか
turkey

2018/10/29 11:43

VACUUM FULLした後にリストアしてみました。 「試したこと【2】」の後、リストア前まではrows=1をキープしていたのですが、リストア後からrowsの値が増加する現象が再発してしまいました。 VACUUM FULL→rows=33になる・・・前回と同じ VACUUM ANALYZE→rows=1になる・・・前回と同じ 恐らくここからはrows=1を維持してくれると思いますが、costが増加し続けます。
sazi

2018/10/29 12:15

actual timeも変化していますか?また、actual timeの方のrowはどうでしょうか? actual timeが小さい値なら実害はないはずなので。
turkey

2018/10/30 00:00

EXPLAN ANALYZEはやってなかったのでactual timeが変化するか様子を見ることにします。 \timingで測定してたのですが、1日で3→350msまで増加します。
sazi

2018/10/30 00:21 編集

結果からだけ見ると、追加または更新が1件とありますけど、見た目上がその状況ということで、無条件の更新若しくは(delete+insert)が頻繁に行われている場合のように見えます。 8.3からは不要なタプルを確認できるのですが、8.1ではできないので、logでupdate等が発行されていないか確認することになるでしょう。
turkey

2018/10/30 00:35

Apacheのログで関連していると思われるプログラムへのアクセスを拾ったつもりだったのですが、Postgresのログから再調査してみることにします。
guest

0

テンプレ回答で申し訳ないけど、
性能に関するヒント
にある事項を理解した上で、
どういう対策をとっているのかを質問文中に記載してほしい。

投稿2018/10/24 03:29

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/10/24 04:12 編集

PostgreSQLに限らず、プログラミング全般において公式またはそれに準ずるドキュメントに辿り着けない方(やケース)って結構いそう(ありそう)なので、こういった回答好きです。
turkey

2018/10/24 04:46

m6u様、リンク情報ありがとうございます。 一部試してる部分もあるのですが、しっかり理解できていないままだったので、整理してから追記します。
turkey

2018/10/24 04:47

ppn様、お恥ずかしい<(_ _)>
退会済みユーザー

退会済みユーザー

2018/10/24 04:49

いえ、恥ずかしながら私もよくそういうことあります(読みづらいor理解しづらいので避けがちというのもありますが(^^; 一方で、平易に書いてあるものもあってものすごく有益だったりもしますが。
turkey

2018/10/24 12:49

EXPLAINとかANALYZEとか、意味を理解しないまま実施していましたが、実行結果をEXPLAINで確認することで動きがなんとなく理解できました。ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問