MariaDB on FlashArray Implementation and Best Practices

Last updated
Save as PDF

MariaDB is an open source relational database management system (RDBMS). MariaDB is highly compatible with MySQL with drop-in replacement capability between the two databases. Because of the high compatibility between the two databases many of the deployment scenarios and platform configuration can be taken from the MySQL on FlashArray Best Practice and implementation Guide.

MariaDB Server can be acquired directly from the MariaDB Foundation or from the GitHub Repository.

Hardware Environment Requirements

The Hardware Environment Requirements are the same as those set out for MySQL.

Recommended Configuration for MariaDB on FlashArray

Operating System Requirements

MariaDB can be deployed on a number of operating platforms. The current supported platforms can be found in the MariaDB OS Compatibility Guide.

The relevant operating system and recommendations can be found below:

Operating System

Recommendations

Red Hat Enterprise Linux/ CentOS / Oracle Linux/Ubuntu/SUSE/Debian

The recommended settings for Linux operating systems can be found in the Linux best practices for FlashArray.

In addition to the existing best practices ensure that the number of requests per volume is set to a higher value. Increasing this value allows individual volumes to service more storage requests at any single time.

Add the following to the udev rules file for FlashArray to increase the maximum number of requests for a single volume:

Set DM devices number of requests to 1024 or higher. This uses 1MB of memory per request
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/nr_requests}="1024"

Microsoft Windows Server

The best practices for Microsoft Windows can be set using the Test-WindowsBestPractices Cmdlet.

File System layout and configuration

The recommended file systems for MariaDB with FlashArray are NTFS (Microsoft Windows) , XFS (Linux) and EXT4 (Linux).

Microsoft Windows file system configuration

If setting the InnoDB page size to 32K or 64K (default is 16K) , NTFS (for the innodb_data_home_dir) should be formatted with an allocation unit size which matches the page size.

Page Size	Allocation Unit Size
16K (default)	16K
32K	32K
64K	64K

Linux file system configuration

XFS

When using the XFS file system for any MariaDB database files the default options are typically all that is required.

XFS file systems can be created on a FlashArray volume using the command:

mkfs.xfs /dev/mapper/<device>

The default mount options in /etc/fstab should be used with XFS in the majority of cases.

/dev/mapper/<device> /mountpoint xfs defaults 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint xfs _netdev,nofail 0 0

For systems using the Linux Kernel 4.13 or later the "nobarrier" option is deprecated for XFS. To increase the possible performance of the volume add the following udev rule to the existing rules file:

FCP and iSCSI

ACTION=="add|change", KERNEL=="sd*[!0-9]", SUBSYSTEM=="block", ENV{ID_VENDOR}=="PURE", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/write_cache}="write through"

NVMe-oF connectivity

ACTION=="add|change", KERNEL=="nvme*[!0-9]", SUBSYSTEM=="block", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="eui.00723ec7b5b427*", ATTR{queue/write_cache}= "write through"

Once the rules have been added reload and apply them using the udevadm utility:

 udevadm control --reload-rules && udevadm trigger

EXT4

The EXT4 file system should be created with the "-o discard" option to enable automating unmap.

mkfs.ext4 -o discard /dev/mapper/<device>

With EXT4 the volume can be mounted using /etc/fstab with the default options but to increase the possible performance the "noatime", "nodiratime" and "barrier=0" options should be used.

/dev/mapper/<device> /mountpoint ext4 noatime,nodiratime,barrier=0 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint ext4 _netdev,nofail,noatime,nodiratime,barrier=0 0 0

Adding an entry to /etc/fstab

Entries in /etc/fstab can be done using the device path or the UUID of the filesystem.

To get the UUID of a file system use the blkid command and match the device to the respective UUID.

[root@DB-01 ~]# blkid
/dev/sdb: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sda: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdc: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdd: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p1: UUID="D759-B5A6" BLOCK_SIZE="512" TYPE="vfat" PARTLABEL="EFI System Partition" PARTUUID="8f317ff7-97f5-48ab-83d8-f54d647fb390"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p2: UUID="97d13054-20c7-436e-b6ab-ef8b8f3ce46b" BLOCK_SIZE="512" TYPE="xfs" PARTUUID="2644a548-dde5-4ac8-8679-c100011a0d78"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p3: UUID="GXujto-h3yl-V3hq-jFZs-3xd6-RhLB-0vFf8l" TYPE="LVM2_member" PARTUUID="380fbf39-eb6e-4bf0-ae25-f4abd6f02803"
/dev/mapper/rhel-root: UUID="5547b1a3-590a-4d54-9d0b-710714ca7e52" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/rhel-swap: UUID="70470107-9368-4d32-82d8-7620f1cdc665" TYPE="swap"
/dev/mapper/rhel-home: UUID="0e08dae8-3d93-451c-a021-b3fe58f9b464" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011884: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011890: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011898: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a0: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a1: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"

Then use the UUID in place of the device path (works for both ext4 and XFS):

UUID=489e7195-46b5-466a-8770-9aa7938c8afb /mountpoint ext4 _netdev,nofail,noatime,nodiratime,barrier=0 0 0

To view how the volumes and file systems should be laid out for InnoDB based configurations see the MySQL Volume and Filesystem Architectural Layout.

Server System Variables

Server System variables are global values which can effect the behavior of various components. Server System variables are different to the variables used for storage engine configuration as they either do not affect or apply across multiple storage engines (where applicable).

The following Server System variables can impact storage configuration :

Parameter Name	Description	Default Value	Recommended Value
basedir	The path to the MariaDB installation base directory.	Microsoft Windows - C:\Program Files\MariaDB <version>\ Linux - /var/lib/mysql	Only change this if using a separate layout for data, binary and transaction log directories.
datadir	The path to the MariaDB server data directory.	Microsoft Windows - C:\Program Files\MariaDB <version>\Data Linux - /var/lib/mysql	Only change this if using a separate layout for data, binary and transaction log directories.
table_open_cache	The number of open tables cached in one table cache instance.	2000	If the value of opened tables is too large (see Optimizing table_open_cache) then increase this value.
table_open_cache_instances	The number of open tables cache instances.	8	To improve scalability by reducing contention among sessions the open tables cache can be partitioned into several smaller cache instances. Modify this value in conjunction with table_open_cache to tune the workload to achieve higher performance.
open_files_limit	The number of file descriptors available to MariaDB from the operating system	5000	Depending on required number of open descriptors , for example a single MyIASM table could have 3 open descriptors. Will require system limits (ulimit -n) to be set for Linux Operating Systems.