read-write SQL performance

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

Attachment: pgp4kP1qIxkpJ.pgp
Description: PGP signature

Relevant Pages

  • read-write SQL performance
    ... The reason for this is that the disk ... In fact mysql and pgsql have identical performance ... Faster disk hardware is necessary to explore database performance ...
    (freebsd-arch)
  • Re: I am totally stumped..with this on..LOAD_FILE Mysql+PHP= FSCK!!
    ... What I am trying to do is to upload files and stuff them in a mysql database. ... I copied the temporary file to somewhere else, and then handed it to MySQL..THAT WORKED.. ... Is there a way to force a close on the file..maybe that's the problem Mysql is opening a file that is not flushed to disk maybe? ... I gew the feeling its maintaining its own picture of file objects, and doesn't actually flush to the disk unless you do a copy or close php.. ...
    (comp.lang.php)
  • Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7?
    ... flaw in how mysql handles non-zero concurrency values here (innodb ... check if it should try to allow more innodb concurrency. ... instead of being bottlenecked by disk speeds and leaving the CPUs mostly ... We should probably submit a bug to MySQL rather then add a patch to ...
    (freebsd-questions)
  • Re: read-write SQL performance
    ... The reason for this is that the disk ... In fact mysql and pgsql have identical performance ... Faster disk hardware is necessary to explore database performance ...
    (freebsd-arch)
  • Re: read-write SQL performance
    ... The reason for this is that the disk ... In fact mysql and pgsql have identical performance ... Faster disk hardware is necessary to explore database performance ...
    (freebsd-performance)