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

On 06/23/2009 04:54 PM, Mathieu Nebra wrote:
>> 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.
It surely is.

> 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).
That even more cries for some in-memory-caching.

>>> 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
You see that your average wait time 'await' is quite high. That
indicates some contention. You have somewhere between 50-200
writes/second, so you may be maxing out your disk (depending on your
config those writes may mainly go to one disk at a time).


>>> 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?
Where do you see the problem?

>>> Which pg version are you using?
> I should have mentionned that before sorry: PostgreSQL 8.2
I definitely would consider upgrading to 8.3 - even without any config
changes it might bring quite some improvement.

But mainly it would allow you to use "asynchronous commit" - which could
possibly increase your throughput tremendously.
It has the drawback that you possibly loose async transactions in case
of crash - but that doesn't sound too bad for your use case (use it only
in the transactions where it makes sense).


But all of that does not explain the issue sufficiently - you should not
get that slow updates.
I would suggest you configure "log_min_statement_duration" to get the
slower queries.
You then should run those slow statements using 'EXPLAIN ANALYZE' to see
where the time is spent.

How are you vacuuming?


Andres