Re: Benchmarking tools, methods

On 18 Listopad 2011, 10:55, CSS wrote:
> Hello,
>
> I'm going to be testing some new hardware (see
> http://archives.postgresql.org/pgsql-performance/2011-11/msg00230.php) and
> while I've done some very rudimentary before/after tests with pgbench, I'm
> looking to pull more info than I have in the past, and I'd really like to
> automate things further.
>
> I'll be starting with basic disk benchmarks (bonnie++ and iozone) and then
> moving on to pgbench.
>
> I'm running FreeBSD and I'm interested in getting some baseline info on
> UFS2 single disk (SATA 7200/WD RE4), gmirror, zfs mirror, zfs raidz1, zfs
> set of two mirrors (ie: two mirrored vdevs in a mirror).  Then I'm
> repeating that with the 4 Intel 320 SSDs, and just to satisfy my
> curiosity, a zfs mirror with two of the SSDs mirrored as the ZIL.
>
> Once that's narrowed down to a few practical choices, I'm moving on to
> pgbench.  I've found some good info here regarding pgbench that is
> unfortunately a bit dated:
> http://www.westnet.com/~gsmith/content/postgresql/
>
> A few questions:
>
> -Any favorite automation or graphing tools beyond what's on Greg's site?

There are talks not listed on that westnet page - for example a recent
"Bottom-up Database Benchmarking" talk, available for example here:

   http://pgbr.postgresql.org.br/2011/palestras.php?id=60

It probably contains more recent info about benchmarking tools and testing
new hardware.

> -Any detailed information on creating "custom" pgbench tests?

The technical info at
http://www.postgresql.org/docs/9.1/interactive/pgbench.html should be
sufficient I guess, it's fairly simple. The most difficult thing is
determining what the script should do - what queries to execute etc. And
that depends on the application.

> -Any other postgres benchmarking tools?

Not really. The pgbench is a nice stress testing tool and the scripting is
quite flexible. I've done some TPC-H-like testing recently, but it's
rather a bunch of scripts executed manually.

> I'm also curious about benchmarking using my own data.  I tried something
> long ago that at least gave the illusion of working, but didn't seem quite
> right to me.  I enabled basic query logging on one of our busier servers,
> dumped the db, and let it run for 24 hours.  That gave me the normal
> random data from users throughout the day as well as our batch jobs that
> run overnight.  I had to grep out and reformat the actual queries from the
> logfile, but that was not difficult.   I then loaded the dump into the
> test server and basically fed the saved queries into it and timed the
> result.  I also hacked together a script to sample cpu and disk stats
> every 2S and had that feeding into an rrd database so I could see how
> "busy" things were.
>
> In theory, this sounded good (to me), but I'm not sure I trust the
> results.  Any suggestions on the general concept?  Is it sound?  Is there
> a better way to do it?  I really like the idea of using (our) real data.

It's definitely a step in the right direction. An application-specific
benchmark is usually much more useful that a generic stress test. It
simply is going to tell you more about your workload and you can use it to
asses the capacity more precisely.

There are some issues though - mostly about transactions and locking. For
example if the client starts a transaction, locks a bunch of records and
then performs a time-consuming processing task outside the database, the
other clients may be locked. You won't see this during the stress test,
because in reality it looks like this

1) A: BEGIN
2) A: LOCK (table, row, ...)
3) A: perform something expensive
4) B: attempt to LOCK the same resource (blocks)
5) A: release the LOCK
6) B: obtains the LOCK and continues

but when replaying the workload, you'll see this

1) A: BEGIN
2) A: LOCK (table, row, ...)
3) B: attempt to LOCK the same resource (blocks)
4) A: release the LOCK
5) B: obtains the LOCK and continues

so B waits for a very short period of time (or not at all).

To identify this problem, you'd have to actually behave like the client.
For example with a web application, you could use apache bench
(https://httpd.apache.org/docs/2.0/programs/ab.html) or something like
that.

> Lastly, any general suggestions on tools to collect system data during
> tests and graph it are more than welcome.  I can homebrew, but I'm sure
> I'd be reinventing the wheel.

System stats or database stats? There's a plenty of tools for system stats
 (e.g. sar). For database stat's it's a bit more difficult - there's
pgwatch, pgstatspack and maybe some other tools (I've written pg_monitor).

Tomas