MySQL is able to process tables stored in a "tmp" directory which is, most of the time, a device name on the root device volume with no I/O performance. So your first thought might be to configure the MySQL tmpdir on a performance disk (
io1 volume). While it may seem like a good idea, remember that each I/O used is billed, so this might get expensive. Since a tmpdir is not designed for persistence, but acts as a bottleneck for the overall performance, let's try to design it cost-effectively.
Ephemeral storage disks are storage solutions made for temporary storage. They are erased each time you stop an instance. This type of disk is available for some instance types. For more information, see Improving Your MySQL Database Cache and Instance Types.
So here is our cost-effective solution: use a disk with a high I/O performance and no extra fee for each I/O used. Ephemeral disks meet those criteria as they have high performance capability and their price is included within the instance type price.
Configuring Your Instances with Ephemeral Disks
Remember that not all our instance types give you the ability to have ephemerals. Here, we are using the i2.xlarge instance which has a 1x800 GiB SSD ephemeral disk.
To specify that you want this volume to be attached, configure the block device mapping. The only thing you need to configure is the VirtualName: you must name your disk with "ephemeral" , followed by the disk index number (for example: ephemeral0, ephemeral1, ephemeral2...).
Go to your instance and run lsblk or fdisk -l.
You should see your ephemeral volume within your instance.
Format the disk with the filesystem you want, and mount it automatically via /etc/fstab.
A new line is added to /etc/fstab.
Let's have a look at the new fstab line:
/dev/xvdz: File system specified in the AttachVolume call. For more information, see the corresponding Outscale documentation or Boto documentation.
/tmp/ephemeral0: Device name where you can read and write to your disk.
ext4: File system type chosen when the disk was formatted with the mfks command.
rw,nobarrier,noatime,nodiratime,noauto: Options for the disk.
nobarrier: Improves disk performance, but may cause serious data corruption in case of power failure. This is not a problem here, as the disk is not meant to be persistent.
noatime: No access date written for files. This reduces unnecessary I/O.
nodiratime: No access date written for directories. This reduces unnecessary I/O.
0 0: Dump and file check deactivated. Those are unnecessary checks for ephemeral disks.
Go to your my.cnf file. This file might be in /etc/my.cnf or in /etc/my.cnf.d, depending on your distribution package.
You can further improve your configuration according to your needs. For more information, see the MariaDB documentation.