pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres optimization
3+ messages / 2 participants
[nested] [flat]

* postgres optimization
@ 2005-07-29 11:34 Kailash Vyas <kailash.vyas@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Kailash Vyas @ 2005-07-29 11:34 UTC (permalink / raw)
  To: pgsql-admin

hi


I need some information to optimize the postgres database server (7.3.10).
I have 2 Gb RAM on the server.

I have set the /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall to 
536870912 (512 Mb)

should i set the shmmax and shmall to use 2 GB or it is better to keep it 
less.
also how does it affect the performance and stability of system


what would be the ideal shared buffer size and effective cache size to be 
set in conf file.

currently i am using this expression to find out shared buffers parameter

max connections = 128

250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity

shared buffers = 65277 (approx)

also regarding the effective cache size how much should i set that to ?

one of the article suggested this so which option would be more optimized

"there is 1.5GB RAM in your machine, shared buffers are set to 32MB and 
effective cache size is set to 800MB. So if a query needs 700MB of data set, 
PostgreSQL would estimate that all the data required should be available in 
memory and would opt for more aggressive plan in terms of optimization, 
involving heavier index usage and merge joins. But if effective cache is set 
to only 200MB, the query planner is liable to opt for the more I/O efficient 
sequential scan."


thanks,
kailash

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* postgres optimization
@ 2005-07-29 13:24 Kailash Vyas <kailash.vyas@gmail.com>
  2005-07-29 15:47 ` Re: postgres optimization Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Kailash Vyas @ 2005-07-29 13:24 UTC (permalink / raw)
  To: pgsql-admin; +Cc: kailash.vyas@gmail.com

hi

i am optmizing postgres database and need some help on it.
I currently have a server with 2 Gb RAM.

I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax to 
536870912 i.e 512 Mb.
should i change it to 2 Gb and how will it affect the perfomance and will 
there be any downsides to it or is it better to keep it less.

I then changed the shared buffers in config file according to this 
expression specifies in postgres manual

max connections=128
250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity


shared buffer=65277

what value should i specify for Effective Cache Size. I came across this 
article but it is slightly confusing.

"Say there is 1.5GB RAM in your machine, shared buffers are set to 32MB and 
effective cache size is set to 800MB. So if a query needs 700MB of data set, 
PostgreSQL would estimate that all the data required should be available in 
memory and would opt for more aggressive plan in terms of optimization, 
involving heavier index usage and merge joins. But if effective cache is set 
to only 200MB, the query planner is liable to opt for the more I/O efficient 
sequential scan."



thanks,
kailash

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: postgres optimization
  2005-07-29 13:24 postgres optimization Kailash Vyas <kailash.vyas@gmail.com>
@ 2005-07-29 15:47 ` Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Scott Marlowe @ 2005-07-29 15:47 UTC (permalink / raw)
  To: Kailash Vyas <kailash.vyas@gmail.com>; +Cc: pgsql-admin

On Fri, 2005-07-29 at 08:24, Kailash Vyas wrote:
> hi
> 
> i am optmizing postgres database and need some help on it.
> I currently have a server with 2 Gb RAM.
> 
> I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax
> to 536870912 i.e 512 Mb.
> should i change it to 2 Gb and how will it affect the perfomance and
> will there be any downsides to it or is it better to keep it less.

Generally speaking, you're better off letting your kernel do the
majority of CACHING, and letting postgresql do BUFFERING.  

On a linux box, the kernel will use all spare memory to cache disk
accesses automagically.  So, assuming postgresql uses up a few hundred
megs, after the machine reaches a steady state, the kernel should be
using the 1.5 gig or so left over for caching.

While the latest versions of postgresql have much improved caching
algorithms for its buffers, it still pretty much dumps the buffers when
the last backend looking at them disconnects. I.e. it's not a persistent
caching system like the kernel cache is.

> I then changed the shared buffers in config file according to this
> expression specifies in postgres manual
> 
> max connections=128
> 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or
> infinity
> 
> 
> shared buffer=65277

That's large, but not unreasonable for a machine handling a large
dataset.  I assume you do have a decent sized dataset (a gig or more) to
handle.

> what value should i specify for Effective Cache Size. I came across 
> this article but it is slightly confusing.

Effective cache size just tells the query planner about how much memory
the OS is using to cache your dataset.

Bring the machine up, run lots of queries, and check the cache and
buffers with top, and there's your amount.  divide by 8k to get the
setting for effective cache size.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-07-29 15:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-29 11:34 postgres optimization Kailash Vyas <kailash.vyas@gmail.com>
2005-07-29 13:24 postgres optimization Kailash Vyas <kailash.vyas@gmail.com>
2005-07-29 15:47 ` Scott Marlowe <smarlowe@g2switchworks.com>

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