From: John Lister <john.lister@kickstone.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative tps question
Date: Fri, 30 Nov 2012 19:59:14 +0000
Message-ID: <50B91012.7020202@kickstone.com> (raw)
In-Reply-To: <CAHyXU0zqN=xaRLM_uKZAfKWFGnSK6ROKfPMgE0rhmQCORoOD2A@mail.gmail.com>
References: <50B659EC.4080509@kickstone.com>
<CAHyXU0xasEPfQ7d8N=0zH5i_N+Giid8x2rUdNHzabSqu8mBwYA@mail.gmail.com>
<50B793C5.3070806@kickstone.com>
<CAHyXU0zqN=xaRLM_uKZAfKWFGnSK6ROKfPMgE0rhmQCORoOD2A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 29/11/2012 17:33, Merlin Moncure wrote:
> Since we have some idle cpu% here we can probably eliminate pgbench as
> a bottleneck by messing around with the -j switch. another thing we
> want to test is the "-N" switch -- this doesn't update the tellers and
> branches table which in high concurrency situations can bind you from
> locking perspective.
Using -N gives around a 15% increase in tps with no major changes in
load, etc. using more threads slightly drops the performance (as
expected with only 32 "cores"). dropping it does give a slight increase
(presumably because half the cores aren't real).
> one thing that immediately jumps out here is that your wal volume
> could be holding you up. so it's possible we may want to move wal to
> the ssd volume. if you can scrounge up a 9.2 pgbench, we can gather
> more evidence for that by running pgbench with the
> "--unlogged-tables" option, which creates the tables unlogged so that
> they are not wal logged (for the record, this causes tables to be
> truncated when not shut down in clean state).
I did notice that using -S drives the tps up to near 30K tps, so it is
possibly the wal volume, although saying that I did move the pg_xlog
directory onto the ssd array before posting to the list and the
difference wasn't significant. I'll try and repeat that when I get some
more downtime (I'm having to run the current tests while the db is live,
but under light load).
I'll have a look at using the 9.2 pgbench and see what happens.
> yeah -- this will tell us raw seek performance of ssd volume which
> presumably will be stupendous. 2x is minimum btw 10x would be more
> appropriate.
>
> since you're building a beast, other settings to explore are numa
> (http://frosty-postgres.blogspot.com/2012/08/postgresql-numa-and-zone-reclaim-mode.html)
> and dell memory bios settings that are occasionally set from the
> factory badly (see here:
> http://bleything.net/articles/postgresql-benchmarking-memory.html).
Cheers for the links, I'd already looked at the numa stuff and disabled
zone reclaim. I was looking at using the patch previously posted that
used shared mode for the master process and then local only for the
workers - excuse the terminology - but time constraints prevented that.
Made sure the box was in performance mode in the bios, unfortunately I
spotted bens blog when I was setting the box up, but didn't have time to
go through all the tests. At the time performance seemed ok (well better
than the previous box :) - but having it live for a while made me think
I or it could be doing better.
Anyway, I still think it would be nice to post tps results for compative
purposes, so if I get a minute or two I'll create a site and stick mine
on there.
John
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john.lister@kickstone.com, mmoncure@gmail.com
Subject: Re: Comparative tps question
In-Reply-To: <50B91012.7020202@kickstone.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox