Search Postgresql Archives

[Fwd: postgres dev question - MVCC slowdown]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



(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


 


 


 


 


 


 


 


 





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux