Percona Server x10 slow than MariaDB

MySQL & MariaDB Percona Server for MySQL 8.0
1

Greetings!

For some time now, I am seeing performance problems with large tables in MySQL (vanilla and Percona) that in MariaDB do not occur, and it is not that it is happening.

For example, I have this simple query, running on a Percona 8.0.31 and a MariaDB 10.6.12:

select * from LOG where IDADMIN != 99999 order by FECHA desc limit 10 offset 0;

MySQL Percona Execution time: 10 rows in set (2.94 sec)
MariaDB Execution time: 10 rows in set (0.334 sec)

The indexes and cardinality are the same, and an explain is equivalent to:

Percona:

image
image1335×159 5.62 KB

MariaDB:

image
image1154×149 6.03 KB

Where I do see differences is in profiling

MySQL Percona

image
image2956×463 22.5 KB

MariaDB

image
image2743×588 48.5 KB

I insist, it is not an isolated case, it happens to me in more queries, I put this one because it is the simplest.

I understand that it is too much difference to be something of some optimization switch of MariaDB.

Can you help me?

Thanks!

2

You can do some more investigations:

  • Optimizer tracing
  • EXPLAIN ANALYZE

If you can provide those reports, that would help.

3

Attached is the output of the optimizer, in mariaDB the information it offers is less, but curiously I don’t see anything about sort, which seems to be what spends all the time on execution.

Analize results:

  • Percona:
-> Limit: 10 row(s)  (cost=119400.59 rows=10) (actual time=3173.585..3173.610 rows=10 loops=1)
    -> Sort row IDs: LOG.FECHA DESC, limit input to 10 row(s) per chunk  (cost=119400.59 rows=1087991) (actual time=3173.570..3173.594 rows=10 loops=1)
        -> Filter: (LOG.IDADMIN <> 99999)  (cost=119400.59 rows=1087991) (actual time=0.185..2923.488 rows=1087991 loops=1)
            -> Table scan on LOG  (cost=119400.59 rows=1087991) (actual time=0.171..2819.877 rows=1087991 loops=1)
  • Mariadb

image
image841×59 2.39 KB

Thank you very much!

Optimizer-Percona.txt (7.1 KB)
Optimizer-MariaDB.txt (3.5 KB)

4

It’s unfortunate that MariaDB doesn’t have EXPLAIN ANALYZE. Anyways, have you verified the result set is exactly the same between them?

Example:

mysql> pager md5
mysql> SELECT ....

The output will be an md5 hash. Run this on both and compare the hashes. Are they the same? I’m asking this because in looking at the optimizer traces, I don’t see MariaDB doing the ORDER BY DESC. The Community/Percona trace is almost 100 lines longer than MariaDB’s trace, containing information on the ORDER BY part of the execution.

If MariaDB isn’t doing that part, then that would explain the faster execution.

As another series of tests, I suggest first dropping the ‘limit 10 offset 0’, and then the next series dropping the ‘order by’.

5

Percona spent most of the time in sorting, mariaDB don’t sort at all.
This is a lot of time to sort such tiny table…
I would

  1. Rebuilt table in percona and in mariaDb and rerun the same test.
  2. Check the OS side (at least some sar statistics
  3. Check memory configurations on both servers.
    In my opinion such sort should be done in memory, do we have any CPU starvation on percona server? (logcal/memory reads/writes mostly consuming CPU usually)
    Since execution plan is a trivial full table scan there is nothing to check on optimizer level.
    To do an FTS database need to read all pages from disk, apply filter , return the result set.

Regards