Re: PG_DUMP very slow because of STDOUT ?? - Mailing list pgsql-general

Hi guys,

well definitely thanks for these first ideas. They at least point in a direction which I havn't considered before (to
behonest, 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
theexample in my original post). So, in that case, it must have been unix sockets. And both cases behaved similarly
whenissuing 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
testwas 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!
Becausewe are using the stock kernel of the latest Ubuntu 10.04 LTS 64-bit server (nothing self compiled, just as
is,andas it comes from Canonical after you downloaded it). Maybe, someone having a machine with Ubuntu 10.04 (server!)
anda 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
systemor something like that to "tweak" it on the fly .... 

Andras Fabian

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

On 09/07/10 22:08, Tom Lane wrote:
> Andras Fabian <Fabian@atrada.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,
butnot 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