This is probably the feature I missed most from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB - can I just copy .ibd files from one server to another and answer "use mysqldump" is quite disappointed.
Jokes aside, I see real needs in this:
- when we need to restore only single table from backup (sometimes developers kill only single table, not whole database )
- to copy single table from production to QA environment. It may sound not so important, but I see needs in this quite often. QA boxes may have their own setup, not so powerful and with not enough space, but QA still needs to have some tables in fresh status.
- resharding databases, moving some shards from one server to another.

So long story short end - we made new mode for XtraBackup, now it can copy and …

Just thought it was worth pointing out that Percona Xtrabackup is not just for XtraDB. It works great for InnoDB tables, too.

So if mysqldump can’t handle it anymore, LVM snapshots kill your server and you don’t want to buy proprietary backup software, you might take a look at Xtrabackup.

Related posts:

1. Hindsight on a scalable replacement for InnoDB A while ag
2. Restoring from a mysqldump into tables with triggers This is ac
3. What is the scalable …

As you can probably guess, I’m catching up on reading my blogs. I’ve just read with interest about TokuDB’s multiple clustering indexes. It’s kind of an obvious thought, once someone has pointed it out to you. I’ve only been around products that insist there can be Only One clustered index (and then there’s ScaleDB, who say “think differently already”).

Anyway, we already know that there are quite a few database products that use clustered indexes and to avoid update overhead, require every non-clustered index to store the clustered key as the “pointer” for row lookups. Thus there are “hidden columns” which are present at the leaf nodes, but not the non-leaf nodes, of secondary indexes. Why not take that idea and run with it a little? Here’s what I mean:

In the last six months, things have gotten much busier in the world of MySQL performance. That is, making MySQL and InnoDB scale faster out of the box. This is a great trend and I hope it keeps going. At this point I’m fighting to find enough time to read about what people are doing; I can’t keep up fast enough to actually understand the improvements. That’s also good.

The blogs that are posting the most news and analysis are MySQL Performance Blog, Mikael Ronstrom’s blog, DimitriK’s blog, and Mark Callaghan’s blog.

I performed a new set of tests of the patch to split the
rollback segment mutex on Linux. All these tests gave
positive results with improvements in the order of 2%.

One could also derive from the results some conclusions.
The first conclusion is that this split mainly improves
things when the number of threads is high and thus
contention of mutexes is higher. At 256 threads a number
of results improved up to 15%.

The numbers on lower number of threads were more timid
although in many cases an improvement was still seen.

What was also noticeable was that the sysbench read-write
with less reads which makes the transactions much shorter
the positive impact was much greater and the positive
impact on long transactions was much smaller (+0.4%
versus +2.5%). The impact on the short transaction test
with less reads was very positive also on …

I have now tried out the buffer split page hash patches on
both a Linux/x86 box and a SPARC/Solaris server (tests done
by Dimitri).

The three variants in short description are:
1) The Google v3 derived patch. This introduces a new array
of mutexes that only protect the buffer page hash. Thus some
extra checking is needed to ensure the page hasn't been
removed from the hash before using it. This is a very simple
and attractive patch from that point of view. The patch uses
an array of 64 mutexes.

2) A variant I developed with some inspiration from the Percona
patches. This patch uses an array of page hashes which each has
its own read-write lock. I've tried this with 1, 4 and 16 page
hashes and 4 is the optimum number. The rw-lock protects the
page hash long enough to ensure that the block hasn't been
possible to remove from the …

In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)innodb_data_home_dir = /dev/shm/mysql/

I've noted that one reason that InnoDB can get difficulties
when there are many concurrent transactions in the MySQL Server
is that the lock time of the kernel_mutex often increases
linearly with the number of active transactions. One such
example is in trx_assign_read_view where each transaction
that does a consistent read creates a copy of the transaction
list to be able to deduce the read view of the transaction or
statement.

This means that each transaction is copied to the local transaction
list while holding the critical kernel_mutex.

Another such case is that most operations will set some kind of
intention lock on the table. This lock code will walk through
all locks on the table to check for compatible locks and the
first time it will even do so twice. Thus if all threads use the
same table (as they do in e.g. sysbench) then the …

I have been trying to analyse a number of new patches we've
developed for MySQL to see their scalability. However I've
have gotten very strange results which didn't at all compare
with my old results and most of changes gave negative impact :(
Not so nice.

As part of debugging the issues with sysbench I decided to go
back to the original version I used previously (sysbench 0.4.8).
Interestingly even then I saw a difference on 16 and 32 threads
whereas on 1-8 threads and 64+ threads the result were the same
as usual.

So I checked my configuration and it turned out that I had changed
log file size to 200M from 1300M and also used 8 read and write
threads instead of 4. I checked quickly and discovered that the
parameter that affected the sysbench results was the log file size.
So increasing the log file size from 200M to 1300M …

I wrote a small DTrace script to understand InnoDB IO statistics. This script shows statistics about different kinds of Innodb IO requests and how many of them result in actual IO. Sample output is shown below

#./inniostat -h
Usage: inniostat [-h] [-d] [-p pid] [interval]
                 -h : Print this message
                 -p : MySQL PID
                 -d : Dump dtrace script being used

# ./inniostat
 __physical__  ___Innodb___ ____read____     ______write______
   r/s    w/s    r/s    w/s   data    pre    log dblbuf dflush     Time
    24    121     24     50     24      0     50      0      0 16:00:57
    26    130     26     51     26      0     51      0      0 16:00:58
    18    134     18     54     18      0     54      0      0 16:00:59
    25    129     25     51     25      0     51      0      0 16:01:00
    29    116     46     47     17     29     47      0      0 16:01:01
    10    140     10    132     10      0     52      0     80 …