Buy Percona ServicesBuy Now!

Few more ideas for InnoDB features

 | June 29, 2009 |  Posted In: Insight for DBAs


As you see MySQL is doing great in InnoDB performance improvements, so we decided to concentrate more on additional InnoDB features, which will make difference.

Beside ideas I put before (and one of them – moving InnoDB tables between servers are currently under development), we have few mores:

– Stick some InnoDB tables / indexes in buffer pool, or set priority for InnoDB tables. That means tables with bigger priority will be have more chances to stay in buffer pool then tables with lower priority. Link to blueprint

– Separate LRU list into several lists, and in this way it will allow us to emulate several buffer pool, with features to keep different tables in different buffer pools and also to decrease contention on buffer pool. Link

– We are looking to include Waffle Grid into XtraDB releases with some additional features like caching buffer pool on SSD.

If ideas are interesting for you and you want to support them, contact us

Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.


  • More on this…. it might be valuable to flush a table from the buffer pool.

    I’ve been thinking about a feature we want to work on which executes an computation on 100s of GBs per table.. but each table is only used once.

    So it just makes sense to FLUSH TABLE between iterations. This way there is no competition for the pages.

    This might be in line with your current feature set.

  • Being able to dump/restore the buffer cache would be a nice feature.

    Being able to ANALYZE TABLE and give a percentage of the table to read, or number of rows to read would be great.

  • It’d be really nice to have an auto-sharding feature.

    Kind of like an extension to table partitioning. User can specify in which server each partition should be located. When servers are added, users can then move the partitions around to rebalance the load.

  • Couple from my wish list:

    * instant table checksums
    * Optionally take tables offline when they see corruption instead of crashing the whole server.

  • The previous list and comments here cover most of what I’ve seen a need for. But specifically, having buffer pools, especially with functionality similar to Oracle’s recycle pool, would be a great addition. They seem to be removing multiple keybuffer support from MyISAM, so this would be a boon for InnoDB, IMHO.

  • I’d love a mechanism to move innodb databases between servers without doing a dump/reload. I’ve got multiple database servers now, all connected to a san. Each database (myisam) is on a separate LUN. If I have capacity issues on a database, I just shut it down, remount the database on a different server, and I’m off again. Whole thing takes minutes, tops.

    I’d *love* to go to innodb for crash recovery if nothing else, but I’d lose my current load balancing approach. Dumping and reloading a 50-100G database just isn’t a practical load balancing solution since we’re talking hours for the dump and sometimes up to days for the load.

    For my use cases, I’d be more than happy to run some command that took the database “offline” from the source system in order to prepare it to move, something like:

    unmount database badger;
    detatch database badger;

    attach database badger;
    mount database badger;

  • While I’m at it, I’ve got one more:

    I’d like a way to run multiple innodb *instances* with one shared buffer pool. This is basically a back-door way to solve the “how do I move a database” problem. If I have 10 innodb instances all running as different processes with different home directories, I can move one no problem.

    Issue, of course, though is that 10 instances each have 1/10 (or less) of the server’s memory available for buffer pool, so they generally perform like the wrong end of a dog.

    You don’t have this issue (as much) with myisam since most of the cache is actually the filesystem buffer cache, so that’s shared across processes. Innodb with its internal buffer pool doesn’t share well, even with other instances of the same thing.

Comments are closed