2

I installed Mysql 8.0.36 on Debian 12 VM (name it VM_A), then created for simple performance testing table your_table and procedure prepare_data (inspired SO answer) like this:

CREATE TABLE your_table (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val bigint);

DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 100;

  WHILE i < 100000 DO
    INSERT INTO your_table (val) VALUES (FLOOR(RAND()*POW(10, FLOOR(15 * RAND()))));
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

Running this procedure took more than 6 minutes. As this felt too long, I tried to run the same procedure on a fairly similar VM (VM_B), but on MariaDB (10.3.39), where it ran for 40 seconds.

I purged MySQL from the VM_A and replaced it with MariaDB (10.11.4). Now the procedure runs 6 times faster, about 1 minute (still about 30% slower than on VM_B).

On VM_A I used the same conf as on VM_B, both for MySQL and MariaDB.

Such a huge difference was surprising, so I repeated it on some older VM (VM_C) with MySQL 5.7.29 and it took 43 seconds to run.

Additionally, on my laptop MySQL 8.0.36 took more than 32 minutes and Maria DB 10.6.16 almost 8 minutes

This does not seem a random or marginal difference, the MariaDB has a much better performance to offer, and Mysql (at least the community edition) has lost its edge.

Or is there a way to improve MySQL's performance?

8
  • 1
    FWIW, doing an iterative test to compare the performance of two engines designed for relational problems is not a good methodology.
    – J.D.
    Commented Feb 1 at 22:42
  • @J.D. Can't argue. My test was made for simple internal testing, but the results surprised me so much, it caused doubt in my actions.
    – w.k
    Commented Feb 1 at 22:50
  • To draw any meaningful conclusions about performance of two different products, you'll need to conduct a statistically meaningful set of repeatable experiments under controlled conditions; what you present looks more like a series of anecdotes.
    – mustaccio
    Commented Feb 1 at 22:58
  • 1
    @mustaccio Did you notice older mysql (5.7.29) runs the same speed as the current MariaDB?
    – w.k
    Commented Feb 2 at 6:56
  • Another thing to consider is how was MySQL and MariaDB configured. Even if they used the same VM if they have different configurations the results would also be different. Commented Feb 3 at 0:11

2 Answers 2

2

You touched on something that might run a lot faster and simpler in MariaDB:

INSERT INTO your_table (id, val)
    SELECT  seq, FLOOR(RAND()*POW(10, FLOOR(15 * RAND())))
        FROM seq_101_to_100000;

(no looping needed; proc is optional)

1
  • This is blazing fast but this was not the target of my test, I wanted to simulate the simplest real interaction with the database.
    – w.k
    Commented Feb 2 at 6:58
0

According to your timings, each row takes several milliseconds.

Pretty much the only thing that can be this slow is flushing transaction logs to disk on commit, which means you did not execute the procedure in a transaction, autocommit is on, and each insert is in its own transaction.

I would recommend checking if innodb_flush_log_at_trx_commit and sync_binlog have the same value in all tests, along with other variables affecting transaction logs, replication, and/or flushing.

https://mariadb.com/kb/en/group-commit-for-the-binary-log/

https://mariadb.com/kb/en/binary-log-group-commit-and-innodb-flushing-performance/

From the timings, your laptop seems to have a slow hard disk while the VMs have something faster, but still too slow to be SSDs.

1
  • Thank you for your input. I'll read your linked resources and consider it. One note still: my goal was to simulate separate inserts as they appear in a real situation. And actually, all media were SSDs
    – w.k
    Commented Feb 5 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.