pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Req. for some help with temp tables 2+ messages / 2 participants [nested] [flat]
* Req. for some help with temp tables @ 2017-01-23 16:19 Tharmarajah, Sam <sambavan.tharmarajah@usask.ca> 0 siblings, 1 reply; 2+ messages in thread From: Tharmarajah, Sam @ 2017-01-23 16:19 UTC (permalink / raw) To: pgsql-admin Hi We have a vendor delivered application running on postgres 9.1. It is creating temp tables per session. Currently the application group is reporting the that they are seeing a very slow response on the application in production and in the logs we see tons and tons of autovaccum of oraphan temp table cleanups. So we have the following questions. 1. How can we mitigate this performance degradation from the database side. The most interesting part is the application vendor doesn't seem to response in a timely manner. Also this application is connecting through glassfish. 2. What can we tune in the database to get this performance improve? 3. Every time the auto vacuum runs it pegs 1 CPU to 100%. Please bear with these novice questions as I am very new to postgres... Thanx, Sam ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Req. for some help with temp tables @ 2017-02-14 14:28 Feike Steenbergen <feikesteenbergen@gmail.com> parent: Tharmarajah, Sam <sambavan.tharmarajah@usask.ca> 0 siblings, 0 replies; 2+ messages in thread From: Feike Steenbergen @ 2017-02-14 14:28 UTC (permalink / raw) To: Tharmarajah, Sam <sambavan.tharmarajah@usask.ca>; +Cc: pgsql-admin First of all, 9.1 is EOL. That, and the fact that there are huge performance gains in every release since then, it is advisable to upgrade. On 23 January 2017 at 17:19, Tharmarajah, Sam <sambavan.tharmarajah@usask.ca > wrote: > [...] seeing a very slow response on the application in production and in the > logs we see tons and tons of autovaccum of oraphan temp table cleanups Could you share some of these log entries? > 1. How can we mitigate this performance degradation from the database > side. 2. What can we tune in the database to get this performance > improve? 3. Every time the auto vacuum runs it pegs 1 CPU to 100%. Are you using actual temporary tables or our you using permanent tables temporarily? I'm asking, because according to the docs[1]: Temporary tables cannot be accessed by autovacuum. So that probably means you're using permanent tables temporarily. - how are these tables created? - when are they dropped? - how are they emptied? (DELETE or TRUNCATE?) [1] https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#AUTOVACUUM regards, Feike ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-02-14 14:28 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-01-23 16:19 Req. for some help with temp tables Tharmarajah, Sam <sambavan.tharmarajah@usask.ca> 2017-02-14 14:28 ` Feike Steenbergen <feikesteenbergen@gmail.com>
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