I want to make this Friday a bit more interesting - how do you feel to train a bit in InnoDB data recovery techniques.
I have .ibd datafile which was created by query
CREATE TABLE `tryme` ( `email` mediumblob, `content` mediumblob ) ENGINE=InnoDB ... (SOME PARAMETERS SKIPPED) ...;

and I inserted one record into this table with email address and English sentence into field (content). Some transformation where applied to both fields so just HEX viewer may not help you there.
Size of meaningful de-transformed content is
length( ... (content) ... ) = 15.

Can you recovery both email and content fields ? To add some competition I want to give a prize for winner, first who sends content (meaningful English sentence) to given email - will receive our book "High Performance MySQL" 2nd edition, or if he has already this book or just hate it by some reason - I can propose Amazon 25$ Gift Card as replacement.

…

One of the hot mutexes in InnoDB is the buffer pool mutex.
Among other things this mutex protects the page hash where
pages reside when they are in the cache.

There is already a number of variants of how to split out
this mutex. Here follows a short description of the various
approaches.

1) Google v3 approach
Ben Hardy at Google took the approach of using an array of
mutexes (64 mutexes) and this mutex only protects the
actual read, insert and delete from the page hash table.
This has the consequence of a very simple patch, it means
also that when the block has been locked one has to check
that the owner of the block hasn't changed since we didn't
protect the block between the read of the hash and the
locking of the block, thus someone is capable of coming in
between and grabbing the block for another page before we
get to lock the …

Keep it. Make sure it gets correctly positioned in the coming months.

It appears that with the Oracle acquisition, the reason-to-exist for Falcon is regarded as gone (a non-Oracle-owned InnoDB replacement), previously seen as a strategic imperative - much delayed though.

But look, each engine has unique architectural aspects and thus a niche where it does particularly well. Given that Falcon exists, I’d suggest to not just “ditch it” but have it live as one of the pluggables. What Oracle will do to it is unknown, but Sun/MySQL can make sure of this positioning by making sure in the coming months that Falcon works in 5.1 as a pluggable engine, perhaps also creating a separate bzr project/tree for it on Launchpad.

Then the good work can find its way into the real world, now.

When I worked with Dimitri on the analysis of the
Split Rollback Segment Mutex he came up with numbers
on InnoDB Thread Concurrency set to 16 and 32 and I was curious
to see if 24 was the optimal setting. So he made some new runs and
some new graphs that I found interesting.

The first graph analyses behaviour of MySQL 5.4.0 on a SPARC
Server using InnoDB Thread Concurrency set to 0, 16, 24 and 32.
Interestingly for both readonly and readwrite benchmarks the
optimal setting for concurrency is 16 whereas the top numbers
(at 32 threads) is achieved with concurrency set to 24 or 32.



…

When I read the blog about Split Rollback Segment Mutex,
I was interested to verify those results in the context of MySQL 5.4.0.

The patch can be found here.

We've analysed this patch both on a large SPARC system and on my
benchmark x86/Linux machine. Our results tend to be positive for
readwrite benchmarks but sometimes negative for readonly
benchmarks. Also the gain is much smaller than found in the
blog.

Also this patch has two negative effects, the first is that it
provides an upgrade problem, this can probably be handled in the
InnoDB code, but requires quite some digging. The other is that
instead of writing UNDO results to one UNDO log, we write …

Here is the performance graph comparing using
InnoDB Thread Concurrency equal to 0 and
InnoDB Thread Concurrency equal to 24 using
sysbench readwrite with the new InnoDB
Thread concurrency algorithm as introduced
in MySQL 5.4.0.

One of the goals we had originally with the MySQL 5.4
development was to improve scaling from 4 cores to
8 cores. So in my early testing I ran comparisons of
the Google SMP + IO + tcmalloc patches on 4, 8 and 12
cores to see how it behaved compared with a stock
MySQL 5.1.28 version (Note the comparison here was
done on a very early version of 5.4, 5.4.0 have a
set of additional patches applied to it).



What we can see here is that the Google SMP patch and use
of tcmalloc makes a difference already on a 4-core server
using 4 threads. On 1 and 2 threads the difference is only
on the order of 1-2% so not really of smaller significance.

An interesting note in the graph is that 8-core numbers using
the Google …

Well, it took us a little while (we’ve been busy !), but we’ve now posted our presentations on InnoDB from the MySQL Conference and Expo 2009. You can download these presentations by Heikki Tuuri, Ken Jacobs and Calvin Sun from the InnoDB website, as follows:

• Ken and Heikki: InnoDB: Innovative Technologies for Performance and Data Protection
• Heikki: Crash Recovery and Media Recovery in InnoDB
…

I recently did a disk bound DBT-2 benchmarking on SSD/HDD (MySQL 5.4.0, InnoDB). Now I'm pretty confident that storing tables on SSD, redo/Binlog/SYSTEM-tablespace on HDD will be one of the best practices for the time being.

This post is a detailed benchmarking report.
(This post is very long and focusing on InnoDB only. If you are familiar with HDD/SSD/InnoDB architecture and understand what my blog title means, skipping section 1 (general theory) then reading from section 2 (benchmarking results) would be fine. )

1. General Theory of HDD, SSD and InnoDB

SSD is often called as a disruptive storage technology. Currently storage capacity is much smaller and unit price is much higher than HDD, but the situation is very rapidly changing. In the near future many people will use SSD instead of HDD.

From DBA's standpoint, you have a couple of choices for storage allocation.
- …

A while ago I posted about a comment a Sun performance engineer made about a scalable replacement for InnoDB. At the time, I did not believe it referred to Falcon. In hindsight, it seems even clearer that the Sun performance experts were already working hard on InnoDB itself.

Sun’s engineers have shown that they can produce great results when they really take the problems seriously. And I’m sure that InnoDB’s performance has untapped potential we don’t see right now. However, it does not follow that their work on InnoDB is what was meant by a scalable replacement for InnoDB. Or does it?

General-purpose MVCC transactional storage engines with row-level locking, whatever their performance and scaling characteristics in …