Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
