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.

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

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