Re: PG_DUMP very slow because of STDOUT ??

Hi guys,

well definitely thanks for these first ideas. They at least point in a direction which I havn't considered before (to be honest, I didn't even think about associating STDOUT with some socket stuff).
With my experiments, I think I have essentially tried both socket types. Once I normally connected via pgAdmin to my server (this would definitely qualify as TCP/IP I think :-), and then also did locally on the machine via "psql" (see the example in my original post). So, in that case, it must have been unix sockets. And both cases behaved similarly when issuing the COPY TO STDOUT (i.e.: sloooowly).
And for difference between COPY TO FILE and and COPY TO STDOUT ... when I did the same experiment on old machine/kernel, there was almost no difference between execution time (almost identical, and fast as expected) - that test was done from "psql"/command line ... so unix-sockets.
So, of course, there might be something with our new kernel. But then many others could have this "problem" too! Because we are using the stock kernel of the latest Ubuntu 10.04 LTS 64-bit server (nothing self compiled, just as is,and as it comes from Canonical after you downloaded it). Maybe, someone having a machine with Ubuntu 10.04 (server!) and a PostgreSQL on it, could try to reproduce it too.
By the way, how could I experiment with the scheduler? I remember, that there were some magic trick via /proc file system or something like that to "tweak" it on the fly ....

Andras Fabian

________________________________________
Von: Craig Ringer [craig(at)postnewspapers(dot)com(dot)au]
Gesendet: Freitag, 9. Juli 2010 22:11
An: Tom Lane
Cc: Andras Fabian; pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] PG_DUMP very slow because of STDOUT ??

On 09/07/10 22:08, Tom Lane wrote:
> Andras Fabian <Fabian(at)atrada(dot)net> writes:
>>> Something weird about the network stack on the new machine, maybe.
>>> Have you compared the transfer speeds for Unix-socket and TCP connections?
>
>> Hmm, no ... but how do Unix-socket / TCP connections relate to STDOUT here (sorry, maybe this is obvious to some, but not to me at the moment)?
>
> Well, COPY TO STDOUT really means "send the data across the network
> connection to the client". It sounds to me like your problem is slow
> data transfer between the database backend and pg_dump (or psql).
> Not sure what could be causing it, unless perhaps you've got a kernel
> packet filter that's performing an unreasonable amount of processing
> per packet.

... and that should be showing up as high "system" CPU time, or it seems
to on my machines with lots of tcp/ip traffic and heavy netfilter rules.
If they're using unix sockets packet filtering shouldn't come into play
anyway.

Andrais: Are you using UNIX sockets or TCP/IP to connect to PostgreSQL?
If you specify any host to connect to, even "localhost", you're using
TCP/IP. If you don't specify a host, you're using unix sockets.

Whichever you are doing, try the other one and see if performance is
different.

Idea: One notable difference between COPY to file and 'copy to stdout"
to a receiving process over the network is that the latter involves the
interaction of two processes. When a kernel change is involved and
you're seeing low performance with lots of apparent idleness, that
immediately makes me think "process scheduler".

Of course, that might be completely off-track, but I've seen weird
performance issues caused by scheduler changes before.

--
Craig Ringer