MySQL Performance Blog

With new updates of FusionIO drivers I was able to test it on our Dell R900 with Ubuntu 8.10 without pain of compiling drives myself and downgrading to older kernel, so I was decided to test it in strict_sync mode.

As I understand FusionIO in default mode, like Intel SSD, is “lying” to application, and fsync() is not real, it still commit only to internal memory, not to final media. And FusionIO has “strict_sync” mode to guarantee fsync is trustworthy.

So let’s benchmark it - as usually I do tpcc-mysql benchmark on 100W (9GB data) with 3GB buffer_pool in O_DIRECT access.

The raw number are here

and graph is
.
Results are in TPM (Transactions Per Minute), more is better, and graph shows how result is changing in time ( axis X ).
It is too obvious from graph to comment it…

I actually did not test if you really loose transactions in case of power outage in default mode, this is something to check.

For one our project I needed proxy solution, and mysql-proxy is one of well-known, so it was logical first choice. The obvious question which come in mind is what performance penalty we have using mysql-proxy, version 0.7.1.
So it is easy to test. (By the way sysbench recently was pushed to Launchpad, see lp:sysbench, and Percona is going to be active developer of this project and scripting benchmarks).

I took lp:sysbench with LUA script oltp_complex_ro, and tested for couple connections, here are results (in transactions per second, more is better)

ok, and let’s look on response time (for 1 thread).

MySQL

per-request statistics:
min: 1.31ms
avg: 1.51ms
max: 5.30ms
approx. 95 percentile: 1.56ms


Proxy+MySQL

per-request statistics:
min: 2.04ms
avg: 2.86ms
max: 6.44ms
approx. 95 percentile: 4.30ms


Well, I expected some penalty of using proxy… but 2-3x times, that’s overkill. Worth to consider if you want to run Query Analyzer with MySQL-proxy on your MySQL Enterprise setup.

There is alternative - Dormando-proxy, which I want to try also, but the problem is it crashed under sysbench load, so I was not able to get any results yet.

Last week we were busy to align XtraDB performance with 5.4, now we have some results. Currently it is available as “hacks” to XtraDB (available on Lauchpad lp:~percona-dev/percona-xtradb/hacks-porting-tune if you are interested). Basically we took improvements from 5.4 and backported ones performance related to XtraDB.

Here are results for tpcc-like workload, 100W (~10GB) ( raw results and parameters are available here
http://spreadsheets.google.com/ccc?key=rxUEhM2dqbX0uAfq9j6WQ_w ). Box Dell PowerEdge R900 (Does Dell have referral program ? ), with RAID10 (8 disks) on ext3, 32GB of RAM.

As you see there almost no difference and you may say what’s the reason in XtraDB ? The most interesting reason is XtraDB based on InnoDB-plugin and contains its nice features like FAST INDEX CREATION and dynamic pages. And XtraDB has some parameters like “adaptive_checkpoint” and control of “read_ahead”. And if in the same benchmark you disable read-ahead (innodb_read_ahead=none), you can see improvement about 15%

Actually control of read_ahead is very simple patch and can easily be included in 5.4 or InnoDB-plugin.

For curiosity I run the same benchmarks on ext3 vs xfs on SSD card, the results are:

Interesting facts:
- xfs is 25% faster ext3
- the gaps on SSD is more visible than on RAID10
- in the same time gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement like on RAID10

I should mention I have strange results on xtradb running it on xfs on RAID10, I have results about 2times slower than for ext3. I am not sure yet - is it xtradb or xfs problem, and why it appears only on RAID10, but not SSD.

In benchmarks passion (see my two previous posts) I managed to setup all three devices (RAID was on board; Intel X25-E SSD connected to HighPoint controller, FusionIO card) on our working horse Dell PowerEdge R900 (btw, to do that I had to switch from CentOS 5.2 to Ubuntu 8.10, as CentOS was not able to start with attached SSD card to HighPoint controller) and along with other tests I ran tpcc-like IO-bound workload on all devices.

For tests I used MySQL 5.4/InnoDB, and all other parameters are the same from previous posts (100W, buffer_pool 3GB). Filesystem - XFS mounted with nobarrier option.

Graphical results are here

and average results:

RAID10 - 7439.850 TPM
SSD - 10681.050 TPM
FusionIO - 17372.250 TPM

However what should be noted - both SSD and FusionIO are run in “non-durable” mode, that is you may lose some transactions in case of power outage (see my post http://www.mysqlperformanceblog.com/2009/03/02/ssd-xfs-lvm-fsync-write-cache-barrier-and-lost-transactions/).

While results for SSD (note it is single device, in comparison to RAID 10 on 8 disks) and FusionIO are impressive, it is worth to consider price/performance parameter.

Here is my very rough calculation:
For RAID 10 we use 8 73GB SAS 2.5″ 15K RPM disks, with price 190$ per disks it gives us 1520$ for 292GB useful space, or ~ 5.2$ per GB.
For SSD I can get 32GB card for 390$, which is ~12.1$ per GB
For FusionIO I really not sure what is price (it was given as only for tests), but quick googling gave me 30$ per GB, so for 160GB card gives 4800$.

Now simple dividing TPM on price of IO system, we have
RAID 10 - 4.8 TPM / $
SSD - 27 TPM / $
FusionIO - 3.6 TPM / $

Please note that price of transaction is not the main criteria to consider, as total TCO for systems with SSD may be much cheaper (considering you need less servers, less space, less power). Also worth to consider that SSD is only 32GB space and to have the same space as FusionIO we need 4 cards (but it still will be cheaper than FusionIO), but it also may improve performance as such setup will be able to handle IO requests in parallel.

As continue to my benchmarks http://www.mysqlperformanceblog.com/2009/04/30/looking-on-54-io-bound-benchmarks/ on 5.4 I tried in-memory load (basically changed buffer pool from 3GB to 15GB, and database size is 10GB). The results are on the same spreadsheet http://spreadsheets.google.com/ccc?key=rYZB2dd2j1pQsvWs2kFvTsg&hl=en#, page CPUBound.

I especially made short warmup (120 sec) and long run (2700sec) to see how different versions go through warmup stage.

The graph is

In default mode I would say XtraDB performs almost the same as 5.4, but dips are a bit worse than in 5.4.

Now about dips - all of them are caused by InnoDB checkpoint activity, InnoDB is doing intensive flushing of buffer_pool pages and that basically causes stall for some period of time in all user processes.
In XtraDB we have special mode adaptive_checkpoint, you see result in this mode. While max performance is worse, there is no dips, and average performance is better.

If Sun Perfomance engineers read this - I call attention to this problem and do not ignore it if Sun started to make changes to InnoDB anyway.

If InnoDB engineers read this and are interested - then, yes, we are ready to provide adaptive_checkpoint patch under BSD license.

Also I was asked what if we set small innodb_max_dirty_pages_pct , will not that help with such dips - you can see results for 5.4 with innodb_max_dirty_pages_pct=15. There really no dips, but average performance is not acceptable. I also tried innodb_max_dirty_pages_pct=30, but results in this case similar to usual 5.4, so I do not show them.

With a lot of talks around 5.4 I decided to check how it works in our benchmarks. For first shoot I took tpcc-like IO-bound benchmark (100W, ~10GB of data, 3GB buffer_pool) and tested it on our Dell PowerEdge R900 box (16 cores, 32GB of RAM, RAID 10 on 8 SAS 2.5″ 15K RPM disks). For comparison I took XtraDB-release5 and 5.0.77-highperf percona release.

For raw results you can check my Google Spreadsheet (it is also being update with my next CPU benchmarks, and benchmark on SSD & FusionIO), also I post graph there:

Results are in TPM (transactions per minute, more is better).

So I can confirm that MySQL team did great job with 5.4 and it shows the best results.
Some more results you can find on Dimitri’s blog, one of Sun Performance Engineers.

From our side we will look on recent improvements and also on Google V3 patches and will integrate them into next release of XtraDB, so stay tuned

So a while ago I wrote about fun post about MySQL Scalability to 256 way….

Besides discussion on the thread itself I had a lot of private comments in my mail from Sun/MySQL employees which tended to agree with me on this being the a large stretch.
[read more...]

Playing with last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter wrote about it some time ago, but I wanted to review that data having some new information. In current multi-core word the compression utility should utilize several CPU to speedup operation, and another my requirement was the ability to work with stdin / stdout, so I could do scripting something like: innobackupex –stream | compressor | network_copy.

My research gave me next list: pigz (parallel gzip), pbzip2 (parallel bzip2), qpress ( command line utility for QuickLZ) and I wanted to try LZO (as lzop 1.03 command line + LZO 2 libraries). Actually lzop does not support parallel operations, but it is know to have good decompression speed even with 1 thread. UPDATE 17-Mar-2009: I added lzma results also by request from comments.

[read more...]

Introduction
When people think about Percona’s microslow patch immediately a question arises how much logging impacts on performance. When we do performance audit often we log every query to find not only slow queries. A query may take less than a second to execute, but a huge number of such queries may significantly load a server. On one hand logging causes sequential writes which can’t impair performance much, on other hand when every query is logged there is a plenty of write operations and obviously performance suffers. Let’s investigate how much.

[read more...]

In many cases I speculate how things should work based on what they do and in number of cases this lead me forming too good impression about technology and when running in completely unanticipated bug or performance bottleneck. This is exactly the case with LVM

Number of customers have reported the LVM gives very high penalty when snapshots are enabled (leave along if you try to run backup at this time) and so I decided to look into it.

I used sysbench fileio test as our concern is general IO performance in this case - it is not something MySQL related.
[read more...]