I had another observation before your last mail: I have compared the /proc/pid/sched Stats of a normal and a slow machine. And there were two counters that really sticked out: - se.sleep_start - se.block_start On a normal machine, both counter remaind at 0 all the time while doing COPY-to-STDOUT. On a "slow" machine, those counters - but always only one of them - got some really big numbers while the process was stuck in congestion_wait. Andras Fabian -----Ursprüngliche Nachricht----- Von: Craig Ringer [mailto:craig@xxxxxxxxxxxxxxxxxxxxx] Gesendet: Dienstag, 13. Juli 2010 11:01 An: Andras Fabian Cc: Tom Lane; pgsql-general@xxxxxxxxxxxxxx Betreff: Re: AW: PG_DUMP very slow because of STDOUT ?? On 13/07/2010 4:05 PM, Andras Fabian wrote: > Craig, thanks for that PS tip (you think, you have used PS for such a long time, but it still has some new tricks available). > So, obviously, for some reason we are waiting too much for a backind_device ... which ever it is at the moment. Because, as I just wrote to Scott Marlowe, the disk system is almost idling (have seen disk utilization on the drive to which I write below 1%). A quick search suggests that most calls into congestion_wait are in the memory management subsystem, and are involved in situations where the system is struggling for memory. However, that includes memory consumed by I/O buffers, writeback for I/O, etc, so it'd also be consistent with very slow I/O causing write throttling as the system tried to write already buffered data to disk. Most other calls are in file system drivers. At this point I'd be taking a closer look at "vmstat 1" and "iostat 1" output, plus "top", to see if any interesting clues about system-wide issues turned up. I'd also be trying to perform each step of the problem operation in isolation as much as possible, so as to see if I could find out what particular part was causing the slowdown. Comparing "\copy" to "COPY ... TO STDOUT", invoking "COPY ... TO STDOUT" with a standalone backend writing output to an on disk file and to /dev/null, etc. > So, the question seems to be, why and where this "idling happens". You can potentially find out more by getting a trace of the kernel function call stack for the backend process. The kernel call stack of a process at any given time can be obtained by reading /proc/$pid/stack . This will tell you not only what call it's waiting in in the kernel, but what function(s) called it, and even the code offset within each function. > Just as a test, I have tried a very simple piping example (which should go trough STDOUT too ... or am I wrong). > - "dd if=/dev/zero of=file_10GB bs=1024 count=10M" created a 10 GB test file on source drive (sdb) > - "time cat file_10GB> /var/tmp/test2.dump" ... pipe the file to target drive (/var/tmp is on sda) Isn't your issue suspected to be with network transfers over unix sockets and/or tcp/ip, rather than with pipes? Try "socat" if you want to test unix socket performance and/or tcp/ip socket performance. It's an amazing sysadmin/network swiss army knife. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
