(see below the original mail I forwarded to the list) Zsombor, As far as I know there's no way to disable the MVCC features in postgres, that's a fundamental design choice in it's workings. However, your problem can be solved by properly running vacuum on the tables you update frequently. A first good step would be to enable autovacuum in the postgres configuration file, that will take care of most of the vacuuming you need. As a long term fix would be for you to understand how postgres works and design your application so it uses it's strengths and avoids it weaknesses... your mail is not very clear if you tuned the postgres settings at all, so maybe you should read the docs if you did not do that already: Documentation contents: http://www.postgresql.org/docs/8.1/interactive/index.html Server configuration: http://www.postgresql.org/docs/8.1/interactive/runtime-config.html You should pay special attention to these: http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html HTH, Csaba. -----Forwarded Message----- From: Heffter Zsombor <heffter.zsombor@xxxxxxxxxxxx> To: nagy@xxxxxxxxxxxxxx Subject: postgres dev question - MVCC slowdown Date: Fri, 23 Mar 2007 01:31:15 +0100 Hi Csaba, I'm new to Postgres and I'm asking you couse you are a pgdeveloper and may have a closer look to the system architecture, so maybe you will have a simple solution to my problem. I have a big table in Potgres 8.1 on Fedora5 named tblphones containing 3million records. My clients execute a sp_LockPhoneID function which queries the table via various filtering syntax and if match found, updates the table like this: declare _PhoneID integer; begin update tblsettings set lockid=lockid+1 where settingid=1; select into _PhoneID phoneid from tblphones where ... limit 1; if FOUND then update tblphones set ... where phoneid=_PhoneID; end if; update tblsettings set lockid=lockid-1 where settingid=1; The problem is, that after 3-4 hours the response time of the sp increases over 5-20 seconds. If I issue vacuum full, the response time's got normal again.... I think the cause is the MVCC on updates. Is there any way to disabe them???? I don't need concurrent running of the function, I would need to wait concurrent instances to finish the previous ones... I've tried : LOCK TABLE tblphones IN EXCLUSIVE MODE; but in this case if a function is in progress and another client executes the function, the previous instance will halt untill the next one finishes... Anyway does TABLE LOCK disable MVCC update overheads??? Thanks a lot, Zsombor
