PostgreSQL performance tweaking on new hardware

From: Ogden <lists(at)darkstatic(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL performance tweaking on new hardware
Date: 2011-09-11 19:50:06
Message-ID: 0E99FE4C-2385-4F21-93D6-28CB40EF821F@darkstatic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I want to thank members on this list which helped me benchmark and conclude that RAID 10 on a XFS filesystem was the way to go over what we had prior. PostgreSQL we have been using with Perl for the last 8 years and it has been nothing but outstanding for us. Things have definitely worked out much better and the writes are much much faster.

Since I want the maximum performance from our new servers, I want to make sure the configuration is what is recommended. Things are running fine and queries that would take seconds prior now only take one or two. I have read a lot of guides on tweaking PostgreSQL as well as a book, however, I would like someone to just review the settings I have and let me know if it's too crazy. It's for a considerably heavy write database with a lot of calculation queries (percentages, averages, sums, etc).

This is my setup:

2 x Intel E5645 (12 Core CPU total)
64 GB Ram
RAID 10 (/var/lib/pgsql lives on it's own RAID controller) on XFS
PostgreSQL 9.0.4 on Debian Squeeze
Database size about 200Gb.

And in postgresql.conf:

max_connections = 200
shared_buffers = 8GB
temp_buffers = 128MB
work_mem = 40MB
maintenance_work_mem = 1GB

wal_buffers = 16MB

effective_cache_size = 48GB

seq_page_cost = 1.0
random_page_cost = 1.1
cpu_tuple_cost = 0.1
cpu_index_tuple_cost = 0.05
cpu_operator_cost = 0.01
default_statistics_target = 1000

With these settings, output from free -m (Megabytes):

total used free shared buffers cached
Mem: 64550 56605 7945 0 0 55907
-/+ buffers/cache: 697 63852
Swap: 7628 6 7622

top shows:
Swap: 7812088k total, 6788k used, 7805300k free, 57343264k cached

Any suggestions would be awesome.

Thank you

Ogden

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2011-09-11 20:10:03 Re: Postgres for a "data warehouse", 5-10 TB
Previous Message J Sisson 2011-09-11 19:30:23 Re: Postgres for a "data warehouse", 5-10 TB