Re: Config help - Mailing list pgsql-general

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@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