Server Configuration

On Tue, May 20, 2008 at 8:14 AM, Andy Dale <andy.dale@gmail.com> wrote:

Hi,

I am currently trying to tweak Postgresql 8.1, to improve the overall
performance of the database. I have read over the following page/artical
http://www.powerpostgresql.com/PerfList/, however at the moment (not for
much longer hopefully) my hands aree tied when it comes to altering the
kernel parameters, and thus allocating more shared buffers.

I have read over the rest of the artical and adjusted some of the other
values mentioned. The values I have altered are:

work_mem = 33554 # min 64, size in KB

Depending on how many connections you're handling and how much memory
you have, this might be a little large, but it's not terrible. Make
sure you aren't running your machine low on spare memory, as this can
cause the machine to start swapping and make it run slower.

maintenance_work_mem = 33554 # min 1024, size in KB
max_fsm_pages = 100000 # min max_fsm_relations*16, 6

bytes

each

vacuum_cost_delay = 50 # 0-1000 milliseconds

Maybe a little high. most people find that 10 is just fine to keep
vacuum from slamming your I/O bandwidth.

wal_buffers = 64 # min 4, 8KB each
commit_delay = 0 # range 0-100000, in
microseconds
commit_siblings = 50 # range 1-1000

Setting a short commit delay may allow for more siblings to get
committed together.

effective_cache_size = 33333000 # typically 8KB each

autovacuum = on # enable autovacuum

subprocess?

autovacuum_naptime = 30 # time between autovacuum runs, in
secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before

vacuum

autovacuum_analyze_threshold = 100 # min # of tuple updates before
analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before

vacuum

autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
analyze

Is it ok to have these settings with increasing the amount of shared

buffers

?

Sure. What's your max connections set to?

On Wed, May 21, 2008 at 12:06 AM, Andy Dale <andy.dale@gmail.com> wrote:

Hi,

I currently have max_connections set to 300, however if i think about it we
will never have that many connections (more like 50 - 100 at most).

Cheers,

Andy

2008/5/20 Scott Marlowe <scott.marlowe@gmail.com>:

On Tue, May 20, 2008 at 8:14 AM, Andy Dale <andy.dale@gmail.com> wrote:

Hi,

I am currently trying to tweak Postgresql 8.1, to improve the overall
performance of the database. I have read over the following
page/artical
http://www.powerpostgresql.com/PerfList/, however at the moment (not for
much longer hopefully) my hands aree tied when it comes to altering the
kernel parameters, and thus allocating more shared buffers.

I have read over the rest of the artical and adjusted some of the other
values mentioned. The values I have altered are:

work_mem = 33554 # min 64, size in KB

Depending on how many connections you're handling and how much memory
you have, this might be a little large, but it's not terrible. Make
sure you aren't running your machine low on spare memory, as this can
cause the machine to start swapping and make it run slower.

maintenance_work_mem = 33554 # min 1024, size in KB
max_fsm_pages = 100000 # min max_fsm_relations*16, 6
bytes
each

vacuum_cost_delay = 50 # 0-1000 milliseconds

Maybe a little high. most people find that 10 is just fine to keep
vacuum from slamming your I/O bandwidth.

wal_buffers = 64 # min 4, 8KB each
commit_delay = 0 # range 0-100000, in
microseconds
commit_siblings = 50 # range 1-1000

Setting a short commit delay may allow for more siblings to get
committed together.

effective_cache_size = 33333000 # typically 8KB each

autovacuum = on # enable autovacuum
subprocess?
autovacuum_naptime = 30 # time between autovacuum runs, in
secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before
vacuum
autovacuum_analyze_threshold = 100 # min # of tuple updates before
analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
analyze

Is it ok to have these settings with increasing the amount of shared
buffers
?

Sure. What's your max connections set to?

Then you should set it to 100 or so. It helps to keep the number of
connections down to something reasonable.

On Wed, May 21, 2008 at 12:06 AM, Andy Dale <andy.dale@gmail.com> wrote:

Hi,

I currently have max_connections set to 300, however if i think about it

we

will never have that many connections (more like 50 - 100 at most).

Cheers,

Andy

2008/5/20 Scott Marlowe <scott.marlowe@gmail.com>:

On Tue, May 20, 2008 at 8:14 AM, Andy Dale <andy.dale@gmail.com> wrote:

Hi,

I am currently trying to tweak Postgresql 8.1, to improve the overall
performance of the database. I have read over the following
page/artical
http://www.powerpostgresql.com/PerfList/, however at the moment (not

for

much longer hopefully) my hands aree tied when it comes to altering

the

kernel parameters, and thus allocating more shared buffers.

I have read over the rest of the artical and adjusted some of the

other

values mentioned. The values I have altered are:

work_mem = 33554 # min 64, size in KB

Depending on how many connections you're handling and how much memory
you have, this might be a little large, but it's not terrible. Make
sure you aren't running your machine low on spare memory, as this can
cause the machine to start swapping and make it run slower.

maintenance_work_mem = 33554 # min 1024, size in KB
max_fsm_pages = 100000 # min max_fsm_relations*16, 6
bytes
each

vacuum_cost_delay = 50 # 0-1000 milliseconds

Maybe a little high. most people find that 10 is just fine to keep
vacuum from slamming your I/O bandwidth.

wal_buffers = 64 # min 4, 8KB each
commit_delay = 0 # range 0-100000, in
microseconds
commit_siblings = 50 # range 1-1000

Setting a short commit delay may allow for more siblings to get
committed together.

effective_cache_size = 33333000 # typically 8KB each

autovacuum = on # enable autovacuum
subprocess?
autovacuum_naptime = 30 # time between autovacuum runs,

in

secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before
vacuum
autovacuum_analyze_threshold = 100 # min # of tuple updates before
analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
analyze

Is it ok to have these settings with increasing the amount of shared
buffers
?

Sure. What's your max connections set to?