InnoDB buffer pool sizing when ColumnStore is also used

[Reynaldo Dalisay]

Hello,

When a MariaDB server has tables that use ColumnStore and InnoDB engines, what are the guidelines for sizing the InnoDB buffer pool settings?  Should we still follow the "75-80% of total RAM" rule-of-thumb for setting innodb_buffer_pool_size?

Here's what the server we're running looks like:

OS:  Ubuntu 20.04.3 LTS

MariaDB version: MariaDB Community Server 10.6.5

Total RAM:  257815 MB

Swap size:  2047 MB

innodb_buffer_pool_size:  103079215104

I'm primarily asking this question because our server has been triggering the Linux out-of-memory "oom killer," apparently due to the system running out of memory while MariaDB is processing queries.  It causes Linux to restart the mariadbd and/or ColumnStore processes (like PrimProc), thereby causing our data jobs to fail.

So, I'm hoping that adjusting the InnoDB buffer pool size can prevent those oom events from happening.  Or, if anyone has other ideas on how we might be able to prevent the oom issue, I'd appreciate it.

Thanks!

[Todd Stoffel]

No that 75-80% recommendation is out dated. It comes from a time when people had smaller instance sizes. I would leave everything at the current default values unless you know that you need a larger innodb buffer.

If you have 256GB of RAM then by default Columnstore will use the following:

(See Columnstore.xml)

NumBlocksPct = 50

TotalUmMemory = 25%

for a grand total of 192GB.

This leaves you 64GB for other system processes including innodb_buffer_pool_size

In this case, set your buffer pool to something like 32GB and leave 32GB for other overhead.

[Reynaldo Dalisay]

Thank you so much for your quick answer.

Yes, we are using those same values for NumBlocksPct and TotalUmMemory.

We'll adjust innodb_buffer_pool_size per your recommendation, and then we'll observe the server's behavior over the next few days.

Thanks again!

[Ferran Gil Cuesta]

Hey Reynaldo,

Make sure you use the most recent version you can, as in 10.6.5 we were having a similar issue as you describe. See https://groups.google.com/g/mariadb-columnstore/c/rnMcDpj6gEQ/m/8Xosta4AAQAJ (up and down).

Cheers,

Ferran

[Todd Stoffel]

Latest version is actually 23.02.4 and it's shipped with community release 11.1.