agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: gmb <gmbouwer@gmail.com>
To: pgsql-sql@postgresql.org
Subject: High memory usage / performance issue ( temp tables ? )
Date: Sun, 17 Aug 2014 00:35:46 -0700 (PDT)
Message-ID: <1408260946918-5815108.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Hi
I feel that there is some issue with temp tables and memory usage. Have seen
a couple of posts online regarding this, but most issues have since been
resolved or have been proved as problem unrelated to Postgres.
I'd appreciate if someone can assist me in this.
My situation:
Version "PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit" ) running on linux.
I have a plpgsql function which takes an XML string payload as input and
does some processing using that payload data. This function makes use of
multiple other postgres functions (sql, plpgsql, plpython3u).
Main purpose of the process is to populate multiple tables, basically doing
inserts on financial transactional tables which in turn triggers to other
tables triggering to other tables again, etc. , etc.
The process follows a method where the XML payload data is inserted into
multiple TEMP tables ( can be up to 10 tables for each function call ) .
These are created using ON COMMIT DROP .
My problem:
We have a process using the above function to process batches of XML payload
files.
After running a batch of 50000 xml files , I definitely see a deterioration
in performance. At first glance, I wrote this down to some sort of memory
problem.
$top
Cpu(s): 25.1%us, 0.1%sy, 0.0%ni, 74.8%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 14371048k total, 14195464k used, 175584k free, 424788k buffers
Swap: 6288380k total, 11972k used, 6276408k free, 12114744k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
20596 postgres 20 0 1688m 1.3g 1.2g S 0 9.1 0:24.38 postgres
29164 postgres 20 0 1721m 966m 933m R 100 6.9 4:30.18 postgres
28165 postgres 20 0 1782m 630m 568m S 0 4.5 0:23.19 postgres
28155 postgres 20 0 1780m 460m 370m S 0 3.3 0:43.76 postgres
(I have to admit, I'm not a linux expert -- I have some guys at the office
who I can ask for help tomorrow).
Now accoring to this post (
http://www.postgresql.org/message-id/165E6919-697C-4C50-9EEE-38728AC6D982@tcdi.com
) , this can be a display issue in top , rather than a real memory problem.
I get the idea that the method of creating temp tables is probably causing
the problem here . At the very least , it could be cause of performance
issues.
Has anyone else been in this same situation with regards to temp tables in
Postgres
I'd appreciate if there are any comments / advice / reprimands.
Regards
gmb
--
View this message in context: http://postgresql.1045698.n5.nabble.com/High-memory-usage-performance-issue-temp-tables-tp5815108.ht...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (10+ messages) latest in thread
Message-ID: <1408260946918-5815108.post@n5.nabble.com>
Permalink: ../1408260946918-5815108.post@n5.nabble.com/
Also on: postgresql.org/message-id/1408260946918-5815108.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gmbouwer@gmail.com
Subject: Re: High memory usage / performance issue ( temp tables ? )
In-Reply-To: <1408260946918-5815108.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox