Skip site navigation (1) Skip section navigation (2)

psql -A (unaligned format) eats too much memory

From: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>
To: pgsql-performance(at)postgresql(dot)org
Subject: psql -A (unaligned format) eats too much memory
Date: 2006-06-04 22:01:24
Message-ID: 44835834.2020309@dunaweb.hu (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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.

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


Responses

pgsql-performance by date

Next:From: Zoltan BoszormenyiDate: 2006-06-04 22:32:38
Subject: Re: psql -A (unaligned format) eats too much memory
Previous:From: Ahmad FajarDate: 2006-06-04 12:00:32
Subject: Re: Bulk loading/merging

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-06-04 22:09:06
Subject: Re: More thoughts about planner's cost estimates
Previous:From: Andrew DunstanDate: 2006-06-04 21:52:45
Subject: Re: 'CVS-Unknown' buildfarm failures?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group