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

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

From: justin <justin(at)emproshunts(dot)com>
To: "pgsql-performance-owner(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How would you store read/unread topic status?
Date: 2009-06-23 13:57:56
Message-ID: 4A40DF64.5010808@emproshunts.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Nikolas EverettDate: 2009-06-23 14:11:33
Subject: Re: How would you store read/unread topic status?
Previous:From: Andres FreundDate: 2009-06-23 12:43:03
Subject: Re: How would you store read/unread topic status?

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