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 InnoDB Doublewrite Buffer (also innodb_checksum can be used to detect a partial write).

Doublewrites, controlled by the innodb_doublewrite 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 a few SSD cards.

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 on a device that supports atomic writes 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 innodb-use-atomic-writes 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:

innodb_use_atomic_writes = 1

to the my.cnf config file.

Note that atomic writes are only supported on Fusion-io devices that use the NVMFS file system in these versions of MariaDB.

About innodb_use_atomic_writes (in to )

The following happens when atomic writes are enabled

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

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

• Whenever an Innodb datafile is opened, a special ioctl() is issued to switch on atomic writes. If the call fails, an error is logged and returned to the caller. This means that if the system tablespace is not located on an atomic write capable device or filesystem, InnoDB/XtraDB will refuse to start.

• if innodb_doublewrite is set to ON, innodb_doublewrite will be switched OFF and a message written to the error log.

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:

• Fusion-io devices with the NVMFS file system . and above.

• Shannon SSD. and above.

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

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:

cat /sys/block/${DEVICE}/queue/scheduler

For instance, it should look like this output:

cat /sys/block/vdb/queue/scheduler
[none] mq-deadline kyber bfq

Older kernels may look like:

cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

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

echo noop >  /sys/block/vdb/queue/scheduler

The impact of schedulers depend significantly on workload and hardware. You can measure the IO-latency using the biolatency 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 mysqld_safe or mariadbd-safe to start mysqld, then see the instructions at mariadbd-safe: Configuring the Open Files Limit.

If you are using systemd to start mysqld, then see the instructions at systemd: Configuring the Open Files Limit.

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

mysql soft nofile 65535
mysql hard nofile 65535

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

$ ulimit -Sn
65535
$ ulimit -Hn
65535

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 mysqld_safe or mariadbd-safe to start mysqld, then see the instructions at mariadb-safe: Configuring the Core File Size.

If you are using systemd to start mysqld, then see the instructions at systemd: Configuring the Core File Size.

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

mysql soft core unlimited
mysql hard core unlimited

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

$ ulimit -Sc
unlimited
$ ulimit -Hc
unlimited

Swappiness

See configuring swappiness.

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

• Swap increases IO over just using disk in the first place as pages are actively swapped in and out of swap. Even something like removing a dirty page that is no longer going to be stored in memory, while designed to improve efficiency, will under a swap situation cost more IO.

• Database locks are particularly inefficient in swap. They are designed to be obtained and released often and quickly, and pausing to perform disk IO will have a serious impact on their usability.

The main way to avoid swapping is to make sure you have enough RAM for all processes that need to run on the machine. Setting the too high can mean that under load the server runs short of memory, and needs to use swap. So understanding what settings to use and how these impact your server's memory usage is critical.

Linux has a swappiness setting which determines the balance between swapping out pages (chunks of memory) from RAM to a preconfigured swap space on the hard drive.

The setting is from 0 to 100, with lower values meaning a lower likelihood of swapping. The default is usually 60 --you can check this by running:

The default setting encourages the server to use swap. Since there probably won't be much else on the database server besides MariaDB processes to put into swap, you'll probably want to reduce this to zero to avoid swapping as much as possible. You can change the default by adding a line to the sysctl.conf file (usually found in /etc/sysctl.conf).

To set the swappiness to zero, add the line:

This normally takes effect after a reboot, but you can change the value without rebooting as follows:

Since RHEL 6.4, setting swappiness=0 more aggressively avoids swapping out, which increases the risk of OOM killing under strong memory and I/O pressure.

A low swappiness setting is recommended for database workloads. For MariaDB databases, it is recommended to set swappiness to a value of 1.

While some disable swap altogether, and you certainly want to avoid any database processes from using it, it can be prudent to leave some swap space to at least allow the kernel to fall over gracefully should a spike occur. Having emergency swap available at least allows you some scope to kill any runaway processes.

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

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 .

• 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 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.

• 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.

• 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!

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

5. Highly Available shared storage with ION. Have two different hosts, Fusion-io cards in them and share/replicate data with Fusion-io's ION software.

6. The luxurious Platinum setup: running on Fusion-io SLC cards on several hosts.

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.

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

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.

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

• 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

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

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

Articles of how to setup your MariaDB optimally on different systems

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.

MariaDB Enterprise Server users should also refer to the Enterprise Server-Specific section for additional configuration files like mariadb-enterprise.cnf.

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:

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.

On Linux and Unix operating systems, 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

On Windows, the option file can be called either my.ini or my.cnf. Depending on the distribution, MariaDB Enterprise Server for Windows may include additional configuration files. These files comply with the load order rules described in the following section. 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

MariaDB Enterprise Server includes additional configuration files that are installed with the Enterprise Server packages. Specifically, the file mariadb-enterprise.cnf is typically placed in a configuration include directory and enables Enterprise-specific features by default (for example, loading the plugins with plugin-load-add, server_audit and other enterprise plugins):

The configuration values defined in these files can be validated at runtime. See .

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.

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:

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.

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:

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.

MariaDB programs reads client options from the following option groups:

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

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:

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:

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.

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 :

After modifying a configuration file in Enterprise Server, you can determine the source of a system variable by querying the table.

Check a specific variable

Explanation of columns:

• GLOBAL_VALUE_PATH: Full path to the configuration file that set this value (e.g. /etc/my.cnf.d/mariadb-enterprise.cnf). Returns NULL if not set in any config file.

• GLOBAL_VALUE_ORIGIN: Shows the origin (config file, a compile-time default, or a command line, etc.).

The global_value_path column, introduced in , shows the full path to the configuration file from which the variable's value was loaded. This is especially helpful in environments with multiple option files as it enables you to determine which configuration file is currently in effect for a given system setting.

Note: The global_value_path column requires MariaDB ES Server 10.5.0 or later. On previous versions, this column doesn't exist.

View all variable set from configuration files

To view all system variables that were set from configuration files, you can run the following:

This query returns all variables with values loaded from option files, along with their file paths.

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

MariaDB and MariaDB Enterprise Server do not support this feature. 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.

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

For example:

Dashes (-) and underscores (_) in option names are interchangeable. Both variants can be used, for example:

The same is true for specifying options on the command line, for example:

This is only different when referring to system variables at runtime. Here, only underscores can be used, for example:

If an option is not explicitly set, the server or client uses the default value for that option.

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 can be set in .

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

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.

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

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

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