Activities

November 2014
M T W T F S S
« Oct   Dec »
 12
3456789
10111213141516
17181920212223
24252627282930

RDS- MySQL : How do I find the database size and bigger tables

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.

mysql> source check_dbsize.sql;
+--------------------+---------------------+
| 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.

mysql> source show_table_size.sql;
+------------------------------------------------------------------+------------+
| 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;

bigger_table

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

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>