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