Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

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) myisam_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

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.