Thanks for the quick and helpful reply. Yes, the storage array has a battery backed cache, it?s 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@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] På vegne av Scott Marlowe > Sendt: 15. november 2009 23:21 > Til: BuyAndRead Test > Kopi: pgsql-general@xxxxxxxxxxxxxx > Emne: Re: Config help > > On Sun, Nov 15, 2009 at 2:43 PM, BuyAndRead Test <test@xxxxxxxxxxxxxx> > 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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
