On Some Problematic Oracle MySQL Server Features

In one of my previous posts I stated that in Oracle's MySQL server some old enough features remain half-backed, not well tested, not properly integrated with each other, and not documented properly. It's time to prove this statement.

I should highlight from the very beginning that most of the features I am going to list are not that much improved by other vendors. But they at least have an option of providing other, fully supported storage engines that may overcome the problems in these features, while Oracle's trend to get rid of most engines but InnoDB makes MySQL users more seriously affected by any problems related to InnoDB.

The Royal Pavilion in Brighton looks nice from the outside and is based on some great engineering decisions, but the decorations had never been completed, some interiors were ruined and never restored, and the building was used for too many different purposes over years.

The list of problematic MySQL server features includes (but is not limited to) the following:

• InnoDB's data compression
  
  Classical InnoDB compression (row_format=compressed) has limited efficiency and does not get any attention from developers recently. Transparent page compression for InnoDB seems to be originally more like a proof of concept in MySQL that may not work well in production on commodity hardware and filesystems, and was not integrated with backup tools.
• Partitioning
  
  Bugs reported for this feature by MySQL Community do not get proper attention. DDL against partitioned tables and partition pruning do not work the way DBAs may expect. We still miss parallel processing for partitioned tables (even though proof of concept for parallel DDL and some kinds of SELECTs was ready and working 10 years ago). Lack of careful testing of partitioning integration with other features is also visible.
• InnoDB's FULLTEXT indexes
  This feature appeared in MySQL 5.6, but 5 years later there are still all kinds of serious bugs in it, from wrong results to hangs, debug assertions and crashes. There are performance regressions and missing features comparing to MyISAM FULLTEXT indexes, and this makes the idea to use InnoDB for everything even more problematic. Current implementation is not designed to work with really large tables and result sets. DBAs should expect problems during routine maintenance activities, like ALTERing tables or dumps and restores when any table with InnoDB FULLTEXT index is involved.
  
  
• InnoDB's "online" DDL implementation
  It is not really "online" in too many important practical cases and senses. Replication ignores LOCK=NONE and slave starts to apply "concurrent" DML only after commit, and this may lead to a huge replication lag. The entire table is often rebuilt (data are (re-)written) to often, in place or by creating a copy. One recent improvement in MySQL 8, "instant ADD COLUMN", was actually contributed by Community. The size of the "online log" (that is kept in memory and in temporary file) created per table altered or index created, depends on concurrent DML workload and is hard to predict. For most practical purposes good old pt-online-schema-change or gh-ost tool work better.
  
  
• InnoDB's persistent optimizer statistics
  
  Automatic statistics recalculation does not work as expected, and to get proper statistics explicit ANALYZE TABLE calls are still needed. The implementation is complicated and introduced separate implicit transactions (in dirty reads mode) against statistics tables. Bugs in the implementation do not seem to get proper priority and are not fixed.

I listed only those features I recently studied in some details in my previous blog posts. I've included main problems with each feature according to my older posts. Click on the links in the list above to find the details.

The Royal Pavilion of InnoDB in MySQL is beautiful from the outside (and somewhere inside), but is far from being completed, and some historical design decisions do not seem to be improved over years. We are lucky that it is still used and works nice for many current purposes, but there are too many dark corners and background threads there where even Oracle engineers rarely look and even less are improving them...

On InnoDB Data Compression in MySQL

Another story that I've prepared back in April for my meeting with one of customers in London was a "compression story". We spent a lot of time on it in several support issues in the past, with only limited success.

In case of InnoDB tables, there are actually two ways to compress data (besides relying on filesystem compression or compressing individual columns at server or application side). Historically the first one was introduced by the Barracuda InnoDB file format and ROW_FORMAT=COMPRESSED it supported. Notable number of related bugs were reported with time, and it may be not that easy to identify them all (you can find current list of bugs tagged with "compression" here). I've picked up the following bugs for my "story":

• Bug #88220 - "compressing and uncompressing InnoDB tables seems to be inconsistent". Over years Simon Mudd, Monty Solomon (see related Bug #70534 - "Removing table compression leaves compressed keys") and other community members reported several bugs related to inconsistencies and surprises with key_block_size option. It is used for both MyISAM and InnoDB storage engines (for compressed tables) and it seems nobody is going to fix the remaining problems until they are gone with MyISAM engine.
• Bug #69588 - "MyISAM to InnoDB compressed slower than MyISAM to InnoDB, Then InnoDB to Compressed". Just a detail to take into account, noted 5 years ago by Joffrey MICHAIE, verified almost 4 years ago and then getting zero public attention from Oracle engineers.
• Bug #62431 - "What is needed to make innodb compression work for 32KB pages?". Nothing can be done according to the manual:
  

  "In particular, ROW_FORMAT=COMPRESSED in the Barracuda file format assumes that the page size is at most 16KB and uses 14-bit pointers."

• Bug #78827 - "Speedup replication of compressed tables". Come on, Daniël van Eeden, nobody cares that
  

  "Replication and InnoDB compressed tables are not efficiently working together."

  The bug is still "Open".
• Bug #75110 - "Massive, to-be-compressed not committed InnoDB table is total database downtime". This problem was reported by Jouni Järvinen back in 2014. Surely this is not a bug, but it seems nobody even tried to speed up compression in any way on multiple cores.
• Bug #84439 - "Table of row size of ~800 bytes does not compress with KEY_BLOCK_SIZE=1". It was reported by Jean-François Gagné, who asked for a reasonable error message at least. Nothing happens after verification.
• Bug #77089 - "Misleading innochecksum error for compressed tables with key_block_size=16". This problem was reported by Laurynas Biveinis more than three years ago, immediately verified and then got zero attention.

The boats above do not use the space for mooring efficiently. They need better compression.

Transparent Page Compression for InnoDB tables was added later and looked promising. If you are lucky to use filesystem with sparse file and hole punching support and proper OS or kernel version, then you could expect notable saving of disk space with very few additional keystrokes (like COMPRESSION="zlib") when defining the table. Different compression libraries were supported. Moreover (see here), only uncompressed pages are stored in memory in this case, and this improved the efficiency of buffer pool usage. Sounded promising originally, but there are still bugs to consider:

• Bug #78277 - "InnoDB deadlock, thread stuck on kernel calls from transparent page compression". This bug alone (reported by Mark Callaghan back in 2015) may be a reason to NOT use the feature in production, as soon as you hit it (chances are high). there are many interesting comments that there are environments where the feature works as fast as expected, but I think this summary is good enough for most users:

  "[19 Oct 2015 15:56] Mark Callaghan
  ...
  Slow on XFS, slow on ext4, btrfs core team tells me it will be slow there. But we can celebrate that it isn't slow on NVMFS - closed source, not GA, can't even find out where to buy it, not aware of anyone running it."

  The bug is still "Open".
• Bug #81145 - "Sparse file and punch hole compression not working on Windows". Not that I care about Windows that much, but still. The bug is "Verified" for 2 years.
• Bug #87723 - "mysqlbackup cannot work with mysql5.7 using innodb page-level compression" Now this is awesome! Oracle's own MySQL Enterprise Backup does NOT support the feature. Clearly they cared about making it useful...
  As a side note, same problem affects Percona's xtrabackup (see PXB-1394). MariaDB resolved the problem (and several related ones like MDEV-13023) with mariabackup tool.
• Bug #87603 - "compression/tablespace ignored in create/alter table when not using InnoDB". COMPRESSION='.../' option is supported for MyISAM tables as well, and this again leads to problems when switching to another storage engine, as Tomislav Plavcic noted.
• Bug #78672 - "assert fails in fil_io during linkbench with transparent innodb compression". This crash (assertion failure) was noted by Mark Callaghan back in 2015. May not crash anymore since 5.7.10 according to the last comment, but nobody cares to close the bug or comment anything useful. The bug is still "Verified".

That's almost all I prepared for my "compression story". It had to be sad one.

What about the moral of the story? For me it's the following:

1. Classical InnoDB compression (page_format=compressed) has limited efficiency and does not get any attention from developers recently. If you hit some problem with this feature you have to live with it.
2. Transparent page compression for InnoDB seems to be originally more like a proof of concept in MySQL that may not work well in production on commodity hardware, and software and was not integrated with backup tools. MariaDB improved it, added support for backing up page compressed tables efficiently with the same familiar xtrabackup-based approach, but there are still open problems to resolve (see MDEV-15527 and MDEV-15528 that I also picked up for my "story").
3. It seems (based on public sources review at least) that both compression options do not get much attention from Oracle developers recently. If you check new features of MySQL 8.0 GA here,  you may notice that zlib version is updated, compressed temporary InnoDB tables are no longer supported and... that's all about compression for InnoDB!

This story could probably be shortened to just one link to the summary post by Mark Callaghan from Facebook (who studied the efficiency of data compression by various engines a lot, among other performance metrics), or by simple statement that if you want data to be compressed efficiently at server side do NOT use current InnoDB implementations and better use RocksDB engine (with MariaDB or Percona Server if you need other modern features also). But I can not write any story about MySQL without referring to some bugs, and this is how I've ended up with the above.

What if you just switched to MySQL 8.0 GA and need some new features from it badly? Then just wait for a miracle to happen (and hope Percona will make it one day :)

Fun with Bugs #49 - Applying PMP to MySQL

As you maybe noted, in several recent posts I've provided some additional details for slides used during my FOSDEM talk on profiling MySQL. The only part not covered yet is related to using Poor Man's Profiler (and pt-pmp version of it). I see no reason to explain what it does and how to use it once again, but would like to show several recent enough MySQL bug reports where this tool was essential to find, explain or demonstrate the problem.

Quick search for active bugs with "pt-pmp" in MySQL bugs database produces 8 hits at the moment:

•  Bug #85304 - "Reduce mutex contention of fil_system->mutex". It was reported by Zhai Weixiang few days ago, and pt-pmp output was used as a starting point for the analysis that ended up with a patch suggested.
• Bug #85191 - "performance regression with HANDLER READ syntax", from the same reporter. In this report pt-pmp was used to prove the point and show what exactly threads were doing.
• Bug #80979 - "Doublewrite buffer event waits should be annotated for Performance Schema", by Laurynas Biveinis. One more case when PMP shows where the time is spent by threads in some specific case, while there is no instrumentation (yet) for the related wait in Perfomance Schema.
• Bug #77827 - "reduce cpu time costs of creating dummy index while change buffer is enabled", again by Zhai Weixiang. In this bug report he had used both perf to show that some notable time was spent on the operation, and pt-pmp to show the related backtraces.
• Bug #73803 - "don't acquire global_sid_lock->rdlock if gtid is disabled". Once again, Zhai Weixiang used pt-pmp output as a starting point for further code analysis.I wonder why this bug is still "Open", by the way...
• Bug #70237 - "the mysqladmin shutdown hangs". Guess who reported it after applying PMP when something hanged. As I stated in all my 3 recent FOSDEM talks, this is exactly what you have to do before killing and restarting MySQL server in production - get backtraces of all threads, raw or at least aggregated with pt-pmp... I am not sure why the bug was not noted in time, there are even ideas of patches shared. Time for somebody to process it formally.
• Bug #69812 - "the server stalls at function row_vers_build_for_consistent_read". Same reporter, same tool used, same result - the bug report is still "Open". Looks like I know what my next post(s) in this "Fun with Bugs" series will be devoted to...
• Bug #62018 - "innodb adaptive hash index mutex contention". It was reported by Mark Callaghan and PMP outputs were used as a part of the evidence. The bug is "Verified" and even got a patch suggested for 5.7.x by Percona engineers, but still had not got any proper attention from Oracle. I may have some more results related to the "cost" and "benefits" of adaptive hash indexing to share soon, so stay tuned...

Surely, there are way more bugs where PMP was used. Let me share one more that I noted while working on my recent talk on profiling (bug had not found time to put it on slides and comment on):

• Bug #78277 - "InnoDB deadlock, thread stuck on kernel calls from transparent page compression", by Mark Callaghan. Again, PMP outputs were provided to prove the point and show where threads are stuck. The bug is "Open".

For many performance related cases applying pt-pmp and sharing the results becomes a de facto community requirement, as you can see, for example, in Bug #84025. Note that Umesh Shastry, who verified the bug, provided pt-pmp outputs in hist testing results. I'd suggest to have gdb and pt-pmp installed and ready to use on any production system using any version and fork of MySQL. Even if your bug will be ignored by Oracle, these outputs are useful for other community members who may hit similar cases or is not lazy to check and work on the code to provide a patch.

MySQL Support Engineer's Chronicles, Issue #1

I have a habit to keep a log of everything I work on (or even just read about while working) every day. It's just a plain text file, with date labels, where most lines are URLs of issues and bugs I contributed to somehow, and my comments on what I did and why. But there are also code fragments, texts, copy-pasted outputs and links to interesting sites, articles and documentation.

Some of lines from this log I post on Facebook separately as I add them, these are mostly related to MySQL bugs and other things that annoy me. I know that some of my Facebook friends actually do not like this kind of posts at all... When I used LiveJornal actively, I've posted digests of my log from time to time (you can check what was there if you read Russian). But that digests contained everything, from funny videos of owls to tips for setting up Informix on Mac OS X, with few MySQL-related things in between. For this blog I decided to resurrect that digests, limit them to things related to MySQL and related forks and technologies, and try to make them regular. The idea is to post what I considered interesting and important at the end of every week. So, this is my first issue of what I am going to call "MySQL Support Engineer's Chronicles". Let's see how it is going to work long term...

This week started with more news related to MyRocks. First of all, it was announced that MyRocks will be included into MariaDB 10.2. New forum was also set up in Google Groups for the related discussions, myrocks-dev. I see only 3 topics there so far, but I still joined this group. I've also spent some time this week testing how well MyRocks scale for SELECTs like those in the famous Bug #68079. Good news is that (while InnoDB after that bug fix still seem to work faster) for that use case MyRocks now runs like 20% faster than last time I checked (end of April, 2016). Both InnoDB in 5.7.15 and MyRocks scale to 32+ threads on my old QuadCore box, that is, they run the same number of queries faster with more threads doing the job. I'll share more details on this test case later, when I get a chance to spend more time running tests with higher concurrency and checking for the bottlenecks.

Other good news were about MySQL & Friends Devroom at FOSDEM 2017. Call for papers is open! FOSDEM is my favorite conference and the only one I am going to participate in by all means every year (as I did in 2015 and 2016). I am going to submit several talks and, besides something with "gdb" in the title, I am thinking about a talk on some special bugs (like Bug #68079, Bug #28404, or Bug #82968) and their influence on MySQL users and further development (maybe even separate talk per each of these bugs), some new MariaDB 10.2 features (like recursive CTEs) or locking in MyRocks.

I've spent some time this week checking different ways to compress data in InnoDB tables (and I expect more work on this next week). Originally the idea was to show that MariaDB's compression really works. I am still thinking about writing a separate blog post showing step by step how it works and why it does not or why somebody can assume it does not (hints: check du and ls -ls commands, don't use just ls -l to make conclusions). For now just check this blog post by my colleague Jan Lindstrom and do not hesitate to try it on HDD with, say, ext4 filesystem. If you care enough to try different compression algorithms (you have to compile MariaDB from source for this), check also this great study by Percona.

Unfortunately these days I can not avoid Galera clusters and related issues, so I spent some time reading and trying to find some proper documentation on what may happen when poor user just decides to shut down all nodes in the cluster (for whatever reason) and then start the cluster again. SST may happen, and this is rarely good. I ended up checking this old post by my former colleague Przemysław Malkowski, some hints by Severalnines and Galera bugs (like Issue #283 and Issue #344). I was reading and thinking about step by step checks for some blog post, but my colleague Geoff Montee already summarized current state of documentation in this Galera Issue #431, "Document that the GCache is invalidated when a node restarts". Take extra care when you decide to shut down the last remaining Galera cluster node!

While checking for known bugs for one of the problems I discussed, I noted this MySQL bug, Bug #80580, "count(*) much slower on 5.7 than 5.6". Unfortunately there was no public test case, even though the bug was "Verified". I do not like to see bug reports like that. So, I've added my test case as a comment. Enjoy and keep thinking that MySQL 5.7 is the best of them all (besides already available 8.0, that added indexes to the tables in Performance Schema). It just has some performance problems with queries that do SELECT count(*) to InnoDB tables, also in case of fulltext indexes, see this recent Bug #83398 by Daniël van Eeden. But nobody cares much about few minor bugs for corner cases (and about those who process that bugs, but that's a different topic)...

By the way, Justin Swanhart had already added support to MySQL 8 to his ps_history schema/tool. Read his blog post for more details. Probably this is one of the first open source third party tools to support MySQL 8 explicitly. Correct me if I am wrong, but the only other one I know about is MySQL Sandbox by Giuseppe Maxia.

During some relatively non-busy time on Friday I decided to upgrade one of my Ubuntu VMs (the one running 15.04) to 16.04, and did that successfully (first to 15.10, then to 16.04). It's nice to know that Ubuntu allowed seamless in-place upgrades since 12.04. I had Percona Server installed from their .deb packages running there, historically, so I made sure it is also upgraded to the latest version for 16.04. In the process I hit a small problem with their repository that Percona tried to warn us all about in this blog post. Unfortunately, as you can see from comments (mine and other user's) suggested way to avoid the warning does not work as expected. I've got no reply from Percona about this.

Also on Friday Alibaba had finally released their AliSQL as open source on GitHub. I've cloned and built it immediately on my Ubuntu 14.04 netbook, and it works! It identifies itself as just MySQL 5.6.32, but read here about some of the great features now available. No bugs found so far, but I had no time to try hard.

So, that were some of things I read about, was excited about, complained about and worked on during this week as a support engineer in MariaDB. Do you consider this kind of digest useful?

Fun with Bugs #39 - Known Bugs in MySQL 5.7.9 GA

These days everybody is excited with recent announcement of MySQL 5.7.9 GA release. If you are not aware of this event yet (I've noted it from numerous posts even during my short vacation), wait for the Oracle Open World 2015 to begin tomorrow to announce it even wider and louder!

I already have 5.7.9 built from source, up and running, so it's time to check what else we can expect from this new GA release besides new great features (this is a topic for a separate post or two) and usual excitement. Yes, I mean known, verified bugs in MySQL 5.7.9.

Let me start with a quick summary and then present the details. So, even though MySQL Community tried hard to check 5.7.x at early stages and report bugs to Oracle, MySQL 5.7.9 GA has a number of known installation and upgrade problems, code is not always clean, not all community suggested patches are included. As usual with new releases, there are some performance issues (one should expect single thread to run slower than on older versions in many cases), including those on replication slaves. Some new features are not completely documented, some implementation details are missing here and there, and transparent page compression, for example, is hardly can be widely used at the moment. All these are expected for users who had seen previous GA releases of MySQL. What's a bit worse, I suspect there are known crashing bugs (sequence of SQL statements that, when executed by a user with enough privileges, crashes non-debug binaries) in MySQL 5.7.9. Also, MySQL 5.7.9 inherits some regression bugs from MySQL 5.6.x.

Quick search for active bugs in version 5.7 reported over last 6 months gives me a list of 157 bugs that probably affect MySQL 5.7.9. I've excluded those that are not yet verified formally, those affecting older versions (5.6.x and 5.5.x), most of documentation and test problems and ended up with the following list that I want to present to those who plan to upgrade to 5.7 GA really soon:

• Bug #78936 - "Installing MySQL on Linux Using Debian Packages missing dependency". This is mostly a documentation request. Please, check comments there for the correct order of .deb packages installation, if you plan to use dpkg.
• Bug #78591 - "mysqld --initialize fails on empty data directory". Some more installation problems on Ubuntu...
• Bug #78895 - "Lack information about generated columns in mysql_fetch_fields function". Now, this is typical, unfortunately, for new MySQL features. They are rarely completely implemented in first few GA releases. Oracle still has to rely on MySQL Community to find these small missing implementation details (or design problems) here and there.
• Bug #78848 - "Docs still claim Performance Schema does not allocate memory after startup". While formally it's just a documentation bug, it is about a notable change in behavior. In 5.7 additional memory may be allocated after startup to support performance_schema. This had never been the case before. My report that is based on real customer case, Bug #78808, "Manual is wrong when explaining the default UMASK value", is of a similar kind - some clear changes in behavior are NOT documented properly before making GA announcement. MySQL 5.7 follows the same bad tradition of 5.0, 5.1, 5.5, and 5.6 GA releases here...
• Bug #78778 - "mysqlclient does not return correct mysql_insert_id via database handle". It seems to be a regression bug (even though it is not tagged so) and as a result your DBD::mysql Perl module may not work with 5.7 the way you expect.
• Bug #78774 - "old-password subcommand is still in mysqladmin --help in 5.7". Minor problem, but I wonder why it could not be fixed in the code before GA release. It seems the build/QA/release process still takes a lot of time in Oracle, so even those bugs noted by Community in RC releases were reported "too late" to influence the GA release. This is unfortunate.
• Bug #78758 - "Inserting (invalid) utf8 data into 5.6.25 master breaks 5.7 slave". There is a workaround documented, but this bug report shows that upgrade from 5.6 may not be seamless and simple (are you surprised?) and that upgrade was NOT really tested really well by the internal QA process. I am not surprised, it had always been the case.
• Bug #78672 - "assert fails in fil_io during linkbench with transparent innodb compression". Facebook engineers are trying hard, but transparent page compression mostly does NOT work well (if only on NVMFS, see Bug #78277 also). Just remember that for now, before you became too excited with the feature.
• Bug #78495 - "Table 'mysql.gtid_executed' cannot be opened." Don't be scared with this warning, it's "Ok"...
• Bug #78374 - "CREATE USER IF NOT EXISTS" reports an error". New feature that just does not work as documented. Well done.
• Bug #78352 - "Slow startup of 5.7.x slave with relay_log_recovery = ON and many relay logs". This my report is also based on a real life customer case. Take care when upgrading, really.
• Bug #78254 - "After running mysql_upgrade proxies_priv user columns are not updated to 32". mysql_upgrade is not able to fix all incompatibilities for further seamless use of MySQL 5.7.
• Bug #78251 - "handle_fatal_signal (sig=11) in mach_double_read". No further comments for now.
• Bug #78236 - "InnoDB: Failing assertion: holder != requestor". Again, no comments.
• Bug #78219 - "compression can be defined on a compressed table if innodb_strict_mode is OFF"
• Bug #78068 - "replication_* tables miss information about binary log position and other". I should not comment on performance_schema any more probably.
• Bug #77740 - "silent failure to start if mysql.gtids_executed gets HA_ERR_LOCK_WAIT_TIMEOUT". You can end up with failures and error log does not help to find out why...
• This bug was actually reported back in February, so it was not in the list of 157 recent reports, and there is nothing unexpected here, but still. Note that single thread performance of MySQL 5.7.x GA may be worse than for older versions, see Bug #75981, "MySQL 5.7.5 30% slower than 5.6.23 for 1 thread write", for example.

Some potential performance issues, mistakes in the code and debug-only assertions (that may show huge internal corruptions) are never noted until somebody starts to test a lot or read the code carefully:

• Bug #78894 - ''buf_pool_resize can lock less in checking whether AHI is on or off".
• Bug #78637 - "Incorrect for loop condition in mysql_crawler.cc"
• Bug #78732 - "InnoDB: Failing assertion: *mbmaxlen < 5 in file ha_innodb.cc line 1803".
• Bug #78728 - "InnoDB: Failing assertion: 0 in file ha_innopart.cc line 3526" 
• ... check the list yourself, there are many more assertion failures and code review problems reported  ...
• Bug #77094 - "Reduce log_sys->mutex contention by allowing concurrent mtr commit and log write". This is just one of many patches contributed by Zhai Weixiang that remain just "Verified"

I expect many more reports of this kind from Roel, Laurynas and my other colleagues now, when Percona is actively working on its own Percona Server 5.7 GA release.

The last but not the least, if you are upgrading to 5.7 GA from 5.5.x or older versions, check known regressions in MySQL 5.6. Many of them are affecting 5.7.9 as well, unfortunately.