Thread: Config help
Hi I need some help with our postgresql.conf file. I would appreciate if someone could look at the values and tell me if it looks alright or if I need to change anything. The db server has 4 GB of memory and one quad core CPU (2,53 GHz). The hard drives is on a iSCSI array and is configured as follows: DB data: 4 x SAS (10.000 rpm) disks in RAID 10 DB WAL: 2 x SAS (10.000 rpm) disks in RAID 1 OS: Linux (Debian Lenny) DB: PostgreSQL 8.4 The DB is used by a website. It has 75 tables and about a total of 10 mill rows. The total size of the DB data (data+indexes?) is reported to be about 4 GB when I use the \l+ command in version 8.4. I have used the following link as a guide: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server This is the changes I have done to the default postgresql.conf file: shared_buffers = 2048MB work_mem = 6MB wal_buffers = 256kB checkpoint_segments = 20 random_page_cost = 3.0 default_statistics_target = 50 Should I change the default value of temp_buffers or maintenance_work_mem as well, and what value should I choose? Is there any other values that should be changed from the default? And another question: Is there a way to find out the maximum simultaneous connections that has been used? I think that I could reduce the max number of connection to save some memory. Regards Bjørn Håkon
On Sun, Nov 15, 2009 at 2:43 PM, BuyAndRead Test <test@buyandread.com> wrote: > Hi > > I need some help with our postgresql.conf file. I would appreciate if > someone could look at the values and tell me if it looks alright or if I > need to change anything. > > The db server has 4 GB of memory and one quad core CPU (2,53 GHz). > The hard drives is on a iSCSI array and is configured as follows: > DB data: 4 x SAS (10.000 rpm) disks in RAID 10 > DB WAL: 2 x SAS (10.000 rpm) disks in RAID 1 Is there a battery backed cache in there somewhere? That would help on handling high write loads. > OS: Linux (Debian Lenny) > DB: PostgreSQL 8.4 > > The DB is used by a website. It has 75 tables and about a total of 10 mill > rows. The total size of the DB data (data+indexes?) is reported to be about > 4 GB when I use the \l+ command in version 8.4. The cheapest performance boost would be more memory. Going to 8Gigs would let the whole db get cached and leave enough memory over for sorts and OS etc. > I have used the following link as a guide: > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > This is the changes I have done to the default postgresql.conf file: > shared_buffers = 2048MB A little high for a machine with only 4G ram. With 8 G if you allocate 4G for share_buffers you'd leave 4G for OS and pg. Here you're only leaving 2G. > work_mem = 6MB Depending on your workload it might be better to raise this and lower shared_buffers. > wal_buffers = 256kB > checkpoint_segments = 20 > random_page_cost = 3.0 > default_statistics_target = 50 The new default is 100, I'd tend to stick with that unless you have very uniform data. > Should I change the default value of temp_buffers or maintenance_work_mem as > well, and what value should I choose? Is there any other values that should > be changed from the default? Always consider cranking up maint work mem because not many things use it and the things that do can really use it. > And another question: Is there a way to find out the maximum simultaneous > connections that has been used? I think that I could reduce the max number > of connection to save some memory. You'd really need to track that yourself with some kind of simple script. (bash) while true; do psql mydb -c "select count(*) from pg_stat_activity" ;sleep 60;done | tee myconn.log or something like that.
Thanks for the quick and helpful reply. Yes, the storage array has a battery backed cache, its a Dell PowerVault MD3000i, with dual controllers. This is a virtual server, so I could give it as much as 8 GB of memory if this will give much higher performance. What should shared_buffere be set to if I use 8 GB, as much as 4 GB? -Bjørn > -----Opprinnelig melding----- > Fra: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] På vegne av Scott Marlowe > Sendt: 15. november 2009 23:21 > Til: BuyAndRead Test > Kopi: pgsql-general@postgresql.org > Emne: Re: [GENERAL] Config help > > On Sun, Nov 15, 2009 at 2:43 PM, BuyAndRead Test <test@buyandread.com> > wrote: > > Hi > > > > I need some help with our postgresql.conf file. I would appreciate if > > someone could look at the values and tell me if it looks alright or > if I > > need to change anything. > > > > The db server has 4 GB of memory and one quad core CPU (2,53 GHz). > > The hard drives is on a iSCSI array and is configured as follows: > > DB data: 4 x SAS (10.000 rpm) disks in RAID 10 > > DB WAL: 2 x SAS (10.000 rpm) disks in RAID 1 > > Is there a battery backed cache in there somewhere? That would help > on handling high write loads. > > > OS: Linux (Debian Lenny) > > DB: PostgreSQL 8.4 > > > > The DB is used by a website. It has 75 tables and about a total of 10 > mill > > rows. The total size of the DB data (data+indexes?) is reported to be > about > > 4 GB when I use the \l+ command in version 8.4. > > The cheapest performance boost would be more memory. Going to 8Gigs > would let the whole db get cached and leave enough memory over for > sorts and OS etc. > > > I have used the following link as a guide: > > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > > This is the changes I have done to the default postgresql.conf file: > > shared_buffers = 2048MB > > A little high for a machine with only 4G ram. With 8 G if you > allocate 4G for share_buffers you'd leave 4G for OS and pg. Here > you're only leaving 2G. > > > work_mem = 6MB > > Depending on your workload it might be better to raise this and lower > shared_buffers. > > > wal_buffers = 256kB > > checkpoint_segments = 20 > > random_page_cost = 3.0 > > default_statistics_target = 50 > > The new default is 100, I'd tend to stick with that unless you have > very uniform data. > > > Should I change the default value of temp_buffers or > maintenance_work_mem as > > well, and what value should I choose? Is there any other values that > should > > be changed from the default? > > Always consider cranking up maint work mem because not many things use > it and the things that do can really use it. > > > And another question: Is there a way to find out the maximum > simultaneous > > connections that has been used? I think that I could reduce the max > number > > of connection to save some memory. > > You'd really need to track that yourself with some kind of simple > script. (bash) > > while true; do psql mydb -c "select count(*) from pg_stat_activity" > ;sleep 60;done | tee myconn.log > > or something like that. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.425 / Virus Database: 270.14.64/2501 - Release Date: > 11/14/09 19:42:00
BuyAndRead Test wrote: > This is a virtual server, so I could give it as much as 8 GB of memory if > this will give much higher performance. What should shared_buffere be set to > if I use 8 GB, as much as 4 GB? > I'd keep it around 1-2GB shared_buffers, and let the rest of the memory be used as file system cache. postgres works quite happily that way.
BuyAndRead Test wrote: >> This is a virtual server, so I could give it as much as 8 GB of memory if >> this will give much higher performance. What should shared_buffere be >> set to >> if I use 8 GB, as much as 4 GB? John R Pierce wrote: > I'd keep it around 1-2GB shared_buffers, and let the rest of the memory > be used as file system cache. postgres works quite happily that way. From what I understand, database tuning is one of the Dark Arts. PG is unique in that it's enterprise-grade but that standard settings work well across a wide range of usage scenarios. If you are dealing with unusually large numbers of connections and/or unusually large working sets, I'm guessing as you approach terabyte-scale dbs and up, it pays to go to even larger shared_buffers and work_mem and do other arcane tuning magic. -- Lew