[GENERAL] Autovacuum not vacuuming pg_largeobject
I'm a bit concerned about the autovacuum daemon. Today I runned a vacuum full during a normal maintainance task and I noticed that the size of pg_largeobject decreased from 14GB to 4GB... Every night we have a procedure that deletes large object no more referenced using the vacuumlo program. This program issues delete commands to the pg_largeobject table in order to erase the rows of the los no more referenced. Autovacuum is up and running... but now i'm thinking it doesn't examine system tables such as pg_largeobject... Am I wrong? Thank you, Denis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Denis Gasparin [EMAIL PROTECTED] writes: I'm a bit concerned about the autovacuum daemon. Today I runned a vacuum full during a normal maintainance task and I noticed that the size of pg_largeobject decreased from 14GB to 4GB... Every night we have a procedure that deletes large object no more referenced using the vacuumlo program. This program issues delete commands to the pg_largeobject table in order to erase the rows of the los no more referenced. Autovacuum is up and running... but now i'm thinking it doesn't examine system tables such as pg_largeobject... Am I wrong? Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. I tried to issue the vacuum command on one of my database and i got the following log messages: LOG: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter I suspect I must increase max_fsm_relations. The value of max_fsm_pages is 2 (the default value). I suspect I must change this to a higher value... but how high should this value be? Thank you in advance, Denis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum not vacuuming pg_largeobject
Denis Gasparin wrote: Yeah, you're wrong. The difference is that plain vacuum does not try very hard to reduce the length of a table file --- it just frees up space within the file for reuse. vacuum full will actually move things from the end of the file to free space nearer the head of the file, so that it can shorten the file. What I suspect the above observations really prove is you don't have max_fsm_pages set high enough, and so pg_largeobject was bloating because the free space was being forgotten instead of reused. I tried to issue the vacuum command on one of my database and i got the following log messages: LOG: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter I suspect I must increase max_fsm_relations. The value of max_fsm_pages is 2 (the default value). I suspect I must change this to a higher value... but how high should this value be? First increase max_fsm_relations to more than the number of tables,etc in your system. Then, after it's running a while, do the same vacuum command and see if it gives you a hint to increase that parameter too. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
