Mathieu Nebra wrote:
> Hi all,
>
> I'm running a quite large website which has its own forums. They are
> currently heavily used and I'm getting performance issues. Most of them
> are due to repeated UPDATE queries on a "flags" table.
>
> This "flags" table has more or less the following fields:
>
> UserID - TopicID - LastReadAnswerID
>
> The flags table keeps track of every topic a member has visited and
> remembers the last answer which was posted at this moment. It allows the
> user to come back a few days after and immediately jump to the last
> answer he has not read.
>
> My problem is that everytime a user READS a topic, it UPDATES this flags
> table to remember he has read it. This leads to multiple updates at the
> same time on the same table, and an update can take a few seconds. This
> is not acceptable for my users.
>
> Question: what is the general rule of thumb here? How would you store
> this information?
>
> Thanks a lot in advance.
> Mathieu.
>
>
Sounds like the server is getting IO bound by checkpoints causing flush
to disk causing a IO to become bound.
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
there is some 8.0-8.2 tuning ideas in this link.
Yes this is acceptable way to store such information.
What is the PG version. performance tuning options are different
depending on the version???
http://wiki.postgresql.org/wiki/Performance_Optimizationhttp://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm