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

Tatsuo Ishii ishii at postgresql.org
Thu Apr 11 10:08:14 JST 2013


> 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 :) )

So are you saying that even if you remove sleep(1), everything goes
well in master/slave mode(with streaming replication)?
If so, it's rather a good news:-) I thought you cnannot do it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

>> 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


More information about the pgpool-general mailing list