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

Robert Haas a écrit :
>>>> Which pg version are you using?
>> I should have mentionned that before sorry: PostgreSQL 8.2
>
> I think there is an awful lot of speculation on this thread about what
> your problem is without anywhere near enough investigation.  A couple
> of seconds for an update is a really long time, unless your server is
> absolutely slammed, in which case probably everything is taking a long
> time.  We need to get some more information on what is happening here.

You're right, I'll give you the information you need.

>  Approximately how many requests per second are you servicing?  Also,

How can I extract this information from the database? I know how to use
pg_stat_user_tables. My table has:

seq_tup_read
133793491714

idx_scan
12408612540

idx_tup_fetch
41041660903

n_tup_ins
14700038

n_tup_upd
6698236

n_tup_del
15990670

> can you:
>
> 1. Run EXPLAIN ANALYZE on a representative UPDATE statement and post
> the exact query and the output.

"Index Scan using prj_frm_flg_pkey on prj_frm_flg  (cost=0.00..8.58
rows=1 width=18)"
"  Index Cond: ((flg_mid = 3) AND (flg_sid = 123764))"

This time it only took 54ms, but maybe it's already a lot.


>
> 2. Run VACUUM VERBOSE on your database and send the last 10 lines or
> so of the output.

It's not very long, I can give you the whole log:

INFO:  vacuuming "public.prj_frm_flg"INFO:  scanned index
"prj_frm_flg_pkey" to remove 74091 row versions
DETAIL:  CPU 0.15s/0.47u sec elapsed 53.10 sec.INFO:  scanned index
"flg_fav" to remove 74091 row versions
DETAIL:  CPU 0.28s/0.31u sec elapsed 91.82 sec.INFO:  scanned index
"flg_notif" to remove 74091 row versions
DETAIL:  CPU 0.36s/0.37u sec elapsed 80.75 sec.INFO:  scanned index
"flg_post" to remove 74091 row versions
DETAIL:  CPU 0.31s/0.37u sec elapsed 115.86 sec.INFO:  scanned index
"flg_no_inter" to remove 74091 row versions
DETAIL:  CPU 0.34s/0.33u sec elapsed 68.96 sec.INFO:  "prj_frm_flg":
removed 74091 row versions in 5979 pages
DETAIL:  CPU 0.29s/0.34u sec elapsed 100.37 sec.INFO:  index
"prj_frm_flg_pkey" now contains 1315895 row versions in 7716 pages
DETAIL:  63153 index row versions were removed.
672 index pages have been deleted, 639 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index "flg_fav" now contains
1315895 row versions in 18228 pages
DETAIL:  73628 index row versions were removed.
21 index pages have been deleted, 16 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index "flg_notif" now
contains 1315895 row versions in 18179 pages
DETAIL:  73468 index row versions were removed.
22 index pages have been deleted, 13 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index "flg_post" now
contains 1315895 row versions in 18194 pages
DETAIL:  73628 index row versions were removed.
30 index pages have been deleted, 23 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index "flg_no_inter" now
contains 1315895 row versions in 8596 pages
DETAIL:  73628 index row versions were removed.
13 index pages have been deleted, 8 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  "prj_frm_flg": found 74091
removable, 1315895 nonremovable row versions in 10485 pages
DETAIL:  326 dead row versions cannot be removed yet.
There were 253639 unused item pointers.
10431 pages contain useful free space.
0 pages are entirely empty.
CPU 1.91s/2.28u sec elapsed 542.75 sec.

Total: 542877 ms.

>
> 3. Try your UPDATE statement at a low-traffic time of day and see
> whether it's faster than it is at a high-traffic time of day, and by
> how much.  Or dump your database and reload it on a dev server and see
> how fast it runs there.

It took 4ms.