Re: PostgreSQL performance tweaking on new hardware

On Sun, Sep 11, 2011 at 1:50 PM, Ogden <lists(at)darkstatic(dot)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 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.