Update:
I just tried running the same test (ab with 150 concurrent connections)
while connecting to postgres through 35 persistent connections (PHP
library), and had roughly the same type of results. This should eliminate
the "new connection" overhead. I've attached top and vmstat. I let it run
until it had completed 800 requests. Unless I'm missing something, there's
more than the "new connection" IO load here.
Jason
> -----Original Message-----
> From: Jason Coene [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 23, 2004 3:08 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: Caching of Queries
>
> Hi All,
>
> It does sound like we should be pooling connections somehow. I'll be
> looking at implementing that shortly. I'd really like to understand what
> the actual problem is, though.
>
> 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.
>
> I get the same results with:
>
> ab -n 10000 -c 150 http://www.gotfrag.com/portal/news/
>
> I've attached results from the above test, showing open locks, top output,
> and vmstat 5.
>
> Tom, I've run the test described in:
>
> http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php
>
> Results attached in mptest.txt. The box did experience the same problems
> as
> we've seen before. I ran it under a separate database (test), and it
> still
> caused our other queries to slow significantly from our production
> database
> (gf) - semwait again.
>
> It does look like the "cs" column under CPU (which I'd assume is Context
> Swap) does bump up significantly (10x or better) during both my ab test,
> and
> the test you suggested in that archived message.
>
> Reading the first thread you pointed out (2004-04/msg00249.php), Josh
> Berkus
> was questioning the ServerWorks chipsets. We're running on the Intel
> E7501
> Chipset (MSI board). Our CPU's are 2.66 GHz with 533MHz FSB,
> Hyperthreading
> enabled. Unfortunately, I don't have physical access to the machine to
> turn
> HT off.
>
>
> Thanks,
>
> Jason
>
>
>
> > -----Original Message-----
> > From: Gaetano Mendola [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, September 23, 2004 1:41 PM
> > To: Jason Coene
> > Subject: Re: Caching of Queries
> >
> > Jason Coene wrote:
> > > Hi Tom,
> > >
> > > Easily recreated with Apache benchmark, "ab -n 30000 -c 3000
> > > http://webserver ". This runs 1 query per page, everything else is
> > cached
> > > on webserver.
> >
> > That test require 30000 access with 3000 connections that is not a
> normal
> > load. Describe us your HW.
> >
> > 3000 connections means a very huge load, may you provide also the result
> > of
> > "vmstat 5" my webserver trash already with -c 120 !
> >
> > how many connection your postgres can manage ?
> >
> > You have to consider to use a connection pool with that ammount of
> > connections.
> >
> >
> > Regards
> > Gaetano Mendola
last pid: 48239; load averages: 5.83, 2.43, 1.50 up 19+22:59:04 15:19:12
127 processes: 16 running, 111 sleeping
CPU states: 17.7% user, 0.0% nice, 20.0% system, 1.0% interrupt, 61.3% idle
Mem: 125M Active, 1456M Inact, 193M Wired, 96M Cache, 112M Buf, 54M Free
Swap: 4096M Total, 4096M Free
PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
48190 pgsql -4 0 97408K 86416K semwai 1 0:01 3.35% 1.32% postgres
47761 pgsql -4 0 96816K 56708K semwai 2 0:01 0.90% 0.88% postgres
47765 pgsql 4 0 96816K 56708K sbwait 3 0:01 0.90% 0.88% postgres
47754 pgsql -4 0 96816K 56708K semwai 2 0:01 0.85% 0.83% postgres
47763 pgsql -4 0 96816K 56708K semwai 0 0:01 0.85% 0.83% postgres
47741 pgsql 4 0 96816K 56708K sbwait 3 0:01 0.75% 0.73% postgres
47674 pgsql -4 0 96264K 38992K semwai 1 0:01 0.74% 0.73% postgres
47753 pgsql -4 0 96816K 56708K semwai 1 0:00 0.65% 0.63% postgres
48204 pgsql -4 0 96856K 46752K semwai 0 0:00 2.15% 0.63% postgres
47698 pgsql 4 0 96240K 37792K sbwait 3 0:01 0.59% 0.59% postgres
47757 pgsql -4 0 96816K 56708K semwai 3 0:01 0.60% 0.59% postgres
47740 pgsql 4 0 96240K 37768K sbwait 0 0:01 0.55% 0.54% postgres
47759 pgsql 4 0 96816K 56708K sbwait 0 0:01 0.50% 0.49% postgres
47735 pgsql -4 0 96240K 37772K semwai 0 0:00 0.50% 0.49% postgres
48223 pgsql -4 0 96984K 55980K semwai 1 0:00 2.69% 0.49% postgres
48102 pgsql 4 0 96136K 54956K sbwait 0 0:00 0.69% 0.44% postgres
47718 pgsql 4 0 96816K 56716K sbwait 1 0:01 0.40% 0.39% postgres
48225 pgsql 123 0 96272K 57156K RUN 0 0:00 2.80% 0.39% postgres
48053 pgsql -4 0 96136K 55040K semwai 0 0:00 0.48% 0.34% postgres
48041 pgsql 4 0 96136K 54992K sbwait 1 0:00 0.47% 0.34% postgres
48222 pgsql -4 0 96872K 57676K semwai 1 0:00 1.88% 0.34% postgres
48216 pgsql -4 0 96800K 54596K semwai 0 0:00 1.54% 0.34% postgres
48050 pgsql 4 0 96136K 56592K sbwait 3 0:00 0.41% 0.29% postgres
48232 pgsql 126 0 96048K 31192K RUN 3 0:00 3.08% 0.29% postgres
48091 pgsql -4 0 96136K 54956K semwai 3 0:00 0.39% 0.24% postgres
48095 pgsql 98 0 96048K 34880K RUN 1 0:00 0.39% 0.24% postgres
48136 pgsql 4 0 96136K 54956K sbwait 0 0:00 0.43% 0.24% postgres
48214 pgsql -4 0 96376K 43628K semwai 3 0:00 1.10% 0.24% postgres
48221 pgsql 121 0 96904K 47788K RUN 3 0:00 1.35% 0.24% postgres
48230 pgsql 126 0 96984K 44964K RUN 2 0:00 2.56% 0.24% postgres
48234 pgsql -4 0 97028K 27496K semwai 3 0:00 5.00% 0.24% postgres
48059 pgsql 4 0 96048K 34916K sbwait 2 0:00 0.28% 0.20% postgres
48057 pgsql 4 0 96136K 54992K sbwait 1 0:00 0.28% 0.20% postgres
48131 pgsql 4 0 96136K 54956K sbwait 0 0:00 0.33% 0.20% postgres
48142 pgsql -4 0 96136K 54956K semwai 0 0:00 0.34% 0.20% postgres
48148 pgsql 102 0 96136K 54956K RUN 1 0:00 0.35% 0.20% postgres
48118 pgsql 4 0 96048K 34884K sbwait 0 0:00 0.33% 0.20% postgres
47708 pgsql 4 0 96916K 49324K sbwait 2 0:00 0.20% 0.20% postgres
48226 pgsql 4 0 96048K 34884K sbwait 2 0:00 1.40% 0.20% postgres
48075 pgsql -4 0 96048K 34884K semwai 1 0:00 0.22% 0.15% postgres
48117 pgsql 4 0 96136K 58208K sbwait 0 0:00 0.25% 0.15% postgres
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
0 0 0 212652 241296 2348 0 0 0 1687 1 0 0 723 0 431 5 3 91
0 0 0 212720 241228 9198 0 0 0 1970 0 2 0 749 0 2400 5 5 91
[begins]
1 0 0 227828 232504 8948 0 0 0 1353 0 8 0 1170 0 29669 17 9 75
0 2 8 297908 190496 17897 0 0 0 1439 0 2 0 1222 0 18880 17 9 74
5 43 4 341412 160524 18352 0 0 0 1372 0 8 0 1481 0 32656 19 12 68
0 48 13 347956 156412 13473 0 0 0 1727 0 28 0 1109 0 60976 19 17 64
5 54 6 357700 150248 14142 0 0 0 1495 0 3 0 1104 0 52658 19 16 65
0 53 8 338828 162376 9523 0 0 0 2361 0 2 0 1029 0 68759 21 18 61
1 0 0 317496 175964 13612 0 0 0 2902 0 4 0 1132 0 44194 18 14 68
[ends]
0 0 0 314544 177624 10144 0 0 0 2014 0 4 0 754 0 2590 6 4 90
0 0 0 195372 253776 7211 0 0 0 5269 0 5 0 717 0 2719 4 4 91
0 0 0 190812 256580 11132 0 0 0 2380 0 33 0 875 0 3014 6 5 89
^C
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
