drizzle-discuss team mailing list archive

[Jay Pipes <Jay.Pipes@xxxxxxx>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Drizzlers,

Over the past three weeks, the Sun Drizzle team has been looking into
the results of recent benchmarks comparing MySQL 5.1 and Drizzle that we
received from Allan Packer.

We'll be blunt. The results of the benchmarks are not flattering for
Drizzle at low concurrency levels.

This email is designed to do 2 things:

* Show the results of the benchmarks and how we produced the results
* Appeal for help in diagnosing, reproducing, and improving throughput
in Drizzle

We, the Sun Drizzle team, had a group meeting yesterday where we all
acknowledged that the way we, as a team, went about trying to "fix the
problem" was wrong.  The fear of being compared poorly to MySQL sent us
into a chaotic mode, with all of us scrambing to figure out why we see
the results we do.

What we *should* have done, and what we will do in the future, is be
open, transparent, and involve the Drizzler community immediately,
instead of bottling up and acting like a closed development group.

So, without further ado, here are the results that we see:

Drizzle

cons	tps
- ------- -----------
2	1085.200000
4	1426.376667
8	1950.926667
16	2625.110000
32	3401.433333
64	3958.116667
128	3960.503333
256	3025.270000
512	2066.236667

MySQL 5.1

cons	tps
- ------- -----------
2	853.223333
4	1636.713333
8	2785.330000
16	5119.126667
32	4757.443333
64	4316.280000
128	1496.44666
256	685.210000
512	570.206667

As you can see, the throughput for MySQL 5.1 up to 16 concurrent
connections is impressive -- almost linear (almost doubling throughput
with a doubling of concurrent connections).  However, at 16 connections
(the number of cores on the box), MySQL 5.1 begins to degrade in
throughput.  After 128 connections, the throughput rapidly degrades.

The Drizzle throughput builds at a much slower pace, but does not
experience the dramatic falloff in throughput, even at 512 concurrent
connections.  However, it is curious that the difference in throuput for
Drizzle at lower concurrencies is so much compared to MySQL 5.1.  It is
the curiosity at finding out why which has distracted the Sun Drizzle
team so much in the last few weeks, and we hope to get input and
involvement from Drizzlers in figuring out this dilemma.

Details on Benchmarking
- -----------------------

The results above are transactions per second at various concurrency
levels (2 through 512 concurrent connections).  The benchmarks were run
on InnoDB tables.

The machine used was a quad Xeon quad-core (so 16 vcpus) with 32G ram).
 The results above are for the readonly workload, all in-memory.

Both Drizzle and MySQL 5.1 were started with identical parameters:

- --innodb_buffer_pool_size=256M
- --innodb_thread_concurrency=0
- --innodb_write_io_threads=8
- --innodb_read_io_threads=8
- --innodb_log_file_size=128M
- --innodb_log_buffer_size=8M
- --character-set-server=utf8 (MySQL 5.1 only, since Drizzle is all utf8)

To benchmark, we ran the Drizzle Sysbench utility (sysbench modified to
work with drizzle client driver).  The setup was like so:

sysbench --max-time=60 --max-requests=0 --test=oltp --drizzle-db=test
- --drizzle-port=4427 --drizzle-host=127.0.0.1 --drizzle-user=root
- --db-ps-mode=disable --db-driver=drizzle --drizzle-table-engine=innodb
- --oltp-read-only=on --oltp-table-size=1000000 [prepare|run]

For each concurrency level, we ran 5 iterations of the above sysbench
run, and averaged across all runs.  At the start of each concurrency
run, we restarted the servers.

I'll be documented a Step-by-Step Instruction Guide for how to reproduce
the benchmarks in a wiki page later today so that you might help us
reproduce/verify the results and run benchmarks on other hardware.

What We Have Ruled Out So Far
- -----------------------------

Here is a list of things that we've ruled out as culprits for the
throughput differences at lower concurrencies.  We've ruled these out
mostly by benchmarking before and after making the changes, or by
examining the call stacks we generated via cachegrind for MySQL and Drizzle.

* UTF8 -- minimal difference
* MRR (multi-range read) implementation -- disabling MRR by hardcoding
optimizer_use_mrr=2 made no appreciable difference in benchmarks
* InnoDB differences -- no difference. We patched our plugin with
changes from MySQL 5.1 InnoDB and there was no difference in results
* ICP (index condition pushdown) - little or no difference observed in
call paths.
* HEAP engine - Our HEAP engine has patches from eBay, but this turned
out not to be much difference in call paths at all.  From looking at the
call stacks, we could determine our HEAP implementation was slightly
faster than MySQl 5.1's, but not enough to see difference in the
benchmark results.
* Excessive calls to get/setsockopt - We noticed an issue where we were
calling sockopt syscalls more than MySQL 5.1.  After fixing this, we
observed no difference in bench results.

A Future Methodology For Benchmarking
- -------------------------------------

As a team, we decided yesterday that we want to be open, transparent,
and public about benchmarks with Drizzle. No hiding. No secrets. No
shame. No bashing others.

Going forward, we want to adhere to these guidelines when discussing
benchmark results and working with other database teams:

* Be open and public about *everything*.
* Document how benchmarks are run on the Drizzle.org wiki
* Have peer review: at least 3 individuals should confirm the results of
a benchmark by running the publicly documented benchmark and verifying
results.
* If you run a benchmark, please do post it to the mailing list and get
input from others.
* Address problems you see and ideas you might have about bottlenecks in
a public forum (i.e. this list)
* Use the Drizzle.org wiki to publicize benchmarks, comment on
methodology, and suggest areas for improvement or where bottlenecks may be.

Summary
- -------

In short, we hope you'll join us in finding out where our bottlenecks
are and helping us make Drizzle a success.

Thanks!

The Sun Drizzle Team
(krow, mtaylor, eday, stewart, FallenPegasus, jaypipes, lbieber)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkntDhgACgkQ2upbWsB4UtEXYgCeOhpjlvfDPg65eJvPYEoWAFL/
gV4AmwRoYqnnqx4TdR0Hq76HY9XX8JbV
=3/T9
-----END PGP SIGNATURE-----