How to check growth of database in mysql?

Question

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

Answer 1

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.

Answer 2

This is how I monitor the growth of MySQL table in a dedicated table

The table to keep the values

CREATE TABLE `tables_sizes` (
 `table_name` VARCHAR(128) NOT NULL,
 `table_size` VARCHAR(25) NOT NULL,
 `measurement_type` VARCHAR(10) NOT NULL CHECK (measurement_type IN ('BYTES','ROWSCOUNT')),
 `measurement_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
) ENGINE=INNODB DEFAULT CHARSET=utf8

The shell script :

#!/bin/sh
export MYSQL_PWD=XXXXXXXXXX
TABLES="table1 table2 table3"
for TABLE in $TABLES;
do
 FILEPATH=/var/lib/mysql/scarprd_self/$TABLE.ibd
 TABLESIZE=`wc -c $FILEPATH | awk '{print 1ドル}'`
 #Size in Bytes
 mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$TABLESIZE', 'BYTES');"
 #Size in rows
 ROWSCOUNT=$(mysql -D scarprd_self -e "SELECT COUNT(*) AS ROWSCOUNT FROM $TABLE;")
 ROWSCOUNT=${ROWSCOUNT//ROWSCOUNT/}
 mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$ROWSCOUNT', 'ROWSCOUNT');"
 mysql -D scarprd_self -e "DELETE FROM tables_sizes WHERE measurement_datetime < TIMESTAMP(DATE_SUB(NOW(), INTERVAL 365 DAY));"
done