In mariaDB there is a way to disable columnstore compression in session level (and set the disable flag in each session). Is there any way to change the default compression mode?
3
-
1Hi, thanks for the link, but I don't want to set the parameter at the session level. I wish to change the default... I have to revise my question...– ssavva05Jan 25, 2022 at 9:10
-
A large part of the beauty of ColumnStore is its compression. What do you hope to gain by changing the default?– Rick JamesJan 25, 2022 at 15:28
-
I want to check if this will improve the insert speed– ssavva05Jan 26, 2022 at 17:00
Add a comment
|
1 Answer
I don't know the details of INSERT in Columnstore, but I would suspect the following:
- Inserting one row is quite costly.
- The code will consider the tradeoffs when adding more data row-by-row
- Probably: Batch inserting (especially of 64K or more rows) is better optimized.
So... How fast are you inserting rows? Are they being inserted in clumps? What I might do is to gather rows until
- I have collected X rows, OR
- Y minutes have passed
After whichever of those happens, then I would do a batch insert.
X might be 10K; Y might be 20 minutes. But those depend on the desired performance characteristics of the application.
(For InnoDB, I recommend batches of 100-1000 or 1MB of gathered Insert text. This runs about 10 times as fast as one-row-at-a time.)
answered Jan 26, 2022 at 18:14
-
I am trying to do a speed test between ColumnStore and InnoDB . I need to find a solution that loads data, analyze it and finally delete it. I am using streamsets and check what each solution can offer.– ssavva05Jan 26, 2022 at 19:50
-
This is really interesting info, and I am trying to apply it for my tests.– ssavva05Jan 26, 2022 at 20:37
-
@ssavva05 - It seems useless to have a table that is not
SELECTed. And that is where ColumnStore will (for some queries) run much faster than InnoDB. Jan 26, 2022 at 22:10