[Cloud Z DB] How should I configure memory settings for MariaDB?

Memory in MariaDB is broadly divided into two areas: 

1. Global Memory Area

When the DB is first started, it uses only the minimum memory, then gradually increases up to the configured value. After reaching that value, it does not release memory but continues to operate within that range.

(Unlike Oracle, which allocates the full configured memory at startup, MariaDB gradually increases memory usage up to the configured value.)

• innodb_buffer_pool_size

• key_buffer_size

• innodb_log_buffer_size

• tmp_table_size

→ Global memory = innodb_buffer_pool_size + key_buffer_size + innodb_log_buffer_size + tmp_table_size

2. Session Memory Area

This is the memory space used per MariaDB session and is related to the max_connections setting.

(When simply connected, only the minimal memory required for the connection is used. However, if operations like joins or sorts are needed, memory will be used up to the configured values below.)

Since the exact usage depends on the number of active sessions at a time, the maximum session memory is usually calculated assuming all connections are active:

• sort_buffer_size

• read_buffer_size

• read_rnd_buffer_size

• join_buffer_size

• thread_stack

• binlog_cache_size

• max_connections

→ Session memory = (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size) × max_connections

*Example:

If your MariaDB is configured as follows:

Global Memory

• innodb_buffer_pool_size: 2048 MB

• key_buffer_size: 16 MB

• innodb_log_buffer_size: 10 MB

• tmp_table_size: 64 MB

• Total ≈ 2.1 GB

Session Memory

• (sort_buffer_size: 2 MB

• read_buffer_size: 1 MB

• read_rnd_buffer_size: 1 MB

• join_buffer_size: 0.128 MB

• thread_stack: 0.128 MB

• binlog_cache_size: 1 MB)
  × max_connections = 300

• Total ≈ 1.6 GB

Therefore, based on the above settings, the total memory usage would be:

• Global Memory Area: 2.1 GB

• Session Memory Area: 1.6 GB

• Plus:

  • Basic MariaDB startup memory: 350 MB

  • performance_schema data: 150 MB

  • OS / file buffering space: about 10% of total memory

→ Based on this, the recommended total memory size is approximately 4.5 GB to 5 GB.

Please refer to the above explanation and example when configuring memory for MariaDB.