MySQL no longer recommended and remove Percona

Did a couple quick tests, grabbed what was on the top of docker hub.

Modified the test sample a bit, removed the selecting from the table which might invoke data access layers, disk caches, etc and went for a simple in memory "select 1" which puts us through some of the most performance impacting portion of the test (connection establishment) and should exercise most of the TCP stack of both implementations.

Test performed on a non-performance tuned laptop.

NOTE:
Mysql cli can treat 'localhost' as a local connection and use the socket. The below tests likely did not tested the TCP layer. Please review later posts for additional timing figures.

MariaDB 11.5.6
Note: this required installing the mysql-client package in the docker image as it is not included by default.

root@c3c4e37d23f4:/# time (for i in {1..1000}; do mysql -h localhost -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m7.469s
user	0m3.735s
sys	0m3.371s


root@c3c4e37d23f4:/# time (for i in {1..1000}; do mysql -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m7.115s
user	0m3.579s
sys	0m3.201s

MYSQL 8.0.39:

root@639faa18ba64:/# time (for i in {1..1000}; do mysql -h localhost -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m7.981s
user	0m4.312s
sys	0m3.289s

root@639faa18ba64:/# time (for i in {1..1000}; do mysql -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m7.839s
user	0m4.219s
sys	0m3.234s

Both tests used stock configs as included in the dockerhub images. Both images do have skip_name_resolve.

Given no configs were given in the examples I would wonder if one of the deployments did not have skip_name_resolve. Over 1000 connections that could easily increase the runtime by a factor of 3x.

I did not load a full database into the system however I noted on SLACK using 5x the disk space does not mean 5.x the IO. It could be provisioning space in advance, the engine might read larger block segments at a time, etc. There are a LOT of factors that are not documented in this post.

There are a lot of factors that go into database performance. I'm not saying Mysql or Mariadb have the same performance, only that as far as databases are concerned, deployment and configuration is generally up to a DBA and a sysadmin.

I would however expect if the performance of MariaDB with TCP is actually always 300% better than Mysql that it should be easiy to cite reference papers that backup the assertion with detailed documentation of the deployments to allow reproducing the results.

Possibly socket test results above

I copied your commands, however I forgot that the mysql cli client gets fancy with "localhost" and can bypass the TCP stack to use the socket. Re-running the tests with 127.0.0.1 which does not bypass the TCP stack.

Any chance you may have made the same error in testing with mariadb?

Mysql:

root@7d4aba32d039:/# time (for i in {1..1000}; do mysql -h 127.0.0.1 -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m13.991s
user	0m7.940s
sys	0m3.851s

root@7d4aba32d039:/# time (for i in {1..1000}; do mysql -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m8.588s
user	0m4.651s
sys	0m3.376s

Mariadb:
NOTE: The results on this are so outlandish I have trouble believing them, however I ran them multiple times with similar results. I consider this result to be invalid however I'm posting them in the spirit of not withholding data and showing that without tuning the setup there can be one setting somewhere that can throw off an entire test set. I have not dug in to evaluate what caused this massive increase compared to Mysql.

root@9fda3699b273:/# time (for i in {1..1000}; do mysql -h 127.0.0.1 -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m55.082s
user	0m43.279s
sys	0m4.949s


root@9fda3699b273:/# time (for i in {1..1000}; do mysql -u root --password='test' test -e "select 1;" 2>&1; done)>/dev/null;

real	0m7.524s
user	0m3.737s
sys	0m3.460s

Even if I assume that worst case Mariadb sees zero decrease using the TCP stack

MariaDB comes through much faster with tcp binding on 127.0.0.1 or a mariadb endpoint on another host compared with MySQL.

I did not see that included in your tests above. At that point your now including a lot more factors. Performance tuning becomes even more important. This is the point that you need to involve your DBA and your Sysadmin to make these configuration changes. I do expect remote connections to be more variable, and to be more susceptible to even minor issues. Again though if your reaching this point you likely are involving the appropriate stakeholders to properly evaluate your database engine choice.

Better yet, try on Azure.

I would suggest we focus on local testing before involving third parties that have even more significant variables related to them that we can not control

How about GitLab CI results? They exercise a large amount of the code base: I grabbed a pipeline earlier today and checked the Functional PHPUnit job (has 7 sub jobs for both). I did not normalize for container setup time or anything else so there is possibly some variance in this data.

MySQL did take about ~35 minutes compared to Mariadb taking ~25. 1.4x the runtime, but not 3x. the runtime. Those tests use a 'network connection' across multiple 'pods'. This does involve full tcp stacks of multiple systems, however it does avoid the random jitter and slowness of real wirespeed.

MariaDBs proxysq

Can you link me to the manual page on this? This sounds like were not longer testing the database itself and now involving a cluster setup, one that perhaps might be included with the product but not enabled by default. At that point are we also testing the same thing? If mysqld doesn't include one in the standard package but allows you to provide your own that can still be expected to be part of 'tuning your database server'

This was discussed amongst the release managers, @catch, @longwave and myself. We touched on the fact that MySQL is sort of stagnating, but also we have issues to bring MongoDB to core. It was a reminder that any 'recommendation' for a database server may change. But also, we can't drop mention of MySQL, it is still widely used and we test using it. We don't test with a Percona server, so that should be removed. It also come up that we understood the 'recommendation' to be about using a MySQL equivalent instead of PostGreSQL or SQLite, and not necessarily MySQL itself.

As for the text, we agreed that that heading could use an update. And we decided to reduce it to "MySQL, MariaDB, or equivalent".

Should we also drop mentions of Percona from the installer here? There are known Percona bugs (#3143562: Lock issues when clearing cache on Percona XtraDB cluster), and several other MySQL-compatible engines that we don't mention.

For reference Percona was added in 2013 in #2062399: Add Percona Server to list of supported MySQL alternative databases.

Even if MySQL would be faster or need less disc space I would try to avoid because I have a long history with problems that came with MySQL in several projects and were be "solved" by switching to MariaDB in the last 15 years. MariaDB even solved a problem with a special error on data structure on upgrading from MySQL 5.* to 8 in a customer project. For the related error code I only could find an entry in the WWW behind a paywall for commercial customers of oracle. We finally exported the database, copied it to an external MariaDB, exported it again and, then imported the database in MySQL 8 because MariaDB was able to just import and fix the error. And since we have fun with the new collations types which are only used by them. See for example: #3264580: MySQL collation complexities leads to backup incompatibilities

We also should warn users that both databases are not longer fully compatible in meaning of import/export is getting more and more complex especially with new JSON features.

While I was reviewing #3568123: Update INSTALL.txt for Drupal 12 last night I was reminded of this issue. Specifically, it's because I saw the Percona server version string in the INSTALL.txt file, which I wonder if anyone gave any thought toward when determining the D12 requirements. Would it be a good idea to wrap up this discussion before the Drupal 12 release?

The MR looks good to me. I grepped core for "Percona" and didn't get any results. This will conflict with #3568123: Update INSTALL.txt for Drupal 12.

The Needs Review Queue Bot tested this issue. It fails the Drupal core commit checks. Therefore, this issue status is now "Needs work".

This does not mean that the patch necessarily needs to be re-rolled or the MR rebased. Read the Issue Summary, the issue tags and the latest discussion here to determine what needs to be done.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

Post-bot-rebellion rebase

Committed and pushed 4a0524ee0a8 to main and b8243e09355 to 11.x. Thanks!

It looks like the performance difference seen between MariaDB and Mysql is caused BY TLS being enabled by default on MYSQL 8.
MariaDB 11.4+ enables TLS by default
http://mariadb.com/docs/server/security/encryption/data-in-transit-encry...

https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html

@heyyo Great point, it explains TCP connection measurments but what about socket connection? Curios to see different results

It seems local socket are already considered secured by MySQL, so TLS is not used.

https://dev.mysql.com/doc/refman/8.4/en/connection-options.html#option_g...