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: 200409231907.i8NJ7rv2004899@ms-smtp-03.nyroc.rr.com (view raw or flat)
Thread:
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 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


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

In response to

Responses

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-2014 The PostgreSQL Global Development Group