(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@xxxxxxxxxxxx> To: "Cstdenis" <cstdenis@xxxxxxxxxx> Cc: <pgsql-performance@xxxxxxxxxxxxxx> 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 >
