MariaDB vs PostgreSQL INSERT - PostgreSQL 2-4 times faster

Question

I'm comparing MariaDB 10.6 vs PostgreSQL 14 in INSERT operation. I created a table in both databases, then I tried to insert 1 Million rows in both. In PostgreSQL, it takes only 2 seconds to INSERT 1 million rows. But in MariaDB, it takes 9 seconds to INSERT 1 million rows. But I want to work with MariaDB, so I tried to improve the my.ini but still PostgreSQL is about 2-4 times faster than MariaDB. This is my table in both databases (no index in both, except the primary key with autoincrement)

CREATE TABLE `project_user` (
    `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_username` VARCHAR(16) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_mobile` VARCHAR(16) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_email` VARCHAR(64) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_first_name` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_first_name_en` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_last_name` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_last_name_en` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_country` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `user_gender` BIT(1) NULL DEFAULT NULL,
    `user_registered_ip` INT(10) UNSIGNED NOT NULL,
    `user_registered_at` INT(10) UNSIGNED NOT NULL,
    `user_status` BIT(1) NOT NULL DEFAULT b'1',
    PRIMARY KEY (`user_id`) USING BTREE
)
COLLATE='ascii_general_ci'
ENGINE=InnoDB
;

in PostgreSQL is equal to this table too (except the tinyint which i used bit) ...

In PostgreSQL, I used this operation to insert 1 million rows

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT i,i+1
FROM generate_series(1, 1000000) AS i;

And in MariaDB I used this function

BEGIN
    FOR i IN 1..1000000
    DO
        INSERT INTO `project_user` (`user_registered_ip`,`user_registered_at`)
        VALUES (i, i+1);
    END FOR;
    RETURN 1;
END

And this is my.ini

[mysqld]
datadir=C:/Program Files/MariaDB 10.6/data
port=3306
max_connections=8192
max_allowed_packet=1G
skip_external_locking
skip_name_resolve
default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=24
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=8M
innodb_log_file_size=2G
innodb_sort_buffer_size=16M
innodb_stats_on_metadata=0
innodb_thread_concurrency=6
innodb_read_io_threads=64
innodb_write_io_threads=64
key_buffer_size=512M
concurrent_insert=2
back_log=512
thread_cache_size=100
thread_stack=192K
interactive_timeout=60
wait_timeout=60
join_buffer_size=4M
read_buffer_size=3M
read_rnd_buffer_size=4M
sort_buffer_size=4M
table_definition_cache=40000
table_open_cache=40000
open_files_limit=60000
max_heap_table_size=128M
tmp_table_size=128M
slow_query_log=0
long_query_time=5
log_queries_not_using_indexes=0

[mysqldump]
quick
max_allowed_packet=1024M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.6/lib/plugin

Is there any way to improve the INSERT speed of mariadb ?

9 seconds to insert 1,000,000 rows! Not sure what you are complaining about. Out of interest, will your application be inserting 1,000,000 rows all in one go very often? — RiggsFolly
– RiggsFolly, Commented Feb 6, 2022 at 12:50
Your methods are not comparable, an optimised sequence with single set-based insert vs an iterative single-insert loop. considering, 9 seconds is not so bad. — Stu
– Stu, Commented Feb 6, 2022 at 12:54
On my machine, that PostgreSQL statements takes 3 seconds. When I use the SQL code block to execute a million INSERT statements, like you do for MariaDB, it takes 6.7 seconds. When you compare, use at least something that you can compare. — Frank Heikens
– Frank Heikens, Commented Feb 6, 2022 at 12:59

Diego Dupin · Accepted Answer · 2022-02-06 18:09:50Z

4

in order to compare the same things : postgresql:

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT i,i+1
FROM generate_series(1, 1000000) AS i;

mariadb equivalent will be :

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT seq,seq+1 FROM seq_1_to_1000000;

that would perform better

answered Feb 6, 2022 at 18:09

Diego Dupin

1,3589 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stu Over a year ago

Not a MariaDB user so unaware of that, +1

Rick James Over a year ago

Depending on what you will be doing next, it may be faster to not even build the table, but simply JOIN seq_... when you need the sequence!

Stu · Accepted Answer · 2022-02-06 13:40:28Z

1

You should try a (slightly) more comparable method, for example a set-based insert in MariaDB using a cross join to create a set:

insert into project_user (user_registered_ip, user_registered_at)
select r, r+1
from (
  select Row_Number() over(order by (select null) ) r
  from information_schema.COLUMNS x
  cross join information_schema.COLUMNS y
  limit 1000000
)x;

For another comparision, on my local SQL Server (2019), using a similar while loop and single-row insert / values, 1 million rows takes 7 seconds; using the above single insert from a cross-join method it's 1 second.

edited Feb 6, 2022 at 13:40

answered Feb 6, 2022 at 13:33

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

MariaDB vs PostgreSQL INSERT - PostgreSQL 2-4 times faster

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related