Re: psql -A (unaligned format) eats too much memory

From: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: psql -A (unaligned format) eats too much memory
Date: 2006-06-04 22:32:38
Message-ID: 44835F86.5040008@dunaweb.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi,

answering to myself. :-)

Zoltan Boszormenyi írta:
> Hi,
>
> I just noticed that psql's unformatted output uses too much
> memory. Is it normal? It seems that psql draws all records
> of a query off the server before it displays or writes the output.
> I would expect this only with formatted output.
>
> Problem is, I have an export that produces 500'000+ records
> which changes frequently. Several (20+) sites run this query
> nightly with different parameters and download it. The SELECTs
> that run in psql -A -t -c '...' may overlap and the query that runs
> in less than 1.5 minutes if it's the only one at the time may take
> 3+ hours if ten such queries overlap. The time is mostly spent
> in swapping, all psql processes take up 300+ MB, so the 1GB
> server is brought to its knees quickly, peek swap usage is 1.8 GB.
> I watched the progress in top and the postmaster processes finished
> their work in about half an hour (that would still be acceptable)
> then the psql processes started eating up memory as they read
> the records.
>
> PostgreSQL 8.1.4 was used on RHEL3.
>
> Is there a way to convince psql to use less memory in unformatted
> mode? I know COPY will be able to use arbitrary SELECTs
> but until then I am still stuck with redirecting psql's output.

The answer it to use SELECT INTO TEMP and then COPY.
Psql will use much less memory that way. But still...

Best regards,
Zoltán Böszörményi

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2006-06-05 03:55:29 Re: Re [HACKERS]: Still not happy with psql's multiline history behavior
Previous Message Tom Lane 2006-06-04 22:09:06 Re: More thoughts about planner's cost estimates

Browse pgsql-performance by date

  From Date Subject
Next Message Jim C. Nasby 2006-06-05 14:35:29 Re: How can I make this query faster (resend)
Previous Message Zoltan Boszormenyi 2006-06-04 22:01:24 psql -A (unaligned format) eats too much memory