Autoscale application fails with MySQL Deadlock errors

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

The Autoscale API and Autoscale applications in the System org and Autoscaling space use the Tanzu Application Service (TAS) MySQL to maintain all data regarding Autoscaler functionality.
 
If there are a lot (several hundred) of applications bound to Autoscaler and / or MySQL is under heavy load - sometimes queries from the Autoscaling applications may fail with the following error:

ERROR 28 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=40604528) Deadlock found when trying to get lock; try restarting transaction

org.springframework.dao.CannotAcquireLockException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement

If you encounter the error above in your Autoscaling applications - then there is a manual workaround available that can minimize the chance of this error from occurring. Specifically, we can add an index to one of the Autoscale database tables.

Environment

Product Version: 2.9

Resolution

We will walk through an example of how to do this on a TAS deployed with internal MySQL.

1. SSH into a mysql VM:

bosh -d <CF-DEP> ssh mysql/0

2. Connect to the mysql client:

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

3. Use the Autoscale database:

USE autoscale;

4. View the current indexes for the service_bindings table:

SHOW INDEX FROM service_bindings;

5. Add the index:

ALTER TABLE service_bindings ADD INDEX app_guid_idx (app_guid);

6. Verify the newly added index is present:

SHOW INDEX FROM service_bindings;

7. Exit

exit

Now the index is added.

Note: If you need to remove the index, repeat steps 1-3 and then run the following command to drop it:

DROP INDEX app_guid_idx ON service_bindings;

If you find that you are still experiencing the MySQL deadlock errors even after adding this index, please contact Tanzu Support.