[pgsql-jp: 41469] Re: AutoCommitをfalseにするとレスポンスが遅くなる
kasaharatt ＠ nttdata.co.jp
kasaharatt ＠ nttdata.co.jp
2013年 7月 24日 (水) 18:08:43 JST
笠原です。
> 試しに、同じJavaプログラムで、バインド変数を使わずに、
> where句の条件にすべて値を直接指定して、SQLを実行すると、
> 実行計画にhash joinが採用され、早くなることがわかりました。
これを見ると PrepareThreshold が影響しているように見えます・・
再度で申し訳ないのですがst.setPrepareThreshold(0)で試行すると、
もしかして良い実行計画になったりしませんか？
PostgreSQL のJDBCは、PrepareThreshold の設定値(デフォルト 5)を以上の
処理を同じPrepararedStatementに対して行うと、DB側で実行計画を
キャッシュして、以降はその実行計画で処理が行われます。
つまり、PrepareThreshold の値未満の実行時は、毎回BIND値を考慮した
実行計画でSQLが処理されます。毎回プランニングされます。
それ以降はどんなBIND値が来ても正しい結果が返るように、やや汎用的な
実行計画が作成され、それを使いまわします。プランニングはスキップします。
バインド変数を使わずに値を直接指定したSQLで良い実行計画になるということは、
おそらく上記の振る舞いが影響しているように思えます。
> 推測ですが、autocommitをfalseにすると、実行計画でバインド変数の
> 中身が考慮されないと思われます。
Autocommitは特にバインド変数の考慮の有無には影響しないと思います。
コードも見てみましたが、特にバインド変数や実行計画に影響を及ぼしそうな
処理はやってなさげです・・
> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of
> pascalpascal ＠ snow.plala.or.jp
> Sent: Wednesday, July 24, 2013 5:30 PM
> To: PostgreSQL Japanese Mailing List
> Subject: [pgsql-jp: 41468] Re: AutoCommitをfalseにするとレスポンスが遅くなる
>
> 宮本です。
>
> 実行計画の内容は今、準備中ですが、ひとつ情報があります。
>
> 試しに、同じJavaプログラムで、バインド変数を使わずに、
> where句の条件にすべて値を直接指定して、SQLを実行すると、
> 実行計画にhash joinが採用され、早くなることがわかりました。
> 推測ですが、autocommitをfalseにすると、実行計画でバインド変数の
> 中身が考慮されないと思われます。
>
> これはそういうものなんでしょうか？
> autocommitをfalseにして、SQLでバインド変数を
> 使用するのはよくありそうですが。。
>
> 実行計画の内容は少々お待ちください。。
>
> ---- kasaharatt ＠ nttdata.co.jp さんは書きました:
> > 笠原です。
> >
> > うーん、何でしょうね・・
> >
> > ちなみに、早い(hash joinが使われる)実行計画と遅い(nested loop joinが使われる)実行計画の
> > プランそのものを見せていただけたりしますか？
> > # できればEXPLAIN ANALYZEの結果があると嬉しいです。
> > テーブル名や列名、コンディションはマスクされてても良いかと・・
> >
> > > -----Original Message-----
> > > From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of
> > > pascalpascal ＠ snow.plala.or.jp
> > > Sent: Wednesday, July 24, 2013 1:30 PM
> > > To: PostgreSQL Japanese Mailing List
> > > Subject: [pgsql-jp: 41466] Re: AutoCommitをfalseにするとレスポンスが遅くなる
> > >
> > > mlusさん、笠原さん
> > >
> > > 宮本です。
> > > ご連絡ありがとうございます。
> > >
> > > > Connection.setReadOnly(true) とか
> > > > createStatement(ResultSet.TYPE_FORWARD_ONLY,
> > > > ResultSet.CONCUR_READ_ONLY ,
> > > > ResultSet.HOLD_CURSORS_OVER_COMMIT)
> > > >
> > > > みたいな感じで、Statementの調整とかもありますが、関係ないですかね。
> > >
> > > Connection.setReadOnly(true)をしたり、Commitしてみましたが、結果は
> > > 変わりませんでした。（遅い）
> > >
> > > > もしそうであれば、ExecuteQuery前に
> > > > st.setPrepareThreshold(1)
> > > > を実行して、プランを比較してみていただけませんか？
> > >
> > > PrepareThresholdはデフォルト値のままでしたので、
> > > st.setPrepareThreshold(1)を実行し、SQLを３回実行して
> > > 比較してみました。
> > > が、残念ながら、３回ともレスポンス、実行計画は
> > > 変わりませんでした。（遅いまま）
> > >
> > > 1回目：182080 msec
> > > 2回目：182218 msec
> > > 3回目：182006 msec
> > >
> > > 追加情報なのですが、以下のように、Javaで実行するSQLに
> > > "explain analyze"をつけて実行すると、レスポンスは早くなり、
> > > explainの結果で出力される実行計画も"hash join"が使用されます。
> > > （期待通りの実行計画になります。）
> > >
> > > String sql = "explain analyze SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"
> > >
> > > よろしくお願いします。
> > >
> > > 以上
> > >
> > > ---- kasaharatt ＠ nttdata.co.jp さんは書きました:
> > > > 笠原と申します。
> > > >
> > > > >> Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > > > >
> > > > > PreparedStatementを使用しています。
> > > > > String sql = "SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"
> > > > > PreparedStatement st = conn.prepareStatement(sql);
> > > > PrepareThreshold はデフォルト値でしょうか？
> > > >
> > > > もしそうであれば、ExecuteQuery前に
> > > > st.setPrepareThreshold(1)
> > > > を実行して、プランを比較してみていただけませんか？
> > > > # できればst.setPrepareThreshold(1)設定後に3回程度連続でSQLを実行してみて欲しいです。
> > > >
> > > > もしかしたら PrepareThreshold が影響しているかもしれません・・
> > > > http://jdbc.postgresql.org/documentation/head/server-prepare.html
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of
> > > > > pascalpascal ＠ snow.plala.or.jp
> > > > > Sent: Wednesday, July 24, 2013 10:33 AM
> > > > > To: PostgreSQL Japanese Mailing List
> > > > > Subject: [pgsql-jp: 41463] Re: AutoCommitをfalseにするとレスポンスが遅くなる
> > > > >
> > > > > MauMauさん
> > > > >
> > > > > ご連絡ありがとうございます。
> > > > >
> > > > > > 問い合わせ計画は自動コミットが有効かどうかによって変わらないように思うのですが、次のことを教えてください。
> > > > >
> > > > > 私も変わらないと思っていましたので、今回の現象は驚いています。
> > > > >
> > > > > > Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > > > >
> > > > > PreparedStatementを使用しています。
> > > > >
> > > > > String sql = "SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"
> > > > > PreparedStatement st = conn.prepareStatement(sql);
> > > > >
> > > > > > Q2 setAutoCommit()で自動コミットを有効/無効にしたそれぞれの場合で、まったく同じSQL分を実行されているでしょ
> うか。
> > > > >
> > > > > SQL、where句の定数、バインド変数の値もまったく同じです。
> > > > >
> > > > > バインド変数は、例えば、
> > > > > st.setObject(1, "00060000");
> > > > > のような感じで指定しています。
> > > > >
> > > > > 簡単にいうと、同じプログラムで、
> > > > > conn.setAutoCommit(false) を入れるか、入れないかで、
> > > > > レスポンス、実行計画が違います。
> > > > >
> > > > > > Q3 それぞれの場合において、テーブル内のデータや統計情報に違いはなかったでしょうか。
> > > > >
> > > > > AutoCommit(false)ありのプログラムを実行したすぐ後に
> > > > > AutoCommit(false)なしのプログラムを実行しても、
> > > > > レスポンスや実行計画が違います。getAutoCommit()して
> > > > > 確認していますが、AutoCommit(false)なしの場合はtrueになっています。
> > > > > 実行計画はauto_explain.log_min_durationを設定して、Postgresqlログで
> > > > > 確認しています。
> > > > >
> > > > > アプリの仕様上、AutoCommitはfalseにしないといけないので、
> > > > > AutoCommitはfalseにしたままで、この問題を解決できるような
> > > > > 回避策がないかと思っています。
> > > > >
> > > > > よろしくお願いします。
> > > > >
> > > > > ---- MauMau <maumau307 ＠ gmail.com> さんは書きました:
> > > > > > MauMauといいます。
> > > > > >
> > > > > > 問い合わせ計画は自動コミットが有効かどうかによって変わらないように思うのですが、次のことを教えてください。
> > > > > >
> > > > > > Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > > > > >
> > > > > > Q2 setAutoCommit()で自動コミットを有効/無効にしたそれぞれの場合で、まったく同じSQL分を実行されているでしょ
> うか。
> > > > > > 同じとは、WHERE句などで指定する定数や、PreparedStatement#setXXX()で
> > > > > > 設定する値も完全に一致する、ということです。
> > > > > >
> > > > > > Q3 それぞれの場合において、テーブル内のデータや統計情報に違いはなかったでしょうか。
> > > > > >
> > > > > >
> > > > > > 以上です。
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: <pascalpascal ＠ snow.plala.or.jp>
> > > > > > To: <pgsql-jp ＠ ml.postgresql.jp>
> > > > > > Sent: Tuesday, July 23, 2013 5:29 PM
> > > > > > Subject: [pgsql-jp: 41453] AutoCommitをfalseにするとレスポンスが遅くなる
> > > > > >
> > > > > >
> > > > > > > 宮本と申します。
> > > > > > >
> > > > > > > 現在、PostgreSQL(9.1.6)を運用しております。
> > > > > > >
> > > > > > > jdbc経由でPostgresqlに接続して、selectを実行するJavaのプログラムで
> > > > > > > レスポンスが遅いという現象があり、調査したところ、
> > > > > > > AutoCommitをfalseにすると、遅いということがわかりました。
> > > > > > >
> > > > > > > AutoCommitをfalseにすると実行計画も違います。長いSQL文で結合が多く、
> > > > > > > データ件数も多いため、Hash joinを使った方が早いのですが、
> > > > > > > AutoCommitをfalseにすると、なぜか、Nested Loopが使われてしまいます。
> > > > > > >
> > > > > > > AutoCommit レスポンス 実行計画
> > > > > > > ---------------------------------------------
> > > > > > > false 遅い（約3分） Nested loop
> > > > > > > true 早い（約6秒） Hash join
> > > > > > >
> > > > > > > 環境
> > > > > > > OS: Redhat Enterprise Linux 5.7
> > > > > > > PostgreSQL: 9.1.6
> > > > > > > jdbc: postgresql-9.1-901.jdbc3.jar
> > > > > > > （jdbcをpostgresql-9.1-903.jdbc3.jarやpostgresql-9.2-1003.jdbc3.jarにしても同じでした。）
> > > > > > >
> > > > > > > これは仕様なのでしょうか。
> > > > > > > それともバグなのでしょうか。
> > > > > > > ネット上でいろいろ調べましたが、わかりませんでした。
> > > > > > > どなたか情報をお持ちの方は教えていただけないでしょうか。
> > > > > > > よろしくお願いします。
> > > > > > >
> > > > > >
> > > >
> >
pgsql-jp メーリングリストの案内