Обсуждение: Autovacuum and pg_largeobject
Hi all I would like to know if the large object table pg_largeobject is routinely checked by the autovacuum daemon. I ask about this because I have a database in wich the pg_largeobject table is being forcibly vacuumed because it's relfrozenxid is now greater than autovacuum_freeze_max_age, and it's killing the server performance. PostgreSQL version is 8.2.14. Thanks
Excerpts from Norberto Delle's message of vie jul 02 08:10:44 -0400 2010: > Hi all > > I would like to know if the large object table pg_largeobject is > routinely checked by the autovacuum daemon. > I ask about this because I have a database in wich the pg_largeobject > table is being forcibly vacuumed because it's relfrozenxid is now > greater than autovacuum_freeze_max_age, and it's killing the server > performance. PostgreSQL version is 8.2.14. It should, as it should vacuum any other table. Perhaps all the routine autovacuums were killed because of locking issues. I admit I haven't investigated the locking behavior of pg_largeobject in particular. Would it be locked more frequently than other system catalogs?
Em 2/7/2010 13:57, Alvaro Herrera escreveu: > Excerpts from Norberto Delle's message of vie jul 02 08:10:44 -0400 2010: >> Hi all >> >> I would like to know if the large object table pg_largeobject is >> routinely checked by the autovacuum daemon. >> I ask about this because I have a database in wich the pg_largeobject >> table is being forcibly vacuumed because it's relfrozenxid is now >> greater than autovacuum_freeze_max_age, and it's killing the server >> performance. PostgreSQL version is 8.2.14. > It should, as it should vacuum any other table. Perhaps all the routine > autovacuums were killed because of locking issues. I admit I haven't > investigated the locking behavior of pg_largeobject in particular. > Would it be locked more frequently than other system catalogs? > As far as I know, It's not being locked. We use it to store digitalized documents and it's getting very big. I think the server is being turned off before it can complete an vacuum pass. The server is not kept on overnight. As a palliative measure, I increased the value of autovacuum_freeze_max_age from 200.000.000 to 300.000.000. I think I will have to schedule a vacuum on that table during the weekend. Thanks for the attention Norberto
Excerpts from Norberto Delle's message of vie jul 02 13:43:37 -0400 2010: > As far as I know, It's not being locked. We use it to store digitalized > documents > and it's getting very big. I think the server is being turned off before > it can complete > an vacuum pass. The server is not kept on overnight. Oh. That's exceptional. I think you'd be better served by scheduling a manual vacuum sometime during the day -- and probably with lower cost_delay parameters, so that it takes less time to complete.