Buy Percona ServicesBuy Now!

My “hot” list for next InnoDB features

 | March 30, 2009 |  Posted In: Insight for DBAs


Many InnoDB scalability problems seem fixed in InnoDB-plugin-1.0.3 and I expect InnoDB-plugin will run fine on 16-24 cores boxes for many workloads. And now it is time to look on systems with 32GB+ of RAM which are not rare nowadays. Working with real customer systems I have wish-list of features I would like to see soon:

  • Fast recovery. Both recovery after crash and recovery from backup can take unacceptable long time, especially if you crashed with full 32GB buffer_pool. There is reported bug, with ETA MySQL-6.0
  • Preload table / index into buffer_pool. You can use custom queries by primary / secondary key to “warm up” part of table, but this solution is ugly and may be slow due to random logical I/O. Implementing preload of full .ibd file with sequential read would be much better solution. This is actually more important feature than it may appear at first look – for example if you put load on slave which is not warmed up properly – slave may never catch up slave, but with small load warm up may take hours to complete, so basically it adds several hours for operations team to complete task which requires restart of slave
  • Copy single .ibd table from one server to different or (basically the same) restore single table from backup, possibly on different server (different slave). It’s all about time – copying whole 500GB backup while you need to restore only single 20GB table is very non-productive
  • Open InnoDB tables in parallel. Currently opening table is serialized, and it is especially bad at start time, when InnoDB takes probes during opening table, as it is slow operation. See also Partially it can be fixed by recent patches by enabling / disabling probes and changing count of probes, but still the solution is far from perfect

As you see the list list is not about performance but mostly about operations tasks, but with current amount of data and memory on servers they become critical. I do not know what is InnoDB plans about it and would like to hear if this is will be implemented anytime soon or never. Anyway I was asked what are our current plans about XtraDB with recent InnoDB-pluging release, as performance improvements in plugin may make XtraDB out of game – so I consider list above as roadmap for XtraDB and hope some or all features are implemented this year.

Do you have any other features you miss in current InnoDB ?

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.


  • Will InnoDB ever be able to do schema changes without blocking writes? People go to great lengths operationally and architecturally to dance around this limitation, and I was just wondering if it’s something InnoDB will ever be able to do.

  • These are great features. In addition to them I want a table (mysql.table_stats) in which I provide statistics for some tables. When there, the table handler does not provide them. Sampling as done by InnoDB is frequently incorrect — and it is hard to get good samples when there is a lot of skew. With this, I would have a batch job to occasionally update the stats.

  • JC’s prototype of remembering the buffer pool on shutdown had potential, and was relatively simple.
    In a nutshell, he merely saved a list of page numbers from the tablespace; I suppose that would need to be expanded a little to support file-per-table. It would also be beneficial to sort the list so that the read on startup is mostly sequential.
    And to make it fancy, the startup read could be done in the background, not delaying the actual startup process.

    I think this would in a way be nicer than preloading complete tables or anything like that, and be suitable both for general use as well as all-in-memory scenarios.

  • Innodb has a very limited tablespace implementation. While you can have multiple data files, there is no way to place tables into a given data file. In your comment about needing only 20GB of 500GB, if there were smaller data files (even for example 100GB), and certain tables were limited to a given data file the recovery time could be reduced. Of course you need the ability to restore just one datafile.

    Also, only the last data file can be set to autoextend.

    Is there merit in Innodb supporting a mixed mode with some tables being individual .ibd files while all remaining tables are in a common data file?

  • I’d also like some background support for gradual recovery of deleted rows space. If you have an innodb table that has frequent deletes and inserts, it can continue to take more and more space, even if the number of rows doesn’t change. If the server was getting pegged, then it could suspend the thread. Currently the only way to get around the problem is to rebuild the table ( or simply use myIsam tables abused in such a manor).

  • i wish too that innodb save the sequence in autoincrement fileds instead calculates when loads table or makes an optimize tasks.

  • I like to add one more feature with 5.1 we have partitions , so if 1 partition gets corrupt innodb crashes the mysql, it should handle the corrupt partition gracefully without causing the assertion. Same could be said for corrupt table as well.
    During innodb crash recovery the modes available should provide some table dictionary i.e. some dynamic view informing the corrupt innodb tables. Presently you dont know how many tables in db are corrupted without spending some good amount of time with innodb recovery tools [ ].

  • Regarding scalability issues what is fixed for the moment – there is no more dramatic performance degradation with upgrading a given server from 8 to 16cores (for ex.). But there is still not to much performance improvement with it, there is even no 50% gain, so work in this direction is far to be finished 🙂 (of course it depends on workload, but it’s what I saw)..

    For other features:

    * parallel degree of query execution – specially for all “full scan” queries where it should be quite easy to implement (and “select count(*)” is one of cases)

    * remove datafile names list from conf file, as well remove data dir path, and keep all datafile related information within a single master file *per* database – in way each database may be independently backed up and restore elsewhere; all datafile operations should be accessible on-line and via SQL commands; move from datafiles to “tablespaces” (may contain one or more datafiles), and then add “tablespace” to CREATE TABLE option, etc; make tablespace “transportable” (keeping all even dictionnary information inside leaving tablespace free to move between databases, systems/servers, etc.)


  • I’d like to be able to use file_per_table, and then move those files to a different server, mount them, and run off them.

    I don’t mind doing something like:

    source machine:

    scp * -> some other machine

    target machine

    Having to do an export/import to move data between innodb servers is really rather onerous with large databases.

  • A ‘show fragmentation’ or equivalent command to give me some idea of exactly how badly I need to run an OPTIMIZE. Any of the traditionally proposed solutions relies on math that is too much of an approximation.

    Like this:


  • Nice….. those are a hot set of features and something at the top of my list for a while now!

    We’d definitely upgrade ASAP to this release when it became available.

    Some of the performance improvements are really nice but these would be bigger wins for us….

  • Make auto_increment persistent in InnoDB. So when we restart the server, it doesn´t lose the latest value (if we deleted it)

  • It’s a table cold backup and restory without shut down of InnoDB

    Restoring a single .ibd file

Comments are closed