Home > Others > Mysql Database Disk Usage.

Mysql Database Disk Usage.

We were running out of disk space on one of the databases server, we need to get information on what the current table/database usage was. Below are few commands for mysql server tables usages.
To get details of table.
show table status from zabbix;
You can use this query to show the size of a table (although you need to substitute the variables first):
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";
Here is the output:
mysql> SELECT 
    ->     table_name AS `Table`, 
    ->     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
    -> FROM information_schema.TABLES 
    -> WHERE table_schema = "zabbix"
    ->     AND table_name = "history";
+---------+------------+
| Table   | Size in MB |
+---------+------------+
| history |       0.03 |
+---------+------------+
1 row in set (0.00 sec)

mysql> 
or this query to list the size of every table in every database, largest first:
SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;
Here is the output:
mysql> SELECT 
    ->      table_schema as `Database`, 
    ->      table_name AS `Table`, 
    ->      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
    -> FROM information_schema.TABLES 
    -> ORDER BY (data_length + index_length) DESC; 
+--------------------+---------------------------------------+------------+
| Database           | Table                                 | Size in MB |
+--------------------+---------------------------------------+------------+
| zabbix             | items                                 |       3.41 |
| zabbix             | auditlog                              |       3.30 |
| zabbix             | functions                             |       3.17 |
| zabbix             | triggers                              |       2.67 |
| zabbix             | images                                |       1.53 |
| zabbix             | items_applications                    |       0.64 |
| zabbix             | events                                |       0.56 |
| mysql              | help_topic                            |       0.46 |
| zabbix             | housekeeper                           |       0.19 |
| zabbix             | history_uint                          |       0.16 |
| zabbix             | auditlog_details                      |       0.13 |
| zabbix             | opconditions                          |       0.03 |
| zabbix             | trigger_discovery                     |       0.03 |
| zabbix             | sysmap_url                            |       0.03 |
| zabbix             | proxy_autoreg_host                    |       0.03 |
| zabbix             | dbversion                             |       0.02 |
| zabbix             | zbxe_translation                      |       0.01 |
...
    < more verbose ... >
...
| mysql              | plugin                                |       0.00 |
| mysql              | func                                  |       0.00 |
| mysql              | time_zone_name                        |       0.00 |
| mysql              | ndb_binlog_index                      |       0.00 |
| mysql              | time_zone_leap_second                 |       0.00 |
| information_schema | EVENTS                                |       0.00 |
| information_schema | PROCESSLIST                           |       0.00 |
| mysql              | time_zone                             |       0.00 |
| information_schema | SESSION_VARIABLES                     |       0.00 |
| information_schema | COLUMN_PRIVILEGES                     |       0.00 |
| mysql              | slow_log                              |       0.00 |
| information_schema | SESSION_STATUS                        |       0.00 |
| information_schema | KEY_COLUMN_USAGE                      |       0.00 |
| information_schema | SCHEMA_PRIVILEGES                     |       0.00 |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |       0.00 |
| information_schema | USER_PRIVILEGES                       |       0.00 |
| information_schema | SCHEMATA                              |       0.00 |
| information_schema | COLLATIONS                            |       0.00 |
| information_schema | GLOBAL_VARIABLES                      |       0.00 |
| mysql              | general_log                           |       0.00 |
| information_schema | CHARACTER_SETS                        |       0.00 |
| information_schema | TABLE_PRIVILEGES                      |       0.00 |
| information_schema | GLOBAL_STATUS                         |       0.00 |
| information_schema | REFERENTIAL_CONSTRAINTS               |       0.00 |
| information_schema | TABLE_CONSTRAINTS                     |       0.00 |
| information_schema | FILES                                 |       0.00 |
| information_schema | PROFILING                             |       0.00 |
| information_schema | TABLES                                |       0.00 |
| information_schema | STATISTICS                            |       0.00 |
| information_schema | ENGINES                               |       0.00 |
+--------------------+---------------------------------------+------------+
157 rows in set (0.16 sec)
Copied straight from stackoverflow just in case I forget.

from Blogger http://ift.tt/20IqBA5
via IFTTT

Advertisements
Categories: Others Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: