Thread: 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
Denis Gasparin <denis@edistar.com> 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
> 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 20000 (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
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 20000 (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.