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

От: Andres Freund
Тема: Re: How would you store read/unread topic status?
Дата: ,
Msg-id: 4A40C988.1090806@anarazel.de
(см: обсуждение, исходный текст)
Ответ на: How would you store read/unread topic status?  (Mathieu Nebra)
Ответы: Re: How would you store read/unread topic status?  (Mathieu Nebra)
Список: pgsql-performance

Скрыть дерево обсуждения

How would you store read/unread topic status?  (Mathieu Nebra, )
 Re: How would you store read/unread topic status?  (Andres Freund, )
  Re: How would you store read/unread topic status?  (Mathieu Nebra, )
   Re: How would you store read/unread topic status?  (Robert Haas, )
    Re: How would you store read/unread topic status?  (Mathieu Nebra, )
     Re: How would you store read/unread topic status?  (Robert Haas, )
     Re: How would you store read/unread topic status?  (Mike, )
     Re: How would you store read/unread topic status?  (Mike, )
   Re: How would you store read/unread topic status?  (Andres Freund, )
    Re: How would you store read/unread topic status?  (Robert Haas, )
   Re: How would you store read/unread topic status?  (Scott Carey, )
    Re: How would you store read/unread topic status?  (Greg Stark, )
     Re: How would you store read/unread topic status?  (Mathieu Nebra, )
      Re: How would you store read/unread topic status?  (Craig James, )
       Re: How would you store read/unread topic status?  (Mathieu Nebra, )
      Re: How would you store read/unread topic status?  (Greg Stark, )
 Re: How would you store read/unread topic status?  (Alexander Staubo, )
  Re: How would you store read/unread topic status?  (Andres Freund, )
  Re: How would you store read/unread topic status?  (Nikolas Everett, )
   Re: How would you store read/unread topic status?  (Matthew Wakeling, )
  Re: How would you store read/unread topic status?  (Mathieu Nebra, )
   Re: How would you store read/unread topic status?  (Guillaume Cottenceau, )
   Re: How would you store read/unread topic status?  (Chris St Denis, )
    Re: How would you store read/unread topic status?  (Mathieu Nebra, )
 Re: How would you store read/unread topic status?  (justin, )
 Re: How would you store read/unread topic status?  (Grzegorz Jaśkiewicz, )
 Re: How would you store read/unread topic status?  (Scott Carey, )
 Re: How would you store read/unread topic status?  (Alexander Staubo, )

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


В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: How would you store read/unread topic status?
От: Greg Stark
Дата:
Сообщение: Re: How would you store read/unread topic status?