CODEWITHSUNDEEP
performancemariadbsql-insertcolumnstore
Kin Cheung
Kin Cheung

Reputation: 860

Inserting a record with many columns into a MariaDB Columnstore table takes a long time

I have a table with 50 columns in a MariaDB columnstore and it takes more than 5 seconds to insert a single record. I am very surprised to see this.

I am not sure if it is expected since there are too many columns or there could be some settings that I might have done wrong.

CREATE TABLE `mytable` (
   id` int(11) NOT NULL COMMENT 'autoincrement=1',
   // mostly int(10) and varchar(20) columns
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

Thanks

Upvotes: 2

Views: 650

Answers (1)

Rick James
Rick James

Reputation: 142528

Because of the way data is stored, it must unpack 50 compressed blobs, each holding up to 64K rows' worth of one column, add the new cell, then recompute the min, max, etc, and recompress it.

It is much better to gather lots of rows (thousands) and insert them all at once. Then it might take not much more than 5 seconds for the entire batch, thereby being only an average of a few milliseconds per row.

Would you describe your application? Columnstore is not the solution for everyone; it has a rather narrow niche. Perhaps InnoDB would be better for your app.

Upvotes: 2

Related Questions