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: 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
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
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
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
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
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
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
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 w
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
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
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
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