>> Then you aren't doing regular vacuum often enough and / or don't have high enough fsm settings. Right now it's just default, can you please point me to a document that elaborates on calculation of FSM for a given load (or to say averaged load) I found ( http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp ) but does not have details. # - Free Space Map - #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~70 bytes each -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] Sent: Thursday, August 30, 2007 4:07 PM To: Nitin Verma Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: What kind of locks does vacuum process hold on the db? On 8/30/07, Nitin Verma <nitinverma@xxxxxxxxxxxxxxx> wrote: > > Why are you doing FULL vacuums? Is there some problem that regular vacuums > aren't solving? > > Using dump/restore from a live DB to fresh DB, I get a DB that takes (49M + > 12M - {I have two table spaces)) 61M of disk. Maximum size that I can grow by > the quota allocated to DB is 100M. > > A regular vacuum doesn't stop the database growth, and DB grows beyond 100M. > Then we have to trigger a script that dump/restores on the live database. For > that we have a small outage (which is right now automated). > > A full vacuum keeps the database below 100M and no outage. Then you aren't doing regular vacuum often enough and / or don't have high enough fsm settings. > > > Yes, vacuum full takes a hard lock on a table. > > That means Table Level AccessExclusiveLock, right? Not sure which name it is. It definitely blocks writes to the table while it is vacuuming it. But that's secondary. You're having to do regular vacuum fulls because of too infrequent regular vacuum and / or too low fsm setting. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
