[pgpool-general: 7970] Re: Hang with Skunk because Flush not honoured

Tatsuo Ishii ishii at sraoss.co.jp
Mon Jan 10 19:28:21 JST 2022


>> I have implemented "simple" flush message tracking. It remebers there is a pending flush request when a flush message is sent from client. When a response message from backend is received, the response is flushed to frontend. The limitation is that it only flushes last 1 reply message.
>> 
>> What do you think?
> 
> 
> Thank you.
> 
> For the specific case of current Skunk, I believe this should indeed be enough of a fix, provided that the earlier mitigation measure (always flushing RowDescription) remains in use a well, and provided that you also flush the response buffer (if nonempty) immediately upon receiving Flush; otherwise it is possible that Pgpool-II receives the Flush message from the client after it has already received the ParseComplete etc. message from the server and it still gets stuck.
> 
> However, as you note yourself, this is not a complete solution, and other clients may still get stuck.

True.

> I can offer another heuristic that trades some more efficiency for some more partial reliability: when you receive Flush, you could start flushing *every* response message for that client (except possibly known-good responses that *must* be followed by flushable messages in the protocol, like in my initial suggestion) and keep doing it until you receive anything else from the same client.

Unfortunately this will not work in some cases. Consider following
scenario.

Parse A received.
Parse B received.
Flush received. So start to flush all response from backend.
Bind A received. So stop flushing.
Parse A complete received.
:
:

>>> But until that is implemented, shouldn’t SimpleForwardToFrontend rather default to flushing *all* responses and have a list of specific exceptions that are allowed to be buffered because the protocol guarantees that they are followed by flushable messages?
>> 
>> I think the change is too risky for major applications such as JDBC in
>> term of performance degration.
> 
> Surely this should not pose a risk for performance if you are careful to bufferize all messages that come in large batches? At the same time, this would reduce the risk of completely breaking another client by bufferizing something that should not be.
> Personally, I think performance is definitely important but correctness/reliability should be even more important. If Pgpool-II were to optimize for a common case at the expense of slowing down a rare case, that would be understandable, but currently it renders rare (supposedly) cases completely unusable.

After all, we need to find a compromise. For me it seems Skunk is
minority.

BTW, I don't understand why Skunk issuses flush after every message to
obtain response. Simple query protocol is already there for that
purpose.

> Does the PostgreSQL protocol even allow bufferizing anything other than extended-query responses? The Flush command is only allowed within the extended query flow, so does that not mean that all other responses *cannot* be delayed?

Don't know. I am sure "Comand complete" or "Error respose" are not
buffered but for other messages such as "Data row" I am not
sure. Anyway I think Pgpool-II should not have any precondition
regarding this if it's not clearly stated in the protocol.

> I apologize if this goes off topic or oversteps boundaries, but why does Pgpool-II have user-space bufferization of responses at all? Is it not enough to rely on the operating system's network stack to bufferize data and combine into packets as it sees fit?

In the early days of Pgpool-II it did not do write buffering and the
performance was terrible. Anyway, in my understanding Pgpool-II does
similar way for buffering as PostgreSQL. PostgreSQL has its own
user space buffer and accumulate data in it. If PostgreSQL needs to
send the data to socket, it explicitly calls sendto().

> It knows better than anyone what the network connection and buffer state is, and it has timeouts to ensure data is never stuck in a buffer indefinitely like is happening in Pgpool-II currently. If you want to hint to the OS that you have more data coming, you can use MSG_MORE/TCP_CORK. (For example, the Linux default with TCP_CORK is to wait at most 200 ms for the application to signal the end of data by unsetting TCP_CORK.)

"is happening in Pgpool-II currently"

What do you mean by this? I don't understand it anything relates to
the issue discussed in this thread.

> Moreover, could Pgpool-II not simply forward whole TCP packets it receives from the upstream server as TCP packets downstream? If that is not possible due to message parsing, then Pgpool-II could detect when the OS says there is no more data ready to be read from the upstream connection (i.e. that you would need to wait for more data packets to arrive) and *then* flush the downstream buffer. The upstream PostgreSQL server has already packed messages into TCP packets as it deemed efficient and permitted by the application-level protocol, so directly replaying flushes downstream would not lose any network efficiency compared to a direct connection between the client and the upstream server.

Problem is, Pgpool-II needs to handle multiple PostgreSQL
servers. Suppose Pgpool-II sends SET command to all PostgreSQL
servers. Then Pgpool-II must not flush the downstream buffer until all
backends reply with Command Complete or Error response.

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp



More information about the pgpool-general mailing list