To continue fun with FusionIO cards, I wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB with Percona patches).
As benchmark engine I used tpcc-mysql with 1000 warehouses ( which gives around 90GB of data + indexes) on my workhourse Dell PowerEdge R900 ( details about box ).

On storage configuration: FusionIO 160GB SLC and 320GB MLC cards are configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard drives, here I followed Yoshinori Matsunobu's recommendations http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html …

Mark wrote about how to find situations where InnoDB read-ahead is a bottleneck. What he didn’t disclose, though, is his trick to disable read-ahead without restart or recompile of MySQL. See, there’s no internal “disable read ahead knob”. But there is…

buf_read_ahead_random(...){ ...
       if (srv_startup_is_before_trx_rollback_phase) {
                /* No read-ahead to avoid thread deadlocks */
                return(0);
        }

This variable is tested at two functions – buf_read_ahead_linear() and buf_read_ahead_random() and nowhere else. So yeah, “server startup is before transaction rollback phase” is another way of saying “don’t do read ahead, please please”.

gdb -ex "set  srv_startup_is_before_trx_rollback_phase=1" \
    --batch -p $(pidof mysqld)

And many …

There’s one bottleneck in MySQL/InnoDB that ultimately sucks. It sucked in 4.0, sucked in 5.0, sucks in 5.1 with newest InnoDB plugin. Opening tables has been a bottleneck on machines that have thousands of tables all the time (as LOCK_open is being held during the process), and while there was a table being opened, everything else would stall on the machine.

It can simply take hours on such systems just to open tables – and the major portion of time spent is randomly diving into InnoDB tables to populate index statistics. It obviously sounds like low hanging fruit – as statistics aren’t needed while you are opening a table, they’re needed just for querying the table.

So, I threw in few thousand tables to my machine, and tried opening them with ten connections. Standard InnoDB code was opening 13.5 tables a second. After spending few minutes and moving …

InnoDB detects deadlocks. Deadlocks are those nasty situations, when transaction 1 tries to acquire locks A and B, whereas transaction 2 tries to acquire locks B and A at the same time. As both are stubborn, InnoDB will decide simply to terminate one of them. If it wouldn’t do that, both transactions would have to wait until lock_wait_timeout to expire otherwise. There is a big chance that longer the transaction is, more likely it is to cause deadlocks. Deadlock detection kind of helps, then, but… at certain costs.

Transaction 1 and 2 case is way too easy, try adding few hundred transactions that contend over same set of locks. To do that, InnoDB deadlock monitor will recursively brute-force lock graph, until it hits a 200-transaction-long chain (it will say it is a deadlock), or until it runs out of paths to check. Still, with the power of modern hardware that will still be milliseconds.

Unfortunately, InnoDB will also hold …

On the 24th of November I attended a talk given by one of of Sun's benchmark specialists , Dimitri Kravtchuk, at the Sun Solution Center of Paris.

The talk was simply called MySQL performance, and I was expecting, as many I suppose (the auditorium was packed !) tips and tricks on how to optimize a MySQL database. However I was quite positively surprised since the 3 hours session wasn't just a long list of tricks and “how to's” but was actually a journey through one and a half year of improvement of the InnoDB engine.

InnoDB could easily scale horizontally but not vertically. Horizontal scaling is the fact to put many small servers with each running a MySQL server (typically in a Master-Slave mode) where vertical scaling would be to put a unique MySQL server on a bigger server, expecting to get better performances. The latter is more and more needed given the multiplication of cores and threads within the CPUs even in entry level …

On the 24th of November I attended a talk given by one of of Sun's benchmark specialists , Dimitri Kravtchuk, at the Sun Solution Center of Paris.

The talk was simply called MySQL performance, and I was expecting, as many I suppose (the auditorium was packed !) tips and tricks on how to optimize a MySQL database. However I was quite positively surprised since the 3 hours session wasn't just a long list of tricks and “how to's” but was actually a journey through one and a half year of improvement of the InnoDB engine.

InnoDB could easily scale horizontally but not vertically. Horizontal scaling is the fact to put many small servers with each running a MySQL server (typically in a Master-Slave mode) where vertical scaling would be to put a unique MySQL server on a bigger server, expecting to get better performances. The latter is more and more needed given the multiplication of cores and threads within the CPUs even in entry level …

Last week Tokutek released version 3.0.0 of TokuDB, adding ACID transactions to its list of features. This post discusses an experiment we ran to measure recovery time following a system crash.

In summary, while actively inserting records into a MySQL database using iiBench, we compared the time to recover from a power-cord pull for both InnoDB and TokuDB.

This is by no means an exhaustive look at recovery performance, but does illustrate the benefits of …

OverviewWhat's this new …

InnoDB flushes pages in very simple logic – it goes to the tail of LRUsed list (which has pages for whole buffer pool), and walks it from tail to head, until it finds enough pages to flush. If flushing is aggressive, dirty pages will be only at the head of the list, not at the tail, but InnoDB will still be traversing it fully, tail to head.

Look at that, Jay Janssen notices:

I rarely say the buffer pool beyond a few percent dirty.

Devananda writes:

by enabling adaptive flushing, InnoDB purged modified data much more aggressively, resulting in very little unflushed changes

See, while it traverses the whole buffer pool it holds global buffer pool lock. When some thread that holds kernel mutex tries to acquire …

As long as the whole world is chasing this meaningless “NoSQL” buzzword, we should recognize that InnoDB is usable as an embedded database without an SQL interface. Hence, it is as much of a NoSQL database as anything else labeled with that term. And I might add, it is fast, reliable, and extremely well-tested in the real world. How many NoSQL databases have protection against partial page writes, for example?

It so happens that you can slap an SQL front-end on it, if you want: MySQL.

Related posts:

1. On the unhelpfulness of NoSQL My favorit

2. A Review of Beginning Database Design by Clare Churcher

   Beginn

…