How To Obtain All Table Sizes in CCDB database in TAS MySQL

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

This short Knowledge Base (KB) article details a short bash loop that will generate a file detailing the table sizes of each table in the CCDB database.

It is recommended to use the Cloud Controller API (CAPI ) instead of directly running queries on MySQL client when possible. However sometimes it may be necessary to collect statistics on CCDB usage and the following loop can be referenced for obtaining all table sizes in CCDB.

Environment

Product Version: 2.11

Resolution

1 - SSH on the TAS MySQL VM:
 

bosh -d cf-GUID ssh mysql/0

2 - Run the following loop which leverages the mysql client along with a config file to iterate over each CCDB table and obtain the table size while outputting the results to a file:
 

for table in `sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "use ccdb; show tables;" | grep -v Tables_in_ccdb`
do
echo -e "\nChecking table $table" >> /tmp/mysql-ccdb-table-sizes.txt
sudo mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf -e "select count(*) from ccdb.$table" >> /tmp/mysql-ccdb-table-sizes.txt
done

3 - Check to make sure that the file generated has contents:
 

mysql/cdcaf58d-fa68-4a0d-98d4-497a6de913cb:~$ ls -l /tmp
total 8
-rw-rw-r-- 1 bosh_77634edc9c77499 bosh_77634edc9c77499 5417 Feb 27 19:01 mysql-ccdb-table-sizes.txt

4 - Collect the file and it shall contain each table along with the number of records per table.