Thread: Server Configuration
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
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
wal_buffers = 64 # min 4, 8KB each
commit_delay = 0 # range 0-100000, in microseconds
commit_siblings = 50 # range 1-1000
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 ?
Thanks for any advice,
Andy
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
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
wal_buffers = 64 # min 4, 8KB each
commit_delay = 0 # range 0-100000, in microseconds
commit_siblings = 50 # range 1-1000
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 ?
Thanks for any advice,
Andy
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?
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
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:Depending on how many connections you're handling and how much memory
> 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
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.Maybe a little high. most people find that 10 is just fine to keep
> 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
vacuum from slamming your I/O bandwidth.Setting a short commit delay may allow for more siblings to get
> wal_buffers = 64 # min 4, 8KB each
> commit_delay = 0 # range 0-100000, in
> microseconds
> commit_siblings = 50 # range 1-1000
committed together.Sure. What's your max connections set to?
> 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
> ?
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? > >
Hi,
I have already done so ;-)
Also looking over the postgresql.conf file, I have changed the checkpoint_segments to 128. From what i understood of the of the PerfList page, this should not effect performance (only use up HD space), and the write performance of the database is OK with this setting.
Cheers,
Andy
I have already done so ;-)
Also looking over the postgresql.conf file, I have changed the checkpoint_segments to 128. From what i understood of the of the PerfList page, this should not effect performance (only use up HD space), and the write performance of the database is OK with this setting.
Cheers,
Andy
2008/5/21 Scott Marlowe <scott.marlowe@gmail.com>:
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?
>
>