Re: general PG network slowness (possible cure) (repost) - Mailing list pgsql-performance

"Also sprach Tom Lane:"
> "Peter T. Breuer" <ptb@inv.it.uc3m.es> writes:
> > And definitely all those could be grouped if there are several to do.
>
> Except that in the situation you're describing, there's only a hundred
> or two bytes of response to each query, which means that only one send()
> will occur anyway.  (The flush call comes only when we are done
> responding to the current client query.)

It may still be useful. The kernel won't necessarily send data as you
push it down to the network protocols and driver. The driver may decide
to wait for more data to accumulate, particularly if it only has a
couple of hundred bytes to send so far and the medium is high speed and
medium latency (fast ethernet).  It'll get fed up with waiting for more
data eventually, and send it out, but it is essentially waiting on
_itself_ in that case, since the outgoing data is required at the other
side of the net as a response to be processed before another query can
be sent out, only then prompting the postmaster to start stuffing the
output buffer with more bytes.

Waiting on oneself is bad for us procrastinators. We need some whips.

I'll try and really force a send, and try some more tricks.
Unfortunately this isn't really quite the right level, so I have to use
some heuristics. Can you guarantee that internal_flush is not called
until (a) the internal buffer is full, OR (b) we have finished
composing a reply, AND (c) there is no other way to send out data?

I also need to find where we begin to compose a reply. That's somewhere
well before internal flush ever gets called. I want to block output at
that point.

As it is, I can either unblock just before internal_flush and block
after, or block just before internal_flush and unblock after (:-)
that's not quite as daft as it sounds, but needs care). Really I want
to do

   query received
   *block output
   process query
   create response
   *unblock output
   send

Instead, I have here to do

   query received
   process query
   create response
   *unblock output
   send
   *block output

Which is not quite the same. It may work though, because the driver
will know nothing is going to go out while it is listening for the next
query, and it will not have sent anything prematurely or kept it back
inopportunely.

> It's possible that for bulk data transmission situations we could
> optimize things a bit better --- in particular I've wondered whether we
> can reliably find out the MTU of the connection and use that as the
> output buffer size, instead of trusting the kernel to choose the best
> message boundaries --- but for the situation you're worried about

Don't bother, I think.  MTU is often effectively only notional these
days at the hardware level in many media.

OTOH, on my little net, MTU really does mean something because it's
10BT.

> there will be only one send.

True.

Peter