October 25, 2014

Few more ideas for InnoDB features

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 http://www.percona.com/blog/2009/03/30/my-hot-list-for-next-innodb-features/ (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 https://blueprints.launchpad.net/percona-patches/+spec/lru-priority-patch

– 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 https://blueprints.launchpad.net/percona-patches/+spec/multiple-lru-patch

– 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

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. 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.

  2. 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.

  3. Andy says:

    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.

  4. Ryan Huddleston says:

    Couple from my wish list:

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

  5. Ken says:

    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.

  6. pat says:

    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:

    Source:
    unmount database badger;
    detatch database badger;

    Target:
    attach database badger;
    mount database badger;

  7. pat says:

    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.

Speak Your Mind

*