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

[Tatsuo Ishii]

> On Wed, Apr 10, 2013 at 6:08 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > 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.
>>
> 
> I'm not sure I understand how it's logically relevant...
> Statements are running on master only, cancellation should only be sent to
> master as well.
> If a statement is cancelled, it wouldn't affect streaming replication, at
> least, I don't see how.
> 
> cancel_request() looks through all pools, and finds which pool, and which
> backend has
> the active session with the key being cancelled. After such is found, it
> iterates through the
> backends again, only re-sending the cancellation request to VALID_BACKEND.
> Which, in case of master-slave mode would be just the master, correct?

No. Even in case of master-slave, pgpool will connect to all
VALID_BACKEND.  But if load balance is disabled, all queries are sent
to master only, thus sending cancel request to master only would makes
sense.

I will dig into this more...
--
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
>>
> 
> 
> 
> -- 
> With best of best regards
> Pawel S. Veselov