MySQL for PCF hangs when server VM persistent disk is full

Products

• VMware Tanzu SQL

Issue/Introduction

Symptoms:
When persistent disk is full, MySQL for Pivotal Cloud Foundry (PCF) hangs and no queries can be executed.

Environment

Resolution

To free space on a MySQL VM persistent disk, some of the older binary logs can be purged. To do this, follow the instructions below:

If MySQL is responsive, queries can then be run to identify and clear the binary logs. 

1. Connect to MySQL using the following command:

if using MariaDB

sudo mysql --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf

If using Percona

sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf

if you are trying to connect to TAS internal MySQL database, please see here for details.

On your MySQL leader or single node, run SHOW BINARY LOGS. The output is similar following:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      3658 |
| mysql-bin.000003 |    204594 |
| mysql-bin.000004 |       217 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |       552 |
+------------------+-----------+
6 rows in set (0.00 sec)

Note: If you have a follower, use the SHOW SLAVE STATUS command to check the log file it is reading.

2. To free up persistent disk space, purge the older binary logs using a query similar to:

PURGE BINARY LOGS TO 'mysql-bin.000005';

Note: In this example, binary logs 000001 through 000005 will be purged.

Note: If MySQL is not responsive and does not process queries, the binary logs can be deleted from the "/var/vcap/store/mysql" directory using the `rm` command. The mysql-bin.index file may need to be updated to take into account the binary logs that were manually deleted if mysql isn't running and the PURGE query cannot be run.

3. In this example, run the following command to identify the binary logs:

ls -l /var/vcap/store/mysql/mysql-bin.*

4. Remove the binary logs that are outputted by the query above:

rm -f /var/vcap/store/mysql/mysql-bin.00000[1-5]

5. After about a minute, re-run the query below to keep the database in sync with the manually deleted binary logs:

PURGE BINARY LOGS TO 'mysql-bin.000005';