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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Jeff Amiel wrote: Bruce Momjian wrote: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation 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. The problem with a frozen Xid (datfrozenxid) that doesn't advance is of Xid-wraparound nature. The system eventually shuts itself down to prevent data loss, so if those temp tables live a really long life, you could be subject to that. (The immediate symptom is that pg_clog segments do not get recycled, which is not serious because it's just wasted disk space, and it's not a lot). (previous thread about our use of temp tables and autovacuum/xid issues) http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php Sorry, I'm offline ATM and can't check those. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Cuando miro a alguien, más me atrae cómo cambia que quién es (J. Binoche) ---(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
Bruce Momjian wrote: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation 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?) (previous thread about our use of temp tables and autovacuum/xid issues) http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
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 Thanks. Added to TODO. -- 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
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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
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. -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Is this item closed? --- Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Yeah, we had better investigate some way to clean them up. It was never obvious before that it mattered to get rid of orphan temp tables, but I guess it does. Would it be enough to delete the tuple from pg_class? No, you need a full DROP. I don't see that that's harder than removing only the pg_class tuple --- the problem in either case is to be sure it's OK. In particular, how to avoid a race condition against an incoming backend that adopts that BackendId? Worst-case, you could be deleting a temp table he just made. Oh, I was just thinking in way for Bruce to get out of his current situation. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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 -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
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 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera [EMAIL PROTECTED] writes: Well, it certainly seems like this shouldn't be happening. Maybe the table belonged to a session that crashed, but the pg_class entry has not been cleaned up -- possibly because that backend has not connected to that particular database. Hm --- a crash would mean that the temp table would remain until some other session (a) connected to the same database (b) using the same BackendId (sinval slot number), and (c) decided to create some temp tables of its own. So indeed it's not implausible that the table could hang around for a long time, especially if you were unlucky enough that the original creator had been using a very high BackendId slot. (Which pg_temp schema is this table attached to, anyway?) Maybe autovacuum itself could do something about cleaning up this kind of stuff on sight (-- dropping temp tables belonging to sessions that crash). I'm not sure though. Yeah, we had better investigate some way to clean them up. It was never obvious before that it mattered to get rid of orphan temp tables, but I guess it does. Another possibility is just to ignore temp tables while computing datvacuumxid. A temp table that survives for 2G transactions is going to be trouble, but I'm not sure there's anything we can usefully do about it anyway --- certainly autovacuum has no power to fix it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Well, it certainly seems like this shouldn't be happening. Maybe the table belonged to a session that crashed, but the pg_class entry has not been cleaned up -- possibly because that backend has not connected to that particular database. Hm --- a crash would mean that the temp table would remain until some other session (a) connected to the same database (b) using the same BackendId (sinval slot number), and (c) decided to create some temp tables of its own. So indeed it's not implausible that the table could hang around for a long time, especially if you were unlucky enough that the original creator had been using a very high BackendId slot. (Which pg_temp schema is this table attached to, anyway?) It's pg_temp_63. Backend 63 is running in another database. It seems perfectly possible that a backend connects to database A, creates a temp table, crashes, then connects to database B after restart and then keeps running there forever :-( Maybe autovacuum itself could do something about cleaning up this kind of stuff on sight (-- dropping temp tables belonging to sessions that crash). I'm not sure though. Yeah, we had better investigate some way to clean them up. It was never obvious before that it mattered to get rid of orphan temp tables, but I guess it does. Would it be enough to delete the tuple from pg_class? I guess that will leave behind the tuples in pg_attribute etc, but I don't see another way to drop it ... Maybe UPDATE to move it to the local temp schema and then DROP it? Or maybe it works to do DROP TABLE pg_temp_63.temp2394 as superuser ...? I haven't tried. Another possibility is just to ignore temp tables while computing datvacuumxid. A temp table that survives for 2G transactions is going to be trouble, but I'm not sure there's anything we can usefully do about it anyway --- certainly autovacuum has no power to fix it. Yes, I was going to suggest that, though it doesn't seem right. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Tiene valor aquel que admite que es un cobarde (Fernandel) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Yeah, we had better investigate some way to clean them up. It was never obvious before that it mattered to get rid of orphan temp tables, but I guess it does. Would it be enough to delete the tuple from pg_class? No, you need a full DROP. I don't see that that's harder than removing only the pg_class tuple --- the problem in either case is to be sure it's OK. In particular, how to avoid a race condition against an incoming backend that adopts that BackendId? Worst-case, you could be deleting a temp table he just made. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Yeah, we had better investigate some way to clean them up. It was never obvious before that it mattered to get rid of orphan temp tables, but I guess it does. Would it be enough to delete the tuple from pg_class? No, you need a full DROP. I don't see that that's harder than removing only the pg_class tuple --- the problem in either case is to be sure it's OK. In particular, how to avoid a race condition against an incoming backend that adopts that BackendId? Worst-case, you could be deleting a temp table he just made. Oh, I was just thinking in way for Bruce to get out of his current situation. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera [EMAIL PROTECTED] writes: Oh, I was just thinking in way for Bruce to get out of his current situation. Oh, for that a manual drop table as superuser should work fine. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
