I have to know the disk consumed by the MySQL data on a RDS server hosted in Amazon cloud. One of the easy thing which I used was PhpMyAdmin UI and check the tables brief list. Unfortunately this database has more than 500 tables. So I feel it’s a lazy approach.
When I started googling (a usual activity) found that there were already few sql statement available for getting these details easily.
How do I find the MySQL database size
1. I created a sql file “check_dbsize.sql” and added the following line
SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
then execute it from the mysql console.
+--------------------+---------------------+
| Database Name | Database Size in MB |
+--------------------+---------------------+
| information_schema | 0.00878906 |
| mysql | 5.50491428 |
| one5_on5one | 20947.15841293 |
| performance_schema | 0.00000000 |
+--------------------+---------------------+
4 rows in set (0.81 sec)
2. How do I find the bigger tables in a database.
a. I created a sql file “show_table_size.sql” and added the following line
SELECT table_name AS “Tables”,
round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”
FROM information_schema.TABLES
WHERE table_schema = “onreve5_onrevenue”
ORDER BY (data_length + index_length) DESC;
b. Execute it from the Mysql console of RDS server.
+------------------------------------------------------------------+------------+
| Tables | Size in MB |
+------------------------------------------------------------------+------------+
| jos_company_newsletter_queue | 14801.56 |
| jos_company_drip_campaign_queue | 1526.11 |
| jos_company_menu_visitor_intelligense | 773.13 |
| jos_company_menu_blogdetails | 536.66 |
| jos_session | 505.06 |
| jos_company_special_mailsend | 469.64 |
| jos_appointment_schedule_office_hours | 382.75 |
| jos_company_mandrill_webhook_newsletter_details | 283.19 |
| jos_company_twilio_call_details | 272.02 |
| jos_company_newsletter_emaillist | 270.81 |
| jos_company_newsletter_logdetails | 257.81 |
3. Find the top 10 larger tables
Here is the one of more descriptive output from a sql statement from Percona. Open a file and save the following lines in it and execute it from the mysql schell. You no need to mention the table name or database name.
SELECT CONCAT(table_schema, ‘.’, table_name),
CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
You can download the sql file here
So simple 🙂 . Now I’m going to optimize the table size by deleting aged entries and execute optimize command to free up the non-used vaccum space inside the innodb file.
Leave a Reply