Re: Comparative tps question

On 28/11/2012 19:21, Merlin Moncure wrote:
> 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.
I must have misread the numbers before when using bonnie++, run it again
and getting 1.3Gb/s read and 700Mb/s write which looks more promising.
In terms of vmstat:
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy
id wa
  5  1      0 275800    564 62541220    0    0 346904 259208 18110
12013  7  3 86  5
and iostat
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
            8.97    0.00    3.95    2.04    0.00   85.03
Device:            tps    kB_read/s    kB_wrtn/s    kB_read kB_wrtn
sdc            4716.00   1271456.00         0.00    1271456 0

obviously the figures varied for read/write speed during the tests. but
iowait averaged about 3% with the system about 85-90% idle. Oddly bonnie
reports near 80% cpu use during the test which seems high?

The H710 is capable of using 6Gbps sata drives although the intel ones
are limited to 3Gbps, given the above results, the io performance looks
to be ok?
>> 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 figured they may influence the planner, caching of the queries. Of the
ones you list only this is changed:
wal_level=hot_standby

> *) 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.
io_wait is typically <20% which is worse than for bonnie.
vmstat typical figures are during pgbench are
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy
id wa
30  1      0 261900  44340 62650808    0    0 88348 74500 103544 175006
53 20 21  6

and iostat (sda is the wal device)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           52.80    0.00   17.94   12.22    0.00   17.03

Device:            tps    kB_read/s    kB_wrtn/s    kB_read kB_wrtn
sda            2544.00         0.00     66432.00          0 132864
sdc            4153.00    132848.00       136.00     265696 272

I noticed that the system values are usually in the 20% region, could
this be the locks? btw pgbench is running on the db server not a client
- would that influence things dramatically.

> *) another interesting test to run is large scaling factor (ideally,
> at least 2x ram) read only test via pgbench -S. merlin
Might give that a go when I next get a chance to run the tests...



John