Re: How can I make this query faster (resend)

(re-sending because my first one forgot the CC to the list. Sorry)

I moved my database to a more powerful server. Mirrored ultra 320 SCSI HDs
and 2GB of ram. It performs much faster.

I also changed some conf settings accordingly
work_mem = 10240
shared_buffers = 25600
max_connections = 450 (Also moved the webserver and needed more connections
during the DNS propagation).

I've been looking into pgpool. If I understand things correctly I can have
persistent connections from all 256 apache processes to a pgpool and it can
have like 30 persistent connections to the actual server thus saving lots of
server memory (due to very high concurrency I would probably actually use at
least 100) Is this correct?

However, memory doesn't seem to be my problem anymore, the query is still
taking longer than I'd like for the larger categories (6 seconds for one
with 1300 pictures) but its more managable. The problem now is that my
server's load average during peak hours has gone as high as 30 (tho the
server seems to still be responding fairly quickly it still worrysome)

Given my new server specs can anyone suggest any other config file
improvements? Perhaps some of the *_cost variables could be adjusted to
better reflect my server's hardware?

----- Original Message -----
From: "Markus Schaber" <schabi(at)logix-tt(dot)com>
To: "Cstdenis" <cstdenis(at)voicio(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Sent: Monday, May 22, 2006 8:30 AM
Subject: Re: [PERFORM] How can I make this query faster (resend)

> Hi, Cstendis,
>
> Cstdenis wrote:
>
> > Server is a dual xeon with a gig of ram dedicated mostly to postgresql.
> > Here is the changed lines in my postgresql.conf:
http://pastebin.ca/57222
>
> 3M is really low for a production server.
>
> Try using pg_pool and limiting it to about 30 or so backend connections,
> and then give them at least 30 megs of RAM each.
>
> This should also cut down the connection creation overhead.
>
> HTH,
> Markus
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf. | Software Development GIS
>
> Fight against software patents in EU! www.ffii.org
www.nosoftwarepatents.org
>