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

On Tue, Jun 23, 2009 at 11:50 AM, Mathieu Nebra<mateo21(at)siteduzero(dot)com> wrote:
>>  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:

I was thinking you might look at your httpd logs. Not sure how to get
it otherwise.

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

That looks like EXPLAIN, not EXPLAIN ANALYZE. And can we also have the query?

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

Is that just for the one table? I meant a database-wide VACUUM
VERBOSE, so you can see if you've blown out your free-space map.

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

Was that at a low traffic time of day, or on a different server?

...Robert