Re: Caching of Queries (now with pgpool)

Hi Josh,

I just tried using pgpool to pool the connections, and ran:

ab -n 1000 -c 50 http://wstg.int/portal/news/

I ran some previous queries to get pgpool to pre-establish all the
connections, and ab ran for a few minutes (with one query per page, eek!).
It was still exhibiting the same problems as before. While so many new
connections at once can surely make the problem worse (and pgpool will
surely help there), shouldn't this prove that it's not the only issue?

We're running FreeBSD 5.2.1

I've attached open locks, running queries, query plans, top output and
vmstat 5 output for while ab was running, from start to finish.

Any ideas?

Jason

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of Josh Berkus
> Sent: Thursday, September 23, 2004 8:06 PM
> To: Jason Coene; 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: [PERFORM] Caching of Queries
>
> Jason,
>
> > 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.
>
> First, your posts show no evidences of the CS storm bug.
>
> Second, 300 *new* connections a second is a lot. Each new connection
> requires a significant amount of both database and OS overhead. This
> is why all the other web developers use a connection pool.
>
> In fact, I wouldn't be surprised if your lockups are on the OS level,
> even; I don't recall that you cited what OS you're using, but I can
> imagine locking up Linux 2.4 trying to spawn 300 new processes a
> second.
>
> --Josh
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend