forked from pivotal-cf/docs-ops-guide
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinternal-databases.html.md.erb
185 lines (139 loc) · 8.18 KB
/
internal-databases.html.md.erb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
---
title: Managing Internal Databases
owner: MySQL
---
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.
<p class="note"><strong>Note</strong>: The procedures do not apply to databases configured as external in the PAS tile <strong>Databases</strong> pane. </p>
PAS components that use system databases include the Cloud Controller, Diego brain, Gorouter, and the User Authorization and Authentication (UAA) server. See [Cloud Foundry Components](../concepts/architecture/index.html).
## <a id='scale-down'></a> 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 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.
### <a id="check-health"></a> Check the Health of Your Cluster
Before scaling down your MySQL cluster, perform the following actions to ensure the cluster is healthy.
1. Use the Cloud Foundry Command Line Interface (cf CLI) to target the API endpoint of your <%= vars.first_product_name %> deployment:
<pre class="terminal">
$ cf api api.YOUR-SYSTEM-DOMAIN
Setting api endpoint to api.YOUR-SYSTEM-DOMAIN...
OK
API endpoint: http<span>s:</span>//api.YOUR-SYSTEM-DOMAIN... (API version: 2.54.0)
Not logged in. Use 'cf login' to log in.
</pre>
1. Log in with your User Account and Authentication (UAA) Administrator user credentials. Obtain these credentials by clicking the **Credentials** tab of the Pivotal Application Service (PAS) tile, locating the **Admin Credentials** entry in the **UAA** section, and clicking **Link to Credential**.
<pre class="terminal">
$ cf login -u admin
API endpoint: http<span>s:</span>//api.YOUR-SYSTEM-DOMAIN
Password>
Authenticating...
OK
</pre>
1. Create a test organization to verify the database across all nodes:
<pre class="terminal">
$ cf create-org data-integrity-test-organization
Creating org data-integrity-test-organization as admin...
OK
Assigning role OrgManager to user admin in org data-integrity-test-organization ...
OK
TIP: Use 'cf target -o data-integrity-test-organization' to target new org
</pre>
1. Obtain the IP addresses of your MySQL server by performing the following steps:
1. From the <%= vars.product_name %> **Installation Dashboard**, click the **Pivotal Application Service** tile.
1. Click the **Status** tab.
1. Record the IP addresses for all instances of the **MySQL Server** job.
1. Retrieve Cloud Controller database credentials from CredHub using the Ops Manager API:
1. Perform the procedures in the [Using the Ops Manager API](../customizing/ops-man-api.html) topic to authenticate and access the Ops Manager API.
1. Use the `GET /api/v0/deployed/products` endpoint to retrieve a list of deployed products, replacing `UAA-ACCESS-TOKEN` with the access token recorded in the previous step:
<pre class="terminal">$ curl "http<span>s:</span>//OPS-MAN-FQDN/api/v0/deployed/products" \
-X GET \
-H "Authorization: Bearer UAA-ACCESS-TOKEN"</pre>
1. In the response to the above request, locate the product with an `installation_name` starting with `cf-` and copy its `guid`.
1. Run the following `curl` command, replacing `PRODUCT-GUID` with the value of `guid` from the previous step:
<pre class="terminal">$ curl "http<span>s:</span>//OPS-MAN-FQDN/api/v0/deployed/products/PRODUCT-GUID/variables?name=cc-db-credentials" \
-X GET \
-H "Authorization: Bearer UAA-ACCESS-TOKEN"</pre>
1. Record the Cloud Controller database `username` and `password` from the response to the above request.
1. SSH into the Ops Manager VM. Because the procedures vary by IaaS, review the [SSH into Ops Manager](../customizing/trouble-advanced.html#ssh) section of the Advanced Troubleshooting with the BOSH CLI topic for specific instructions.
1. For each of the MySQL server IP addresses recorded above, perform the following steps from the Ops Manager VM:
1. Query the new organization with the following command, replacing `YOUR-IP` with the IP address of the MySQL server and `YOUR-IDENTITY` with the `identity` value of the CCDB credentials obtained above:
<pre class="terminal">
$ mysql -h YOUR-IP -u YOUR-IDENTITY -D ccdb -p -e "select created_at, name from organizations where name = 'data-integrity-test-organization'"
</pre>
1. When prompted, provide the `password` value of the CCDB credentials obtained above.
1. Examine the output of the `mysql` command and verify the `created_at` date is recent.
<pre class="terminal">
+---------------------+----------------------------------+
| created_at | name |
+---------------------+----------------------------------+
| 2016-05-28 01:11:42 | data-integrity-test-organization |
+---------------------+----------------------------------+
</pre>
1. If each MySQL server instance does not return the same `created_at` result, contact [Pivotal Support](https://support.pivotal.io/) 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.
### <a id="scale-down"></a> Set Server Instance Count to 1
1. From the <%= vars.product_name %> **Installation Dashboard**, click the **Pivotal Application Service** tile.
1. Select **Resource Config**.
1. Use the dropdown to change the **Instances** count for **MySQL Server** to `1`.
1. Click **Save**, then **Review Pending Changes** and **Apply Changes** to apply the changes.
1. Delete your test organization with the following cf CLI command:
<pre class="terminal">$ cf delete-org data-integrity-test-organization</pre>
## <a id='sizing-examples'></a> MySQL Cluster Sizing Examples
This topic describes two sizing examples 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.
### <a id="pws"></a> Example 1: Pivotal Web Services Production Environment
The information in this section comes from the Pivotal-managed Cloud Foundry deployment, Pivotal Web Services (PWS).
<p class="note"><strong>Note</strong>: This deployment differs from most <%= vars.product_name %> deployments in that the MySQL database is used for Diego, but not the Cloud Controller. This means that while there are a large number of queries per second, most of them are reading data, and the number of writes is not realistically reflected.</p>
* **IAAS**: AWS
* **App Instances**: ~23,000
* **Average SQL Queries Per Minute**: ~308,000
* **Average IOPS**: 220
* **Storage Volume Usage**: 80%
#### <a id="pws-sizing"></a> VM Sizing
The following table displays MySQL VM settings for this environment.
<table>
<tr>
<th>Setting</th>
<th>Value</th>
</tr>
<tr>
<td>VM Type</td>
<td>c4.2xlarge</td>
</tr>
<tr>
<td>Storage Volume Type</td>
<td>io1</td>
</tr>
<tr>
<td>Storage Volume Size</td>
<td>98GB</td>
</tr>
<tr>
<td>Storage Volume IOPS</td>
<td>2000</td>
</tr>
</table>
### <a id="diego"></a> Example 2: 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
#### <a id="diego-sizing"></a> VM Sizing
The following table displays MySQL VM settings for this environment.
<table>
<tr>
<th>Setting</th>
<th>Value</th>
</tr>
<tr>
<td>VM Type</td>
<td>n1-standard-16</td>
</tr>
<tr>
<td>Storage Volume Type</td>
<td>pd-ssd</td>
</tr>
<tr>
<td>Storage Volume Size</td>
<td>1TB</td>
</tr>
</table>