On Sun, Sep 11, 2011 at 1:50 PM, Ogden <lists@xxxxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
