How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file
EXAMPLES

• Database A contains all tables in INNODB storage engine

• Database B contains all tables in MyISAM storage engine

• Database C contains a mixture of InnoDB and MyISAM tables

This is very funny. I just answered a question where I posted queries you can run to tell you how much disk space is consumed by each storage engine by database.

See that post from Jan 13, 2013 : Translating backup size to database size

In essence, you need to consult the information_schema for data and index sizes as viewed by mysqld. Here are some examples of how to get the needed information:

MyISAM

To get the amount of diskspace used by all your MyISAM tables, run this:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
) A;

To get this by database

SELECT db,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM
(
    SELECT table_schema db,engine,SUM(data_length+index_length) myisam_bytes
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql')
    GROUP BY table_schema,engine
) A;

InnoDB

To get the amount of diskspace used by all your InnoDB tables, run this:

SELECT
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
) A;

To get this by database

SELECT db,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema
) A;

To get this by database and engine

SELECT db,engine,
    innodb_bytes/power(1024,1) innodb_kb,
    innodb_bytes/power(1024,2) innodb_mb,
    innodb_bytes/power(1024,3) innodb_gb
FROM
(
    SELECT table_schema db,engine,
    SUM(data_length+index_length) innodb_bytes
    FROM information_schema.tables WHERE engine='InnoDB'
    GROUP BY table_schema,engine
) A;

CAVEAT #1

I have discussed this many times before

• Sep 13, 2011 : Determining max database and table size supported and present size

• Dec 01, 2011 : MySQL Workbench Database Sizes

• Jul 07, 2012 : Find the largest databases on my server

CAVEAT #2

Keep in mind that these queries will report how much actual space is occupied for data and indexes. The actual file sizes may actually be more than the information_schema says. That being the case, you must run OPTIMIZE TABLE tblname; for each table that has fragmentation.

For example, suppose you have a MyISAM table mydb.mytable and you run this query:

SELECT
    myisam_bytes/power(1024,1) myisam_kb,
    myisam_bytes/power(1024,2) myisam_mb,
    myisam_bytes/power(1024,3) myisam_gb
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

Goto to the OS and run on of the following:

• ls -l mytable.MY[DI] (Linux)

• dir mytable.MY* (Windows)

The sum of the bytes listed may be more than what the information_schema says. If that is the case, go run OPTIMIZE TABLE tblname; and run the same query, you may notice that the table will shrink.

That being said make sure run periodically run OPTIMIZE TABLE against all your tables that experiences heavy updates, delete, and inserts.

StackExchange Database Administrators Q#31864, answer score: 6