MySQL Performance Blog

Arjen posted a good note about MyISAM concurrent-insert features, though I should mention concurrent-insert can be cause of
scalablity and peformance problems on SMP boxes, especially on queries processing range-queries. The reason of problems is POSIX read-write locks, which are used to protect key_buffer from concurrent changes and called for each processed row. More info you can get from my UC2006 talk, in short on main platforms (Linux, Solaris, Windows) rw-locks have a bad implementation and too many calls cause waste of CPU in user-space (Solaris) or kernel-space (Linux).

Some results for MyISAM table, query SELECT id FROM sbtest WHERE id BETWEEN N AND N+20000, id – primary key.
Boxes: Sun V40z, Solaris 10, 4 x Dual Core Opteron @ 2.2GHz (8 logical cpu), 16GB of RAM, StorEdge 3310
and Quadxeon, RedHat AS 3, 2.4.21-15.Elsmp, 4 x Intel(R) XEON(TM) MP CPU 2.00GHz, 4GB of RAM, SATA RAID 10
The results in queries per sec (more is better)

So if you are using range-queries and doing INSERT not often than disabling concurrent-insert with –skip-concurrent-insert can improve MyISAM performance.

As I said the reason of bad scalability is rw-locks and currently MySQL developers are working on CPU-depended rw-locks implementstation, this will be available in MySQL 5.1 – 5.2

There are a lot of talks around new coming Ubuntu 6.06, so I decided to make quick benchmarks.
I used sysbench 0.4.6 oltp-read-only workload with 1000000 rows against InnoDB and MyISAM tables.
Such workload is CPU-bound and allows to compare CPU / OS if we are using the same version of MySQL.
So I used MySQL 5.0.22 and my box Dual Core Athlon 3800+, 1Gb of RAM.
For comparison I tested the same workload on my primary Suse 10.0.

Here are results (in transactions per sec, more is better):

In general Suse 10.0 looks better, though the difference is not significant.