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