Fun with Bugs #14 - InnoDB in MySQL 5.6

InnoDB improvements in MySQL 5.6 are well known. One of the key reasons to upgrade to MySQL 5.6 for most users is to get the benefits of improved performance, scalability, new monitoring features and fulltext indexes support in InnoDB.

Is there anything to double check before assuming that InnoDB in MySQL 5.6 is just better than any older version for any practical purposes? Let's review known public InnoDB-specific bug reports. Here is my "Top 10" list, as of MySQL 5.6.12, starting with most recent reports:

1. Bug #69424  - maybe I miss something (I am not the only one though), but I see no way to continue using raw devices (on Linux at least) to store InnoDB data. You had working raw device in 5.5.32, then you upgrade to 5.6.12 and just can not start MySQL any more. Check this bug for the details and maybe you'll find out what we all miss in this case.
2. Bug #69356 - insert (or change, if you prefer) buffer is a known source of problems for InnoDB, historically. This is one more case when server can not start up successfully because "ibuf_restore_pos failed when trying to restore cursor to the first record of a leaf page". There is work in progress on similar/related internal bug report it seems, so we may expect some fix soon.
3. Bug #69325 - with default settings MySQL may consume unexpected amounts (too much) of memory while altering InnoDB table with many partitions. Take care!
4. Bug #69316 - Drop/Alter table takes much longer time in 5.6 than 5.5 (with big InnoDB buffer pool that is mostly filled). We all remember long story of fixing similar cases in MySQL 5.5, so here we have really bad news: it seems some of these fixed had not made their way to MySQL 5.6, or there is a regression for some other reason. But that's it - things you expect to work fast enough for a long time already may be slow again in MySQL 5.6.
5. Bug #69236 - skip the beginning of the bug report (impact of utf8 or client problems is a topic for other reports and posts) and move on to perf results at the end. This is what was verified, I assume. MySQL 5.6 is spending a lot more time in rec_get_offsets_func, trx_undo_report_row_operation and btr_cur_optimistic_insert functions comparing to older versions. This is one of the reasons for performance regressions for single-threaded workloads reported by Facebook. Note that on slave it is usually single SQL thread that does a lot of work, so this regression matters.
6. Bug #69179 - persistent statistics for InnoDB tables in MySQL 5.6 is not really persistent. Even with read only workload statistics changes when you query from information_schema.partitions, and this may cause query plans to change.
7. Bug #69174  - it seems page cleaner thread may sleep too long time (is not working hard enough) in some cases. Hardly to be fixed any time soon. Bug #69170 has somewhat similar future it seems in terms of getting fix from Oracle. Some day, maybe.
8. Bug #69168 - not sure if this is in 5.6 only, but SELECT COUNT(*)...GROUP BY sometimes returns wrong results on partitioned InnoDB tables. Check this to make sure your queries are not affected.
9. Bug #69141 - SELECT from InnoDB table hangs being run in parallel with replicating LOAD DATA. It seems you may get a surprise after upgrading slave to MySQL 5.6...
10. Bug #69002 - interesting finding by Domas: InnoDB reads transaction logs on writes. Let me quote: "With large transaction logs that means that either InnoDB will hit this a lot, or amount of memory equal to innodb transaction log will be wasted". If you are a big fan of Domas and/or Sinisa and/or discussion it's worth reading, no matter how big are your logs or what MySQL version you use.

I had to stop now because of "Top 10" format (too many bugs to list before my Bug #68079 that questions scalability improvements in a simple enough real life use case anyway, so why bother...). I've skipped numerous bug reports about "wasted work",  or "redundant code" - while important for further performance improvements, they are hardly important for those who consider upgrade to MySQL 5.6. Something that looks like a regression or change in behavior, or no real improvement in performance, or lack of documentation is more important in this case, I think. I've also skipped Windows-specific (legacy) problems like Bug #69326 and bugs that are surely repeatable with older MySQL server versions as well. So, it's as much about MySQL 5.6-specific surprises as possible.

To summarize: you may get problems at InnoDB level as well when upgrading from older versions to MySQL 5.6. From inability to use raw devices to slow DROP table (again), wrong results of your queries and huge memory use/swapping. Or you may just enjoy improved scalability and great new features... It depends, as usual. Just make sure you reviewed know bug reports before making final decision on upgrade and set your expectations accordingly.