Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)
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=4 # ~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
[EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmmax
33554432
[EMAIL PROTECTED]:~/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 0x200 /* 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)
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=4 # ~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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] postgresql-8.0.1 performance tuning
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
Re: [PERFORM] postgresql-8.0.1 performance tuning
Ups, i'm sorry. i've set the following values: postgresql.conf: shared_buffers = 7 effective_cache_size= 1744762 work_mem= 32768 maintenance_work_mem= 262144 max_fsm_pages = 20 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 = 7 effective_cache_size= 1744762 work_mem= 32768 maintenance_work_mem= 262144 max_fsm_pages = 20 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
SHMMAX / SHMALL Was (Re: [PERFORM] postgresql-8.0.1 performance tuning)
On 6/1/05, Mark Kirkwood [EMAIL PROTECTED] 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 [EMAIL PROTECTED]:~/data$ cat /proc/sys/kernel/shmmax 33554432 [EMAIL PROTECTED]:~/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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 / 1upm / sca) /dev/sdb is in raid 10 (4x 35GB / 1upm / 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 = 2 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= 1 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 =:- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to
Re: [PERFORM] postgresql-8.0.1 performance tuning
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/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 / 1upm / sca) /dev/sdb is in raid 10 (4x 35GB / 1upm / 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 = 2 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= 1 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 =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] postgresql-8.0.1 performance tuning
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 / 1upm / sca) /dev/sdb is in raid 10 (4x 35GB / 1upm / 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 = 2 20 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= 1 4 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
