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. These are the two first steps in configuring a new MariaDB server to behave like a relational database server, and to allocate all available memory, but no more, in ways that will result in solid baseline performance.

The concept of atomicity, the “A” in ACID, is easy to take for granted these days in a SQL database server. The idea is simple: all, or nothing. This is the basis for database transactions. Jim Gray, an IBM researcher, originally articulated the idea in the early '70s, and it was implemented as a feature of the IBM Information Management System as early as 1973.

Simply put, when a database update is enclosed in a transaction, if any part of the update fails, then every other part of the update should “fail” too, so that no partial or half-way updates are possible. This is transactional integrity. When we move items, they should not be gone from the origin unless they arrive at the destination. For example, when I make a payment, the funds should not be credited to the recipient without also being debited from my account.

Unfortunately, MariaDB does not work this way by default: the value for innodb_rollback_on_timeout is OFF. This means that without some corrective configuration, if I start a transaction, subtract $10 from my account, and am unable to successfully add $10 to the recipient’s account as part of the same transaction before innodb_lock_wait _timeout fails the update, as might happen if an unrelated query were holding a lock on the recipient's data, then the server will not roll back the update to the first row. Instead, it will save the the $10 deduction from my account and credit it nowhere. In other words, it will not have treated the operation atomically, as a transaction, instead saving the update half-done. The implications of this are dire: no transactional integrity in this case. Administrators are almost universally unaware of this problem. Therefore it is imperative to always remember to add “innodb_rollback_on_timeout=ON” to the configuration if we wish to run an ACID database server.

Then there is the memory configuration. When 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 as a function of the sum of some default & configured values. Instead, it starts with a very conservative overall set of default configuration values that will fit comfortably in a 4G RAM environment, and leaves the rest up to you. It is very important to keep in mind that if you choose to change configuration defaults, the responsibility is 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 hardly practical to try to predict precisely how much RAM a MariaDB server will need to use at any given time, there is a pragmatic logic that can give you a very good functional approximation of upper limits. In over 6 years working in various capacities doing & managing Technical Support at MariaDB, over 30% of the production problems I see are heavily 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.