Service Binding Unbind Failure due to high SQL CPU usage

Products

• VMware Tanzu Application Service

Issue/Introduction

Service binding fails with below error.

“An unbind operation for the service binding between app xxxx and service instance xxxx-config-v3 failed: Service broker error: 500 INTERNAL_SERVER_ERROR”

Also, you will observe unusually high CPU usage on the SQL instance.

SSH to the SQL VM and run top command to see the high utilized CPU.

Environment

TAS

Cause

Service binding failure is due to high SQL CPU usage.

Resolution

Collecting pidstat or the performance_schema queries would likely be insightful in chasing the cpu usage down further.

By running the pidstat utility and observing some CPU snapshots of mysql thread activity. That might be captured with the following commands:

$ bosh -d <cf-deployment> ssh mysql/xxx$ sudo -i# nsenter -t $(pidof mysqld) -m -p pidstat -t -p $(</var/vcap/store/pxc-mysql/mysql.pid) 5 10 > /tmp/mysql_pidstat.txt

Note: the final arguments to this command - 5 10 - wait 5 seconds between obtaining a sample and take 10 samples so this command will take roughly 1 minute to run.

Additionally, MySQL 8.0 support CPU instrumentation of queries. This feature must be enabled, but would provide fairly fine-grained insight into what users and queries are consuming the most CPU. This might be enabled with the following procedure:

$ bosh -d <cf-deployment> ssh mysql/xxx$ sudo -i# mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf# mysql> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE NAME = 'events_statements_cpu';

CPU usage is only visible for queries executed after this instrumentation is enabled. Please wait around 15 minutes and then run a query of this form:

# mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -v -e 'SELECT DIGEST_TEXT, SCHEMA_NAME, FORMAT_PICO_TIME(SUM_CPU_TIME) AS CPU_TIME, COUNT_STAR, SUM_ROWS_SENT FROM `performance_schema`.`events_statements_summary_by_digest` ORDER BY SUM_CPU_TIME DESC LIMIT 10\G' > /tmp/mysql_cpu_consumers.txt

The output of the select query above would be useful to analyze consumers of high CPU.

CPU instrumentation may be disabled afterwards by running this query:

# mysql> UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE NAME = 'events_statements_cpu';

Using this way, we can determine if the CPU utilization is valid or not. Upon determining the CPU utilization is valid, consider increasing the CPU resource.