実運用中のテーブルに対し、日次で reindex を実行したところ、時々、デッドロックが発生してしまいます。
どうやら、reindex と update の間でデッドロックが起きているようです。

エラーメッセージを見た感じでは、以下の状態でデッドロックになっているように読めます。
・update がテーブルのインデックスに対するロックを取得し、テーブルに対する RowExclusiveLock を取得しようとしている
・reindex がテーブルに対するロックを取得し、テーブルのインデックスに対する AccessExclusiveLock を取得しようとしている

※ 参考までに、update は select for update してから update しています。

Postgre のバグではないかと思うのですが、デッドロックを回避する方法はありますでしょうか?
(なるだけ、reindex を実行しているプロセスの方に手を入れることで対応できないかと思っています。例えば、reindexの前に事前にロックを取得する、とか・・・)

よろしくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (1件)

REINDEXをテーブルごとに行っているならば、以下の手順でデッドロックは回避できます。



BEGIN;
LOCK tbl IN ACCESS EXCLUSIVE MODE;
REINDEX TABLE tbl;
COMMIT;

ただ、REINDEX中は参照も更新も待たされるため、基本的には、オンライン処理と並行しては実行できません。代わりに CREATE INDEX CONCURRENTLY + 古いインデックスを DROP INDEX というような運用を行うことはよくあります。

参考URL:http://www.postgresql.jp/document/current/html/s …
    • good
    • 0
この回答へのお礼

ご回答、有り難うございます。

私も、検証環境で現象を再現した上で、同様の変更(私の場合は EXCLUSIVE MODE を使いました)を試みたところ、デッドロックは出なくなりました。

CREATE INDEX CONCURRENTLY については、別な問題があるようですので、採用していません。
対象のテーブルがバッチ制御用のテーブルで、一時的に参照がブロックされても直接オンライン処理に影響が出ないため、REINDEX を使っています。

なお、質問時に書き忘れたのですが、参考までに環境について記載します。

vl60_cmn=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)

OSは CentOS 5 を使っています。

お礼日時:2011/04/23 13:28

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QAccessShareLock はどの程度気にする必要がある?

PostgreSQL8.2とJavaを使ったWebアプリケーションを構築しています。
pgAdminでサーバー状態>ロック で参照してみたところ、AccessShareLockというのが大量に発生していました。
マニュアルを参照してみたところ、

>AccessShareLock を除き,トランザクション内で獲得されたすべての ロックモードは,そのトランザクション実行の間維持されます.

とあり、つまりAccessShareLockはトランザクションの間でも維持されないということだと思うのですが、どうも解放されてはいないようです。

これは通常の(つまり、更新しない)select文を発行したときに発生するロックだと思うのですが、いろいろ調べても、どうもこのAccessShareLockというのをどのくらい気にしたらいいのかがわかりません。
このロックが残っていると、どのようなときに困るでしょうか。
また、解放するためには通常のselectのあとでもcommit(またはrollback)をする必要があるということになるでしょうか。その際にはcommitとrollback、どちらがいいということはあるでしょうか。
ご存じのかたがいらっしゃいましたらご教示いただければと思います。よろしくお願いいたします。

PostgreSQL8.2とJavaを使ったWebアプリケーションを構築しています。
pgAdminでサーバー状態>ロック で参照してみたところ、AccessShareLockというのが大量に発生していました。
マニュアルを参照してみたところ、

>AccessShareLock を除き,トランザクション内で獲得されたすべての ロックモードは,そのトランザクション実行の間維持されます.

とあり、つまりAccessShareLockはトランザクションの間でも維持されないということだと思うのですが、どうも解放されてはいないようです。

これは通常...続きを読む

Aベストアンサー

回答番号: No.2 に対して訂正があります。

> 手元の psql で確認した限りでは、トランザクション内で SELECT を実
> 行してコミットしていない状態だと、SELECT の対象となっているテーブ
> ルの他にいくつかのシステムカタログに対しても Access Share Lock が
> 取得されたままでした。

と書きましたが、これは psql 内でテーブル名などの補完を行ったためで
した。

いずれにしてもトランザクション内で SELECT を実行してコミットせずに
放置しているのであれば、対象となるテーブルと、そのテーブルに定義さ
れたインデックスにも Access Share Lock が取得されたままとなるので、
トランザクションをコミットするようにしてみてください。

トランザクション内で更新していなければロールバックでも構いません。

QVACUUM ANALYZE の頻度について

表題の件について質問させてください。

環境は
PostgreSQL8.2.1
pgpoolにてDB2台で運用しております。

現在、とあるECサイトの運用などに携わっております。
1日平均が2~3千件の注文があり、注文情報を格納してるテーブルが150万件くらいになっています。
元々の作りが全てその注文テーブルに色々な情報を持たせてしまっており、更新頻度も結構高いです。

その状態で、現在は3時間に1回VACUUM ANALYZEをかけているのですが、その影響かはわからないのですが、セッション数(pg_stat_activityの件数)が正常運用時の3倍程度になる事が、ここ最近頻発しており、サイト全体の処理速度に影響しております。

1.VACUUM ANALYZEは、ロックがかからずにSELECT,UPDATE,INSERTなどが出来ると認識しておりますが、実行中に更新がかかっても問題ないという認識はあっておりますでしょうか?
ロックがかかったまま、セッションに残ってしまっているのは別の原因という事であっていますでしょうか?

2.VACUUM ANALYZEの頻度として、3時間に1回という頻度は多いのでしょうか?
やはり、pg_stat_activityで監視をしていると、VACUUM ANALYZEの処理に時間がかかって、セッション数が多くなり、処理速度にかなり影響をあたえているのでは無いかと言う気がしてなりません。

以上、2点について、何かご教授頂ければ幸いです。
宜しくお願い致します。

表題の件について質問させてください。

環境は
PostgreSQL8.2.1
pgpoolにてDB2台で運用しております。

現在、とあるECサイトの運用などに携わっております。
1日平均が2~3千件の注文があり、注文情報を格納してるテーブルが150万件くらいになっています。
元々の作りが全てその注文テーブルに色々な情報を持たせてしまっており、更新頻度も結構高いです。

その状態で、現在は3時間に1回VACUUM ANALYZEをかけているのですが、その影響かはわからないのですが、セッション数(pg_stat_activityの件数...続きを読む

Aベストアンサー

私の担当する顧客のPostgreSQL-DBも120万件以上のデータがあります。
1・お使いのバージョンであれば問題ないはずです。ただし、VACUUM時にSELECTなどのレスポンスは当然落ちます。

2・私の方針(笑)では、キー情報のUPDATEが多くない限り、VACUUM ANALYZEまたはFULLは、多くても1週間に1回しかVACUUMしません。
DBの構造にもよりますが、2~3千件のINSERTで3時間に1回のVACUUMは
多い気がしますが、3時間に1回VACUUMしないとレスポンスが悪いのでしょうか?
このあたりはマシンのスペックによってもかなり影響されるので、はっきりとした答えは出ないでしょう。

検索などのレスポンスが悪い場合は、ANALYZEも有効ですが、やはり
定期的にdump&restoreするのが一番いい方法なんですがね。
ノンストップ運用なら無理ですが・・・・

QInner join と Left joinの明確な違いは?

Inner join と Left joinの違いがよくわかりません。
教えてください。

Aベストアンサー

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
4               NULL
5               NULL
6               NULL
の6レコードが出力されますが、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 INNER JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3               3
の3レコードしか出力されません。

出てくる結果が違います。

テーブル1のフィールド1に、






が、

テーブル2のフィールド1に、






が入力されている場合、

SELECT [テーブル1].[フィールド1], [テーブル2].[フィールド1]
FROM テーブル1 LEFT JOIN テーブル2 ON [テーブル1].[フィールド1]=[テーブル2].[フィールド1];
では、結果は、
テーブル1.フィールド1 テーブル2.フィールド1
1               1
2               2
3           ...続きを読む

Q索引の再構築をすべき?

とあるプロジェクトに放り込まれた新人SEです。
当該PJでは、客先にDBを構築して納入しているのですが、最近動作が遅くなってきたというクレームを耳にしました。調べてみると、特定のテーブルのSelect処理に時間がかかっているようです。問題のテーブルは、毎日数十万件のデータがInsertされ、一定の保持期限より古いデータは毎日削除されます。
問題はここからで、索引は設定されていますが、それをメンテしているという話を聞かないのです。
ちなみに、日付に対応するパーティションがあり、表・索引ともにパーティションで管理されています。毎日深夜に一番古いパーティションを削除し、翌日分のパーティションを新規作成するデーモンが動きます。
OracleSilver挑戦中の頭で一生懸命考えたのですが、納入から時間が経っているので索引が凄いことになっているのでは・・・という考えと、毎日新しいパーティションが生成されてるから大丈夫なのでは・・・という根拠の無い考えが渦巻いています。
アドバイス、お願いできますでしょうか。

Aベストアンサー

>CBOでは実行計画の最適化には統計情報が使われるのですか・・・!
>ということは、定期的にANALYZEしないとボケた実行計画を選択してしまう、ということですか。
>(ちなみにCBO or RBOは少し調べて理解したのですが、どこかで明示されているんでしょうか?)

CBOは、原則として、統計情報が必要です。
統計情報にあるテーブル/インデックスの特性と実態が異なるようなケースでは、
統計情報が悪さをして、まともな実行計画が立案できません。
なので、テーブルの登録内容が大きく変わる場合は、統計情報の更新を行う必要が
あります。
ただし、統計情報が一度も採取されていないテーブルやインデックスについては、
CBOは、類推して、実行計画を算定します。
これが偶々良い方向で実行計画に作用する場合も無いとは限りません。
少なくとも、大嘘付きの統計情報を使うくらいなら、ない方がましかもしれません。


現在どのオプティマイザを使っているかは、初期化パラメータに
指定があるはずです。(デフォルトはCHOOSE)
特定のSQLのみルールベースにしたり、コストベースオプティマイザの種類を変更したり
というのは、オプティマイザヒントで行えるので、統計情報が信頼できず、
統計情報の更新タイミングが持てないような運用の場合、ヒントで切り抜けるのが
ラクな解決法だと思います。
少なくとも、他への影響がないので。

>CBOでは実行計画の最適化には統計情報が使われるのですか・・・!
>ということは、定期的にANALYZEしないとボケた実行計画を選択してしまう、ということですか。
>(ちなみにCBO or RBOは少し調べて理解したのですが、どこかで明示されているんでしょうか?)

CBOは、原則として、統計情報が必要です。
統計情報にあるテーブル/インデックスの特性と実態が異なるようなケースでは、
統計情報が悪さをして、まともな実行計画が立案できません。
なので、テーブルの登録内容が大きく変わる場合は、統計情...続きを読む

Qポートの80と443

こちらのサービス(https://secure.logmein.com/)を利用すると、インターネットを見られるサーバーのポートの80と443が空いていればルータやファイアウォールに特段の設定なく外部からサーバーを操作できるそうですが、逆にサーバーのポートの80や443を空けることには何か危険性があるのでしょうか。

Aベストアンサー

ポート80は一般的なHTTP、ポート443はHTTPSです。
この2つのポートがあいていなければインターネット接続(WEBブラウジング)は出来ません。
ですから、ほとんどのファイアウォールでこのポートは開いています。(インターネット接続を制限している社内LANでは当然閉じていますが)

ちなみに、よく使うポートとしてはFTPで20、21、SMTP(送信メール)で25、受信メールPOP3で110あたりです。セキュリティポリシー上、この辺は制限される事も多いですが、HTTP 80、HTTPS(暗号化用)443は通常閉じません。


危険性?
WEBプロトコルを使ってFTP的なファイル転送(WebDAV)やVPN等も出来るようになっています。当然そこにはある種の危険はつきものですが、WEBブラウジングに伴う危険と大きく変わりません。ウィルス等に感染していればこの2つのポートだけでも相当危険でしょうね。

参考まで。

QPostgreSQLが起動しない・・・

はじめまして、tanu_2です。
玄箱HGをDebian化し、いろいろと遊んでいます。

PostgreSQLをapt-getでインストールし、Webアプリを動かしていたのですが、昨日より突然、PostgreSQLが動かなくなってしまいました。
原因が分からず、サーバを再起動し、
# /etc/init.d/postgresql start

$ pg_ctl start
などを試してみたのですが、動きません。
念のため、初期化(initdb)も試みたのですが、これも失敗してしまいます。
/var/log/postgresql/postgres/logを覗いてみると、

FATAL: could not write lock file "/var/lib/postgres/data/postmaster.pid": No space

となっており、上記ディレクトリにpostmaster.pid書き込みできないよ、と言われているっぽかったので、パーミッションを変更したりして
みましたが、postgresql startにしろ、pg_ctl startにしろ、それぞれのコマンドで起動すると、強制的にパーミッションが「700」に変更されてしまうようで、結果は同じでした。

どなたか、解決策をご教示願えませんでしょうか?

よろしくお願いします。m(_ _)m

はじめまして、tanu_2です。
玄箱HGをDebian化し、いろいろと遊んでいます。

PostgreSQLをapt-getでインストールし、Webアプリを動かしていたのですが、昨日より突然、PostgreSQLが動かなくなってしまいました。
原因が分からず、サーバを再起動し、
# /etc/init.d/postgresql start

$ pg_ctl start
などを試してみたのですが、動きません。
念のため、初期化(initdb)も試みたのですが、これも失敗してしまいます。
/var/log/postgresql/postgres/logを覗いてみると、

FATAL: could not wri...続きを読む

Aベストアンサー

それ以外にも、以下のようなコマンドを使ってデータベースサーバプログラムの起動が出来ますよね。

postmaster -D /usr/local/pgsql/data

ただ、以下のURLのドキュメント内には、次のような記述があります。

「何を実行するにしても、サーバはPostgreSQLユーザアカウントで起動させなければなりません。 rootであってはいけませんし、他のユーザでもいけません。」

パッケージによるインストールであれば、自動でpostgresユーザが作られているはずだと思うけど。(Debianは使ったことないので、確証はなし。)

su - postgres

上記のコマンドでログインするなり、パスワードを忘れたのならroot権限にてpasswdコマンドから変更するなりした後、もう一度postmasterを起動してみよう!

ちなみに、パーミッション関係のエラーは、「Permission denied」ですよね。

参考URL:http://www.postgresql.jp/document/pg803doc/html/postmaster-start.html

それ以外にも、以下のようなコマンドを使ってデータベースサーバプログラムの起動が出来ますよね。

postmaster -D /usr/local/pgsql/data

ただ、以下のURLのドキュメント内には、次のような記述があります。

「何を実行するにしても、サーバはPostgreSQLユーザアカウントで起動させなければなりません。 rootであってはいけませんし、他のユーザでもいけません。」

パッケージによるインストールであれば、自動でpostgresユーザが作られているはずだと思うけど。(Debianは使ったことないので、確証は...続きを読む

Qエクセルでの指定文字 カウントについて

エクセルで並んだデータでの指定した名前だけの個数をカウントするにはどうすればいいのでしょうか?

山田 高橋 佐藤
高橋 梅田 赤田
 西 山田 梅田
佐藤 山田 梅田

名前が並んだデータで「高橋」という名前が何個あるのかをカウントしたいのですがどうすればいいのでしょうか?

Aベストアンサー

 データは入力されているセルの範囲を「A1:C4」とすれば、

=COUNTIF(A1:C4,"高橋")

QデットロックとFOR UPDATE

こんにちは。
PostgreSQLのマニュアルを見ますと。次のような文の組み合わせはデットロックになる可能性があるとされてます。

プロセス1
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス2
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;

#PostgreSQL8.1.5のマニュアルの12.3.3を参照しています。
#http://www.postgresql.jp/document/pg815doc/html/explicit-locking.html#LOCKING-DEADLOCKS

こういった場合の回避方法なのですが、
プロセス1
SELECT * FROM accounts WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス2
SELECT * FROM accounts WHERE acctnum=22222 AND acctnum = 11111 FOR UPDATE;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
とするのは大丈夫なのでしょうか?
本来なら、マニュアルにもある通り、UPDATEの順番を揃えるのがいいのでしょうが、実際にはWHEREの部分が変数で変化してしまうため、少々手間なのです。

実際のところ、このような場合、SELECT ~ FOR UPDATEで大丈夫なのでしょうか?それともテーブルレベルロックを用いるべきでしょうか?あるいはソートを用いるなどしてでも順番をそろえるべきなのでしょうか?

実際の処理はpl/pgsqlの関数の中で、EXECUTE文によって行っています。
なので、なるべく複雑な処理は避けたいところなのです。

できれば情報ソースなども示してご説明いただけるとありがたいです。よろしくお願いいます。

こんにちは。
PostgreSQLのマニュアルを見ますと。次のような文の組み合わせはデットロックになる可能性があるとされてます。

プロセス1
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
プロセス2
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;

#PostgreSQL8.1.5のマニュアルの12.3.3を参照し...続きを読む

Aベストアンサー

質問に対する直接的な話ではないのですが。。。

WHERE acctnum=11111 AND acctnum = 22222 FOR UPDATE



ANDでなくてOR

INにした方が、速い

QwiresharkでパケットモニタするとRetransmissionが多発しているという意味は?

現在、自分で作成したパケット送信クライアントプログラムをテストしており、3秒に1回のタイミングでインターネット上にあるサーバのグローバルipアドレスに対し、TCPパケットを発信させて受信するというテストを行っています。
しかし、3秒に一回データを送っているはずなのに、その間隔10秒とか20秒とか間隔が開いてしまう時があります。

wiresharkというパケットモニタソフトで送信側、受信側共にパケットモニタを行ってみたところ、”Retransmission”が多発しているということがわかりました。(tcp.analysis.retransmissionというフィルタ設定で検索)この現象はある時とない時があります。テストして10日ぐらい経つのですが、このパケットが確認されるのはお昼の12時頃と夕方の6時頃が多いのですが、このことからどのようなことが起こっていると考えられますか?

わかる方いらっしゃいましたらご教授よろしくお願いいたします。

Aベストアンサー

簡単に言うと「トラフィック過多によるパケットの再送が多発している」です。

噛み砕いて言えば「回線が混雑していて、送信したパケットが、他の誰かが送信したパケットと衝突(コリジョンが発生)してパケットが消えた。なので、もう一度、送り直した」と言う事。

>このパケットが確認されるのはお昼の12時頃と夕方の6時頃が多いのですが、このことからどのようなことが起こっていると考えられますか?

「お昼休み、終業時間の6時になると、みんな、メールをチェックしたり、個人的にインターネットを閲覧し、トラフィック過多が起き、回線が異常に混雑する」と言う事が起きていると考えられます。

解消するには以下の方法があります。
・「休み時間も、終業時間後も、プライベートでネットを使うな!」と言う「通達」を全社に出す
・社内LANを、トラフィック過多によるコリジョンが起きないよう高速で帯域のあるネットワークカード、LANハブ、ルーターに変える
・受信側と送信側を、社内LANから(電気的、アドレス的に)独立した別のLANにする

要は「混んでる時間帯なので仕方が無い」って事です。

簡単に言うと「トラフィック過多によるパケットの再送が多発している」です。

噛み砕いて言えば「回線が混雑していて、送信したパケットが、他の誰かが送信したパケットと衝突(コリジョンが発生)してパケットが消えた。なので、もう一度、送り直した」と言う事。

>このパケットが確認されるのはお昼の12時頃と夕方の6時頃が多いのですが、このことからどのようなことが起こっていると考えられますか?

「お昼休み、終業時間の6時になると、みんな、メールをチェックしたり、個人的にインターネットを...続きを読む

QSELECT文でのデッドロックに対しての対処方

先日よりデッドロックが発生するようになり、確認の為にトレースログを
抽出するように設定かけたのですが、UPDATE文とSELECT文がぶつかり
デッドロックが発生している事がわかりました。

しかし、UPDATE文にはトランザクションをかけていますが
SELECT文には特にロックかけていない為、どうして起こっているのか
わかっていません。

SELECT文を読んでいる時にUPDATE文によって
レコードに変更があった為、デッドロックが発生したのでしょうか?

SELECT文にはUNLOCKをつけた方がいいのでしょうか?



プログラムはDELPHI
DBはSQLSERVER2000になります。

お忙しい所申し訳ありません。
宜しくお願い致します。

Aベストアンサー

#6です。
気になったの書き込みます。

参照側で考えていましたが、更新側のロック範囲は適正でしょうか?
不必要に広い範囲に排他ロックを掛けていないでしょうか。

例えば更新が select xx from yy where zz>aa for browse のようになっていると広範囲にロックが確保され要注意です。
また、更新がバッチであれば、途中コミットして排他時間を短くするなりの対策が考えられます。
とにかく参照処理も更新処理も出来るだけ範囲を小さくされることをお勧めします。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報