On Tue, Jun 23, 2009 at 11:50 AM, Mathieu Nebra<mateo21@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
