Re: Which PARAMETER is most important for load query?? - Mailing list pgsql-performance

From Christopher Kings-Lynne
Subject Re: Which PARAMETER is most important for load query??
Date
Msg-id 41EFD590.3010001@familyhealth.com.au
Whole thread Raw
In response to Which PARAMETER is most important for load query??  (amrit@health2.moph.go.th)
List pgsql-performance

amrit@health2.moph.go.th wrote:
> I'm dealing with big database [3.8 Gb] and records of 3 millions . Some of the
> query seems to be slow eventhough just a few users in the night. I would like
> to know which parameter list below is most effective in rising the speed of
> these queries?
>
> Shmmax = 32384*8192 =265289728
> Share buffer = 32384

That's the one you want to increase...

> sort_mem = 34025    <===== I guess increase this one is most effective but too

You should reduce this.  This is memory PER SORT.  You could have 10
sorts in one query and that query being run 10 times at once, using 100x
that sort_mem in total - causing lots of swapping.  So something like
8192 would probably be better, even lower at 4096 perhaps.

> effective cache = 153204

That's probably about right.

Chris

pgsql-performance by date:

Previous
From: Richard_D_Levine@raytheon.com
Date:
Subject: Re: PostgreSQL clustering VS MySQL clustering
Next
From: Rod Taylor
Date:
Subject: Re: PostgreSQL clustering VS MySQL clustering