MariaDB on FlashArray Implementation and Best Practices
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. |