Databases require I/O performance, which is provided by our BSU io1 volume. Cloud computing aims to be a solution where each component is on-demandso let's use this capability to improve our MySQL database cache.

Introduction

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 Disks

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.

  1. 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...).

    Boto3 example
    # Define the block device mapping
    BlockDeviceMappings = [{'DeviceName': '/dev/xvdz', 'VirtualName': 'ephemeral0'}]
    # Run Instance
    instance = ocb.fcu.run_instances(ImageId='ami-d4f6551d', KeyName='Test-Name', SecurityGroups=['test-from-work'], InstanceType='i2.xlarge', BlockDeviceMappings=BlockDeviceMappings, MinCount=1, MaxCount=1)
  2. Go to your instance and run lsblk or fdisk -l.
    You should see your ephemeral volume within your instance.

    $> lsblk
    NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
    xvdz      8:0    0  800G  0 disk
    vda     253:0    0   10G  0 disk
    ??vda1  253:1    0    9G  0 part / 
  3. Format the disk with the filesystem you want, and mount it automatically via /etc/fstab.
    A new line is added to /etc/fstab.

    Device name
    $> mfks.ext4 /dev/xvdz
    $> mkdir /tmp/ephemeral0
    $> echo "/dev/xvdz	/tmp/ephemeral0 ext4	rw,nobarrier,noatime,nodiratime	0 0" >> /etc/fstab
    $> mount -a


    Let's have a look at the new fstab line:

    $> /dev/xvdz /tmp/ephemeral0 ext4 rw,nobarrier,noatime,nodiratime,noauto 0 0


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

Configuring MySQL

  • Go to your my.cnf file. This file might be in /etc/my.cnf or in /etc/my.cnf.ddepending on your distribution package.

    my.cnf
    [mysqld]
    tmpdir = /tmp/ephemeral0

You can further improve your configuration according to your needs. For more information, see the MariaDB documentation.


Windows® is a registered mark of Microsoft Corporation in the United States and/or other countries.

AWS™ and Amazon Web Services™ are trademarks of Amazon Technologies, Inc or its affiliates in the United States and/or other countries.

See Legal Mentions.