This is my last post in series on FlashCache testing when the cache is placed on Intel SSD card.

This time I am using tpcc-like workload with 1000 Warehouses ( that gives 100GB of data) on Dell PowerEdge R900 with 32GB of RAM, 22GB allocated for buffer pool and I put 70GB on FlashCache partition ( just to simply test the case when data does not fit into cache partition).

Please note in this configuration the benchmark is very write intensive, and it is not going be easy for FlashCache, as in background it has to write blocks to RAID anyway, and write rate in final place is limited by RAID. So all performance benefits will come from read hits

The full report and results are available on benchmark Wiki
http://www.percona.com/docs/wiki/benchmark:flashcache:tpcc:start.

Short version of …

InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: "The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500..."

What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it enters InnoDB, this number is decremented until it reaches zero ("entering InnoDB" …

Previously I covered simple case with FlashCache, when data fits into cache partitions, now I am trying to test when data is bigger than cache.

But before test setup let me address some concern (which I also had). Intel X25-M has a write cache which is not battery backuped, so there is suspect you may have data loss in the case of power outage.
And in the case with FlashCache it would mean you can send your database to trash, as there is no way to recovery from that ( only restore from backup).
I personally did couple of power failure tests and there is article on this topic http://www.anandtech.com/show/2614/10. I did not see any data loss in my tests, and the article says that the write cache "..isn't used for user data because of the risk of data …

I wrote about FlashCache there, and since that I run couple benchmarks, to see what performance benefits we can expect.
For initial tries I took sysbench oltp tests ( read-only and read-write) and case when data fully fits into L2 cache.

I made binaries for FlashCache for CentOS 5.4, kernel 2.6.18-164.15, you can download it from our testing stage. It took some efforts to make binary, you may get my instructions for CentOS on FlashCache-dev mail-list, most likely it will not work for different CentOS / Kernel.

The full results, scripts and settings are on …

Oracle managed to score a major victory last week at the MySQL Conference by announcing performance gains of 200-360% in the forthcoming version 5.5.  This is a tremendous improvement and comes in part due to closer collaboration between what were historically two distinct (and occasionally competitive) groups: the InnoBase team and the MySQL Server team.  Bringing the InnoBase team under the direction of the MySQL Server team under Tomas Ullin is a great benefit not only to MySQL developers, but also for MySQL users.  No doubt these performance gains are a result of many months of hard work by not only Tomas, but also a good number of folks on both teams including guys like Mikael Ronstrum, Kojstja, Calvin Sun and others.  

Reaction to the new release has been positive in the community from the likes of …

MySQL-5.5.4 ® is the great release with performance improvements, let's see how it performs in
tpcc-like workload.

The full details are on Wiki page
http://www.percona.com/docs/wiki/benchmark:mysql:554-tpcc:start

I took MySQL-5.5.4 with InnoDB-1.1, tpcc-mysql benchmark with 200W ( about 18GB worth of data),
InnoDB log files are 3.8GB size, and run with different buffer pools from 20GB to 6GB. The storage is FusionIO 320GB MLC card with XFS-nobarrier. .

While the raw results are available on Wiki, there are graphical results.

I intentionally put all line on the same graph to show trends.

…

Along with maximal possible fsync/sec it is interesting how different software RAID modes affects throughput on FusionIO cards.

In short conclusion, RAID10 modes really disappoint me, the detailed numbers to follow.

To get numbers I run sysbench fileio test with 16KB page size, random read and writes, 1 and 16 threads, O_DIRECT mode.

FusionIO cards are the same as in the previous experiment, as I am running XFS with nobarrier mount options.

OS is CentOS 5.3 with 2.6.18-128.1.10.el5 kernel.

For RAID modes I use:

• single card ( for baseline)
• RAID0 over 2 FusionIO cards
• RAID1 over 2 FusionIO cards
• RAID1 over 2 RAID0 partitions (4 cards in total)
• RAID0 over 2 RAID1 partitions (4 cards in total)
• special …

So you're running the benchmark/stress test - how do you tell if MySQL server is really loaded ? This looks like the trivial question but in fact, especially when workload consists of simple queries I see the load generation and network really putting a lot less load on MySQL than expected. For example you may have 32 threads (or processes) running queries as fast as they can... does it really mean there is an 32 concurrent queries ran all the time ? It may be the case or it may be not...

Take a look at this server for example:

PLAIN TEXT CODE:

1. [root@db01 ~]# mysqladmin -i1  extended | grep Threads_running
2. | Threads_running                       | 1                    |
3. | Threads_running        …

I recently was asked how many fsync / sec ( and therefore durable transactions / sec) we can get on FusionIO card.

It should be easy to test, let's take sysbench fileio benchmark and run, the next command should make it:


./sysbench --test=fileio --file-num=1 --file-total-size=50G --file-fsync-all=on --file-test-mode=seqrewr --max-time=100 --file-block-size=4096 --max-requests=0 run


PLAIN TEXT CODE:

1. Operations performed:  0 Read, 922938 Write, 922938 Other = 1845876 Total
2. Read 0b  Written 3.5207Gb  Total transferred 3.5207Gb  (36.052Mb/sec)
3.  9229.35 Requests/sec executed

So that's 9229.35 req/sec, which is pretty impressive.

For comparison the same run on PERC 6i RAID10 with BBU:

…

I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on:

- durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
- do we use binary logs ( I used ROW based replication for 5.1)
- do we have sync_binlog options.

So why would not take these as variable parameters and run simple benchmark.
I took sysbench update_key scenario ( update indexed field on simple table)
and used Dell PowerEdge R900 with 16 cores, FusionIO as storage for table and RAID 10 with BBU as storage for innodb log files, innodb system table space and binary logs. And I used Percon-XtraDB-5.1.43-9.1 for benchmarks. All used partitions are formatted in XFS and mounted with nobarrier …