Thanks a lot, everyone! That solved my problem. But I still want to be able to set transaction timeout. Any chance of that in the next release? Eugene --- Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> wrote: > 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 > __________________________________ Yahoo! Mail for Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail ---------------------------(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
