So you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache and pushing application to the swap. This causes swapping and causes performance problems. This much is obvious. But how bad is it ? Should you count it same as normal Disk IO as the box is having or is it worse than that ?

Swapping is going to impact your performance more than just normal IO and here are 3 reasons why. If you know more please let me know, for my taste these 3 are bad enough so I have not been looking for more.

Cache in the Swap File will multiply IO compared to just having less cache. What happens when page in cache is replaced which is swapped out itself ? First you have to find space to swap in the page (we're speaking about memory pressure right?) which means swapping out some page. This would normally happen in background but still it has to be done. When the pages is …

In a follow-up experiment to an earlier post on TokuDB recovery times, I tried to create a better apples-to-apples comparison to InnoDB recovery time. If I measure recovery times when both DBs are doing the same amount of work, TokuDB requires only 2s to recover from a crash, compared to 1020s for InnoDB.

Background

In the first experiment, I compared recovery times when both storage engines (TokuDB and InnoDB) were inserting at maximum rates. In that experiment, following a power cord pull and server restart, TokuDB recovered in 501s, InnoDB in 18505s. In …

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 …