MySQL should no longer be "Recommended"

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.

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.

I see socket tests above , I don't see a TCP ip in the select statement mentioned above.

With that said, our testing has focused on Azure. There's most likely cloud (pay-wall) tcp throttling software that is at play.

To fully see the TCP performance gap between MariaDBs proxysql and mysql, suggest to consider:

Webserver host -> DB Server on another host

Better yet, try on Azure.

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'

Interesting results from your tests!

Try reducing the count from 1000 to 1 and remove the redirect to null (>/dev/null) to make sure that the test is returning the expected result.

I didn't mention this but to ensure the test is running correctly before scaling it up to 1000 and remove the dev null redirect.

With that said, a simple test of drush cr showed well above 30 seconds for a mysql Server B setup within the azure cloud
Whereas the cheapest mariadb on server B was much better.

Our tcp results may be skewed due to packet filtering in black blox networks we're dealing with.

also, I made sure that all the configs were default between tests. the -client software for mariadb used when connecting to mariadb and the mysql client software used to connect to mysql , all with default configurations. I believe even max_allowed_packet is fine for Drupal now with the latest defaults but if there was a setting changed, this would be the only setting.