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_Optimization
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
