How much memory should we assign to MariaDB?

How much memory should we assign to MariaDB?

Before answering, I’ll show you a procedure I use to get the amount of memory I’m using. See the comments to make it work on Oracle MySQL.

CREATE PROCEDURE _.show_buffers()
	CONTAINS SQL
	COMMENT 'Show information about buffers size'
BEGIN
	SET @memory_per_thread := (
			  @@global.read_buffer_size
			+ @@global.read_rnd_buffer_size
			+ @@global.sort_buffer_size
			+ @@global.thread_stack
			+ @@global.join_buffer_size
			+ @@global.binlog_cache_size
		);
	SET @global_memory := (
			  @@global.innodb_buffer_pool_size
			+ @@global.innodb_additional_mem_pool_size
			+ @@global.innodb_log_buffer_size
			+ (SELECT SUM(FULL_SIZE) FROM information_schema.KEY_CACHES) -- MySQL: @@global.key_buffer_size
			+ @@global.aria_pagecache_buffer_size -- only MariaDB
			+ @@global.query_cache_size
		);
	SELECT
		  @global_memory AS global_memory
		, @memory_per_thread AS memory_per_thread
		, @@global.max_connections AS max_connections
		, @memory_per_thread * @@global.max_connections AS max_session_memory
		, CAST((@global_memory + @memory_per_thread * @@global.max_connections) AS UNSIGNED) AS max_total_memory
		;
	SET @memory_per_thread := NULL;
	SET @global_memory := NULL;
END;


Results on my dev machine:

MariaDB [(none)]> CALL _.show_buffers() \G
*************************** 1. row ***************************
     global_memory: 419430400
 memory_per_thread: 2949120
   max_connections: 151
max_session_memory: 445317120
  max_total_memory: 864747520
1 row in set (0.00 sec)


So, what do these columns mean?

  • global_memory: Memory used by global buffers.
  • memory_per_thread: Memory used by each thread (session buffers).
  • max_connections: Max allowed concurrent connectins (max_connections variable).
  • max_session_memory: Memory used by each thread, multiplied by the max allowed concurrent connections.
  • max_total_memory: Total memory occupied by MariaDB with the maximum of concurrent connections.

Let’s see which settings affect these values.

global_memory = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + aria_pagecache_buffer_size + query_cache_size.

memory_per_thread = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size.

If you use non-common storage engines, you may want to add more settings to these formulas. For example, if you use TokuDB, you should add tokudb_cache_size.

So, basically, my advice is: Tune these values so that max_total_memory reaches 80% your memory. Start by setting query_cache_size and unused storage engines caches to 0, then tune the buffer pool. Usually you don’t want to touch session buffers.

Why 80%? Well, an higher percentage could be ok, but I consider 80% safer for the general case. Because:

  • The system needs memory, too.
  • InnoDB buffer pool slightly exceeds the limit you set.
  • Some operations (like building a big index or repairing a non-transactional table) require memory.
  • Some connections could resize their session buffers.
  • There are more buffers, that we didn’t consider here.
  • You don’t want MariaDB/MySQL to swap. But if you turn off the swap and you run out of memory, you could experience various failures, or even crashes.
Advertisements

4 thoughts on “How much memory should we assign to MariaDB?

  1. innodb_additional_mem_pool_size: deprecated in Oracle 5.6.3, removed in 5.7.4. (I don’t know when the merge into MariaDB will or has happened.)

    join_buffer_size — multiple ones can be allocated for a single, complex, SELECT.

    read_buffer_size — before 5.6.4, it is allocated full size; after that, it grows as needed.

    You left out
    LEAST(max_heap_table_size, tmp_table_size)
    which may be allocated multiple times in a complex SELECT.

    I have computed expressions like these on many machines. Almost always the computation adds up to far more than RAM, but almost never is the memory in any real threat. That is, take the computations with a grain of salt.

    Absolutely, avoid swapping!

    As for binlog_cache_size, the manual says “A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled.” Further, the 5.5.9 and 5.6.1 changelogs say “Due to changes made in MySQL 5.5.3, settings made in the binlog_cache_size and max_binlog_cache_size server system variables affected both the binary log statement cache (also introduced in that version) and the binary log transactional cache (formerly known simply as the binary log cache). This meant that the resources used as a result of setting either or both of these variables were double the amount expected. To rectify this problem, these variables now affect only the transactional cache. The fix for this issue also introduces two new system variables binlog_stmt_cache_size and max_binlog_stmt_cache_size, which affect only the binary log statement cache.”

    Even with the default of 32K, Binlog_cache_disk_use rarely shows any disk usage. (This was a survey of 60 machines.)

    • Hi!

      Thank you for additional info. I will try to improve the procedure – even thought, as you stated, these are purely indicative.

      I don’t know a way to calculate an average realistic value for read_buffer_size. I think I will just leave the value of the variable.

      Similar considerations for join_buffer_size. As far as I understand, it can also be allocated 0 times (for a simple SELECT). Since I don’t know how to calculate a realistic average, I will just suppose it’s 1 instance per thread.

      Similar formulas (with fewer variables, if I remember correctly) are included in books like Troubleshooting MySQL and Instant InnoDB.

  2. hi,here is the error

    “global_memory = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size.

    memory_per_thread = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + aria_pagecache_buffer_size + query_cache_size.”

    it should be this

    “memory_per_thread = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size
    global_memory = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + aria_pagecache_buffer_size + query_cache_size +binlog_cache_size.”

    and is binlog_cache_size really a session memory?

    • Yes, I swapped “memory per thread” and “global memory” in that paragraph. Fixed it. But the procedure is correct.
      Thank you for reporting my error!

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s