Scaling your internal MySQL

Here you will learn how to scale down your VMware Tanzu Application Service for VMs (TAS for VMs) MySQL cluster. This page also provides example sizing data from two environments that have significant load on their MySQL clusters.

For additional resources about scaling Internal MySQL, see the Deployments using internal MySQL section of the Scaling TAS for VMs topic and the Scalable components section of the High Availability in TAS for VMs topic.

ImportantThe procedure does not apply to databases configured as external in the Databases pane of the TAS for VMs tile.

TAS for VMs components that use system databases include the Cloud Controller, Diego Brain, Gorouter, and User Authorization and Authentication (UAA) server. For more information, see TAS for VMs Components.

Scale down your MySQL cluster

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 do this procedure.

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.

Verify the health of your cluster

Before scaling down your MySQL cluster, you must ensure that the cluster is healthy.

To verify the health of your cluster:

Use the Cloud Foundry Command Line Interface (cf CLI) to target the API endpoint of your Operations Manager deployment. Run:
```
cf api api.YOUR-SYSTEM-DOMAIN
```
Where YOUR-SYSTEM-DOMAIN is the system domain defined in the Domains pane of the TAS for VMs tile.
To obtain your UAA admin user credentials:
1. Click the Credentials tab of the TAS for VMs tile.
2. Locate the Admin Credentials entry in the UAA section.
3. Click Link to Credential.
Log in with the UAA admin credentials you obtained in the previous step 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:
1. From the Tanzu Operations Manager Installation Dashboard, click the TAS for VMs tile.
2. Click the Status tab.
3. Record the IP addresses for all instances of the MySQL Server job.
To retrieve Cloud Controller database credentials from CredHub using the Tanzu Operations Manager API:
1. Follow the procedures in Using the Tanzu Operations Manager API to authenticate and access the Tanzu Operations Manager API.
2. Retrieve a list of deployed products by running:
```
curl "https://OPS-MANAGER-FQDN/api/v0/deployed/products" \
-X GET \
-H "Authorization: Bearer UAA-ACCESS-TOKEN"
```
  Where:
  - OPS-MANAGER-FQDN is the fully-qualified domain name (FQDN) of your Tanzu Operations Manager deployment.
  - UAA-ACCESS-TOKEN is the access token you recorded in the previous step.
3. In the response to the request described earlier, locate the product with an installation_name starting with cf- and copy its guid.
4. Run:
```
curl "https://OPS-MANAGER-FQDN/api/v0/deployed/products/PRODUCT-GUID/variables?name=cc-db-credentials" \
-X GET \
-H "Authorization: Bearer UAA-ACCESS-TOKEN"
```
  Where:
  - OPS-MANAGER-FQDN is the fully-qualified domain name (FQDN) of your Tanzu Operations Manager deployment.
  - PRODUCT-GUID is the value of guid you recorded in the previous step.
  - UAA-ACCESS-TOKEN is the access token you recorded in the previous step.
5. Record the Cloud Controller database username and password from the response to the request.
SSH into the Tanzu Operations Manager VM. Because the procedures vary by IaaS, see SSH into Tanzu Operations Manager in Advanced troubleshooting with the BOSH CLI for instructions specific to your IaaS.

For each of the MySQL server IP addresses that were previously recorded:

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'"
```
Where:
- YOUR-IP is the IP address of the MySQL server.
- YOUR-IDENTITY is the identity value of the CCDB credentials you obtained earlier.
When prompted, provide the password value of the CCDB credentials you obtained earlier.

Examine the output of the mysql command and verify the created_at date is recent. For example:

+---------------------+----------------------------------+
| created_at          | name                             |
+---------------------+----------------------------------+
| 2016-05-28 01:11:42 | data-integrity-test-organization |
+---------------------+----------------------------------+

If each MySQL server instance does not return the same created_at result, contact Broadcom 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.

Set server instance count to 1

To scale your server instance count to 1:

Select Resource Config in the TAS for VMs tile.
Use the drop-down menu to change the Instances count for MySQL Server to 1.
Click Save, then Review Pending Changes and Apply Changes.

Delete your test organization by running:

cf delete-org data-integrity-test-organization

MySQL cluster sizing example

This topic describes a sizing example for internal MySQL in TAS for VMs.

Use this data as guidance to ensure that your MySQL Clusters are scaled to handle the number of app instances running on your deployment.

Example: Cloud Foundry Diego test environment

The information in this section comes from an environment used by the 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

VM sizing

The following table displays MySQL VM settings for this environment:

Setting	Value
VM Type	n1-standard-16
Storage Volume Type	pd-ssd
Storage Volume Size	1 TB