<div dir="ltr">Hi Tatsuo<div><br></div><div>Did you get a chance to look into this issue ? or the patch I have shared </div><div><br></div><div>Thanks</div><div>Usama </div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Tue, May 20, 2014 at 6:11 PM, Muhammad Usama <span dir="ltr"><<a href="mailto:m.usama@gmail.com" target="_blank">m.usama@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Hi Tatsuo<div><br></div><div>I am trying to fix the issue of backend hangs happens because of missing sync message pgpool while doing extended protocol. Although I believe this should ideally be fixed on PostgreSQL backend side but till the time I tried to get around this problem by sending the sync message instead of flush message from the do_query function when backend transaction state is IDLE.</div>
<div>Can you please take a look at the attached patch if it can serve the purpose.</div><div>I am getting the clean regression with the patch and this patch is also apparently fixing the backend state active issue. But I am not sure if this approach might break some other scenarios which are not covered by the regression.</div>
<div><br></div><div>Thanks</div><span class="HOEnZb"><font color="#888888"><div>Muhammad Usama</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, May 13, 2014 at 2:03 PM, Muhammad Usama <span dir="ltr"><<a href="mailto:m.usama@gmail.com" target="_blank">m.usama@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Thanks for looking into it. So this is the known issue, which needs to be fixed on PostgreSQL backend side. Or is there any way possible to get around this in production environment?</div>
<div><br></div>
<div>Thanks</div><span><font color="#888888"><div>Usama</div><div><br></div><div><br></div></font></span></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Mon, May 12, 2014 at 7:32 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Usama,<br>
<br>
It looks like the exact case for one of our FAQ items:<br>
<br>
<a href="http://www.pgpool.net/mediawiki/index.php/FAQ#When_I_check_pg_stat_activity_view.2C_I_see_a_query_like_.22SELECT_count.28.2A.29_FROM_pg_catalog.pg_class_AS_c_WHERE_c.oid_.3D_pgpool_regclass.28.27pgbench_accounts.27.29_AND_c.relpersistence_.3D_.27u.27.22_in_active_state_for_very_long_time._Why.3F" target="_blank">http://www.pgpool.net/mediawiki/index.php/FAQ#When_I_check_pg_stat_activity_view.2C_I_see_a_query_like_.22SELECT_count.28.2A.29_FROM_pg_catalog.pg_class_AS_c_WHERE_c.oid_.3D_pgpool_regclass.28.27pgbench_accounts.27.29_AND_c.relpersistence_.3D_.27u.27.22_in_active_state_for_very_long_time._Why.3F</a><br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
<br>
From: Muhammad Usama <<a href="mailto:m.usama@gmail.com" target="_blank">m.usama@gmail.com</a>><br>
Subject: Backend process hangs with pgpool<br>
Date: Mon, 12 May 2014 18:35:08 +0500<br>
Message-ID: <CAEJvTzUkCtQFsMH4yvudB=<a href="mailto:M3jVp_Qf5xrP8GoqCjQ2n-CKKOYw@mail.gmail.com" target="_blank">M3jVp_Qf5xrP8GoqCjQ2n-CKKOYw@mail.gmail.com</a>><br>
<div><div><br>
> Hi Tatsuo<br>
><br>
> I am looking at the problem faced by one of the customer. He has reported<br>
> that PostgreSQL backed is frequently getting stuck on the production<br>
> environment. And the analysis till this point suggests that pgpool-II's<br>
> internal query ("SELECT count(*) FROM pg_catalog.pg_class AS c WHERE<br>
> c.relname = 'foldershare' AND c.relpersistence = 'u' ") is responsible for<br>
> the backend to get stuck, by not issuing the necessary SYNC message when it<br>
> is expected by backend.<br>
> I am pasting all the information and discussion regarding the problem we<br>
> have so far.<br>
> Can you please suggest on what could be the problem and if the problem is<br>
> correctly identified what should be the proper way to fix it.<br>
> I am also attaching the not working patch (temp.patch) which tries to send<br>
> the sync message to backend from do_query() but doesn't worked out<br>
><br>
> Please find below for the discussion, and the information regarding this<br>
> problem<br>
> Log , configuration files and gdb trace is also attached with the mail<br>
><br>
> #query info<br>
> ============<br>
> enterprisedb@edb# select * from pg_stat_activity where pid = 19497;<br>
><br>
> -[ RECORD 1<br>
> ]----+---------------------------------------------------------------------------------------------------------<br>
> datid | 1110943<br>
> datname | cloud<br>
> pid | 19497<br>
> usesysid | 16584<br>
> usename | cloud<br>
> application_name |<br>
> client_addr | 10.27.28.241<br>
> client_hostname |<br>
> client_port | 2653<br>
> backend_start | 10-APR-14 08:36:26.454733 +09:00<br>
> xact_start | 10-APR-14 08:36:26.523032 +09:00<br>
> query_start | 10-APR-14 08:36:26.551021 +09:00<br>
> state_change | 10-APR-14 08:36:26.551022 +09:00<br>
> waiting | f<br>
> state | active<br>
> query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE<br>
> c.relname = 'foldershare' AND c.relpersistence = 'u'<br>
><br>
> # explain<br>
><br>
> enterprisedb@edb# explain analyze SELECT count(*) FROM pg_catalog.pg_class<br>
> AS c WHERE c.relname = 'foldershare' AND c.relpersistence = 'u';<br>
> QUERY PLAN<br>
> ---------------------------------------------------------------------------------------------------------------------------------------------<br>
> Aggregate (cost=4.27..4.28 rows=1 width=0) (actual time=0.011..0.011<br>
> rows=1 loops=1)<br>
><br>
> -> Index Scan using pg_class_relname_nsp_index on pg_class c<br>
> (cost=0.00..4.27 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)<br>
><br>
> Index Cond: (relname = 'foldershare'::name)<br>
><br>
> Filter: (relpersistence = 'u'::"char")<br>
><br>
> Total runtime: 0.050 ms<br>
><br>
> (5 rows)<br>
><br>
> #OS process<br>
><br>
> [09:56:21 enterprisedb@cloud-post-mdb /db/pgdba/logs]$ ps -ef | grep 19497<br>
><br>
> 534 19497 7260 0 08:36 ? 00:00:00 postgres: cloud cloud<br>
> 10.27.28.241[2653] SELECT<br>
><br>
><br>
> Internal discussion on the problem<br>
> =============================<br>
><br>
> Noah Misch Wrote:<br>
> ============<br>
><br>
> Looking at the customer's PPAS stack trace, it could be explained by PPAS<br>
> not<br>
> receiving a Sync message after a series of extended query protocol messages.<br>
> When this happens, pg_stat_activity and the "ps" line will give the<br>
> appearance<br>
> of an active query. An affected backend will appear to ignore<br>
> pg_cancel_backend(), but it will promptly respond to pg_terminate_backend().<br>
> Assuming that's a correct diagnosis, I see three potential explanations:<br>
><br>
> (1) A network problem between the pgpool server and the PPAS server cuts off<br>
> the transfer in the middle of an extended query protocol message sequence.<br>
> (Though I don't see that the customer ever stated it directly, the files<br>
> provided suggest pgpool is on a separate system.)<br>
><br>
> (2) pgpool, through some bug, omits a necessary Sync message.<br>
><br>
> (3) The client software omits the Sync message, and pgpool just passes on<br>
> that<br>
> omission. I think we can discount this possibility, because one of the<br>
> customer-reported hung queries is generated internally by pgpool:<br>
> SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = '%s' AND<br>
> c.relpersistence = 'u'<br>
><br>
> Either (2) or (1) could be it. I feel (2) is more likely, because it is a<br>
> stretch to believe that this would be the sole symptom of network trouble.<br>
> Tracking down the bug behind (2) could be difficult, alas.<br>
><br>
> I propose that support file a Redmine case against pgpool to investigate<br>
> explanation (2). Usama, if you would like more detail from the customer to<br>
> aid the pgpool team in such analysis, now is a good time to request it. Note<br>
> that the customer is offering remote access to their system.<br>
><br>
> One can exercise this hang situation on the PPAS backend side by having a<br>
> client program call PQexecParams() with a libpq patched like so:<br>
><br>
> -- a/src/interfaces/libpq/fe-exec.c<br>
> +++ b/src/interfaces/libpq/fe-exec.c<br>
> @ -1558,10 +1558,12 @ PQsendQueryGuts(PGconn *conn,<br>
> pqPutMsgEnd(conn) < 0)<br>
> goto sendFailed;<br>
><br>
> #if 0<br>
> /* construct the Sync message */<br>
> if (pqPutMsgStart('S', false, conn) < 0 ||<br>
> pqPutMsgEnd(conn) < 0)<br>
> goto sendFailed;<br>
> #endif<br>
><br>
> /* remember we are using extended query protocol */<br>
> conn->queryclass = PGQUERY_EXTENDED<br>
><br>
><br>
><br>
> Noah Misch Wrote:<br>
> ================<br>
> The PostgreSQL wire protocol has two messages, "Flush" and "Sync", for the<br>
> client to signal that it is waiting for the result of its previous requests.<br>
> Sync ends any implicit transaction; Flush does not. Sync causes the session<br>
> to show as "idle" in pg_stat_activity; Flush leaves it "active". pgpool<br>
> uses<br>
> Flush for certain internal queries, because it does not wish to disturb an<br>
> outstanding implicit transaction. The consequence for pg_stat_activity<br>
> output<br>
> is undesirable, but it is rightly considered more tolerable than closing a<br>
> user-initiated implicit transaction.<br>
><br>
> In the context of this support ticket, it's enough to say that the behavior<br>
> is<br>
> a known limitation of pg_stat_activity and does not indicate a malfunction.<br>
><br>
> As far as improving the situation, two options come to mind. The first<br>
> option<br>
> is to have pgpool track whether an implicit transaction could be open; when<br>
> pgpool sees a ReadyForQuery message from the server, any implicit<br>
> transaction<br>
> is definitely closed. For these internal metadata queries, it can then<br>
> issue<br>
> a Sync instead of a Flush if the session was idle (no implicit transaction)<br>
> when the internal query began. A second option is to change the server to<br>
> report a pg_stat_activity.state value other than "active" (perhaps "idle in<br>
> transaction" or a novel "idle in implicit transaction") when a query has<br>
> ended<br>
> with a Flush.<br>
><br>
><br>
> Thanks<br>
> Usama<br>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>