ibtmp1 taking up MySQL disk space for Tanzu Application Service

Products

• VMware Tanzu SQL

Issue/Introduction

When using MySQL for Tanzu Application Service, it may be observed that the file ibtmp1 has reached a large size. 

This file is located in a different directory, depending on the product.

For Tanzu Application Service:

mysql/9f2a29cc-84d0-4a68-aa46-dd86e94c525a:/var/vcap/store/pxc-mysql# ls -ltrlh | grep ibtmp1

-rw-r----- 1 vcap vcap  16G Feb  8 02:46 ibtmp1

For VMware SQL with MySQL for Tanzu Application Service:

mysql/86ee39fe-2e03-4f50-a9d5-202862c917bb:/var/vcap/store/mysql/data# ls -ltrlh | grep ibtmp1

-rw-r----- 1 vcap vcap  16G Feb  8 05:37 ibtmp1

This can cause the persistent disk to fill up, causing MySQL to fail.

Environment

Product Version: 2.10

Resolution

ibtmp1 is a temporary tablespace used by MySQL. More details regarding it can be found here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

To reduce the file size of ibtmp1, you need to restart the MySQL process on the VM. You can do this with the following command:

For Tanzu Application Service:

monit restart galera-init

For VMware SQL with MySQL for Tanzu Application Service:

monit restart mysql

This will cause the file to reduce back to the initial size of 12MB