Skip site navigation (1) Skip section navigation (2)

Re: Caching of Queries

From: "Jason Coene" <jcoene(at)gotfrag(dot)com>
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
Date: 2004-09-23 19:07:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

I've attached results from the above test, showing open locks, top output,
and vmstat 5.

Tom, I've run the test described in:

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.



> -----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

Attachment: ab_150conn.txt
Description: text/plain (8.5 KB)
Attachment: mptest.txt
Description: text/plain (5.2 KB)

In response to


pgsql-performance by date

Next:From: Jason CoeneDate: 2004-09-23 19:24:33
Subject: Re: Caching of Queries
Previous:From: Tom LaneDate: 2004-09-23 19:06:52
Subject: Re: Caching of Queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group