 | | From: | amrit at health2.moph.go.th | | Subject: | Which PARAMETER is most important for load query?? | | Date: | Thu, 20 Jan 2005 22:45:05 +0700 |
|
|
 | 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
sort_mem = 34025 <===== I guess increase this one is most effective but too
high cause reading the swap , is that right?
effective cache = 153204
My server has 4 Gb. ram and ~ 140 clients in rush hours.
Amrit
Thailand
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Christopher Kings-Lynne | | Subject: | Re: Which PARAMETER is most important for load query?? | | Date: | Thu, 20 Jan 2005 16:00:16 +0000 |
|
|
|
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
