CODEWITHSUNDEEP
mysqlpostgresqlmariadb
HelloMachine
HelloMachine

Reputation: 353

MariaDB vs PostgreSQL INSERT - PostgreSQL 2-4 times faster

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 ?

Upvotes: 0

Views: 924

Answers (2)

Diego Dupin
Diego Dupin

Reputation: 1348

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

Upvotes: 4

Stu
Stu

Reputation: 32609

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.

Upvotes: 1

Related Questions