Getting Started with PCF MySQL Galera
This topic explains how a developer on PCF can start using MySQL with their apps. It is important to note that MySQL for PCF v1 is different than standard MySQL and has some limitations that you must be aware of.
- Only InnoDB tables are supported. Writes to other types of tables, such as MyISAM tables will not replicate across the cluster.
- Explicit locking is not supported, i.e.
FLUSH TABLES tableA WITH READ_LOCK.
- Large DDL (ie, schema changes like ALTER TABLE) will lock all schemas, affecting all sessions with the DB. This can be mitigated via a manual step using Galera’s RSU feature.
- Table partitioning may cause the cluster to get into a hung state. This is as a result of the implicit table locks that are used when running table partition commands.
- MySQL for PCF supports table triggers; however multiple triggers per table are not supported
- All tables must have a primary key; multi-column primary keys are OK. This is because of the the way Galera replicates using row based replication and ensuring unique rows on each instance
- While not explicitly a limitation, large transaction sizes may inhibit the performance of the cluster and thus the applications using the cluster. In a MariaDB Galera cluster, writes are processed as “a single memory-resident buffer”, so very large transactions will adversely affect cluster performance.
- Do not execute a DML statement in parallel with a DDL statement when both statements affect the same tables. Locking is lax in Galera, even in single node mode. Rather than the DDL waiting for the DML to finish, they will both apply immediately to the cluster and may cause unexpected side effects.
- Do not rely on auto increment values being sequential as Galera guarantees auto-incrementing unique non-conflicting sequences, so each node will have gaps in IDs. Furthermore, Galera sets user’s to READ ONLY in regards to auto increment variables. Without this feature, Galera would require shared locking of the auto increment variables across the cluster, causing it to be slower and less reliable
- MySQL for PCF does not support MySQL 5.7’s JSON
- Max size of a DDL or DML is limted to 2GB
- Defining whether the node splits large
Load Datacommands into more maneageable units
Certain types of queries may cause deadlocks. For example, transactions like
SELECT ... for UPDATE when querying rows in opposite order will cause the queries to deadlock. Rewriting these queries and SQL statements will help minimize the deadlocks that your application experiences. One such solution is to query for a bunch of potential rows, then do an update statement. The MySQL documentation provides more information about InnoDB Deadlocks and Handling InnoDB Deadlocks.
As part of installation the product is automatically registered with Pivotal Cloud Foundry Elastic Runtime (see Lifecycle Errands). On successful installation, the MySQL service is available to application developers in the Marketplace, via the web-based Developer Console or
cf marketplace. Developers can then provision instances of the service and bind them to their applications:
$ cf create-service p-mysql 100mb-dev mydb $ cf bind-service myapp mydb $ cf restart myapp
For more information about the use of services, see the Services Overview.
To help application developers get started with MySQL for PCF, we have provided an example application, which can be downloaded here. Instructions can be found in the included README.
The following tools let developers check the usage of their MySQL for PCF service instances, and access their databases directly.
Developers can check their current storage usage and service plan quota in the service instance dashboard. You can access this dashboard by either navigating to it from Apps Manager or obtaining its URL from the Cloud Foundry Command-Line Interface (cf CLI):
- From Apps Manager
- Select the space that the service instance runs in.
- Select the Services tab.
- Under Services click the service instance to check.
- Click Manage at top right to open the service instance dashboard.
- From the cf CLI
- Log into the space that the service instance runs in.
cf service INSTANCE-NAME:
$ cf service acceptDB Service instance: acceptDB Service: p-mysql Plan: 100mb-dev Description: MySQL service for application development and testing Documentation url: Dashboard: https://p-mysql.sys.acceptance.cf-app.example.com/manage/instances/ddfa6842-b308-4983-a544-50b3d1fb62f0
- Navigate to the URL listed in the output as
Dashboard. In the example above, the instance dashboard URL is
The MySQL for PCF service instance dashboard shows how much storage the instance currently uses and the maximum usage allowed by its service plan. It does not show or manage database contents. You can think of it as a gas gauge, while the Pivotal MySQL Database Management App provides an interface through which you can drive.
Note: The service instance dashboard is distinct from the proxy dashboard that PCF operators can access to check the proxy instances handling queries for all MySQL for PCF service instances in a PCF deployment.
The Pivotal MySQLWeb app provides a web-based UI for managing MySQL for PCF databases. The free app lets you view and operate on tables, indexes, constraints, and other database structures, and directly execute SQL commands.
You can run the Pivotal MySQLWeb app in two ways:
- Standalone on your own machine
- Deployed to PCF
If you deploy Pivotal MySQLWeb to PCF, you can configure it in the deployment manifest to automatically bind to a specific service instance.
To connect to your MySQL for PCF databases from a command line, use the Cloud Foundry Command-Line Interface (cf CLI) MySQL plugin. The plugin lets you:
- Inspect databases for debugging.
- Manually adjust database schema or contents in development environments.
- Dump and restore databases.
To install the cf CLI MySQL plugin, run the following:
$ cf install-plugin -r "CF-Community" mysql-plugin
For more information, see the cf-mysql-plugin repository.