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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
