Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > No, it isn't. Please add a TODO item about it: > * Prevent long-lived temp tables from causing frozen-Xid advancement > starvation > >> > > Jeff Amiel wrote: > >> Can somebody explain this one to me? because of our auditing technique, > >> we > >> have many LONG lived temp tables.(one per pooled connection)...so as > >> long as the pool isn't disturbed, these temp tables can exist for a long > >> time (weeksmonths?) > > > Hmm. The problem is that the system can't advance the frozen Xid for a > > database when there are temp tables that live for long periods of time. > > Autovacuum can't vacuum those tables; if the app vacuums them itself > > then there's no problem, but you can only vacuum them in the same > > session that creates it. > > I'm not convinced there's a huge problem here. Surely Jeff's app is > going to either vacuum or truncate those temp tables occasionally; > otherwise they'll bloat to the point of uselessness. Either action > will fix the problem. > > The real issue is that the app has to remember to do that. Perhaps > a better TODO item would be > * Find a way to autovacuum temp tables > though I admit I have no clue how to do that without giving up most > of the performance advantages of temp tables. TODO updated: * Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera <[EMAIL PROTECTED]> writes: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation >> > Jeff Amiel wrote: >> Can somebody explain this one to me? because of our auditing technique, we >> have many LONG lived temp tables.(one per pooled connection)...so as >> long as the pool isn't disturbed, these temp tables can exist for a long >> time (weeksmonths?) > Hmm. The problem is that the system can't advance the frozen Xid for a > database when there are temp tables that live for long periods of time. > Autovacuum can't vacuum those tables; if the app vacuums them itself > then there's no problem, but you can only vacuum them in the same > session that creates it. I'm not convinced there's a huge problem here. Surely Jeff's app is going to either vacuum or truncate those temp tables occasionally; otherwise they'll bloat to the point of uselessness. Either action will fix the problem. The real issue is that the app has to remember to do that. Perhaps a better TODO item would be * Find a way to autovacuum temp tables though I admit I have no clue how to do that without giving up most of the performance advantages of temp tables. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Andrew Hammond wrote: > On 9/13/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > Alvaro Herrera wrote: > > > Bruce Momjian wrote: > > > > > > > > Is this item closed? > > > > > > No, it isn't. Please add a TODO item about it: > > > * Prevent long-lived temp tables from causing frozen-Xid advancement > > >starvation > > > > Sorry, I don't understand this. Can you give me more text? Thanks. > > > > s/long-lived/orphaned/ ? And possibly this means better orphan detection and > removal. Added: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
On 9/13/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Alvaro Herrera wrote: > > Bruce Momjian wrote: > > > > > > Is this item closed? > > > > No, it isn't. Please add a TODO item about it: > > * Prevent long-lived temp tables from causing frozen-Xid advancement > >starvation > > Sorry, I don't understand this. Can you give me more text? Thanks. > s/long-lived/orphaned/ ? And possibly this means better orphan detection and removal. Andrew
