MySQL for Pivotal Platform Recommended Usage and Limitations

Page last updated:

MySQL is a powerful open-source relational database used by apps since the mid-90s. Developers have relied on MySQL as a first step to storing, processing and sharing data. As its user community has grown, MySQL has become a robust system capable of handling a wide variety of use cases and very significant workloads. Unlike other traditional databases which centralize and consolidate data, MySQL lends itself to dedicated deployment supporting the “shared nothing” context of building apps in the cloud.

About On-Demand Plans

  • Each on-demand plan instance is deployed to its own VM and is suitable for production workloads.
  • The maximum-number of on-demand plan instances available to developers is set by the operator and enforced on both a global and per-plan level quota. The global quota cannot exceed 200.
  • Operators can update the plan settings, including the VM size and disk size after the plans have been created. Operators should not downsize the VMs or disk size as this can cause data loss in pre-existing instances.
  • All plans deploy a single VM of the specified size with the specified disk type.
  • Cannot scale down the size of VMs on the plan once deployed (this protects against data loss).
  • The default maximum number of connections is set at 750.

Resource Usage Planning for On-Demand Plans

For information on setting limits and calculating costs for on-demand service instances, see Setting Limits for On-Demand Service Instances.

Availability Using Multiple AZs

MySQL for Pivotal Platform supports configuring multiple availability zones (AZs). However, assigning multiple AZs to MySQL jobs does not guarantee high availability.

  • You can assign on-demand plans to any of the configured AZs.
  • BOSH randomly deploys service instances across configured AZs. This minimizes the impact of an AZ outage and removes single points of failure.
  • For all MySQL for PCF plans, select three AZs. Choosing three AZs does not increase the number of AZs assigned to service instances to three.

Downtime During Redeploys

By default, MySQL for Pivotal Platform does a rolling deploy during upgrades or when you apply configuration changes. For single node and leader-follower plans, this results in MySQL for Pivotal Platform being inaccessible to apps for a brief period of time.

If you are using a HA cluster plan, MySQL for Pivotal Platform uses rolling redeploys and your service does not incur downtime. You can mitigate downtime by using HA cluster plans.

For more information about downtime in MySQL for Pivotal Platform, see RPOs and RTOs.

Persistent Disk Usage

The persistent disk receives, temporarily stores, and encrypts MySQL backups. This results in increased disk usage while backups are being processed. For single node and leader-follower service plans, backups are processed on the MySQL VM. For highly available (HA) clusters, backups are processed on the jumpbox VM.

In addition to backups, the persistent disk also stores InnoDB redo logs and binlogs. InnoDB redo logs use a static amount of disk size. If your app is write-heavy, increase your persistent disk to accommodate the binlogs.

For the amount of persistent disk required for your service plan, see the below sections:

Single Node and Leader-Follower

You can configure the persistent disk size for single node or leader-follower VMs. For instructions for configuring disk size, see Configure Service Plans.

Use the following table to determine the amount of persistent disk space you need:

InnoDB Redo Logs Total Disk Size Minimum Total Disk Size
512 MB 3N + 512 MB 5 GB

In the Total Disk Size formula from the table above, each N represents the following items in equal value:

  • Maximum app data. Remember to include binlogs in your size estimate for app data.
  • Data for copied backups.
  • Data for compression and encryption.

Each N combined makes 3N, as seen in the formula.

The following diagram shows how persistent disk space is used:

Persistent Disk diagram.
    The information depicted in the diagram is explained above.

HA Cluster Jumpbox

You can configure the persistent disk size for HA cluster jumpbox VMs. For instructions for configuring disk size, see Configure Service Plans.

Use the following table to determine the amount of persistent disk space you need:

InnoDB Redo Logs Total Disk Size Minimum Total Disk Size
2 GB 2N + 2 GB 10 GB

In the Total Disk Size formula from the table above, each N represents the following items in equal value:

  • Data for copied backups.
  • Data for compression and encryption.

Each N combined makes 2N, as seen in the formula.

The following diagram shows how persistent disk space is used:

Persistent Disk diagram.
    The information depicted in the diagram is explained above.

HA Cluster Node

You can configure the persistent disk size for HA cluster node VMs. For instructions for configuring disk size, see Configure Service Plans.

Use the following table to determine the amount of persistent disk space you need:

InnoDB Redo Logs Total Disk Size Minimum Total Disk Size
2 GB N + 2 GB 5 GB

In the Total Disk Size formula from the table above, N represents maximum app data. Remember to include binlogs in your size estimate for app data.

The following diagram shows how persistent disk space is used:

Persistent Disk diagram.
    The information depicted in the diagram is explained above.