How to get the size of a MySQL table or a MySQL Database

Products

• VMware Tanzu Application Service

Issue/Introduction

This procedure on how to determine the size of a MySQL table or a database. The size of table or database will help you plan how to scale your MySQL vm database disk size. This will also help determine which tables are taking the most space.

Resolution

Here are procedure on how you can determine specific table size, all tables, database sizes:

1. ssh into any MySQL vm 
2. Login into your MySQL database where you want to restore your CCDB. Sample login command

$ bosh -d DEPLOYMENT ssh mysql-vm

$ sudo -i

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

 

3. Run the following MySQL queries.

• For calculating for a particular table in a database. Where <your-database-name> is where your table is located and <your-table-name> is the table name you want to calculate size

SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM
    information_schema.TABLES
WHERE
    table_schema = '<your-database-name>'
    AND table_name = '<your-table-name>';

• For listing all table sizes on all your databases;

SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size in (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

• For listing all databases sizes 

SELECT 
  TABLE_SCHEMA AS "Database", 
  SUM(data_length + index_length) / 1024 / 1024 AS `Size in (MB)` 
FROM information_schema.TABLES 
  GROUP BY table_schema