Re: Caching of Queries

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:jcoene(at)gotfrag(dot)com]
> Sent: Thursday, September 23, 2004 3:08 PM
> To: pgsql-performance(at)postgresql(dot)org
> Cc: mendola(at)bigfoot(dot)com; tgl(at)sss(dot)pgh(dot)pa(dot)us; josh(at)agliodbs(dot)com
> 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:mendola(at)bigfoot(dot)com]
> > 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