pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql -A (unaligned format) eats too much memory
6+ messages / 3 participants
[nested] [flat]
* psql -A (unaligned format) eats too much memory
@ 2006-06-04 22:01 Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 6+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-04 22:01 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql -A (unaligned format) eats too much memory
@ 2006-06-04 22:32 Zoltan Boszormenyi <zboszor@dunaweb.hu>
parent: Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 6+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-04 22:32 UTC (permalink / raw)
To: ; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much memory
@ 2006-06-05 15:22 Jim C. Nasby <jnasby@pervasive.com>
parent: Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-06-05 15:22 UTC (permalink / raw)
To: Zoltan Boszormenyi <zboszor@dunaweb.hu>; +Cc: pgsql-hackers@postgresql.org
Moving to -hackers
On Mon, Jun 05, 2006 at 12:32:38AM +0200, Zoltan Boszormenyi wrote:
> >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...
I've been able to verify this on 8.1.4; psql -A -t -c 'SELECT * FROM
largetable' > /dev/null results in psql consuming vast quantities of
memory. Why is this? ISTM this is a bug...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much memory
@ 2006-06-05 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-06-05 15:27 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> I've been able to verify this on 8.1.4; psql -A -t -c 'SELECT * FROM
> largetable' > /dev/null results in psql consuming vast quantities of
> memory. Why is this?
Is it different without the -A?
I'm reading this as just another uninformed complaint about libpq's
habit of buffering the whole query result. It's possible that there's
a memory leak in the -A path specifically, but nothing said so far
provided any evidence for that.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much memory
@ 2006-06-05 15:53 Jim C. Nasby <jnasby@pervasive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jim C. Nasby @ 2006-06-05 15:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > I've been able to verify this on 8.1.4; psql -A -t -c 'SELECT * FROM
> > largetable' > /dev/null results in psql consuming vast quantities of
> > memory. Why is this?
>
> Is it different without the -A?
Nope.
> I'm reading this as just another uninformed complaint about libpq's
> habit of buffering the whole query result. It's possible that there's
> a memory leak in the -A path specifically, but nothing said so far
> provided any evidence for that.
Certainly seems like it. It seems like it would be good to allow for
libpq not to buffer, since there's cases where it's not needed...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much memory
@ 2006-06-05 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-06-05 16:00 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
>> I'm reading this as just another uninformed complaint about libpq's
>> habit of buffering the whole query result. It's possible that there's
>> a memory leak in the -A path specifically, but nothing said so far
>> provided any evidence for that.
> Certainly seems like it. It seems like it would be good to allow for
> libpq not to buffer, since there's cases where it's not needed...
See past discussions. The problem is that libpq's API says that when it
hands you back the completed query result, the command is complete and
guaranteed not to fail later. A streaming interface could not make that
guarantee, so it's not a transparent substitution.
I wouldn't have any strong objection to providing a separate API that
operates in a streaming fashion, but defining it is something no one's
bothered to do yet. In practice, if you have to code to a variant API,
it's not that much more trouble to use a cursor...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-06-05 16:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-04 22:01 psql -A (unaligned format) eats too much memory Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-04 22:32 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-05 15:22 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-05 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-05 15:53 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-05 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox