Re: High memory usage / performance issue ( temp tables ? )

>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 .

Are you using the same temp tables for the whole batch or do you generate a few 100K
of them ? The latter may cause performance degradation as temp objects are written to the catalog,
resulting in some bloating. It would be interesting to see the output of vacuum full verbose,
at least on pg_class and pg_attributes (beware that this may lock on busy systems).
I avoid temp tables wherever I can. It is often possible with CTEs or unlogged tables.


Another possibility is that the table statistics are getting outdated as your batch processes.
If you start with empty target tables, postgres may use full scans and keep with this
as long as the tables are not analyzed. It may help to call analyze  explicitly on the touched tables
a few times during your process. Here a look at the monitoring statistics may give some clue.
(http://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql)

regards,
Marc Mamin

>
>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