Re: Comparative tps question - Mailing list pgsql-performance

On Wed, Nov 28, 2012 at 12:37 PM, John Lister <john.lister@kickstone.com> wrote:
> Hi, I've just been benchmarking a new box I've got and running pgbench
> yields what I thought was a slow tps count. It is dificult to find
> comparisons online of other benchmark results, I'd like to see if I have the
> box set up reasonably well.
>
> I know oracle, et al prohibit benchmark results, but was surprised that
> there doesn't seem to be any postgresql ones out there..
>
> Anyway, the machine is a Dell R720 with the data on a raid 10 using 8x intel
> 320 SSDs and a mirrored pair of 15k SAS HDDs configured for the pg_xlog,
> both on a dell H710 raid controller, in addition it has 64Gb of 1600Mhz
> memory and 2x E5-2650 processors (with HT=32 cores). The arrays are all
> setup with XFS on and tweaked as I could. The drives are 160Gb and
> overprovisioned by another 15%.
>
> I'm running postgresql 9.1 on ubuntu 12.04
>
> bonnie++ (using defaults) shows about 600MB/s sequential read/write IO on
> the main data array, this doesn't seem too bad although the specs show over
> 200MB/s should be achievable per drive.

Probably this limitation is coming from sata bus. It shouldn't be a
problem in practice.   Can you report bonnie++ seek performance?
Another possibility is the raid controller is introducing overhead
here.

> pgbench (using a scaling factor of 100 with 100 clients and 25 threads)
> gives an average of about 7200tps.
>
> Does this look acceptable? Instinctively it feels on the low side, although
> I noted that a couple of blogs show
> (http://www.fuzzy.cz/en/articles/ssd-benchmark-results-read-write-pgbench/
> and
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html)
> show around 1500tps for a single ssd, so maybe this is what is expected.
>
> The interesting param differences from the postgresql conf are:
> share_buffers=6Gb
> work_mem=64Mb
> max_stack_depth=4Mb
> random_page_cost=1.1
> cpu_tuple_cost=0.1
> cpu_index_tuple_cost=0.05
> cpu_operator_cost=0.025
> effective_cache_size=40Gb

*) none of the above settings will influence storage bound pgbench
results.  Influential settings are fsync, synchronous_commit,
wal_sync_method, wal_level, full_page_writes, wal_buffers,
wal_writer_delay, and commit_delay.  These settings are basically
managing various tradeoffs, espeically in the sense of safety vs
performance.

> I'd be happy to provide any other configs, etc assuming the tps values are
> way off the expected.

*) Very first thing we need to check is if we are storage bound (check
i/o wait) and if so where the bind up is.  Could be on the wal or heap
volume.  Another possibility is that we're lock bound which is a
completely different issue to deal with.

so we want to see top, iostat, vmstat, etc while test is happening.

*) another interesting test to run is large scaling factor (ideally,
at least 2x ram) read only test via pgbench -S.

merlin