Hello!

We are in the process to migrate the database of our application from mysql5.5 to mariadb10.4.14, and we have experiencing some performance issues. Some queries are taking significantly longer when running to mariadb10.4.14 if compared to mysql5.5.
We would like to ask if these performance issues could be be due to a bug in the mariadb implementation.

Context

Our application is storing timeseries data in a SQL table called timeseries, defined like this:

MariaDB [timemachine]> DESCRIBE timeseries;

+-----------+---------------------+------+-----+---------+----------------+

| Field     | Type                | Null | Key | Default | Extra          |

+-----------+---------------------+------+-----+---------+----------------+

| W_ID      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |

| VER_ID    | bigint(20) unsigned | NO   |     | 0       |                |

| BR_VER_ID | bigint(20) unsigned | NO   |     | 0       |                |

| BR_ID     | int(10) unsigned    | NO   | MUL | 0       |                |

| USER      | bigint(20) unsigned | NO   |     | 0       |                |

| GEN_TYPE  | char(8)             | YES  |     | NULL    |                |

| NODE_ID   | bigint(20) unsigned | NO   |     | 0       |                |

| TS        | int(11)             | NO   |     | 0       |                |

| val       | double              | YES  |     | NULL    |                |

| UTS_FROM  | int(11)             | NO   |     | 0       |                |

| UTS_TO    | int(11)             | NO   |     | 0       |                |

| LEN_CAT   | int(11)             | NO   |     | 0       |                |

| VALID_FOR | int(11)             | YES  |     | 0       |                |

| DATA      | text                | YES  |     | NULL    |                |

| ARCHIVE   | tinyint(1)          | NO   |     | 0       |                |

+-----------+---------------------+------+-----+---------+----------------+

Each time a value in a timeseries is added or modified in the application, a new row is written in the database. The relevant columns in this context are:

• VER_ID: based on transaction ID, used to find newest version of a value
• NODE_ID: every timeseries is associated with a node in the node database.
• TS: timestamp when the value was written; unix timestamp
• val: The actual value
• UTS_FROM: this value should be valid from this unix timestamp
• UTS_TO: this value should be valid until this unix timestamp
• ARCHIVE: used for marking entries that should be deleted. The values are first set from 0 to 1, and the ones marked with 1 are then deleted.

The VER_ID column contains the version of each value: in other words, all the rows with the same NODE_ID, UTS_FROM and UTS_TO will have an increasing VER_ID value, which can be used to track the history of the changes of that value in that given interval.

On a nightly basis, we run our tshistorycleaner script, which is tasked to clean up the history of all the timeseries (in order to reduce the size of the database), keeping only the latest N values (where N is configurable and defaults to 5). Here is where we are experiencing the performance drop: this script takes almost twice the time when running on mariadb10.4.14 with respect to mysql5.5.

The cleaning procedure happens in two steps: first step, the script identifies which rows have to be deleted, and set their ARCHIVE column to 1. Then, all the rows where ARCHIVE = 1 are deleted.

How to reproduce it

The attached files contain our development database images with mariadb10.4.14 and mysql5.5 which can be used for testing. The file mariadb-vs-mysql-perf-vae_no_db.zip contains a stand alone script which can be executed with python3, simulating the cleaning steps (I have taken the relevant functions from the application and made them working as an independent script).
There is also a sample database which can be used for testing, splitted in 4 files. To get the database, please do:

cat db.gz.parta* > timemachine.sql.gz
gunzip timemachine.sql.gz

First start up the database docker images with the docker-compose.yml file, then the script can be executed (see the README.md or use -h option for details).

On the provided sample database, we observe:

   | Time (m/d H:M)  | Database used | Archiving time |  Deleting time | Command line

   |  07/09 14:31   |  mariadb10.4   |    69.253088   |    23.937851  | ['-c', 'database.cfg', '-d', 'timemachine.sql']

   |   07/09 14:31  |      mysql5.5  |     39.321083   |    30.345936 | ['-c', 'database.cfg', '-d', 'timemachine.sql', '-my']

While the deleting time is slower on mariadb, the archiving time is higher. Same behavior is observed on production databases.

I'm glad to answer any further question you might have.

Best regards,
Valentino.