read-write SQL performance

[Kris Kennaway]

I did some benchmarking of sysbench in read-write mode (previous tests
have focused on read-only mode).  The reason for this is that the disk
hardware in my 8-core test system is slow (single disk) and is too
easily saturated.  In fact mysql and pgsql have identical performance
when writing to disk.  In other words, I seem to be mostly
benchmarking the disk performance and not database or kernel
performance.

Faster disk hardware is necessary to explore database performance
differences or kernel bottlenecks.  An upper bound on possible
read-write performance comes from using a memory disk instead of
physical disk hardware.  I replicated the databases onto a suitably
large (2gb) tmpfs and reran the tests together with some mutex
profiling.

Results are here:

  http://obsecurity.dyndns.org/sysbench-write.png

There are a couple of interesting features.

mysql has better peak performance than pgsql, but then quickly falls
in the toilet.  Profiling indicates that at peak there is some
contention on lockmgr locks and the proc lock, but most of the
contention is in userland (i.e. within mysql itself).  At higher loads
the bottleneck is overwhelmingly within mysql (and the system is
actually 90-100% idle).  This seems to be a serious scaling problem
within mysql.

Peak pgsql performance is lower than mysql, but there is comparatively
little degradation at higher loads.  Profiling shows that the dominant
bottleneck at all workloads is lockmgr.

Fortunately there is a lockmgr rewrite in progress by Attilio for SoC,
so there is great scope for performance improvements to pgsql.
Significant mysql performance improvements may require fundamental
architectural work by the mysql developers.

Kris
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-performance/attachments/20070804/61cc1271/attachment.pgp