pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Which PARAMETER is most important for load query?? 2+ messages / 2 participants [nested] [flat]
* Which PARAMETER is most important for load query?? @ 2005-01-20 15:45 amrit@health2.moph.go.th 0 siblings, 1 reply; 2+ messages in thread From: amrit@health2.moph.go.th @ 2005-01-20 15:45 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Which PARAMETER is most important for load query?? @ 2005-01-20 16:00 Christopher Kings-Lynne <chriskl@familyhealth.com.au> parent: amrit@health2.moph.go.th 0 siblings, 0 replies; 2+ messages in thread From: Christopher Kings-Lynne @ 2005-01-20 16:00 UTC (permalink / raw) To: amrit@health2.moph.go.th; +Cc: 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-01-20 16:00 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-01-20 15:45 Which PARAMETER is most important for load query?? amrit@health2.moph.go.th 2005-01-20 16:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox