MySQL get disk usage of all databases

I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.

MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.

The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.

I suggest, in a previous post, place all these procedures in a database called tools, but you can still choose different location.

So when call the procedure will have a global view of information that will be useful for maintenance tasks.

 

mysql> call tools.sp_overview();
+------------------------------+---------+--------+----------+---------+
| Database                     | Charset | Tables | Routines | Size Mb |
+------------------------------+---------+--------+----------+---------+
| database1                    | utf8    |     43 |       28 |     7.0 |
| database2                    | latin1  |     43 |       28 |   205.0 |
| database3                    | utf8    |    116 |        0 |   126.2 |
| database4                    | utf8    |     99 |        0 |     0.3 |
| database5                    | utf8    |    165 |        0 |    77.4 |
| database6                    | utf8    |    121 |        2 |   719.4 |
| database7                    | utf8    |    122 |        0 |    91.3 |
| database8                    | utf8    |    116 |        0 |    89.7 |
| database9                    | utf8    |    124 |        0 |     4.5 |
| database10                   | utf8    |    113 |        0 |   147.7 |
| database11                   | latin1  |    119 |        3 |   436.4 |
| database12                   | latin1  |    122 |        0 |   439.1 |
| database13                   | latin1  |    122 |        4 |   452.7 |
| database14                   | utf8    |    115 |        0 |   273.0 |
| database15                   | utf8    |    122 |        0 |   265.5 |
| database16                   | utf8    |      0 |        0 |    NULL |
| database17                   | latin1  |     26 |        0 |     0.9 |
| database18                   | latin1  |      9 |        0 |     0.0 |
| database19                   | latin1  |     22 |        0 |     0.3 |
+------------------------------+---------+--------+----------+---------+
19 rows in set (3.01 sec)

 

The first time execution maybe you will get results in a few seconds.

There is the source code:

 

DELIMITER $$
 
DROP PROCEDURE IF EXISTS tools.sp_overview$$
CREATE PROCEDURE tools.sp_overview()
BEGIN
 
    SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`,
        COUNT(t.TABLE_NAME) AS `Tables`,
 
        (SELECT COUNT(*) FROM information_schema.ROUTINES AS r
            WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`,
 
         round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb`
 
        FROM information_schema.SCHEMATA AS s
            LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
        WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')
 
    GROUP BY s.SCHEMA_NAME;
END$$
DELIMITER ;

2 Responses to MySQL get disk usage of all databases

  1. Are you familiar with common_schema? It has a lot of built in views to provide information like this. For example, you can get disk usage of all databases like this:

    select table_schema as “Database”,
    count_tables as “Tables”,
    round((total_size / 1024 / 1024),1) as “Size Mb”
    from common_schema.data_size_per_schema;

    Here’s the link to common_schema:
    http://code.google.com/p/common-schema/

  2. Nice proc.

    The common_schema for MySQL has a similar solution (http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/data_dimension_views.html), and in addition many more diagnostic and administrative views / tools / utilities.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

Go back to top