MySQL 5.7 slow with Bacula 7.4

I'll use your tests to try to make a simple test case for this, and pass it on upstream.

OK, thanks. Since you have an Oracle email address, you might be
particularly nterested to know that I tested the MariaDB on Ubuntu 16.04
in place of MySQL, and it was even slower :-)

On 06/24/2016 01:08 PM, Lars Tangvald wrote:
> I'll use your tests to try to make a simple test case for this, and pass
> it on upstream.
>

That might actually turn out to be important; IIRC the version of MariaDB in Ubuntu 16.04 is closer to MySQL 5.6 than 5.7, so this might be a performance regression introduced in 5.6. Thanks :)

I can't seem to reproduce this issue.

I've tested according to the instructions at http://www.bacula.org/7.4.x-manuals/en/developers/Bacula_Regression_Testing.html (commenting out all disk tests except the two you noted), and I get about the same results as the ones you report for Postgres on:
MySQL 5.5, 5.6 and 5.7.13 (upstream) on Ubuntu 14.04
and Mysql 5.7.12 and 5.7.13 (upstream) on Ubuntu 16.04
i.e similar to:
  ===== acl-xattr-test OK 16:43:56 00:00:20 20s =====
  ===== action-on-purge-test OK 16:45:15 00:01:18 78s =====

I did struggle a bit to get the tests running with 5.7, both due to linking issues with mysqlclient_r and user config, but those made the tests fail outright, not take longer. Tested on fairly clean virtualbox vms with Ubuntu server.
I'll try to test on a desktop system as well. Do you have anything else installed that uses MySQL?

Also, were the tests run with a clean MySQL install, or upgraded from previous versions?

Also also, could you post the output from the test with REGRESS_DEBUG=1?

Yes, on 5.7 the mysqlclient_r was replaced with mysqlclient, which caused some linking problems until I corrected the configure.in script.

No, I do not have anything else installed that uses MySQL, but I can run more of the regression tests. All the tests on 5.7 were run on a new installation of MySQL without any upgrade.

Note, since those tests, I have rebuilt the Ubuntu 16.04 machine with a new installation of Ubuntu with new slightly faster disks, but more importantly the system is booted on ZFS, while the previous 5.7 test were run on an ext4 system.

I will re-run the tests with what I have but with more tests. This will take a day or two, since I am still restoring the user files ...

I will be happy to run with REGRESS_DEBUG=1, but I am not sure it will help -- except more debug will be printed. What exactly are you looking for?

I didn't look too closely at the extra debug output yet, so can't be sure it'll help, but my thought was simply to compare it to my run and see if anything stands out, if any specific part takes much longer in one run, etc.
Also note I haven't run the tests with Postgres to compare, but doubt my system would be 10x faster than yours.
I didn't see any difference between 5.5/5.6/5.7 either, though with running times from 17-24 seconds that's a bit uncertain.

The good news is that I still have the disk with the Ubuntu 16.04 install where I did my original testing, so I plugged it in and booted it. I am now running tests with exactly the same environment I previously used.

The bad news is that I still see substantial timing differences between MySQL 5.7 and Postgres 9.5. Even more concerning is that there seem to be a number of Bacula regression failures with MySQL 5.7. I am working on this and will get back to you with some more detailed information on MySQL 5.7 timing and test failures, both of which I want to examine in some detail before reporting back.

Lars,

I am sorry. First, I had installed MariaDB and so the failures were for MariaDB and not MySQL.
MariaDB is more than 10 times slower than PostgreSQL, and it shows very clearly in iostat and perf top output. iostat shows much higher iowait numbers than PostgreSQL, it appears that most of the difference in performance is just MariaDB (and thus probably MySQL) sitting waiting for I/Os.

Unfortunately after installing MariaDB, it is not possible to remove (purge) MariaDB and then re-install MySQL. The installation of the server always fails, and I get lots of problems such as Can't connect to local MySQL server through socket ... as well of errors saying that the unix_socket plugin is not installed. One of the things I could not do was to compare the default my.cnf files on 14.04 and 16.04 to see if anything that could change the "out-of-the-box" performance changed.

At this point, I have to give up, because I have too many urgent projects pressing on my. I am convinced that there is a serious performance problem on both MySQL and MariaDB compared to PostgreSQL when working with Bacula and both being untuned.

I also think there is a "bug" in apt-get purge of MariaDB server that leaves some traces of MariaDB lying around so that a subsequent install of MySQL fails -- that probably should be the subject of another bug report. I see there is lots of advice on the web for fixing this problem, but I keep running into problems such as no mysql_safe, ... It is so bad that even apt-get purge mysql-server fails in horrible ways.
Running as root:

======================
apt-get purge mysql-server mysql-client
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package 'mysql-client' is not installed, so not removed
Package 'mysql-server' is not installed, so not removed
0 upgraded, 0 newly installed, 0 to remove and 43 not upgraded.
1 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Setting up mysql-server-5.7 (5.7.12-0ubuntu1.1) ...
mysql_upgrade: Got error: 1524: Plugin 'unix_socket' is not loaded while connecting to the MySQL server
Upgrade process encountered error and will not continue.
mysql_upgrade failed with exit status 11
dpkg: error processing package mysql-server-5.7 (--configure):
 subprocess installed post-installation script returned error exit status 1
Errors were encountered while processing:
 mysql-server-5.7
E: Sub-process /usr/bin/dpkg returned an error code (1)

=============

I would really like to find out what is hurting the performance, but it will have to wait to a later time.

I suspect the problem is that purging MariaDB doesn't remove the database (the maintainer scripts are supposed to ask, but seems there's a bug with this). MariaDB and MySQL are no long valid drop-in replacements of each other, so MySQL won't be able to to work with a MariaDB database, which is probably why the installation fails. rm -rf -ing /var/lib/mysql and /etc/mysql should fix it.

I can try testing with MariaDB on my box and see if I get the same result, at least.

I was able to get similar performance problems with MySQL by severely limiting the limit on file descriptors allowed by systemd (it would also make some tests fail outright because it was at the edge of what the server can live with). If there's something in Maria's default config that sets this, it might be the cause.

With the default MariaDB install on my Xenial vm I get about the same numbers for those two tests I got with MySQL: 21s and 77s, so must be caused by something more than just the default configs.

Thanks for your tip. It did get me past the problem with apt not wanting to downgrade from version 10 to 5.7. However, it created other problems.

Unfortunately, I am unable to re-install MySQL even after removing all traces of MariaDB and MySQL. There are always errors.

It is so bad that even:

  apt-get purge mysql-server mysql-client mysql-common

fails.

All attempts to install/remove/purge MySQL fail.

Bottom line: if you install MariaDB, it renders your system in a state that you can no longer revert to MySQL.

I suggest the MySQL purge and MySQL install packages need some testing under the condition that MariaDB is installed. In my case, I first installed MySQL server and client, then with no problems installed MariaDB (it supposedly removed MySQL, which was not really what I wanted, but it wasn't unreasonable).

I have been using Ubuntu for a very long time now, and sometimes I break packages or they fail, and I have always been able to recover from package installation problems using some combination of purge, dpkg --configure -a and apt-get -f install. In this case, I seem to be blocked every way I turn. The latest of many is:

apt-get purge mysql-server-5.7
dpkg: error processing package mysql-server-5.7 (--purge)
 package is a very bad inconsistent state; you should
 reinstall it before attempting a removal.

Are there any mysqld processes still running?

No, there are no MySQL programs running, and the daemon is not running.

I have tried to clear the errors the best I can and go back and reload everything, but I am completely stuck with trying to remove the mysql-server-5.7. The problem is (reproduced here manually):

AppArmor parser error for /etc/apparmor.d/user.sbin.mysqld in /etc/aptarmor.d/usr.sbin.mysqld at line 9: Could not open "abstractions/mysql".

I have tried everything to fixup apparmor with the exception of removing it, because it will remove 90% of the system. At this point I will probably have wipe the disk and reload from a USB stick. That will take several days (the disk holds about 1.5TB of data). I'll check if there is some way to reload the whole OS without erasing the disk, but I find that the installation process are not very clear on what the various options will really do to your partitions prior to letting it run.

At this point the least frustrating and possibly the fastest is to bring up a new Ubuntu 16.04 installation on another disk (without MariaDB!) and copy the bulk of the 1.5TB of data files (mainly my VMs to build various OS versions of Bacula).

I am rather embarrassed to be taking your time for something seemingly so elementary and simple. I would feel better to disappear for a week and come back when I have a running system.

I've seen such issues pop up before with apparmor, unfortunately; profiles being left in a halfway state when testing with rapidly installing/uninstalling various packages.
It might be simple to fix, though, as I think I've cleared this up before:
Is there an etc/apparmor.d/usr.sbin.mysqld file? If so, try just deleting it and running sudo /etc/init.d/apparmor reload

I tried the latest apparmor trick, but it fails. It still complains about mysql and does not seem able to fix the problem. In addition, it is also complaining about dovecot :-(

Thanks to both you and Robie (off bug). I definitely want Bacula to run well with MySQL and will keep working on this, but this evening I will bring up a new disk because it will give me a clean system that I can count on.

By the way, one of the main reasons for bringing up Ubuntu 16.04 on by "build" machine is for the lxc containers.

OK, I have rebuilt my Ubuntu 16.04 test server from scratch with MySQL loaded and running.

The bad news is that I can definitively say that Bacula with MySQL 5.7 runs something like 10 times slower than it did with the prior version of MySQL as well as ~10 times slower than Postgres.

The good news is that I had some time while rebuilding the server, and I successfully figured out what the problem is. When Bacula is doing big inserts (we call it batch insert) at the end of the job, it batches the metadata all together in transactions of 25,000 insertions at a time for insertion into the catalog (DB), but this batching is only done if MySQL is multi-threaded, and the old code tests explicitly for libmysqlxxx_r prior to even trying mysql_thread_safe(). So on any current Bacula with MySQL 5.7 there is no batch insert and a huge performance hit.

I have made a first quickie patch and it seems to be working (now MySQL is only ~20-50% slower than the out-the-box postgres, which is normal).

I will get back to you with a correct patch either later today or tomorrow (once I commit it).

I attach a patch to Bacula 7.4.1 that corrects the slow MySQL by properly detecting that the MySQL 5.7 library is thread safe and hence Bacula batch mode can be enabled. Not this patch may also apply to your patched version of Bacula in bug #157092749.

The attachment "Fixes broken Bacula batch mode for MySQL 5.7 -- applies to Bacula version 7.4.1" seems to be a patch. If it isn't, please remove the "patch" flag from the attachment, remove the "patch" tag, and if you are a member of the ~ubuntu-reviewers, unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by ~brian-murray, for any issues please contact him.]

Ah, that's great!
Odd that I didn't see the issue. I've been using my system to test various things like upgrading from earlier MySQL versions, so it's possible I hadn't cleaned up earlier versions completely and the check still passed with 5.7 somehow.

Should make a note of this and get into the habit of scanning any packages with "odd" problems for the old client_r library :)
Thanks for the help!

Oh, of course; to fix the initial mysqlclient_r build failure I replaced every instance of mysqlclient_r in the source with mysqlclient, which probably fixed the check as well. Might be a better solution (mysqlclient_r is identical with mysqlclient in 5.5 and 5.6 as well, so no need to keep the old behavior for any live version of MySQL)?

Yes, your solution will work quite fine. However, it has the downside of only working with MySQL 5.7 and later, so it not appropriate for the project where people link against lots of older versions of MySQL.

I think the original Ubuntu bug report (https://bugs.launchpad.net/bugs/1570923) on the linking problems with MySQL has a patch that uses my patch (and now hopefully my second patch).

libmysqlclient_r was included in 5.5 and 5.6 for backwards compatibility, but it's simply a symlink to libmysqlclient which was thread safe in 5.5, so my suggestion should work just as well for systems with 5.5 and 5.6. But a better long-term solution is to simply remove all checks of this nature since they are no longer relevant and only create clutter in the code.

It's mentioned at the bottom of this page: https://dev.mysql.com/doc/refman/5.5/en/c-api-implementations.html

If I could, I would do what you suggest as it would, as you mentioned, simplify the code. The problem is then Bacula 7.4.x would no longer build and run on platforms such as RedHat 4.0, ...

I believe the problem is now resolved in what will be released as 7.4.2, and it is backward compatible with all old MySQL versions.

Thanks for sticking with this problem. If you had not been active at responding, I might have dropped it (given my pains after trying to uninstall MariaDB) and a lot of Bacula + MySQL users would have suffered for quite some time before the performance issue became evident.

Status changed to 'Confirmed' because the bug affects multiple users.

Reassigning to bacula as the fix needs to be in bacula and not mysql.

And thanks Kern for figuring this out and providing us with a patch! I'll assign to Nish, as I know he's looking at patching bacula with some other fixes.

@Kern,

I may have been over-exuberant in my own builds. Can you clarify if the same issue is seen in Xenial?

-Nish

Nish,

The slow speed of MySQL is definitely seen in MySQL 5.7 on Xenial (and perhaps other platforms that release MySQL 5.7). I can say for certain that it does not exist in Ubuntu 14.04 (I am not sure about the intermediate Ubuntu releases 14.10, 15.04, and 15.10). Both the linking problem (mysql_thread_safe() not found), and the slow speed were caused by libmysqlxx_r no longer being present.

I will release Bacula 7.4.2 tomorrow, which will have the problem fixed within the Bacula source code (i.e. it will have the two patches that I have posted).

@Kern,

Great, thanks for the clarification. If you would be so kind as to test the Xenial & Yakkety (maybe in lxc?) builds from my PPA, that would be very helpful! I'll also submit all the patches to Debian from Yakkety.

Thanks,
Nish

Hello all,

After reviewing the other open Bacula bugs, I've set up a new PPA with a more normal versioning and (hopefully) fixes to all the issues I know about on 16.04 and 16.10 (and I think handles upgrading from 14.04 to 16.04)

https://launchpad.net/~nacc/+archive/ubuntu/bacula3

16.04: 7.0.5+dfsg-4ubuntu0.1~ppa1
16.10: 7.4.2+dfsg-1ubuntu1~ppa1

Please test these versions and report back, I'd like to see if we can these both sponsored quickly.

Hello Kern, or anyone else affected,

Accepted bacula into xenial-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/bacula/7.0.5+dfsg-4ubuntu0.1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-needed to verification-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

Verified with 7.0.5+dfsg-4ubuntu0.1.

This bug was fixed in the package bacula - 7.0.5+dfsg-4ubuntu0.1

---------------
bacula (7.0.5+dfsg-4ubuntu0.1) xenial; urgency=medium

  * debian/patches/libmysqlclient_r.patch: Fix bug where MySQL 5.7 is
    improperly linked on Ubuntu 16.04. Closes LP: #1570923.
  * debian/patches/disable_mysql_NO_ZERO_DATE.patch: Disable
    NO_ZERO_DATE mode for MySQL table creation. Closes LP: #1591397,
    LP: #1577858.
  * d/rules: do not use -Bsymoblic-functions when linking. Closes
    LP: #1553563, LP: #1567824.
  * debian/patches/fix_batch_mode.patch: Detect that MySQL 5.7 is thread
    safe and enable batch mode. Closes LP: #1592749.

 -- Nishanth Aravamudan <email address hidden> Fri, 15 Jul 2016 14:34:59 -0700

The verification of the Stable Release Update for bacula has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.