On Wed, 2005-07-27 at 10:31, Dr NoName wrote: > > Sure. Like this: > > > > Client A accesses table T, and "hangs." > > Client B attempts to get an ACCESS EXCLUSIVE lock on > > table T in > > preparation for VACUUM FULL. > > Client C connects to the database and waits for > > client B to get and > > release his lock on table T. > > Client D connects to the database and waits for > > client B to get and > > release his lock on table T. > > Client E connects to the database and waits for > > client B to get and > > release his lock on table T. > > etc... > > oh! my! gawd! > Finally a clear explanation that makes perfect sense. > Now why did it take so long? Because your initial definition of the problem kinda led us all in the wrong direction for 24 hours? :) Remember, it took like three times of folks asking "what's happening that locks your database" before the vacuum full issue came up. From there, 24 more hours. Actually not bad. And don't forget, the docs on vacuum pretty clearly state: "The second form is the VACUUM FULL command. This uses a more aggressive algorithm for reclaiming the space consumed by expired row versions. Any space that is freed by VACUUM FULL is immediately returned to the operating system. Unfortunately, this variant of the VACUUM command acquires an exclusive lock on each table while VACUUM FULL is processing it. Therefore, frequently using VACUUM FULL can have an extremely negative effect on the performance of concurrent database queries." And then later on: "VACUUM FULL is recommended for cases where you know you have deleted the majority of rows in a table, so that the steady-state size of the table can be shrunk substantially with VACUUM FULL's more aggressive approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery." So, daily vacuum fulls are not recommended. > So all I need to do is take out the FULL? Is regular > VACUUM sufficient? How often do we need FULL? (I know > it's a stupid question without providing some more > context, but how can I estimate it?) Please read up on vacuuming in the docs, at: http://www.postgresql.org/docs/8.0/static/maintenance.html#ROUTINE-VACUUMING It's quite enlightening about this. Basically, assuming your fsm settings are high enough for your update/delete load, yes, plain vacuums should be enough. > > I suppose the ultimate solution would be a wrapper > script that works as follows: > > check if there are any waiting/idle in transaction > processes > if such processes exist, do a regular VACUUM and send > out a warning email > otherwise, do VACUUM FULL. Nah, that's probably overkill. I'd rather just run plain vacuum verboses and check them by hand once a week or so to make sure I'm reclaiming all the space. > I like this solution a lot more than getting support > calls on weekends. Amen brother, amen... > Out of curiousity, how is lock acquisition implemented > in postgresql? All the processes have to go through > some sort of queue, so that locks are granted in FIFO > order, as you described. Just trying to understand it > better. See here: http://www.postgresql.org/docs/8.0/static/mvcc.html PostgreSQL's locking system is quite impression. I kinda giggle when someone says "Well, not MySQL has feature Y, so why bother with PostgreSQL?" It's pretty obvious they haven't really read up on pgsql when they say things like that. ---------------------------(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
