This article on MySQL Workloads was coauthored by Andrew Sillifant and Nihal Mirashi.
Selecting the right database is key to organizations getting the most out of their business data. And many have chosen MySQL for their production environments for a variety of use cases, including as a relational database or as a component of the LAMP web application stack. In fact, MySQL consistently ranks as one of the most popular database systems according to the DB-Engines rankings website.
MySQL has many attributes that make it ideal for business. It can be virtualized. It offers numerous high-availability solutions. And it has the protective benefits of specialized cluster servers and regular backups to separate nodes or data centers. Since MySQL was designed as a cross-platform database, it runs on Linux, macOS, and Windows. In addition, MySQL delivers high performance; if a website receives millions of queries a day, users experience timely responses.
In a previous blog post, I covered 4 common MySQL issues and how to solve them. For those who have chosen to run their business on MySQL, this article will focus on three common scenarios that data and infrastructure professionals often face and offer tips to accelerate their MySQL workloads.
Virtualizing MySQL Workloads
Virtualizing MySQL servers offers advantages like more efficient resource allocation. But, because database applications like MySQL can be particularly demanding, it’s important to ensure the right resources are allocated to each virtual machine. Making use of the all-flash performance capabilities of Pure Storage® FlashArray™ for storage can help.
VMware vSphere, the leading virtualization platform, offers a wide range of virtualization products. Pairing VMware vSphere and FlashArray storage allows for even greater consolidation, operational efficiency, and cost reduction. Figure 1 below is a visual representation of a typical MySQL stack running in a FlashArray and VMware vSphere environment.
Figure 1. A virtualized MySQL stack that uses Pure Storage FlashArray.
FlashArray volumes can be used with the VMware virtual infrastructure presenting volumes as VMware vSphere vVols, virtual machine file system (VMFS)-formatted volumes, or mapped devices.
- VMware vSphere vVols: vVols are a storage technology that provides policy-based, granular storage configuration and control of virtual machines. With FlashArray, vVols let virtual disks and regular block storage volumes be seen as one and the same. Large virtual disks can be contained within the single vVol datastore, vastly simplifying management at this scale.
- VMFS-formatted volumes: A single FlashArray volume can serve as a VMFS datastore of up to 64TB in size. This volume can then contain multiple virtual disks for one or more VMs that collectively do not exceed the datastore size.
- Mapped devices: A single volume can serve as a mapped device with a raw device mapping (RDM). RDMs provide up to 64TB of storage for a single VM. The mapped device consumes the entire volume, which can then be shared between one or more VMs.
Pure offers additional architectural guidance and best practices for deploying MySQL workloads on VMware in the new guide, “Running MySQL Workloads on VMware vSphere with FlashArray Storage.”
MySQL Workloads Disaster Recovery
MySQL databases are the heart of many businesses. If a MySQL database goes down, it can disrupt services, lead to data inaccessibility, impact business operations, and require prompt action to recover and restore normal operations. This means, of course, that admins need a disaster-recovery plan in place in case a database disruption occurs. When using FlashArray as the storage for any MySQL database, admins gain access to data services that can be used to protect or provide an additional layer of resilience for MySQL databases.
A basic approach is to use simple backups. This can be accomplished with FlashArray volume snapshots. These are immutable, point-in-time images of the contents of one or more volumes. Volume snapshots can be created individually or as part of a protection group. Volume snapshots are always thin-provisioned, deduplicated, compressed, and require no snapshot capacity reservations.
A more comprehensive disaster-recovery strategy can be implemented with Pure Storage ActiveDR™. ActiveDR can replicate data to other FlashArray deployments across long distances while still retaining low recovery point objectives (RPOs).
The following are some of the simplified management aspects of ActiveDR:
Pod replication. Pods are management containers for volumes that use ActiveDR and provide a simple management construct for organizing data volumes and their settings into groups. When pods are linked together on separate systems using replica links, the data in the source pod automatically starts replicating to the secondary pod.
Continuous change tracking. ActiveDR automatically manages changes without the need to provision or monitor journal devices.
Single-command failover. ActiveDR makes it simple to implement, test, and manage disaster recovery. To minimize risk, orchestration steps for the entire environment stay the same during the test or in an actual failover event.
Multi-direction replication. Pods can replicate in multiple directions between two FlashArray environments.
Pure Storage FlashArray uses the continuous replication of volumes between Pure Cloud Block Store™ instances to enable ActiveDR. In this context, ActiveDR uses pods that contain all of the volumes that are to be protected. Each pod is a replication group and a consistency group, and all volumes within a pod are failed over together.
MySQL High Availability
We know that a truly resilient business strategy means ensuring availability at multiple levels. However, there is no single high-availability solution that fits in all situations. One option is Pure Storage ActiveCluster™, a storage volume–based synchronous replication technology included with every FlashArray.
ActiveCluster provides high-availability data services that protect the volumes on which MySQL data files reside. It helps ensure that they remain available in the event of a site or array failure. ActiveCluster also lets admins configure zero RPOs and recovery time objectives (RTOs) between two FlashArray environments with active/active bi-directional synchronous replication and transparent failover.
ActiveCluster uses the following components:
Pure1® Cloud Mediator. This component determines which array will continue data services should a power outage occur.
Active/active clustered array pairs. These FlashArray pairs use synchronous replication to maintain a copy of the protected data on each array and present both copies as one consistent copy to hosts attached to one or both arrays.
Stretched storage containers. Management containers that collect storage objects such as volumes are stretched between two arrays. Stretched storage containers also provide consistent input/output (I/O) behavior for the storage objects within them.
Getting the Most Value Out of Data
Virtualizing MySQL, planning for disaster recovery, and ensuring high availability are common scenarios that data professionals face on the journey to get the most value from business data. All three require careful planning and implementation. As always, the goal is to make the most of infrastructure investments. VMware vSphere and FlashArray storage allow for consolidation, operational efficiency, and cost reduction. Volume snapshots and specialized storage solutions like ActiveDR and ActiveCluster can help keep data protected and available.
Learn more about getting the most value out of your MySQL business data with Pure Storage.
