pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql-8.0.1 performance tuning
13+ messages / 5 participants
[nested] [flat]
* postgresql-8.0.1 performance tuning
@ 2005-05-27 13:41 Martin Fandel <martin.fandel@alphyra-evs.de>
0 siblings, 7 replies; 13+ messages in thread
From: Martin Fandel @ 2005-05-27 13:41 UTC (permalink / raw)
To: pgsql-performance
Hi @ all,
i'm trying to tune my postgresql-db but i don't know if the values are
right
set.
I use the following environment for the postgres-db:
######### Hardware ############
cpu: 2x P4 3Ghz
ram: 1024MB DDR 266Mhz
partitions:
/dev/sda3 23G 9,6G 13G 44% /
/dev/sda1 11G 156M 9,9G 2% /var
/dev/sdb1 69G 13G 57G 19% /var/lib/pgsql
/dev/sda is in raid 1 (2x 35GB / 10000upm / sca)
/dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
######### /Hardware ############
######### Config ############
/etc/sysctl.conf:
kernel.shmall = 786432000
kernel.shmmax = 786432000
/etc/fstab:
/dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback
1 2
/var/lib/pgsql/data/postgresql.conf
superuser_reserved_connections = 2
shared_buffers = 3000
work_mem = 131072
maintenance_work_mem = 131072
max_stack_depth = 2048
max_fsm_pages = 20000
max_fsm_relations = 1000
max_files_per_process = 1000
vacuum_cost_delay = 10
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
bgwriter_delay = 200
bgwriter_percent = 1
bgwriter_maxpages = 100
fsync = true
wal_sync_method = fsync
wal_buffers = 64
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 256
checkpoint_timeout = 900
checkpoint_warning = 30
effective_cache_size = 10000
random_page_cost = 4
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.001
cpu_operator_cost = 0.0025
geqo = true
geqo_threshold = 12
geqo_effort = 5
geqo_pool_size = 0
geqo_generations = 0
geqo_selection_bias = 2.0
deadlock_timeout = 1000
max_locks_per_transaction = 64
######### /Config ############
######### Transactions ############
we have about 115-300 transactions/min in about 65 tables.
######### /Transactions ############
I'm really new at using postgres. So i need some experience to set this
parameters in the postgresql- and the system-config. I can't find
standard
calculations for this. :/ The postgresql-documentation doesn't help me
to
set the best values for this.
The database must be high-availble. I configured rsync to sync the
complete
/var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
make the
dumps of the database to improve the performance of the master-db.
In my tests the synchronization works fine. I synchronised the hole
directory
and restarted the database of the hotstandby. While restarting,
postgresql turned
back the old (not archived) wals and the database of my hotstandby was
consistent. Is this solution recommended? Or must i use archived wal's
with
real system-snapshots?
best regards,
Martin Fandel
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-05-31 18:46 John A Meinel <john@arbash-meinel.com>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: John A Meinel @ 2005-05-31 18:46 UTC (permalink / raw)
To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-performance
Martin Fandel wrote:
> Hi @ all,
>
> i'm trying to tune my postgresql-db but i don't know if the values are
> right
> set.
>
> I use the following environment for the postgres-db:
>
> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz
>
> partitions:
> /dev/sda3 23G 9,6G 13G 44% /
> /dev/sda1 11G 156M 9,9G 2% /var
> /dev/sdb1 69G 13G 57G 19% /var/lib/pgsql
>
> /dev/sda is in raid 1 (2x 35GB / 10000upm / sca)
> /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
> ######### /Hardware ############
You probably want to put the pg_xlog file onto /dev/sda rather than
having it in /dev/sdb. Having it separate from the data usually boosts
performance a lot. I believe you can just mv it to a different
directory, and then recreate it as a symlink. (Stop the database first :)
>
> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000
>
Not really sure about these two.
> /etc/fstab:
> /dev/sdb1 /var/lib/pgsql reiserfs
> acl,user_xattr,noatime,data=writeback 1 2
>
Seems decent.
> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections = 2
> shared_buffers = 3000
> work_mem = 131072
> maintenance_work_mem = 131072
These both seem pretty large. But it depends on how many concurrent
connections doing sorting/hashing/etc you expect. If you are only
expecting 1 connection, these are probably fine. Otherwise with 1GB of
RAM I would probably make work_mem more like 4096/8192.
Remember, running out of work_mem means postgres will spill to disk,
slowing that query. Running out of RAM causes the system to swap, making
everything slow.
> max_stack_depth = 2048
> max_fsm_pages = 20000
> max_fsm_relations = 1000
> max_files_per_process = 1000
> vacuum_cost_delay = 10
> vacuum_cost_page_hit = 1
> vacuum_cost_page_miss = 10
> vacuum_cost_page_dirty = 20
> vacuum_cost_limit = 200
> bgwriter_delay = 200
> bgwriter_percent = 1
> bgwriter_maxpages = 100
> fsync = true
> wal_sync_method = fsync
> wal_buffers = 64
> commit_delay = 0
> commit_siblings = 5
> checkpoint_segments = 256
> checkpoint_timeout = 900
> checkpoint_warning = 30
> effective_cache_size = 10000
> random_page_cost = 4
> cpu_tuple_cost = 0.01
> cpu_index_tuple_cost = 0.001
> cpu_operator_cost = 0.0025
> geqo = true
> geqo_threshold = 12
> geqo_effort = 5
> geqo_pool_size = 0
> geqo_generations = 0
> geqo_selection_bias = 2.0
> deadlock_timeout = 1000
> max_locks_per_transaction = 64
> ######### /Config ############
>
> ######### Transactions ############
> we have about 115-300 transactions/min in about 65 tables.
> ######### /Transactions ############
>
> I'm really new at using postgres. So i need some experience to set this
> parameters in the postgresql- and the system-config. I can't find standard
> calculations for this. :/ The postgresql-documentation doesn't help me to
> set the best values for this.
>
> The database must be high-availble. I configured rsync to sync the
> complete
> /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
> make the
> dumps of the database to improve the performance of the master-db.
>
I didn't think an rsync was completely valid. Probably you should look
more into Slony.
http://slony.info
It is a single-master asynchronous replication system. I believe it is
pretty easy to setup, and does what you really want.
> In my tests the synchronization works fine. I synchronised the hole
> directory
> and restarted the database of the hotstandby. While restarting,
> postgresql turned
> back the old (not archived) wals and the database of my hotstandby was
> consistent. Is this solution recommended? Or must i use archived wal's
> with
> real system-snapshots?
>
> best regards,
>
> Martin Fandel
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 05:30 Cosimo Streppone <cosimo@streppone.it>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 2 replies; 13+ messages in thread
From: Cosimo Streppone @ 2005-06-01 05:30 UTC (permalink / raw)
To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-performance
Martin Fandel wrote:
> i'm trying to tune my postgresql-db but i don't know if the values are
> I use the following environment for the postgres-db:
I assumed you're running Linux here, you don't mention it.
> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz
I think 1Gb RAM is quite minimal, nowadays.
Read below.
> partitions:
> /dev/sda3 23G 9,6G 13G 44% /
> /dev/sda1 11G 156M 9,9G 2% /var
> /dev/sdb1 69G 13G 57G 19% /var/lib/pgsql
>
> /dev/sda is in raid 1 (2x 35GB / 10000upm / sca)
> /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
I've seen good performance boost (and machine load lowered)
switching to 15k rpm disks.
> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000
I think you have a problem here.
kernel.shmmax should *not* be set to an amount of RAM, but
to maximum number of shared memory pages, which on a typical linux system
is 4kb. Google around:
http://www.google.com/search?q=kernel.shmall+tuning+postgresql+shared+memory
> /etc/fstab:
> /dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback 1 2
I use similar settings on ext3 (which I'm told it is slower than reiser
or xfs or jfs).
I indicate the values I use for a machine with 4Gb RAM
and more 15 krpm disks but layout similar to yours.
(3 x RAID1 arrays for os, logs, ... and 1 x RAID10 array with 12 disks)
For Pg configuration (others please comment on these values,
it is invaluable to have feedback from this list).
> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections = 2
> shared_buffers = 3000
16384
> work_mem = 131072
32768
> maintenance_work_mem = 131072
262144
> max_fsm_pages = 20000
200000
> fsync = true
false
> commit_delay = 0
> commit_siblings = 5
If you have an high transactions volume, you should
really investigate on these ones.
> effective_cache_size = 10000
40000
> random_page_cost = 4
Check out for unwanted "seq scans". If you have really fast
disks, you should experiment lowering a little this parameter.
> max_locks_per_transaction = 64
512
> I'm really new at using postgres. So i need some experience to set this
> parameters in the postgresql- and the system-config. I can't find standard
> calculations for this. :/ The postgresql-documentation doesn't help me to
> set the best values for this.
There's no such thing as "standard calculations" :-)
> The database must be high-availble. I configured rsync to sync the complete
> /var/lib/pgsql-directory to my hot-standby
> [...]
> In my tests the synchronization works fine. I synchronised the hole
> consistent.
> [...]
> Is this solution recommended? Or must i use archived wal's with
> real system-snapshots?
In some situations, I also used rsync to do the job.
Obviously, always stop the postmaster before syncing.
Maybe you can look at "slony", if you haven't yet.
http://www.slony.info
--
Cosimo
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 06:33 Mark Kirkwood <markir@paradise.net.nz>
parent: Cosimo Streppone <cosimo@streppone.it>
1 sibling, 1 reply; 13+ messages in thread
From: Mark Kirkwood @ 2005-06-01 06:33 UTC (permalink / raw)
To: Cosimo Streppone <cosimo@streppone.it>; +Cc: Martin Fandel <martin.fandel@alphyra-evs.de>; pgsql-performance
Cosimo Streppone wrote:
> ######### Config ############
>> /etc/sysctl.conf:
>> kernel.shmall = 786432000
>> kernel.shmmax = 786432000
>
>
> I think you have a problem here.
> kernel.shmmax should *not* be set to an amount of RAM, but
> to maximum number of shared memory pages, which on a typical linux system
> is 4kb. Google around:
>
>
>
This is somewhat confusing :
kernel.shmmax is in bytes (max single segment size)
kernel.shmall is in (4k) pages (max system wide allocated segment pages)
cheers
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 06:44 Cosimo Streppone <cosimo@streppone.it>
parent: Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 13+ messages in thread
From: Cosimo Streppone @ 2005-06-01 06:44 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: pgsql-performance
Mark Kirkwood ha scritto:
> Cosimo Streppone wrote:
>
>> ######### Config ############
>>
>>> /etc/sysctl.conf:
>>> kernel.shmall = 786432000
>>> kernel.shmmax = 786432000
>>
>> I think you have a problem here.
>> kernel.shmmax should *not* be set to an amount of RAM, but
Sorry, I thought "shmall" but written "shmmax".
Thanks Mark!
--
Cosimo
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 07:22 Mark Kirkwood <markir@paradise.net.nz>
parent: Cosimo Streppone <cosimo@streppone.it>
0 siblings, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2005-06-01 07:22 UTC (permalink / raw)
To: Cosimo Streppone <cosimo@streppone.it>; +Cc: pgsql-performance
Cosimo Streppone wrote:
> Mark Kirkwood ha scritto:
>
>> Cosimo Streppone wrote:
>>
>>> ######### Config ############
>>>
>>>> /etc/sysctl.conf:
>>>> kernel.shmall = 786432000
>>>> kernel.shmmax = 786432000
>>>
>>>
>>> I think you have a problem here.
>>> kernel.shmmax should *not* be set to an amount of RAM, but
>
>
> Sorry, I thought "shmall" but written "shmmax".
> Thanks Mark!
>
Hehe - happens to me all the time!
On the shmall front - altho there is *probably* no real performance
impact setting it to the same as shmmax (i.e. allowing 4096 allocations
of size shmmax!), it is overkill. In addition it does allow for a DOS by
a program that allocates thousands of segments (or somehow starts
thousands of Pg servers on different ports...)!
For a dedicated Pg server I would size shmall using a calculation along
the lines of:
shmall = (no. of postgresql servers) * (shmmax/4096)
If there are other daemons on the box that need to use shared memory,
then add their likely requirements to shmall too!
cheers
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 08:50 Martin Fandel <martin.fandel@alphyra-evs.de>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: Martin Fandel @ 2005-06-01 08:50 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
Hi John,
thank you very much for the answer :). I moved the pg_xlog to another
partition and made a symlink to it. Know the database is much more
faster than before. A sample select which was finished in 68seconds
before, is now finished in 58seconds :).
I will test the other changes today also and will write a feedback
after testing. :)
Thanks a lot. I'm very confusing to tuning the postgresql-db. #:-)
best regards
Martin
Am Dienstag, den 31.05.2005, 13:46 -0500 schrieb John A Meinel:
> Martin Fandel wrote:
>
> > Hi @ all,
> >
> > i'm trying to tune my postgresql-db but i don't know if the values are
> > right
> > set.
> >
> > I use the following environment for the postgres-db:
> >
> > ######### Hardware ############
> > cpu: 2x P4 3Ghz
> > ram: 1024MB DDR 266Mhz
> >
> > partitions:
> > /dev/sda3 23G 9,6G 13G 44% /
> > /dev/sda1 11G 156M 9,9G 2% /var
> > /dev/sdb1 69G 13G 57G 19% /var/lib/pgsql
> >
> > /dev/sda is in raid 1 (2x 35GB / 10000upm / sca)
> > /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
> > ######### /Hardware ############
>
> You probably want to put the pg_xlog file onto /dev/sda rather than
> having it in /dev/sdb. Having it separate from the data usually boosts
> performance a lot. I believe you can just mv it to a different
> directory, and then recreate it as a symlink. (Stop the database first :)
>
> >
> > ######### Config ############
> > /etc/sysctl.conf:
> > kernel.shmall = 786432000
> > kernel.shmmax = 786432000
> >
> Not really sure about these two.
>
> > /etc/fstab:
> > /dev/sdb1 /var/lib/pgsql reiserfs
> > acl,user_xattr,noatime,data=writeback 1 2
> >
> Seems decent.
>
> > /var/lib/pgsql/data/postgresql.conf
> > superuser_reserved_connections = 2
> > shared_buffers = 3000
> > work_mem = 131072
> > maintenance_work_mem = 131072
>
> These both seem pretty large. But it depends on how many concurrent
> connections doing sorting/hashing/etc you expect. If you are only
> expecting 1 connection, these are probably fine. Otherwise with 1GB of
> RAM I would probably make work_mem more like 4096/8192.
> Remember, running out of work_mem means postgres will spill to disk,
> slowing that query. Running out of RAM causes the system to swap, making
> everything slow.
>
> > max_stack_depth = 2048
> > max_fsm_pages = 20000
> > max_fsm_relations = 1000
> > max_files_per_process = 1000
> > vacuum_cost_delay = 10
> > vacuum_cost_page_hit = 1
> > vacuum_cost_page_miss = 10
> > vacuum_cost_page_dirty = 20
> > vacuum_cost_limit = 200
> > bgwriter_delay = 200
> > bgwriter_percent = 1
> > bgwriter_maxpages = 100
> > fsync = true
> > wal_sync_method = fsync
> > wal_buffers = 64
> > commit_delay = 0
> > commit_siblings = 5
> > checkpoint_segments = 256
> > checkpoint_timeout = 900
> > checkpoint_warning = 30
> > effective_cache_size = 10000
> > random_page_cost = 4
> > cpu_tuple_cost = 0.01
> > cpu_index_tuple_cost = 0.001
> > cpu_operator_cost = 0.0025
> > geqo = true
> > geqo_threshold = 12
> > geqo_effort = 5
> > geqo_pool_size = 0
> > geqo_generations = 0
> > geqo_selection_bias = 2.0
> > deadlock_timeout = 1000
> > max_locks_per_transaction = 64
> > ######### /Config ############
> >
> > ######### Transactions ############
> > we have about 115-300 transactions/min in about 65 tables.
> > ######### /Transactions ############
> >
> > I'm really new at using postgres. So i need some experience to set this
> > parameters in the postgresql- and the system-config. I can't find standard
> > calculations for this. :/ The postgresql-documentation doesn't help me to
> > set the best values for this.
> >
> > The database must be high-availble. I configured rsync to sync the
> > complete
> > /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
> > make the
> > dumps of the database to improve the performance of the master-db.
> >
> I didn't think an rsync was completely valid. Probably you should look
> more into Slony.
> http://slony.info
>
> It is a single-master asynchronous replication system. I believe it is
> pretty easy to setup, and does what you really want.
>
> > In my tests the synchronization works fine. I synchronised the hole
> > directory
> > and restarted the database of the hotstandby. While restarting,
> > postgresql turned
> > back the old (not archived) wals and the database of my hotstandby was
> > consistent. Is this solution recommended? Or must i use archived wal's
> > with
> > real system-snapshots?
> >
> > best regards,
> >
> > Martin Fandel
>
> John
> =:->
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 09:57 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Cosimo Streppone <cosimo@streppone.it>
1 sibling, 1 reply; 13+ messages in thread
From: Steinar H. Gunderson @ 2005-06-01 09:57 UTC (permalink / raw)
To: pgsql-performance
On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
>>fsync = true
> false
Just setting fsync=false without considering the implications is a _bad_
idea...
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 10:22 Cosimo Streppone <cosimo@streppone.it>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 13+ messages in thread
From: Cosimo Streppone @ 2005-06-01 10:22 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
Steinar wrote:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
>
> > > fsync = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...
I totally agree on that.
--
Cosimo
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-01 10:26 Martin Fandel <martin.fandel@alphyra-evs.de>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: Martin Fandel @ 2005-06-01 10:26 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
Yes, i think also that this setting should be enabled :).
Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...
>
> /* Steinar */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-02 12:50 Martin Fandel <martin.fandel@alphyra-evs.de>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: Martin Fandel @ 2005-06-02 12:50 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
Hi,
hmmm i don't understand which are the best values for shmmax and shmall.
I've googled around but every site says something different.
I've 2GB of RAM now and set it to:
kernel.shmmax=715827882
kernel.shmall=2097152
Is that value ok for 2GB of RAM?
I've set the shared_buffers in my postgresql.conf to 87381
(87381*8*1024 = ~715827882).
Can I use www.powerpostgresql.com as reference to set this
parameters? Or which site can i use?
Best regards,
Martin
Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync = true
> > false
>
> Just setting fsync=false without considering the implications is a
_bad_
> idea...
>
> /* Steinar */
Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...
>
> /* Steinar */
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-02 13:10 Martin Fandel <martin.fandel@alphyra-evs.de>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: Martin Fandel @ 2005-06-02 13:10 UTC (permalink / raw)
To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-performance
Ups,
i'm sorry. i've set the following values:
postgresql.conf:
shared_buffers = 70000
effective_cache_size = 1744762
work_mem = 32768
maintenance_work_mem = 262144
max_fsm_pages = 200000
sysctl.conf:
vm.swappiness=10
kernel.shmmax=715827882
kernel.shmall=2097152
Are the values ok for a 2 GB machine? I'm testing these settings
with contrib/pgbench. With this configuration i become up to 200tps
including connection establishing. Is that value ok for this hardware?:
1xP4 3Ghz (hyperthreading enabled)
2GB 266 Mhz RAM CL2.5
pg_xlog is on sda (raid1 with two 10k discs) and the database on
sdb(raid10 with four 10k discs).
My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1.
best regards,
Martin
Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel:
> Hi,
>
> hmmm i don't understand which are the best values for shmmax and shmall.
> I've googled around but every site says something different.
>
> I've 2GB of RAM now and set it to:
>
> kernel.shmmax=715827882
> kernel.shmall=2097152
>
> Is that value ok for 2GB of RAM?
>
> I've set the shared_buffers in my postgresql.conf to 87381
> (87381*8*1024 = ~715827882).
>
> Can I use www.powerpostgresql.com as reference to set this
> parameters? Or which site can i use?
>
> Best regards,
> Martin
>
> Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > >>fsync = true
> > > false
> >
> > Just setting fsync=false without considering the implications is a
> _bad_
> > idea...
> >
> > /* Steinar */
>
>
> Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > >>fsync = true
> > > false
> >
> > Just setting fsync=false without considering the implications is a _bad_
> > idea...
> >
> > /* Steinar */
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql-8.0.1 performance tuning
@ 2005-06-02 13:12 Martin Fandel <martin.fandel@alphyra-evs.de>
parent: Martin Fandel <martin.fandel@alphyra-evs.de>
6 siblings, 0 replies; 13+ messages in thread
From: Martin Fandel @ 2005-06-02 13:12 UTC (permalink / raw)
To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-performance
I've forgotten the settings for the pgbench-tests. I use 150 clients
with 5 transactions each.
Am Donnerstag, den 02.06.2005, 15:10 +0200 schrieb Martin Fandel:
> Ups,
> i'm sorry. i've set the following values:
>
> postgresql.conf:
> shared_buffers = 70000
> effective_cache_size = 1744762
> work_mem = 32768
> maintenance_work_mem = 262144
> max_fsm_pages = 200000
>
> sysctl.conf:
> vm.swappiness=10
> kernel.shmmax=715827882
> kernel.shmall=2097152
>
> Are the values ok for a 2 GB machine? I'm testing these settings
> with contrib/pgbench. With this configuration i become up to 200tps
> including connection establishing. Is that value ok for this hardware?:
>
> 1xP4 3Ghz (hyperthreading enabled)
> 2GB 266 Mhz RAM CL2.5
>
> pg_xlog is on sda (raid1 with two 10k discs) and the database on
> sdb(raid10 with four 10k discs).
>
> My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1.
>
> best regards,
> Martin
>
> Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel:
> > Hi,
> >
> > hmmm i don't understand which are the best values for shmmax and shmall.
> > I've googled around but every site says something different.
> >
> > I've 2GB of RAM now and set it to:
> >
> > kernel.shmmax=715827882
> > kernel.shmall=2097152
> >
> > Is that value ok for 2GB of RAM?
> >
> > I've set the shared_buffers in my postgresql.conf to 87381
> > (87381*8*1024 = ~715827882).
> >
> > Can I use www.powerpostgresql.com as reference to set this
> > parameters? Or which site can i use?
> >
> > Best regards,
> > Martin
> >
> > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > > >>fsync = true
> > > > false
> > >
> > > Just setting fsync=false without considering the implications is a
> > _bad_
> > > idea...
> > >
> > > /* Steinar */
> >
> >
> > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > > >>fsync = true
> > > > false
> > >
> > > Just setting fsync=false without considering the implications is a _bad_
> > > idea...
> > >
> > > /* Steinar */
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-06-02 13:12 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-27 13:41 postgresql-8.0.1 performance tuning Martin Fandel <martin.fandel@alphyra-evs.de>
2005-05-31 18:46 ` John A Meinel <john@arbash-meinel.com>
2005-06-01 05:30 ` Cosimo Streppone <cosimo@streppone.it>
2005-06-01 06:33 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-01 06:44 ` Cosimo Streppone <cosimo@streppone.it>
2005-06-01 07:22 ` Mark Kirkwood <markir@paradise.net.nz>
2005-06-01 09:57 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-01 10:22 ` Cosimo Streppone <cosimo@streppone.it>
2005-06-01 08:50 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-01 10:26 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-02 12:50 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-02 13:10 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-02 13:12 ` Martin Fandel <martin.fandel@alphyra-evs.de>
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