MySQL Performance Blog

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

Jeremy Cole recently posted very nice post about MySQL Replication Performance. There are however few points I should comment on.

• Jeremy Speaks about MyISAM and Innodb in the same terms, in fact they are quite different for Replication. As you probably know MyISAM uses buffered IO so if master crashes you’re out of sync anyway - whatever sync_binlog option was set to. Well it is still a bit better with sync_binlog enabled as slave could have consistent data after it catches up but master may have some of its tables out of sync with binary log and it is not easy to find which.
• I’m surprised to read enabling binary log drops update performance. I guess Jeremy speaks about enabling binary log and setting sync_binlog option which is good for safe replication. But it is setting which is off by default so you should not see such effect just by enabling log_bin.
  
• Innodb tables also have to flush logs on transaction commit unless you disable it so you should not be getting 1500 update transactions per second from the single thread without battery backed up cache. If you do it is quite possible Operation system is faking fsync() for you. In certain cases it only does so for sequential writes this is why sync_binlog changes things. Two logs are synced now which requires seeks which make certain disks to flush their cache.
• Watch out for MySQL 5.0 - if you enabled Binary log with MySQL 5.0 you loose group commit which can dramatically increase commit rate for multiple user load. See this post by Vadim for benchmark results.
• Solution with Battery Backed up RAID is great one and is not that expensive these days. Other two probably just create conditions for Operation System to fake fsync. You need to get data to the disk and you can’t physically get data to the disk surface at 1500 operarions per second. Jeremy is speaking about seeks but it is only part of the latency. Disk rotation is another big contibutor. Even if we stay on the same track we’ll be only able to do single disk write per rotation, which is about 250 for 15000 RPM drive. If you’re getting more without battery backed up cache something is likely faking it.

I also should mention this covers only Master overhead of Replication. Slave is where you can expect more problems, especially on high end systems. Slave serializes all operations to single stream so group commit can’t work. Furthermore even if you run with innodb_flush_logs_at_trx_commit=2 you still do not solve all problems. You still will practically have only One CPU to handle replication load. Furthermore if you have multiple disks they will not be used effectively as replication SQL thread will typically submit IO requests to the disk one by one so only one drive will do the work and other will be idle. You mostly really affected by this if you have a lot of writes to large database size.
This is one more reason why you might want to go with scale out and keep single server a box with few CPUs and few hard drives while running MySQL.