I think I have found the solution. Yes, I now can get constantly high 
throughput with COPY-to-STDOUT, even if free -m only shows me 82 Mbytes (so no, 
this solution is not cleaning the cache). Always around 2 3/4 minutes.

I have compared all the /proc/sys/vm settings on my new machines and the old 
machines (which never had problems), and of course found some differences, some 
new settings etc. (of course, lot of changes can happen between 2.6.26 and 
2.6.32). And there was one, which stood out from the mass, because its name 
reminded me of some functions which I have always seen in the kernel stack 
while having congestion_wait:

- zone_reclaim_mode
(yes, in the kernel stack there was always also a call to "zone_reclaim").

Interestingly, on the old machine this was set to "0" and on the new machine - 
obviously per Ubuntu default - to "1" ... What these all means is shortly 
described here:

http://www.linuxinsight.com/proc_sys_vm_zone_reclaim_mode.html

Then I though, lets give it a try. Nd I set it to "0" on the new server too ... 
and voila, it is running at high speed in COPY-to-STDOUT. I can even switch 
back and forth between 0 and 1 and see, how congestion_wait comes back or 
disappears.

Now, someone with big kernel know-how could try to describe me in detail, what 
exactly could be at odds here. 

But for me it is now obvious, that I will put in my start up settings a change 
of "zone_reclaim_mode = 0".

And tomorrow I will see how my nightly backup runs with this setting.

Andras fabian




-----Ursprüngliche Nachricht-----
Von: Craig Ringer [mailto:cr...@postnewspapers.com.au] 
Gesendet: Dienstag, 13. Juli 2010 14:11
An: Andras Fabian
Cc: pgsql-general@postgresql.org
Betreff: Re: AW: AW: AW: AW: [GENERAL] PG_DUMP very slow because of STDOUT ??

On 13/07/10 18:57, Andras Fabian wrote:
> OK, so here I should - maybe - look around the sockets. Hmm. Well, in the 
> case of my experiments we are talking about Unix sockets, as I am only 
> connecting locally to the server (not real networking involved). Are there 
> any ideas, where such a Unix Socket could impose such extreme buffering ??? 
> And can/could I control its behavior at all?? (or would it be a thing, which 
> can only be controlled from C-Code ... which would fall back to PostgreSQL as 
> the initiator).

Confirm your issue is actually the socket between client and server first.

Are you sure the client isn't buffering the data? Or a pager like
"less"? Or terminal scrollback?

Run "top" and sort by memory use by pressing Shift-M. Run your query.
Look at the output and see if anything grows lots.

You really need to start looking at if this is memory consumption, and
what's using it. Don't get fixated on one answer until you've excluded
other possibilities.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to