Skip site navigation (1) Skip section navigation (2)

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

From: Andres Freund <andres(at)anarazel(dot)de>
To: Mathieu Nebra <mateo21(at)siteduzero(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How would you store read/unread topic status?
Date: 2009-06-23 12:24:40
Message-ID: 4A40C988.1090806@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Alexander StauboDate: 2009-06-23 12:37:10
Subject: Re: How would you store read/unread topic status?
Previous:From: Mathieu NebraDate: 2009-06-23 11:12:39
Subject: How would you store read/unread topic status?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group