On Sun, Sep 11, 2011 at 1:50 PM, Ogden <lists@darkstatic.com> wrote:
> I want to thank members on this list which helped me benchmark and conclude that RAID 10 on a XFS filesystem was the
wayto go over what we had prior. PostgreSQL we have been using with Perl for the last 8 years and it has been nothing
butoutstanding 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
lotof guides on tweaking PostgreSQL as well as a book, however, I would like someone to just review the settings I have
andlet 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.
