MySQL Performance Blog

Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but not on Linux.

However I follow the project SystemTap for couple of years (it was started in 2005), which is supposed to provide similar to DTrace functionality.
[read more...]

I mentioned earlier that IO scheduler CFQ coming by default in RedHat / CentOS 5.x may be not so good for MySQL. And yesterday one customer reported that just changing cfq to noop solved their InnoDB IO problems. I ran tpcc scripts against XtraDB on our Dell PowerEdge R900 server (16 cores, 8 disks in RAID10, controller Perc/6i with BBU) to compare cfq, deadline, noop and anticipatory (last one just to get number, I did not expect a lot from anticipatory).
[read more...]

One of our customers reported strange problem with MySQL having extremely poor performance when sync-binlog=1 is enabled, even though the system with RAID and BBU were expected to have much better performance.

The problem could be repeated with SysBench as follows:

[read more...]

Some time ago I get brand new IBM POWER6 server as the replacement for "old" P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is not much documentation all around the web, I tried to set-up system and database traditional way. The first problem I hit was memory allocation and I think it is the best place share my remarks. Let's start from the beginning..

[read more...]

So you're running dedicated MySQL Linux box with plenty of memory, so the good question arises if you should have swap file enabled or disable it ? I've seen production successfully running on boxes both with and without swap file so it is not the question of you must do it this or that way but rather understanding advantages of both approaches.

I also would like to hear what you do yourself, and why
[read more...]

I've just been pointed to the nice tool which I was waiting for years to see. It is fincore - little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.
[read more...]

Working on performance optimization project (not directly MySQL related) we did a test - creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back.

The performance difference was huge - we could read single file at 80MB/sec while fragmented files only deliver about 2MB/sec - this is a massive difference.
[read more...]

Found a great article about Linux IO Schedulers today which is quite interesting. It goes in details about schedulers and explains in which of workloads which of schedulers is best.

The interesting thing this article points out is - there are multiple versions of each of the schedulers, while name remains the same. This means unless you really know mapping between kernel versions and scheduler versions it is very hard to evaluate benchmark results.

This could be noticed by benchmarks we've done over years. Long time ago "AS" scheduler could be several times slower than deadline for MySQL workloads such as SysBench or DBT2 when it went down to 30% difference and in the last runs we've done difference was not really significant.

This article also points out benchmarking IO schedulers you should look at more numbers than aggregate bandwidth - you also better to measure per client bandwidth as well as max latency as this is what can be the problem. Take a look at these old results for example. It also means you'd better to perform IO scheduler benchmarks on mixed load with different of task, for example mixing OLTP with some reporting queries if you really want to see the difference.

From the article it looks like CFQ should be good choice for databases and it is also found to work pretty well by some benchmarks we've done. The only question if it is doing as good as it could - In the docs it is mentioned it uses "per process" scheduling while MySQL is single process but single thread - does each thread gets its own queue in reality or is it shared ?

We should look into this when we'll run more benchmarks for IO Schedulers.

I'm continuing my experiments with different OS and today I tested FreeBSD 6.0 on my box.
(more details about box and benchmark see here http://www.mysqlperformanceblog.com/2006/06/13/quick-look-at-ubuntu-606/).
Initially I was very pessimistic about FreeBSD, as results were (in transactions/sec, more is better.
for comparison the results from Suse 10.0):

The crash with many threads in FreeBSD is known problem and is not MySQL fault. More info is available in FreeBSD bug report

I'm not big expert in FreeBSD and did not saw http://wikitest.freebsd.org/MySQL before. This page recommends to use libthr instead of libthreads.
The results with libthr looks better:

Interesting thing with 4-64 threads FreeBSD is better than Suse in InnoDB benchmark. I think it is related to InnoDB's implementation of syncronious primitives. For MyISAM Suse is stable better.

Configuration params:
Box: Dual Core Athlon 3800+, 1Gb of RAM, Motherboard ASUS A8N-E

MySQL 5.0.22

params for InnoDB:
--innodb-buffer-pool-size=500M --max-connections=500

params for MyISAM:
--key-buffer-size=500M --max-connections=500 --skip-innodb

Suse 10.0:
kernel-smp-2.6.13-15.x86_64
NTPL

Schedulers comparsion.
By request I made tests with 4BSD scheduler:

Interesting with 256 threads BSD scheduler looks better.

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