0

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
  • 1
    Hi, 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...
    – ssavva05
    Jan 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 James
    Jan 25, 2022 at 15:28
  • I want to check if this will improve the insert speed
    – ssavva05
    Jan 26, 2022 at 17:00

1 Answer 1

0

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.)

3
  • 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.
    – ssavva05
    Jan 26, 2022 at 19:50
  • This is really interesting info, and I am trying to apply it for my tests.
    – ssavva05
    Jan 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.
    – Rick James
    Jan 26, 2022 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.