Articles of how to setup your MariaDB optimally on different systems

Partial Write Operations

When Innodb writes to the filesystem, there is generally no guarantee that a given write operation will be complete (not partial) in cases of a poweroff event, or if the operating system crashes at the exact moment a write is being done.

Without detection or prevention of partial writes, the integrity of the database can be compromised after recovery.

innodb_doublewrite--an Imperfect Solution

Since its inception, Innodb has had a mechanism to detect and ignore partial writes via the (also innodb_checksum can be used to detect a partial write).

Doublewrites, controlled by the system variable, comes with its own set of problems. Especially on SSD, writing each page twice can have detrimental effects (write leveling).

Atomic Write - a Faster Alternative to innodb_doublewrite

A better solution is to directly ask the filesystem to provide an atomic (all or nothing) write guarantee. Currently this is only available on .

Enabling Atomic Writes from

When starting, and beyond automatically detects if any of the supported SSD cards are used.

When opening an InnoDB table, there is a check if the tablespace for the table is and if yes, it will automatically enable atomic writes for the table. If atomic writes support is not detected, the doublewrite buffer will be used.

One can disable atomic write support for all cards by setting the variable to OFF in your my.cnf file. It's ON by default.

Enabling Atomic Writes in to

To use atomic writes instead of the doublewrite buffer, add:

to the my.cnf config file.

Note that atomic writes are only supported on in these versions of MariaDB.

About innodb_use_atomic_writes (in to )

The following happens when atomic writes are enabled

• if is neither O_DIRECT, ALL_O_DIRECT, or O_DIRECT_NO_FSYNC, it is switched to O_DIRECT

• is switched ON (files are extended using posix_fallocate rather than writing zeros behind the end of file)

Here is a flowchart showing how atomic writes work inside InnoDB:

Devices that Support Atomic Writes with MariaDB

MariaDB currently supports atomic writes on the following devices:

• . and above.

• . and above.

_{This page is licensed: CC BY-SA / Gnu FDL}

Why to Avoid Swapping

Obviously, accessing swap memory from disk is far slower than accessing RAM directly. This is particularly bad on a database server because:

• MariaDB's internal algorithms assume that memory is not swap, and are highly inefficient if it is. Some algorithms are intended to avoid or delay disk IO, and use memory where possible - performing this with swap can be worse than just doing it on disk in the first place.

Linux kernel settings

IO scheduler

For optimal IO performance running a database on modern hardware we recommend using the none (previously called noop) scheduler.

Recommended schedulers are none, for SSDs and NVMes, and mq-deadline (previously called deadline) for hard disks.

You can check your scheduler setting with:

For instance, it should look like this output:

Older kernels may look like:

Writing the new scheduler name to the same /sys node will change the scheduler:

The impact of schedulers depend significantly on workload and hardware. You can measure the IO-latency using the bcc-tools script with an aim to keep the mean as low as possible.

Resource Limits

Configuring the Open Files Limit

By default, the system limits how many open file descriptors a process can have open at one time. It has both a soft and hard limit. On many systems, both the soft and hard limit default to 1024. On an active database server, it is very easy to exceed 1024 open file descriptors. Therefore, you may need to increase the soft and hard limits. There are a few ways to do so.

If you are using to start mysqld, then see the instructions at .

If you are using to start mysqld, then see the instructions at .

Otherwise, you can set the soft and hard limits for the mysql user account by adding the following lines to :

After the system is rebooted, the mysql user should use the new limits, and the user's ulimit output should look like the following:

Configuring the Core File Size

By default, the system limits the size of core files that could be created. It has both a soft and hard limit. On many systems, the soft limit defaults to 0. If you want to , then you may need to increase this. Therefore, you may need to increase the soft and hard limits. There are a few ways to do so.

If you are using to start mysqld, then see the instructions at .

If you are using to start mysqld, then see the instructions at .

Otherwise, you can set the soft and hard limits for the mysql user account by adding the following lines to :

After the system is rebooted, the mysql user should use the new limits, and the user's ulimit output should look like the following:

Swappiness

See .

_{This page is licensed: CC BY-SA / Gnu FDL}

This article will help you configure MariaDB for optimal performance.

By default, MariaDB is configured to work on a desktop system and therefore use relatively few resources. To optimize installation for a dedicated server, you have to do a few minutes of work.

For this article we assume that you are going to run MariaDB on a dedicated server.

Feel free to update this article if you have more ideas.

my.cnf Files

MariaDB is normally configured by editing the my.cnf file. In the next section you have a list of variables that you may want to configure for dedicated MariaDB servers.

Storage Engine

InnoDB is normally the default storage engine with MariaDB.

• You should set to about 80% of your memory. The goal is to ensure that 80 % of your working set is in memory.

The other most important InnoDB variables are:

Some other important InnoDB variables:

• . Deprecated and ignored from .

• . Deprecated and ignored from .

Storage Engine

• MariaDB uses by default the Aria storage engine for internal temporary files. If you have many temporary files, you should set to a reasonably large value so that temporary overflow data is not flushed to disk. The default is 128M.

You can check if Aria is configured properly by executing:

If Aria_pagecache_reads is much smaller than Aria_pagecache_read_request andAria_pagecache_writes is much smaller than Aria_pagecache_write_request#, then your setup is good. If the is big enough, the two variables should be 0, like above.

• If you don't use MyISAM tables explicitly (true for most + users), you can set to a very low value, like 64K.

Using in memory temporary tables

Using memory tables for internal temporary results can speed up execution. However, if the memory table gets full, then the memory table will be moved to disk, which can hurt performance.

You can check how the internal memory tables are performing by executing:

Created_tmp_tables is the total number of internal temporary tables created as part of executing queries like SELECT.Created_tmp_disk_tables shows how many of these did hit the storage.

You can increase the storage for internal temporary tables by setting and high enough. These values are per connection.

Lots of Connections

A Lot of Fast Connections + Small Set of Queries + Disconnects

• If you are doing a lot of fast connections / disconnects, you should increase and if you are running or below .

• If you have a lot (> 128) of simultaneous running fast queries, you should consider setting to pool_of_threads.

Connecting From a Lot of Different Machines

• If you are connecting from a lot of different machines you should increase to the max number of machines (default 128) to cache the resolving of hostnames. If you don't connect from a lot of machines, you can set this to a very low value!

See Also

External Links

_{This page is licensed: CC BY-SA / Gnu FDL}

The ioDrive branded products can be used as block devices (super-fast disks) or to extend basic DRAM memory. ioDrive is deployed by installing it on an x86 server and then installing the card driver under the operating system. All main line 64-bit operating systems and hypervisors are supported: RHEL, CentOS, SuSe, Debian, OEL etc. and VMware, Microsoft Windows/Server etc. Drivers and their features are constantly developed further.

ioDrive cards support software RAID and you can combine two or more physical cards into one logical drive. Through ioMemory SDK and its APIs, one can integrate and enable more thorough interworking between your own software and the cards - and cut latency.

The key differentiator between a Fusion-io and a legacy SSD/HDD is the following: A Fusion-io card is connected directly on the system bus (PCIe), this enables high data transfer throughput (1.5 GB/s, 3.0 GB/s or 6GB/s) and the fast direct memory access (DMA) method can be used to transfer data. The ATA/SATA protocol stack is omitted and therefore latency is cut short. Fusion-io performance is dependent on server speed: the faster processors and the newer PCIe-bus version you have, the better is the ioDrive performance. Fusion-io memory is non-volatile, in other words, data remains on the card even when the server is powered off.