Re: How would you store read/unread topic status?

On 06/23/2009 01:12 PM, Mathieu Nebra wrote:
> 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.
Have you analyzed why it takes that long? Determining that is the first
step of improving the current situation...

My first guess would be, that your disks cannot keep up with the number
of syncronous writes/second. Do you know how many transactions with
write access you have? Guessing from your description you do at least
one write for every page hit on your forum.

With the default settings every transaction needs to wait for io at the
end - to ensure transactional semantics.
Depending on your disk the number of possible writes/second is quite low
- a normal SATA disk with 7200rpm can satisfy something around 130
syncronous writes per second. Which is the upper limit on writing
transactions per second.
What disks do you have?

On which OS are you? If you are on linux you could use iostat to get
some relevant statistics like:
iostat -x /path/to/device/the/database/resides/on 2 10

That gives you 10 statistics over periods of 2 seconds.

Depending on those results there are numerous solutions to that problem...

> Question: what is the general rule of thumb here? How would you store
> this information?
The problem here is, that every read access writes to disk - that is not
going to scale very well.
One possible solution is to use something like memcached to store the
last read post in memory and periodically write it into the database.

Which pg version are you using?

Andres