[Fwd: postgres dev question - MVCC slowdown]

(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(dot)zsombor(at)euronetrt(dot)hu>
To: nagy(at)ecircle-ag(dot)com
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