A technical WebScaleSQL review and comparison with Percona S_MySQL

The recentWebScaleSQLannouncement has made quite a splash in the MySQL community over the last few weeks, and with a good reason. The collaboration between the major MySQL-at-scale users to develop a single code branch that addresses the needs of, well, web scale, is going to benefit the whole community. But I feel that the majority of community opinions and comments to date have been based on the announcement itself and the organizational matters only. What we have been missing is an actual look at thecode. What actual new features and bug-fixes are there? Let’s take a look.

At the same time, as Percona is also a developer of an enhanced MySQL replacement database server, it’s natural to try to compare the two. So let’s try to do that as well, but an important caveat applies here. Both MySQL branches(a branch and an upstream-tracking fork would be more exact)are being developed with different goals and for different end users. WebScaleSQL is all things scale-performance: diagnostics, specific features-for a relatively narrow and highly proficient group of users. There are no binary releases, the code base is supposed to serve as a basis for further code branches, specific to each corporate contributor. On the other hand, Percona Server is a general-purpose server that is developed with broad input from Percona’s customers, professional services departments, and general community. Thus, it would be unfair to say that one of the branches should be considered better than the other just because a certain feature is missing or not reaching as far. The software serves different needs.

The rest of this post is an annotated list of WebScaleSQL-specific code commits: user-visible features, performance fixes, general fixes, and finally the stuff of interest to developers.

New features

• Ability for clients to specify millisecond (as opposed to second in MySQL)read/write/connect timeouts. This patch also carries an internal cleanup to introduce a timeout data type to avoid second-milisecond unit conversion errors.
• Super read-onlywhen regular read-only is not enough, that is, when writes by SUPER users need to be prevented as well.

All of the above are absent from Percona Server but possible to merge if there is interest. I’d also note that this list is rather short at the moment with some obvious stuff missing, such as the user statistics patch. I’d expect this to change in near future.

Performance-related features and fixes

It isWeb Scale, remember.

• Add an option todisable InnoDB deadlock detection. Removes overhead of the feature if you don’t need it.
• Do not log compressed InnoDB page images by default. If InnoDB compression is being used, reduces (I guess dramatically so) the redo log write rate at the cost of introducing a very slight chance to break crash recovery if zlib was upgraded between an instance crash and its restart.

These two changes require understanding on the user’s part of what are the tradeoffs. They are missing in Percona Server, but, again, possible to merge if there is interest in them.

• Fixfor MySQL bug#72123. Helps if you have many row or table lock waits.
• Prefix index query optimisation. Avoids a primary index read for rows that have the whole row data in the prefix index itself.

Again Percona Server does not carry these. Different from the previous ones, these should be safe for every single user and we could merge them without having to give any further thought to their merits. Oracle MySQL should do the same.

• NUMA options.Our version.

That is one general performance-related change that Percona Server has too.

• Do not compile in Performance Schema by default. A reasonable decision given that MySQL bug#68413is alive and well, but not something we can do in Percona Server by default and keep its drop-in quality.

InnoDB flushing performance fixes

These would belong to the previous section, but I’d like to highlight them separately. We spent a lot of effort to analyse and improve the 5.6 InnoDB flushing before the Percona Server 5.6 GA release and continue to do so in the point releases. The WebScaleSQL changes below show that we and they have discovered a lot of identical improvement areas independently, and provided different fixes for the same issues. For an overview of XtraDB 5.6 changes in this area, gohere andhere. Note that these changes are somewhat more extensive, especially for the high-concurrency cases.

• Back port of 5.7WL #7047 and a fixfor MySQL bug #71411 (fixed in Percona Server too).WL #7047reduces the buffer list scan complexity. We have identified the same issue but attempted to work around it with flushing heuristic tweaks.
• Fixfor MySQL bugs#70500 and#71988 to remove potential flushing instabilities. Both fixed in Percona Server.
• Fixfor MySQL bug#62534, enabling finer-grained setting of innodb_max_dirty_pages_pct and unbreaking it for value zero. Not fixed in Percona Server, but the Oracle fix should be coming in 5.6.19.
• Fixfor MySQL bug#70899, removing redundant flush on server startup, which should speed up crash recovery with large buffer pools. Not fixed in Percona Server. Oracle fix expected in 5.6.20.
• Ability to specify idle system flushing rate. Absent in Percona Server. I believe it should be possible to get the same effect by tuning existing variables: setting innodb_io_capacity lower and innodb_io_capacity_max higher, but it needs experimenting before being able to tell for sure.

General fixes

Fixes for assorted MySQL bugs. None of them are present in Percona Server, they might be merged as needed. Our own list of assorted MySQL bugs we have fixed ishere. I have omitted fixes for MySQL developer-specific bugs, these are listed in the next session.

• Fix for MySQL bug #64751– Make NO_UNSIGNED_SUBTRACTION SQL mode work for additions too, i.e. unifying the cases of “foo – 1″ and “foo + -1″.
• Stopspawning one extra thread on server startup to work around a bug in glibc that was fixed in 2006. Interestingly I was not able to find any MySQL bug report for this. Anyone?
• Preserve slave I/O thread connection settings if compression is used. Again, is there a MySQL bug report for this?
• Fix for MySQL bug#64347– database option mix-up if lower_case_table_names = 0 and the database names differ only by case.

Build changes

Making MySQL play nicer with the system libs, and other assorted changes.

• Static linking of semisync replication plugins, based on a MariaDB patch. This might have a performance angle to it – MySQL bug#70218?
• Do not embedOpenSSL and zlib in the static libraries.
• Fix buildingwith system OpenSSL and zlib. Using system libraries whenever possible make packaging easier and more conformant to Linux distribution requirements. We have been working on this too.
• Fix buildingwith system libreadline (MySQL bug#63130, closed without fix). Likewise.

Developer changes

These are patches of interest to MySQL / WebScaleSQL developers and not immediately visible for end users. I’m omitting some things, such as testcase compatibility with various build options, testsuite timeout tweaks, and patches that integrate with tools used for project development: Jenkins, Phabricator, etc.

• Switchto C++11 and C99, the newer C and C++ language versions. It’s a big change from development perspective and one that is possible to pull off only if the project does not need to support older systems and their compilers (or even the newest compilers on some platforms). This is precisely the kind of thing that is easiest to implement for WebScaleSQL than for everybody else. As for the benefits of the change, the project already makes use of C++11 memory model – see the next item.
• An efficient atomic stat counter framework, using C++11. I wonder how its performance compares to that of get_sched_indexer_t, which is present in Oracle MySQL 5.6, but not used?
• Makingthe Performance Schema MTR suite slightly more sane by not recording stuff that tests nothing and at the same time is prone to change. Performance Schema MTR bits are something I’m sure every single 5.6 branch developer has encountered. This particular commit fixes MySQL bug#68714. Fixed in Percona Server. This is useful if one configures the build to re-enable the Performance Schema.
• More of the same. Half of that commit fixes MySQL bug #68635, which is fixed in Percona Server too but unfortunately was considered by upstream as not requiring any fixes.
• Stabilising the MTR testsuite, SHOW PROCESSLISTbits. Is there a MySQL bug report for this?
• Stabilising the MTR testsuit, missing ORDER BY in 5.6.17 bits. Likewise, is there a bug report for this one?
• Fixfor MTR breaking if there is a ‘@’ somewhere in the working directories. Jenkins CI likes to put ‘@’ there. Same question re. bug report?
• Unbreak a bunch of tests in the parts suite. This looks to me like MySQL bug#69252, but it has been already fixed by Oracle. Is the WebScaleSQL fix still required?
• Re-enable AIO if MTR –mem option is passed.
• Stress tests in MTR.
• Fix compilation with Bison 3, based on a MariaDB patch (MySQL bug#71250). Fixed in Percona Server.
• Fix compilationwarnings (more). A bug report?
• Fixuninitialised variable use warnings as reported by AddressSanitizer. Is there a bug report?
• Fixa potential out-of-bound access, found by AddressSanitizer. Is there a MySQL bug for this?
• Fix CMake confusionof two different ways to ask for a debug build resulting in different builds (MySQL bug#70647, fixed in 5.7).

Notice that the last list is quite long, especially if compared to the list of user-visible features added to date. That makes perfect sense for the project at this stage: building a solid development foundation first so that the features can follow in good quality and reduced maintenance effort. Add a whole bunch of performance fixes to make a big picture view for today: A solid foundation for further development; numerous performance fixes; a few general fixes and new features.

As for comparing to Percona Server, currently the biggest overlap is in the performance-InnoDB flushing-fixes. For the rest, we can merge from WebScaleSQL as necessary – if you thinkthat a certain WebScaleSQL feature or a fix would benefit you,drop us a lineto discuss the options. And of course we invite WebScaleSQL to take any our fixes or patches they would like.