OPEN LOCKS: gf=# SELECT r.relname, l."mode", count(*) AS numlocks FROM pg_locks l, pg_class r WHERE r.oid = l.relation GROUP BY r.relname, l."mode" ORDER BY count(*) DESC; relname | mode | numlocks ----------------------------------+---------------------------- threads | AccessShareLock | 43 threads_ix_nuked_lastpost | AccessShareLock | 35 threads_ix_nuked_gameid_lastpost | AccessShareLock | 7 pg_attribute | AccessShareLock | 1 v_locks | AccessShareLock | 1 pg_class | AccessShareLock | 1 usersessions | AccessShareLock | 1 countries | AccessShareLock | 1 users | AccessShareLock | 1 userstats_ix_id | AccessShareLock | 1 pg_statistic_relid_att_index | AccessShareLock | 1 pg_attribute_relid_attnum_index | AccessShareLock | 1 userstats | AccessShareLock | 1 demos | AccessShareLock | 1 pg_cast_source_target_index | AccessShareLock | 1 pg_locks | AccessShareLock | 1 users_ix_id | AccessShareLock | 1 buddies | AccessShareLock | 1 buddies_ix_userid | AccessShareLock | 1 pg_cast | AccessShareLock | 1 pg_statistic | AccessShareLock | 1 (21 rows) RUNNING QUERIES (AND HOW LONG FOR): gf=# SELECT pg_stat_activity.usename, round(date_part('epoch'::text, now() - pg_stat_activity.query_start)) AS duration, pg_stat_activity.current_query FROM pg_stat_activity ORDER BY round(date_part('epoch'::text, now() - pg_stat_activity.query_start)) DESC; usename | duration | current_query ---------+----------+----------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------- gf | 4 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 1 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 3 | gf | 3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 3 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 7 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 2 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 2 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 2 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 1 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 2 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 0 | gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 AND gameid = 2 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8 gf | 0 | gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 gf | 0 | gf | 0 | gf | 0 | (46 rows) QUERY PLAN: gf-# SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..1.99 rows=8 width=39) (actual time=32.174..32.336 rows=8 loops=1) -> Index Scan Backward using threads_ix_nuked_lastpost on threads (cost=0.00..16824.36 rows=67511 width=39) (actual time=32.164..32.298 rows=8 loops=1) Filter: (nuked = 0) Total runtime: 32.446 ms (4 rows) VMSTAT 5 d01.int> vmstat 5 procs memory page disks faults cpu r b w avm fre flt re pi po fr sr da0 fd0 in sy cs us sy id 2 0 0 211536 691068 2449 0 0 0 1693 1 0 0 724 0 482 5 3 91 0 0 2 213688 689700 9772 0 0 0 2221 0 32 0 895 0 3376 7 4 89 0 1 2 212900 690588 12264 0 0 0 3233 0 3 0 997 0 9096 9 7 84 0 0 0 207552 693696 11579 0 0 0 2749 0 47 0 1030 0 9517 8 7 85 2 0 0 212688 690580 9031 0 0 0 2150 0 6 0 839 0 3206 6 4 90 0 0 0 209172 692644 10863 0 0 0 2789 0 8 0 933 0 5356 8 5 87 1 0 0 213060 690040 12255 0 0 0 3213 0 12 0 1154 0 6508 9 5 86 0 0 0 207552 693640 12229 0 0 0 2169 0 28 0 861 0 5764 7 6 87 2 0 0 211632 690812 7772 0 0 0 1963 0 5 0 786 0 2787 5 4 91 0 0 1 211432 691160 12525 0 0 0 2881 0 15 0 1003 0 4029 8 5 87 0 0 0 208320 693168 9280 0 0 0 2262 0 2 0 832 0 3390 7 5 89 5 7 0 231384 679220 15424 0 0 0 2408 0 7 0 1035 0 7909 10 7 83 begin AB 0 50 5 298548 637676 16323 0 0 0 1123 0 2 0 1341 0 50716 18 14 68 0 43 6 290120 641824 10121 0 0 0 2464 0 3 0 1254 0 66648 18 16 65 5 40 1 279568 649060 5221 0 0 0 1790 0 29 0 1273 0 102972 21 23 56 2 35 11 281128 647952 8595 0 0 0 1894 0 10 0 1461 0 65727 20 15 65 1 12 14 264744 658852 7410 0 0 0 2058 0 1 0 1347 0 67184 21 16 63 0 2 5 258092 662808 9405 0 0 0 2667 0 7 0 1543 0 43952 20 12 68 5 3 0 268944 656192 9765 0 0 0 1905 0 7 0 1479 0 37269 21 10 70 4 8 3 268076 656292 9561 0 0 0 2223 0 6 0 1485 0 29425 18 8 73 0 45 3 287296 643892 10656 0 0 0 1914 0 24 0 1542 0 58277 23 13 65 5 41 0 283708 646428 9064 0 0 0 2218 0 12 0 1447 0 64764 20 15 65 0 49 4 296012 638116 10023 0 0 0 2314 0 8 0 1397 0 64031 19 17 64 5 36 3 299804 635440 9287 0 0 0 2048 0 3 0 1177 0 81789 19 20 61 end AB 0 1 1 260944 660812 13731 0 0 0 3377 0 11 0 999 0 11240 9 8 83 0 0 0 256324 663836 8794 0 0 0 2318 0 2 0 796 0 3622 6 4 90 1 0 0 256956 663632 7928 0 0 0 1941 0 37 0 853 0 2717 5 3 91 0 0 0 256176 664044 9236 0 0 0 2159 0 5 0 847 0 2620 6 4 90 0 0 3 257784 663008 10665 0 0 0 2521 0 4 0 844 0 4582 7 6 87 0 0 0 254572 665016 16023 0 0 0 2689 0 10 0 1027 0 9869 8 8 84 TOP: last pid: 97030; load averages: 5.33, 2.30, 1.55 up 20+04:55:04 21:15:12 118 processes: 4 running, 110 sleeping, 4 lock CPU states: 19.4% user, 0.0% nice, 13.2% system, 0.8% interrupt, 66.7% idle Mem: 86M Active, 1008M Inact, 204M Wired, 59M Cache, 112M Buf, 566M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 96626 pgsql -4 0 96048K 21076K semwai 0 0:02 2.19% 2.05% postgres 96649 pgsql -4 0 96048K 21076K semwai 3 0:01 2.15% 2.00% postgres 96638 pgsql -4 0 96048K 21076K semwai 0 0:02 2.09% 1.95% postgres 96627 pgsql 4 0 96048K 21076K sbwait 0 0:02 2.09% 1.95% postgres 96635 pgsql -4 0 96048K 21076K semwai 0 0:01 1.88% 1.76% postgres 96647 pgsql -4 0 96048K 21076K semwai 1 0:01 1.88% 1.76% postgres 96652 pgsql -4 0 96048K 21076K semwai 0 0:02 1.73% 1.61% postgres 96640 pgsql -4 0 96048K 21144K semwai 3 0:02 1.73% 1.61% postgres 95637 pgsql -4 0 96240K 31868K semwai 1 0:02 1.61% 1.61% postgres 96637 pgsql -4 0 96048K 21076K semwai 1 0:01 1.73% 1.61% postgres 96645 pgsql -4 0 96048K 21076K semwai 0 0:02 1.67% 1.56% postgres 96630 pgsql -4 0 96048K 21076K semwai 3 0:01 1.67% 1.56% postgres 95549 pgsql -4 0 96240K 32364K semwai 0 0:02 1.51% 1.51% postgres 96629 pgsql -4 0 96048K 21076K semwai 0 0:01 1.62% 1.51% postgres 96648 pgsql -4 0 96048K 21076K semwai 0 0:01 1.62% 1.51% postgres 96661 pgsql -4 0 96048K 21076K semwai 1 0:01 1.57% 1.46% postgres 96644 pgsql -4 0 96048K 21076K semwai 3 0:01 1.57% 1.46% postgres 96653 pgsql 4 0 96048K 21076K sbwait 0 0:01 1.47% 1.37% postgres 96651 pgsql -4 0 96048K 21076K semwai 3 0:02 1.41% 1.32% postgres 96657 pgsql -4 0 96048K 21076K semwai 0 0:01 1.41% 1.32% postgres 96646 pgsql -4 0 96048K 21076K semwai 0 0:01 1.36% 1.27% postgres 96643 pgsql -4 0 96048K 21076K semwai 3 0:01 1.36% 1.27% postgres 96654 pgsql -4 0 96048K 21076K semwai 3 0:01 1.36% 1.27% postgres 96632 pgsql -4 0 96048K 21076K semwai 1 0:01 1.30% 1.22% postgres 96659 pgsql -4 0 96048K 21076K semwai 0 0:01 1.20% 1.12% postgres 96655 pgsql -4 0 96048K 21076K semwai 1 0:01 1.15% 1.07% postgres 96660 pgsql -4 0 96048K 21076K semwai 3 0:01 1.10% 1.03% postgres 96636 pgsql -4 0 96048K 21076K semwai 1 0:01 1.04% 0.98% postgres 96650 pgsql -4 0 96048K 21076K semwai 0 0:01 1.05% 0.98% postgres 96656 pgsql -4 0 96048K 21076K semwai 3 0:01 1.05% 0.98% postgres 96658 pgsql -4 0 96048K 21076K semwai 0 0:01 0.99% 0.93% postgres 96631 pgsql -4 0 96048K 21076K semwai 0 0:01 0.99% 0.93% postgres 97004 pgsql -4 0 96356K 41576K semwai 3 0:00 3.50% 0.49% postgres 97009 pgsql -4 0 97028K 24004K semwai 3 0:00 2.56% 0.24% postgres