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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
