PostgreSQL performance tweaking on new hardware
PostgreSQL performance tweaking on new hardware
От:
Ogden <lists@darkstatic.com>
Дата:
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
Re: PostgreSQL performance tweaking on new hardware
От:
Scott Marlowe <scott.marlowe@gmail.com>
Дата:
On Sun, Sep 11, 2011 at 1:50 PM, Ogden wrote: > 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. Well, what's your workload like? If you'd like to smooth out lots of heavy writing, then look at cranking up checkpoint_segments, increase checkpoint timeout to 2h, and play with checkpoint completion target. If you write a lot of the same rows over and over, then keep it down in the 0.5 range. If you tend to write all unique rows, then closer to 1.0 is better. We run at 0.8. As you increase checkpoint completion target, you'll increase the amount of writes that have to happen twice to the storage array, so unless you're 100% sure you don't write to the same blocks / tuples a lot, keep it below 1.0. Also if you're NOT using a battery backed caching RAID controller look into upgrading to one.
