Note: This post is part 2 of 4 on building our training workshop.
Last week I talked about why you don’t want to shard. This week I’m following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.Â Building this list was not really a scientific process – It’s always difficult to say which feature is better than another, because a lot of it depends on the individual workload.Â My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:
- CPU scalability fixes – XtraDB improves performance on systems with multi-cpus (see docs 1, 2).
- Import/Export Tables – XtraDB allows you to import an arbitrary Â table from one server to another, by backing up the .ibd file with Xtrabackup (see docs).
- IO scalability fixes – A lot of the internal algorithms of InnoDB are based on the non-configurable assumption that the server has only a single disk installed (100 iops). Â One problem in particular that this causes, is that InnoDB the algorithm which chooses if InnoDB is too busy to flush dirty pages can consider it’s self busy very easily.Â Keeping a large percentage of pages dirty increases recovery time, and will lead to more work when checkpoints are eventually forced at the end of a log file. Â XtraDB improves this with innodb_io_capacity, as well as configuration items for innodb_read_threads, innodb_write_threads (see docs).
- Better Diagnostics – The SHOW ENGINE INNODB STATUS command in XtraDB shows a lot more information than the standard InnoDB status (see docs). Â The built-in InnoDB status also has some problems with the placement of items (a long transaction list will prevent the rest of the information shown).Â In addition to this, XtraDB diagnostics include the ability to see the contents of the buffer pool (see docs), and InnoDB row statistics are inserted into the slow query log (see docs).
- Fast Crash Recovery – In the built-in InnoDB, the crash recovery process is sometimes best measured in hours and days – this restricts users to using very small transaction log files (innodb_log_file_size), which is worse for performance. Â In a simple test, XtraDB recovered ten times faster (see docs).
- InnoDB Plugin Features – XtraDB is derived from the InnoDB plugin, which has fast index creation (as opposed to recreating the whole table!) and page compression.
- Adaptive Checkpointing – The built-in InnoDB can have erratic dips in performance as it approaches the end of a log file and needs to checkpoint – which can cause a denial of service to your application (this can be seen in any benchmark – such as this one). Â In XtraDB, adaptive checkpointing can smooth out the load, and checkpoint data more aggressively as you approach the end of a log file (see docs).
- Insert Buffer control – The insert buffer is a great feature of InnoDB that is not often discussed. Â It allows you to delay the writing of non-unique secondary index pages, which can often lead to a lot of merged requests and reduced IO. Â TheÂ problem with the insert buffer in the built-in InnoDB, is that there are no options to tweak it. Â It can grow to 1/2 the size of your buffer pool, and when it does, it doesn’t try to aggressively free entries (a full buffer provides no use) or reduce its size (see docs).
- Data dictionary control – Once an InnoDB table is opened it is never freed from the in-memory data dictionary (which is unlimited in size). Â XtraDB introduces a patch to be able to control this, which is useful in cases where users have a large number of tables. (see docs).
- Additional undo slots – In the built-in InnoDB, the number of open transactions is limited to 1023 (see bug report). Â XtraDB allows this to be expanded to 4072 (Warning: Incompatible change!). Â (see docs).
My next post in this series will be on XtraDB: The Top 10 Configuration Parameters.