read-write SQL performance

[Abdullah Ibn Hamad Al-Marri]

On 8/4/07, Kris Kennaway <kris at obsecurity.org> wrote:
> 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

Maybe Greg would be interested in the MySQL issues?


-- 
Regards,

-Abdullah Ibn Hamad Al-Marri
Arab Portal
http://www.WeArab.Net/