Hi Josh,
I just tried using pgpool to pool the connections, and ran:
ab -n 1000 -c 50 http://wstg.int/portal/news/
I ran some previous queries to get pgpool to pre-establish all the
connections, and ab ran for a few minutes (with one query per page, eek!).
It was still exhibiting the same problems as before. While so many new
connections at once can surely make the problem worse (and pgpool will
surely help there), shouldn't this prove that it's not the only issue?
We're running FreeBSD 5.2.1
I've attached open locks, running queries, query plans, top output and
vmstat 5 output for while ab was running, from start to finish.
Any ideas?
Jason
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Josh Berkus
> Sent: Thursday, September 23, 2004 8:06 PM
> To: Jason Coene; [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Caching of Queries
>
> Jason,
>
> > Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
> > connections
> > / second is realistic, if not underestimated. As is the nature of
> > our site
> > (realtime information about online gaming), there's a huge fan base
> > and as a
> > big upset happens, we'll do 50,000 page views in a span of 3-5
> > minutes.
>
> First, your posts show no evidences of the CS storm bug.
>
> Second, 300 *new* connections a second is a lot. Each new connection
> requires a significant amount of both database and OS overhead. This
> is why all the other web developers use a connection pool.
>
> In fact, I wouldn't be surprised if your lockups are on the OS level,
> even; I don't recall that you cited what OS you're using, but I can
> imagine locking up Linux 2.4 trying to spawn 300 new processes a
> second.
>
> --Josh
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
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 | <IDLE>
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 | <IDLE>
gf | 0 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0
ORDER BY nuked DESC, lastpost DESC LIMIT 8
gf | 0 | <IDLE>
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 | <IDLE>
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 | <IDLE>
gf | 0 | <IDLE>
gf | 0 | <IDLE>
(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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
