Managing Internal Databases
Page last updated:
This topic describes two procedures for managing the internal MySQL databases used by Pivotal Application Service (PAS) system components: scaling down your MySQL cluster, and migrating the cluster to a database stack that uses TLS encryption. It also provides example sizing data from two environments that have significant load on their MySQL clusters.
Note: The procedures do not apply to databases configured as external in the Databases pane of the PAS tile.
PAS components that use system databases include the Cloud Controller, Diego Brain, Gorouter, and the User Authorization and Authentication (UAA) server. For more information, see PAS Components.
This procedure explains how to safely scale your MySQL cluster down to a single node. If you are already running the MySQL cluster with a single node, you do not need to perform these steps.
By default, internal MySQL deploys as a single node. To take advantage of the high availability features of MySQL, you may have scaled the configuration up to three or more server nodes.
Before scaling down your MySQL cluster, you should ensure the cluster is healthy. To check the health of your cluster:
Using the Cloud Foundry Command Line Interface (cf CLI), target the API endpoint of your Ops Manager deployment by running:
cf api api.YOUR-SYSTEM-DOMAIN
YOUR-SYSTEM-DOMAINis the system domain you configured in the Domains pane of the PAS tile.
To obtain your User Account and Authentication (UAA) Administrator user credentials:
- Select the Credentials tab in the PAS tile.
- Under UAA, locate the Admin Credentials.
- Click Link to Credential.
Log in to the API endpoint of your Ops Manager deployment by running:
cf login -u admin
Create a test organization to verify the database across all nodes by running:
cf create-org data-integrity-test-organization
To obtain the IP addresses of your MySQL server:
- Select the Status tab in the PAS tile.
- Record the IP addresses for all instances of the MySQL Server job.
To retrieve Cloud Controller database credentials from CredHub using the Ops Manager API:
- Follow the procedures in Using the Ops Manager API to authenticate and access the Ops Manager API.
GET /api/v0/deployed/productsendpoint to retrieve a list of deployed products by running:
curl "https://OPS-MAN-FQDN/api/v0/deployed/products" \ -X GET \ -H "Authorization: Bearer UAA-ACCESS-TOKEN"
UAA-ACCESS-TOKENis the access token you recorded in the previous step.
In the response to the above request, locate the product with an
cf-and copy its
curl "https://OPS-MAN-FQDN/api/v0/deployed/products/PRODUCT-GUID/variables?name=cc-db-credentials" \ -X GET \ -H "Authorization: Bearer UAA-ACCESS-TOKEN"
OPS-MAN-FQDNis the fully-qualified domain name (FQDN) of your Ops Manager installation.
guidvalue you recorded in the previous step.
UAA-ACCESS-TOKENis the is the access token you recorded earlier.
Record the Cloud Controller database
passwordfrom the response to the above request.
SSH into the Ops Manager VM. For instructions specific to your IaaS, see the SSH into Ops Manager section of the Advanced Troubleshooting with the BOSH CLI topic.
For each of the MySQL server IP addresses recorded above:
From the Ops Manager VM, query the new organization by running:
mysql -h YOUR-IP -u YOUR-IDENTITY -D ccdb -p -e "select created_at, name from organizations where name = 'data-integrity-test-organization'"
YOUR-IPis the IP address of the MySQL server.
identityvalue of the Cloud Controller database credentials you obtained earlier.
When prompted, provide the
passwordvalue of the Cloud Controller database credentials you obtained earlier.
Examine the output of the
mysqlcommand and verify the
created_atdate is recent.
+---------------------+----------------------------------+ | created_at | name | +---------------------+----------------------------------+ | 2016-05-28 01:11:42 | data-integrity-test-organization | +---------------------+----------------------------------+
If each MySQL server instance does not return the same
created_atresult, contact Pivotal Support before proceeding further or making any changes to your deployment. If each MySQL server instance does return the same result, then you can safely proceed to scaling down your cluster to a single node by performing the steps in the following section.
From the Ops Manager Installation Dashboard, click the PAS tile.
Select Resource Config.
Use the dropdown to change the Instances count for MySQL Server to
Click Save, then Review Pending Changes and Apply Changes to apply the changes.
Delete your test organization by running:
cf delete-org data-integrity-test-organization
This section describes a sizing example for internal MySQL in PAS. Use this data as guidance to ensure your MySQL clusters are scaled to handle the number of app instances running on your deployment.
The information in this section comes from an environment used by Diego to test the MySQL cluster with a high load similar to one generated large deployment.
- IAAS: GCP
- App Instances: 250,000
- Average SQL Queries Per Minute: ~5,100,000
- Average IOPS:
- Reads: 3.63
- Writes: 363.98
The following table displays MySQL VM settings for this environment:
|Storage Volume Type||pd-ssd|
|Storage Volume Size||1TB|