Thread: postgresql-8.0.1 performance tuning
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
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
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 =:->
Attachment
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
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
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
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
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 > =:-> >
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/
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
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 */
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 */
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
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 >
On 6/1/05, Mark Kirkwood <markir@paradise.net.nz> wrote: > 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) Can someone resummarize the situation with these linux parameters for the dummies? I thought I had my calculations all sorted out but now I've confused myself again. The documentation at http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html puts the same figure into both values but the posts here seem to suggest that is wrong? Or is it different on a 2.4 kernel and the documentation needs updating? In my specific case I have about 800meg of memory on a linux 2.4 kernel box. Based on the powerpostgresql.com Performance Checklist [1] and Annotated Postgresql.conf [2] I understand that: -I should have less than 1/3 of my total memory as shared_buffers -For my server 15000 is a fairly reasonable starting point for shared_buffers which is ~120MB -I have 100 max_connections. So I was going to set SHMMAX to 134217728 (ie 128 Meg) What should SHMALL be? The current system values are postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax 33554432 postgres@localhost:~/data$ cat /proc/sys/kernel/shmall 2097152 ie SHMALL seems to be 1/16 of SHMMAX Paul [1] http://www.powerpostgresql.com/PerfList/ [2] http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
Paul McGarry wrote: > Based on the powerpostgresql.com Performance Checklist [1] and > Annotated Postgresql.conf [2] I understand that: > -I should have less than 1/3 of my total memory as shared_buffers > -For my server 15000 is a fairly reasonable starting point for > shared_buffers which is ~120MB > -I have 100 max_connections. > > So I was going to set SHMMAX to 134217728 (ie 128 Meg) > > What should SHMALL be? > > The current system values are > postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax > 33554432 > postgres@localhost:~/data$ cat /proc/sys/kernel/shmall > 2097152 > > ie SHMALL seems to be 1/16 of SHMMAX > No - shmall is in 4k pages _ so this amounts to 8G! This is fine - unless you wish to decrease it in order to prevent too many shared memory applications running. BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and shmall=2097152 (was going to point you at them - but I cannot find them on the Postgresql site anymore...). There seems to be some longstanding confusion in the Linux community about the units for shmall (some incorrect documentation from Oracle on the issue does not help I am sure....) - to the point where I downloaded kernel source to check (reproducing here): linux-2.6.11.1/include/linux/shm.h:13-> #define SHMMAX 0x2000000 /* max shared seg size (bytes) */ #define SHMMIN 1 /* min shared seg size (bytes) */ #define SHMMNI 4096 /* max num of segs system wide */ #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide (pages) */ #define SHMSEG SHMMNI Hope that helps Best wishes Mark
Aah ok :)
I've set my values now as follow (2GB RAM):
SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
$1*1024/3}'`
echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
sysctl.conf:
kernel.shmmax=708329472
kernel.shmall=44270592
postgresql.conf:
max_connections=500
shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000
best regards,
Martin
Am Freitag, den 03.06.2005, 18:11 +1200 schrieb Mark Kirkwood:
> Paul McGarry wrote:
>
> > Based on the powerpostgresql.com Performance Checklist [1] and
> > Annotated Postgresql.conf [2] I understand that:
> > -I should have less than 1/3 of my total memory as shared_buffers
> > -For my server 15000 is a fairly reasonable starting point for
> > shared_buffers which is ~120MB
> > -I have 100 max_connections.
> >
> > So I was going to set SHMMAX to 134217728 (ie 128 Meg)
> >
> > What should SHMALL be?
> >
> > The current system values are
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax
> > 33554432
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmall
> > 2097152
> >
> > ie SHMALL seems to be 1/16 of SHMMAX
> >
>
> No - shmall is in 4k pages _ so this amounts to 8G! This is fine -
> unless you wish to decrease it in order to prevent too many shared
> memory applications running.
>
> BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and
> shmall=2097152 (was going to point you at them - but I cannot find them
> on the Postgresql site anymore...).
>
> There seems to be some longstanding confusion in the Linux community
> about the units for shmall (some incorrect documentation from Oracle on
> the issue does not help I am sure....) - to the point where I downloaded
> kernel source to check (reproducing here):
>
>
> linux-2.6.11.1/include/linux/shm.h:13->
>
> #define SHMMAX 0x2000000 /* max shared seg size (bytes) */
> #define SHMMIN 1 /* min shared seg size (bytes) */
> #define SHMMNI 4096 /* max num of segs system wide */
> #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide
> (pages) */
> #define SHMSEG SHMMNI
>
>
> Hope that helps
>
> Best wishes
>
> Mark
Martin Fandel wrote:
> Aah ok :)
>
> I've set my values now as follow (2GB RAM):
>
> SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
> $1*1024/3}'`
> echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
> SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
> echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
>
> sysctl.conf:
> kernel.shmmax=708329472
> kernel.shmall=44270592
>
> postgresql.conf:
> max_connections=500
> shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000
>
Hmmm - shmall set to 168G... err why? Apologies for nit picking a little
- but shmall seems unreasonably high. I can't see much reason for
setting it bigger than (physical RAM in bytes)/4096 myself. So in your
case this is 2*(1024*1024*1024)/4096 = 524288
Cheers
Mark
ok i set it to 524288. ;)
Am Freitag, den 03.06.2005, 21:10 +1200 schrieb Mark Kirkwood:
> Martin Fandel wrote:
> > Aah ok :)
> >
> > I've set my values now as follow (2GB RAM):
> >
> > SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
> > $1*1024/3}'`
> > echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
> > SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
> > echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
> >
> > sysctl.conf:
> > kernel.shmmax=708329472
> > kernel.shmall=44270592
> >
> > postgresql.conf:
> > max_connections=500
> > shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000
> >
>
> Hmmm - shmall set to 168G... err why? Apologies for nit picking a little
> - but shmall seems unreasonably high. I can't see much reason for
> setting it bigger than (physical RAM in bytes)/4096 myself. So in your
> case this is 2*(1024*1024*1024)/4096 = 524288
>
> Cheers
>
> Mark
>