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.

I don't know how many writes/s Pgsql can handle on my server, but I
first suspected that it was good practice to avoid unnecessary writes.

I do 1 write/page for every connected user on the forums.
I do the same on another part of my website to increment the number of
page views (this was not part of my initial question but it is very close).

> >
> > 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?

We have 2 SAS RAID 0 15000rpm disks.

> >
> > 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...

Here it is:

$ iostat -x /dev/sda 2 10
Linux 2.6.18-6-amd64 (scratchy) 23.06.2009

avg-cpu: %user %nice %system %iowait %steal %idle
18,02 0,00 12,87 13,13 0,00 55,98

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,94 328,98 29,62 103,06 736,58 6091,14 51,46
0,04 0,25 0,04 0,51

avg-cpu: %user %nice %system %iowait %steal %idle
39,65 0,00 48,38 2,00 0,00 9,98

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,00 0,00 10,00 78,00 516,00 1928,00 27,77
6,44 73,20 2,75 24,20

avg-cpu: %user %nice %system %iowait %steal %idle
40,15 0,00 48,13 2,24 0,00 9,48

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,00 0,00 6,47 100,50 585,07 2288,56 26,87
13,00 121,56 3,00 32,04

avg-cpu: %user %nice %system %iowait %steal %idle
45,14 0,00 45,64 6,73 0,00 2,49

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 1,00 0,00 34,00 157,50 1232,00 3904,00 26,82
26,64 139,09 3,03 58,00

avg-cpu: %user %nice %system %iowait %steal %idle
46,25 0,00 49,25 3,50 0,00 1,00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,00 0,00 27,00 173,00 884,00 4224,00 25,54
24,46 122,32 3,00 60,00

avg-cpu: %user %nice %system %iowait %steal %idle
44,42 0,00 47,64 2,23 0,00 5,71

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,00 0,00 15,42 140,30 700,50 3275,62 25,53
17,94 115,21 2,81 43,78

avg-cpu: %user %nice %system %iowait %steal %idle
41,75 0,00 48,50 2,50 0,00 7,25

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,50 0,00 21,11 116,08 888,44 2472,36 24,50
12,62 91,99 2,55 34,97

avg-cpu: %user %nice %system %iowait %steal %idle
44,03 0,00 46,27 2,99 0,00 6,72

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 9,00 0,00 10,00 119,00 484,00 2728,00 24,90
15,15 117,47 2,70 34,80

avg-cpu: %user %nice %system %iowait %steal %idle
36,91 0,00 51,37 2,49 0,00 9,23

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,99 0,00 14,78 136,45 390,15 2825,62 21,26
21,86 144,52 2,58 39,01

avg-cpu: %user %nice %system %iowait %steal %idle
38,75 0,00 48,75 1,00 0,00 11,50

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0,00 0,00 7,54 67,34 377,89 1764,82 28,62
5,38 71,89 2,95 22,11

> >
>> >> 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.

That's what I thought.

> > One possible solution is to use something like memcached to store the
> > last read post in memory and periodically write it into the database.
> >

We're starting using memcached. But how would you "periodically" write
that to database?

> >
> > Which pg version are you using?

I should have mentionned that before sorry: PostgreSQL 8.2

Thanks a lot!

Andres Freund a écrit :
> 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