4 Common MySQL Issues and How to Solve Them

IDC forecasts that the compound annual growth rate for storage capacity will reach 17.8% from 2019 to 2024. Consider a one terabyte MySQL database in 2019. At this rate of growth, the same database could be more than 2TB by 2024. This data growth can cause difficulties for business operations, including lower productivity, longer time to market, and potential revenue loss.

Here are four common MySQL issues you may encounter and how you can solve them with Pure Storage® products.

MySQL Issue 1: Inconsistent Performance

FlashArray//X accelerates Tier 0 and Tier 1 block and file application workloads through all-flash, 100% NVMe-driven storage performance. FlashArray//C offers optimal storage capacity and performance for Tier 2 workloads. FlashArray is powered by the Purity Operating Environment. Purity provides agile data services such as intelligent quality of service (QoS) and non-disruptive everything.

QoS is always on and ensures that consolidated workloads on a single FlashArray™ don’t affect one another adversely. QoS is a global policy, but you can also apply it on a per-volume basis. You can limit MySQL volumes for an instance to have a bandwidth or IOPS limit. Doing so ensures that workload spikes don’t affect other instances or specific databases.

You can also apply QoS policies per database/schema and even by storage engine. To apply storage-based QoS policies to a specific database/schema, you need only to implement this volume and directory structure using soft links. Then, apply the QoS policy to the relevant volume(s).

For MySQL deployments, you can rest assured that there will be no disruption during software upgrades or in the event of hardware failures. At Pure, we’ve built the concept of non-disruption into our products.

Pure FlashBlade® provides a unified platform for fast file and object storage in which performance scales linearly with the data footprint. Backup and recovery scenarios can encounter issues meeting SLAs, as traditional backup targets may not perform at the required scale. FlashBlade is fantastic for multiple MySQL instances using mysqldump or MySQL Enterprise Backup. It will scale linearly with the number of database connections or recover any single database/schema rapidly.

MySQL Issue 2: Long Seeding Times for New Replicas

You can use MySQL database cloning for replication or duplication scenarios. When adding a new replica, you need to copy data from an existing database to the new system (seeding). This process can take a long time and is limited by the speed of network connectivity. Once initial seeding is complete, you can use the new replica for data access operations.

One of the drawbacks of this process is that large databases will take a long time to seed data to new replicas. For example, a 5 terabyte database could take 69 minutes to become available when using a 10GB Ethernet connection between the source and replica.

FlashArray solves this through the use of storage snapshots. When adding the new replica, create a volume snapshot of any online replica and copy this snapshot to a volume attached to the new replica.

Problem 3: Inefficient Data Footprints and Management Overhead

One of the by-products of MySQL replication scenarios you must provision additional storage for each instance added to the group. In environments focused on the use of direct-attached storage (DAS), this can become inefficient because each server requires its own storage and has no central way to manage it.

Shared storage can increase management productivity by providing a single view of the storage landscape. What makes FlashArray truly unique as shared storage is that deduplication is always on. This creates a Modern Data Experience™ for MySQL databases because:

• You can reduce physical storage space for MySQL databases by up to 2x.
• Any additional replica in the MySQL replication group won’t require additional physical storage space.
• FlashArray storage replication for MySQL database volumes with technologies such as ActiveCluster™, ActiveDR™, and asynchronous replication and snapshot offload is more efficient because it moves less data over networks and consumes less space on offload targets.

Problem 4: Lack of Data Mobility between On-Premises and Public-Cloud Environments

Due to the popularity of MySQL, it’s not surprising that it had more than 35% of deployments on public-cloud infrastructure in 2019. One of the issues emerging from MySQL migrations to and from the public cloud is the lack of commonality between the two environments.

Pure Cloud Block Store™ is a storage solution for Amazon Web Services and Microsoft Azure that uses the same Purity operating environment as FlashArray//X and FlashArray//C. This allows for data mobility and common data management across on-premises and public-cloud environments.

For MySQL deployments on Pure Cloud Block Store, you can bring the benefits of consistent performance through QoS, non-disruptive everything, and data reduction to public-cloud environments. You can also use storage snapshots to eliminate long seeding times using storage snapshots.

Data mobility, however, doesn’t stop at on-premises to public-cloud migration. Physical to virtual or cloud interoperable data mobility is also possible using volume snapshots and data offload from FlashArray and Pure Cloud Block Store.