Re: How would you store read/unread topic status?
От | Andres Freund |
---|---|
Тема | Re: How would you store read/unread topic status? |
Дата | |
Msg-id | 4A40F13F.7030605@anarazel.de обсуждение исходный текст |
Ответ на | Re: How would you store read/unread topic status? (Mathieu Nebra <mateo21@siteduzero.com>) |
Ответы |
Re: How would you store read/unread topic status?
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Guillaume CottenceauДата:
Сообщение: Re: How would you store read/unread topic status?