Refactor the mysql tests

[soymgomez]

Hello,

I come to propose to change the way to do the MySQL test because calculating in MySQL both SHA and AES is not the best way and just depending on the implementation of the database engine can change a lot (from a MariaDB, to MySQL or Percona).

For example MariaDB commented in their issues that they had problems replicating the behaviour of MySQL8's AES encryption: https://jira.mariadb.org/browse/MDEV-9069

On the other hand WordPress doesn't use these functions as often as a SELECT or INSERT. As they commented in another issue maybe they better redo this part separating it in 2 tests:

• On the one hand writing (with inserts to a table).
• On the other hand writing (making on that table selects and some join to another table?).

I leave the idea in the air to see what you think @kevinohashi .

Best regards

[kevinohashi]

That is an interesting question.

So the hashing is to test CPU speed mostly. The goal was to run intensive function over and over stressing the CPU via MySql which hashing does a good job of. Reading the description on mariadb, I am not sure I understand it well enough. It looks like there's some issues adding extended parameters and compatibility. Do you think that impacts the core function usage which seems to be able to execute on mariadb now?

The WP benchmark component actually does insert, delete, update, delete already. So what you're suggesting (I think?) it kind of already done in a way.

Did you take a look at that? If so, maybe I am misunderstanding your suggestion

[soymgomez]

With what you tell me I understand that this part is more to check the performance of MySQL than to check the performance of the CPU.

My idea is that if you want to check CPU performance it would be better to use some PHP directly, because the problem I see with using SQL's AES and SHA2 is that each database engine has its own implementation.

The link I posted before is where the MariaDB developers are looking at how to optimize the AES function in their engine as they are still inheriting the function from when they forked MySQL 5.5.

I say this because I have seen tests with this plugin on an empty server with 20 cores, 40 threads, 128Gb of RAM and NVME disks penalize at this point for using MariaDB and get better results if you switch to MySQL8 or Percona.

That's why I was talking about giving a twist to this test to make this part of the test more realistic, so to speak.

As I said before it's just an idea, I'm not sure how to do it, but it seems to me that it can give wrong ideas when you see the results.

[kevinohashi]

So I found some time to actually test what you're saying and it seems to be true. I setup two near identical environments using Local just changing between mysql 8.0.16 and mariadb 10.4.10 and the results were significantly slower on mariadb. I even ran the AES_ENCRYPT directly via adminer and saw that impact.

So you're right and it's replicable.

Now the question becomes, what function(s) can replace it? Just removing the mysql component completely and testing PHP? Would that make it even more PHP version dependent? I imagine newer versions of PHP would generally be faster, if that's true, would that be a problem?

For my benchmarks, unless I change it before publishing, I might have to make note on the Mariadb issue. I am curious why implementing the same hashing function is so much slower in mariadb.

[soymgomez]

If you want to continue with some MySQL test I would see better to import a large dataset to one or more tables and perform several queries on that data(select with sub-select, some joins .... but more common WordPress queries).

On the other hand if we go to a PHP test we can go to something like this: https://serverdiary.com/linux/benchmark-php-8-1-vs-php-8-0-php-7-4-php-7-3-php-7-0-php-5-6-and-php-5-5/

And of course the newer the PHP version the better, but the difference is that it is advisable to use the most modern version of PHP possible, so this test would benefit the environments where this good practice is maintained. On the other hand in SQL environments where it is more advisable to be in a LTS version (the case of MariaDB 10.6).

I have tried to keep an eye on it and compare it with something else, but here the problem is that MySQL8 is completely proprietary, and as far as I understand MaraDB developers are not experts in encryption algorithms and that is why it will be costing them more to improve it (not like Oracle who will have many more resources in this regard).

PS: As you can see I'm more inclined to open-source solutions ;)