MariaDB Server Setup Step 1: Memory Configuration

Setting up a MariaDB server to reliably host a database, running stable and making good use of resources, involves more than just installing the software. The first step is to configure resource allocation so that the server is getting the benefit of all available RAM without oversubscribing (which will lead to the OOM reaper killing the server at the worst possible times, when it's heavily loaded.)

As the MariaDB server runs, it does not check to see what system resources are available, or limit the amount of memory the process uses, except indirectly as a function of the sum of some default & configured values. The default configuration values are conservative and fit comfortably in at least 4G available RAM. If you have a smaller instance, it can easily OOM. If your instance is larger, you're leaving significant performance on the table.

It is very important to keep in mind that if you choose to change configuration defaults, the responsibility is also yours to make sure you don't overrun what's physically available on the machine, so here is a basic calculation to make sure RAM is configured in a sustainable way.

Although it's not very practical to try to predict exactly how much RAM a MariaDB server will use at any given time, there is a pragmatic logic that can give you a very good functional approximation of the maximum it can use, the upper limit. In over 7 years supporting 1000s of customer servers at MariaDB, about 1/4 to 1/3 of production problems I have seen are influenced by server configurations that deviate from these rules and, as a result, don't match the available resources. Setting these correctly from the start can save a lot of headaches.

The MariaDB server essentially allocates RAM in two categories: global and per connection. Global RAM is what is allocated to server functions like buffers for different database engines. Per connection is RAM that needs to be allocated exclusively to manage a session each time a connection is made to the database. For example, when you connect to the database and run a 'select order by' statement, the data may need to be sorted somewhere on the server before being sent back to the client, and that 'sort buffer' uses memory that belongs to your connection. So the most basic formula for predicting what RAM will be allocated for a MariaDB server is:

global RAM + (per connection RAM * total number of possible connections)

This generally translates to these global variables:

global RAM: 

    aria_pagecache_buffer_size 
    + key_buffer_size 
    + query_cache_size 
    + innodb_buffer_pool_size 
    + innodb_log_buffer_size

plus

per connection RAM: 

    read_buffer_size
    + read_rnd_buffer_size 
    + sort_buffer_size 
    + join_buffer_size 
    + binlog_cache_size 
    + thread_stack 
    + tmp_table_size

times

maximum number of possible simultaneous connections: 

    max_connections

** there are many other small (and, less frequently, large) consumers of RAM in the server such as network buffers, parallel replica worker threads, Galera transactions, etc..., so this is about 98% accurate. That’s an order of magnitude better than shot-in-the-dark configurations; it's viable.

When you first install your MariaDB server, and want to allocate RAM to get the best from it, these are the basic defaults you will want to change right out of the box:

1. Decide what the greatest number of concurrent sessions your new installation will really need to support, and set a number maybe 20% higher than that – don’t be too greedy; each connection needs RAM:

        max_connections=75

    2. Find out how much RAM you have available:

• you can run this in most any linux shell:

        cat /proc/meminfo | grep MemTotal

• in macOS, you can use:

        sysctl hw.memsize

• in Windows, this will work:

        systeminfo | find "Available Physical Memory"

Also in this step, decide how much the MariaDB server should use in total. If this is a dedicated server, then leaving somewhere between 4G for a 16G server to 16G for a 256G server is a good practice. If other services or applications need to share the available RAM, then you’ll need to reduce the projected available RAM for MariaDB accordingly. Try reserving approximately √(total system RAM in G) for the OS.

So let's say you have a 16G RAM server dedicated to MariaDB & want to allocate 12G, leaving 4G for the OS. Here's the key variables you need to set in /etc/my.cnf.d/server.cnf (for Rocky/CentOS/Fedora/RHEL - /etc/mysql/my.cnf for Debian/Ubuntu) in the [mysqld] or [mariadb] sections:

    1. set max_connections. Let's say you need 75 as above:

        max_connections=75

    2. the default value for each per-connection variable adds up to the server using an approximate maximum of 19,861,504 bytes per connection, so multiply that by max_connections to figure out how much RAM every available connection can use at the same time:

        75 * 19,861,504 = 1,489,612,800

    ...so we've used up a little over 1G RAM for per-connection memory requirements. Now we have 16G - 4G - 1G left to play with, roughly.

    3. looking at global RAM allocations, adding up everything except the buffer pool amounts to 286,261,248 bytes, or 273M. This leaves a generous amount remaining that we can allocate to the buffer pool for good performance:

    16G (total available RAM on the server/VM) - 4G (reserved for the O/S) is 12G, the total RAM we have to work with:

        17,179,869,184 - 4,294,967,296 = 12,884,901,888

    1,489,612,800 is the total amount of RAM in bytes we expect every connection to be able to use at once.

    286,261,248 is the total amount of global RAM MariaDB will use NOT COUNTING the default innodb_buffer_pool_size setting of 134,217,728.

...so to calculate what we can safely give the buffer pool, we just subtract from the total the amount reserved for the O/S, the max amount every concurrent connection can use, and the requirements for every other global memory consumer, and we get the best size fore the buffer pool:

    innodb_buffer_pool_size = 
        17,179,869,184 (total RAM) 
        - 4,294,967,296 (reserved for O/S) 
        - 1,489,612,800 (predicted max for 75 connectios) 
        - 286,261,248 (total global RAM needed by the MariaDB server 
            not counting the buffer pool) 
    = 11,109,027,840

The best InnoDB buffer pool size for a 16G dedicated MariaDB server (with a data size greater than 10G) that can handle 75 concurrent connections is about 10G (10 * 1024^3 = 10,737,418,240, pretty close to our calculated 11,109,027,840)

If we change the max_connection figure to 500, we can see that this number changes quite quickly:

    innodb_buffer_pool_size = 
        17,179,869,184 (total RAM) 
        - 4,294,967,296 (reserved for O/S) 
        - 9,930,752,000 (predicted max for 75 connections) 
        - 286,261,248 (total global RAM needed by the MariaDB server not counting the buffer pool) 
    = 2,667,888,640

Now, the sustainable innodb_buffer_pool_size setting would be 2,667,888,640, or 2.5G. In practice this might suffice for the type of workload a server with 500 concurrent sessions and moderate processing demands might require, but one would be remiss not to monitor such a server closely.

To see this RAM requirement ceiling for a running server, this query will return the value in gigabytes:

select
    round(
        (
            @@aria_pagecache_buffer_size 
            + @@innodb_buffer_pool_size 
            + @@innodb_log_buffer_size 
            + @@key_buffer_size 
            + @@query_cache_size 
            + @@max_connections * ( 
                @@binlog_cache_size 
                + @@join_buffer_size 
                + @@read_buffer_size 
                + @@read_rnd_buffer_size 
                + @@sort_buffer_size 
                + @@thread_stack 
                + @@tmp_table_size 
            )
        ) / power( 1024, 3)
    , 2 ) as RAM_GB;

Of course there is a lot more to this story, and in subsequent posts I'll be discussing some additional settings you might want to fix on a first installation, how to look at IOPS and configure what the server consumes in the background for optimal performance, threads, tuning, disaster recovery, high availability, how to handle specific emergencies, popular configuration errors, and related topics. For now, let’s start from the ground up.