1

I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with 8GB RAM. This is the table, a trigger and the timings:

MariaDB [test]> show create table analytics_test\G
*************************** 1. row ***************************
       Table: analytics_test
Create Table: CREATE TABLE `analytics_test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg\G
*************************** 1. row ***************************
               Trigger: test_trg
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg before insert on analytics_test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:14:28.81
1 row in set (0.000 sec)

MariaDB [test]> insert into analytics_test set id=1;
Query OK, 1 row affected (0.817 sec)

MariaDB [test]> insert into analytics_test set id=2;
Query OK, 1 row affected (0.560 sec)

MariaDB [test]> insert into analytics_test set id=3;
Query OK, 1 row affected (0.611 sec)

MariaDB [test]> select * from analytics_test;
+------+----------+
| id   | str      |
+------+----------+
|    1 | Value: 1 |
|    2 | Value: 2 |
|    3 | Value: 3 |
+------+----------+
3 rows in set (0.085 sec)

I think .5 sec for a simple insert is very slow - compare to the same table in innodb:

MariaDB [test]> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg1\G
*************************** 1. row ***************************
               Trigger: test_trg1
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg1 before insert on test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:45:07.85
1 row in set (0.000 sec)

MariaDB [test]> insert into test set id=1;
Query OK, 1 row affected (0.010 sec)

Is there anything I need to do in order to make inserts perform better?

1
  • "Is there anything I need to do" - quit using single row insert latency as the measure of performance in a column-oriented OLTP database.
    – mustaccio
    Jan 7, 2021 at 13:33

1 Answer 1

3

Column-oriented database engines, not just MariaDB ColumnStore, are specifically optimized for read operations on large volumes of data. For this they use auxiliary data structures (like ColumnStore extent maps), query parallelism, compression etc. This comes at the cost of increased overhead for standard DML operations. This is often explicitly stated in various technical documents, like this quote from the InfiniDB technical overview:

Note that because InfiniDB is a column-oriented database, insert and delete statements will not normally run as fast on InfiniDB as they will on row-oriented databases (more blocks must be touched).

or this from the MariaDB architecture presentation (see slide 22):

DML Writes

  • Slow compared to other engines
    • INSERT is very slow compared to cpimport

Vendors often provide special tools for bulk loading data into columnar tables (e.g. the cpimport utility mentioned above) to address this problem. This fits well into the typical columnar engine use case, that of an OLAP system with (relatively) infrequent mass updates and heavy read-only workloads.

In other words, you should not judge a column-oriented database by its DML performance, because that's not what it is designed for.

2
  • I understand that - still, it seems to be a couple of magnitudes slower, that's what surprises me.
    – j4nd3r53n
    Jan 7, 2021 at 14:14
  • I disagree that this is just a general columnar db problem. In my testing, managed aws redshift, self-managed clickhouse, and self-managed postgres columnar are both several orders of magnitude faster at inserts than self-managed mariadb columnstore.
    – webb
    Jan 27, 2023 at 13:35

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.