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:

  1. CPU scalability fixes – XtraDB improves performance on systems with multi-cpus (see docs 1, 2).
  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).
  3. 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).
  4. 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).
  5. 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).
  6. 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.
  7. 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).
  8. 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).
  9. 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).
  10. 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).

All of these 10 items will be covered in our Training workshops for InnoDB and XtraDB.  In Santa Clara / San Francisco between 14-16 September?  Come along!

My next post in this series will be on XtraDB: The Top 10 Configuration Parameters.

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
William Newton

I understand most of the points will make administrating *much* easier. I haven’t really seen much of a difference between xtradb and the innodb plugin for cpu or io scalability in your previous benchmarks. Did I miss that somewhere along the lines? I understand the theory behind why your changes should improve the situation, but in such a complex beast as mysql benchmarks really help.

0
Morgan Tocker

@William – the comparisons in this article are between the built-in InnoDB and XtraDB. In general both the plugin and XtraDB are in a class of their own for both CPU and IO over the built-in InnoDB. For the InnoDB plugin, you can see a history of posts here:
http://www.mysqlperformanceblog.com/?s=innodb+plugin&search_posts=true&search_pages=true&search_comments=true&search_sortby=date

Recent benchmarks are available via Dimitri’s blog:
http://dimitrik.free.fr/blog/archives/08-01-2009_08-31-2009.html

Do note when reading benchmarks that a lot has changed in the last year (or even the last week!). We are still to publish a benchmark of plugin 1.0.4 ourselves.

0
Mark Callaghan

#11 — I learn a lot by reading XtraDB code. I also get many ideas from XtraDB. And finally, when I want to solve a problem different than XtraDB, I have something to compare it to.

0
Morgan Tocker

Thanks Mark 😉 I think we have you/the team at Google to thank for items 1,3,4 and 8.

0
Mark Callaghan

And Yasufumi. He got us started with his code, results and presentation at the user conference a few years back.

0