Articles of how to setup your MariaDB optimally on different systems

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}

MariaDB makes use of numerous environment variables that may be set on your system. Environment variables have the lowest precedence, so any options set on the command line or in an option file will take precedence.

It's usually better not to rely on environment variables, and to rather set the options you need directly, as this makes the system a little more robust and easy to administer.

Here is a list of environment variables used by MariaDB:

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}

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.

Use Cases

1. You can start by using ioDrive for database files that need heavy random access.

2. Whole database on ioDrive.

3. In some cases, Fusion-io devices allow for atomic writes, which allows the server to safely disable the .

4. Use ioDrive as a write-through read cache. This is possible on server level with Fusion-io directCache software or in VMware environments using ioTurbine software or the ioCache bundle product. Reads happen from ioDrive and all writes go directly to your SAN or disk.

Atomic Writes

Starting with , MariaDB Server supports atomic writes on Fusion-io devices that use the NVMFS (formerly called DirectFS) file system. Unfortunately, NVMFS was never offered under ‘General Availability’, and SanDisk declared that NVMFS would reach end-of-life in December 2015. Therefore, NVMFS support is no longer offered by SanDisk.

MariaDB Server does not currently support atomic writes on Fusion-io devices with any other file systems.

See for more information about MariaDB Server's atomic write support.

Future Suggested Development

• Extend InnoDB disk cache to be stored on Fusion-io acting as extended memory.

Settings For Best Performance

Fusion-io memory can be formatted with different sector size of either 512 or 4096 bytes. Bigger sectors are expected to be faster, but only if I/O is done in blocks of 4KB or multiples of that. Speaking of MariaDB: if only InnoDB data files are stored in Fusion-io memory, all I/O is done in blocks of 16K and thus 4K sector size can be used. If the InnoDB redo log (I/O block size: 512 bytes) goes to the same Fusion-io memory, then short sectors should be used.

Note: XtraDB has the experimental feature of an increased InnoDB log block size of 4K. If this is enabled, then both redo log I/O and page I/O in InnoDB will match a sector size of 4K.

As of file systems: currently XFS is expected to yield the best performance with MariaDB. However depending on the exact kernel version and version of XFS code in use, one might be affected by . This has or (because of .

For the pitbull machine where I have run such tests, ext4 was faster than xfs for 32 or more threads:

• up to 8 threads xfs was few percent faster (10% on average).

• at 16 threads it was a draw (2036 tps vs. 2070 tps).

• at 32 threads ext4 was 28% faster (2345 tps vs. 1829 tps).

• at 64 threads ext4 was even 47% faster (2362 tps vs. 1601 tps).

Those numbers are for spinning disks. I guess for Fusion-io memory the XFS numbers will be even worse.

Example Configuration

Card Models

There are several card models. ioDrive is older generation, ioDrive2 is newer. SLC sustains more writes. MLC is good enough for normal use.

1. ioDrive2, capacities per card 365GB, 785GB, 1.2TB with MLC. 400GB and 600GB with SLC, performance up to 535000 IOPS & 1.5GB/s bandwidth

2. ioDrive2 Duo, capacities per card 2.4TB MLC and 1.2TB SLC, performance up to 935000 IOPS & 3.0GB/s bandwidth

3. ioDrive, capacities per card 320GB, 640GB MLC and 160GB, 320GB SLC, performance up to 145000 IOPS & 790MB/s bandwidth

4. ioDrive Duo, capacities per card 640GB, 1.28TB MLC and 320GB, 640GB SLC, performance up to 285000 IOPS & 1.5GB/s bandwidth

Additional Software

• directCache - transforms ioDrive to work as a read cache in your server. Writes go directly to your SAN

• ioTurbine - read cache software for VMware

• ION - transforms ioDrive into a shareable storage

• ioSphere - software to manage and monitor several ioDrives

See Also

_{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.

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}

You can configure MariaDB to run the way you want by configuring the server with MariaDB's option files. The default MariaDB option file is called my.cnf on Unix-like operating systems and my.ini on Windows. Depending on how you've installed MariaDB, the default option file may be in a number of places, or it may not exist at all.

Global Options Related to Option Files

The following options relate to how MariaDB handles option files. These options can be used with most of MariaDB's command-line tools, not just mariadbd. They must be given as the first argument on the command-line:

Default Option File Locations

MariaDB reads option files from many different directories by default. See the sections below to find out which directories are checked for which system.

For an exact list of option files read on your system by a specific program, you can execute a command like this ($program stands for any MariaDB program, including the Server):

For example:

The option files are each scanned once, in the order given by --help --verbose. The effect of the configuration options is the same as if they would have been given as command-line options in the order they are found.

Default Option File Locations on Linux, Unix, Mac

On Linux, Unix, or Mac OS X, the default option file is called my.cnf. MariaDB looks for the MariaDB option file in the locations and orders listed below.

The locations are dependent on whether the DEFAULT_SYSCONFDIR option was defined when MariaDB was built. This option is usually defined as /etc when building , but it is usually not defined when building or .

• If the DEFAULT_SYSCONFDIR cmake option is undefined, MariaDB looks for the MariaDB option file in the following locations, and in the following order:

• If the DEFAULT_SYSCONFDIR cmake option is defined, MariaDB looks for the MariaDB option file in the following locations in the following order:

• MARIADB_HOME or MYSQL_HOME is the containing the path to the directory holding the server-specific my.cnf file. If MYSQL_HOME is not set, and the server is started with , MYSQL_HOME is set as follows:

  • If there is a my.cnf file in the MariaDB data directory, but not in the MariaDB base directory, MYSQL_HOME

Default Option File Locations on Windows

On Windows, the option file can be called either my.ini or my.cnf. MariaDB looks for option files in the following locations, and in the following order:

• The System Windows Directory is the directory returned by the function. The value is usually C:\Windows. To find its specific value on your system, open and execute:

• The Windows Directory is the directory returned by the GetWindowsDirectory function. The value may be a private Windows Directory for the application, or it may be the same as the System Windows Directory returned by the GetSystemWindowsDirectory function.

• INSTALLDIR is the parent directory of the directory where mysqld.exe is located. For example, if mysqld.exe

Default Option File Hierarchy

MariaDB looks in all of the above locations, in order, even if it has already found an option file, and it's possible for more than one option file to exist. For example, you could have an option file in /etc/my.cnf with global settings for all servers, and another option file in ~/.my.cnf (that is, your user account's home directory) which specifies additional settings (or override previously specified settings) that are specific only to that user.

Option files are usually optional. However, if the option is set, but the file does not exist, MariaDB raises an error. If the --defaults-file option is set, MariaDB only reads the option file referred to by this option.

If an option or system variable is not explicitly set, then it will be set to its default value. See for a full list of all server system variables and their default values.

If is set, there are various other log file naming options that, if set, should be placed later in the configuration file hierarchy. Later settings override earlier settings, so log-basename overrides any earlier log file name settings. See for details.

Custom Option File Locations

MariaDB can be configured to read options from custom options files with the following command-line arguments. These command-line arguments can be used with most of MariaDB's command-line tools, not just . They must be given as the first argument on the command line:

Option File Syntax

The syntax of MariaDB option files is as follows:

• Lines starting with # are comments.

• Empty lines are ignored.

• Option groups use the syntax [group-name]. See the section below for more information on available option groups.

Option Groups

A MariaDB program can read options from one or many option groups. For an exact list of option groups read on your system by a specific program, you can execute ($program is a placeholder for any MariaDB program):

For example:

Server Option Groups

MariaDB programs reads server options from the following server option groups:

X.Y in the examples above refer to the base (major.minor) version of the server. For example, MariaDB 11.4 would read from [mariadb-11.4]. By using the mariadb-X.Y syntax, you can create option files that have MariaDB-only options in the MariaDB-specific option groups. That would allow the option file to work for both MariaDB and MySQL.

Client Option Groups

MariaDB programs reads client options from the following option groups:

Tool-Specific Option Groups

Many MariaDB tools reads options from their own option groups as well:

Custom Option Group Suffixes

MariaDB can be configured to read options from option groups with a custom suffix by providing the following command-line argument. This command-line argument can be used with most of MariaDB's command-line tools, not just . It must be given as the first argument on the command line:

The default group suffix can also be specified via the MYSQL_GROUP_SUFFIX .

Example: To create a custom prompt for the mariadb command-line client via a custom group, add this to the option file (for instance, /etc/my.cnf):

Then, launch mariadb like this:

Including Option Files

It is possible to include additional option files from another option file. For example, to include /etc/mysql/dbserver1.cnf, an option file could contain:

Including Option File Directories

It is also possible to include all option files in a directory from another option file. For example, to include all option files in /etc/my.cnf.d/, an option file could contain:

The option files within the directory are read in alphabetical order.

All option file names must end in .cnf on Unix-like operating systems. On Windows, all option file names must end in .cnf or .ini.

Checking Program Options

You can check which options a given program is going to use by using the command-line argument:

This command-line argument can be used with most of MariaDB's command-line tools, not just . It must be given as the first argument on the command-line:

You can also check which options a given program is going to use by using the utility, and providing the names of the option groups that the program reads:

The my_print_defaults utility's --mariadbd command-line option provides a shortcut to refer to all of the :

Obfuscated Authentication Credential Option File

MySQL supports an obfuscated authentication credential option file called .mylogin.cnf that is created with .

MariaDB does not support this. The passwords in MySQL's .mylogin.cnf are only obfuscated, rather than encrypted, so the feature does not really add much from a security perspective. It is more likely to give users a false sense of security, rather than to seriously protect them.

Option Prefixes

MariaDB supports certain prefixes that can be used with options. The supported option prefixes are:

For example:

Options

Dashes (-) and underscores (_) in option names are interchangeable.

If an option is not explicitly set, then the server or client will simply use the default value for that option.

MariaDB Server Options

MariaDB Server options can be set in .

For a list of options that can be set for MariaDB Server, see the list of options available for .

Most of the can also be set in MariaDB's option file.

MariaDB Client Options

MariaDB client options can be set in .

See the specific page for each to determine what options are available for that program.

Example Option Files

Most MariaDB installations include a sample MariaDB option file called my-default.cnf.

In source distributions, the sample option files are usually found in the support-files directory, and in other distributions, in the share/mysql directory that is relative to the MariaDB base installation directory.

You can copy one of these sample MariaDB option files and use it as the basis for building your server's primary MariaDB option file.

Example Minimal Option File

Minimal my.cnf file that you can use to test MariaDB:

Example Hybrid Option File

The following is an extract of an option file that one can use if one wants to work with both MySQL and MariaDB.

See Also

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