[pgpool-general: 1594] Re: (3.1.3 hatsuiboshi) sleep on query cancel

[Pawel Veselov]

On Wed, Apr 10, 2013 at 5:18 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Bottom line is you cannot avoid the sleep.
>

In my case, it's irrelevant because I only use pgpool for failover
(master_slave_mode). I would think that in failover configuration, the
sleep does not help with anything. As I said, it's causing very serious
problems with my J2EE server, so I had to cut it out of the source code, at
least for now.

Also, though WebLogic isn't behaving nicely (I don't think it should be
canceling all the statements), it's within its rights to do what it's
doing, I then have to turn around and really blame this on postgres JDBC
driver, who issues unconditional cancelQuery() for any statement that is
associated with the connection, whether any statement is running or not.
This is where I would have fixed it, but it's a lot more complicated (than
remove sleep(1) from pgpool source, sorry :) )


> Background:
> Query canceling works as following(this is required by PostgreSQL
> protocol, not pgpool implementation).


> 1) pgpool receives cancel request packet from client
> 2) check if it's a valid one (by checking valid cancel key)
> 3) open new connection to a backend
> 4) send cacnel packet to the bacjend
> 5) sleep 1 sec to ensure that other node alreay starts to execute the
>    query to be canceld. query cancel won't be accepted by PostgreSQL
>    if the query has not started yet
>

I wonder how is it known that 1 second is enough to know that another node
started to execute the query, i.e. what's so magical about 1 second.

6) if other backend remains goto #3
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> > Hi.
> >
> > Is there any reason for pgpool to sleep when processing query
> cancellation?
> > Is it possible to unconfigure this sleep (set it to 0)?
> >
> > I see explicit attempt to sleep on processing cancellation request here
> > (strace):
> >
> > # received connection from client, recognized it's cancellation request
> > 24626      0.070666 accept(5, {sa_family=AF_INET, sin_port=htons(64885),
> > sin_addr=inet_addr("172.18.10.42")}, [16]) = 6
> > 24626      0.000066 read(6, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 1024)
> =
> > 16
> > # connecting to postgres and relaying
> > 24626      0.000063 connect(7, {sa_family=AF_INET, sin_port=htons(5432),
> > sin_addr=inet_addr("172.18.10.230")}, 16) = 0
> > 24626      0.000060 write(7, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 16)
> = 16
> > # closing connection to postgres
> > 24626      0.000186 shutdown(7, 1 /* send */) = 0
> > 24626      0.000105 close(7)            = 0
> > # sleeping for 1 sec (!)
> > 24626      0.000061 nanosleep({1, 0}, 0x7fff93d85090) = 0
> > # closing connection to client
> > 24626      1.000350 shutdown(6, 1 /* send */) = 0
> > 24626      0.000290 close(6)            = 0
> >
> > It's a HUGE problem for me, as I'm using pgpool with WebLogic server.
> > WebLogic cancels all cached statements when
> > - rolling back (an XA?) transaction
> > - closing the pool
> >
> > The closure is done on a single thread, so for large pools it takes long
> > minutes to close, and often a shared lock is held (as there is apparently
> > no expectation that cancellation would take a long amount of time)
> Postgres
> > JDBC driver also doesn't know better and keeps calling cancelQuery() on
> the
> > same connection, even if multiple statements are being closed.
> >
> > Thank you,
> >   Pawel.
>



-- 
With best of best regards
Pawel S. Veselov
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130410/18ad1161/attachment-0001.html>