Finding out the exact size of the database after inserting records?

Question

I have this query:

SELECT
  table_schema "db.name"
, Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM
  information_schema.tables
GROUP BY
  table_schema;

Does this query show accurate results?

Answer 1

To be honest with you, that formula is correctly expressed but does not reveal fragmentation. If you were to defragment all your tables, then, yes, it is accurate.

If you want to figure out the fragmentation, let give you my method.

I have an old post, How do you remove fragmentation from InnoDB tables?. If you look on the bottom of that post, I show you how to find the fragmentation of a single InnoDB table. Let us go through this exercise.

SCENARIO

• datadir is /var/lib/mysql
• Given table mydb.mytable
• Physical file /var/lib/mysql/mydb/mytable.ibd (If table is InnoDB)
• Physical file /var/lib/mysql/mydb/mytable.MYD (If table is MyISAM)
• Physical file /var/lib/mysql/mydb/mytable.MYI (If table is MyISAM)

TOTAL SIZE OF A SINGLE TABLE (mydb.mytable)

You would run the query

SELECT data_length+index_length tblsize
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

From the OS

DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_OSBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done

Combining the two methods, you get this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
TB=mytable
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l ${TB}.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF A DATABASE (mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
DB=mydb
cd /var/lib/mysql/${DB}
TBL_PHYBYTES=0
for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}'";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"

TOTAL SIZE OF ALL DATABASES

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_PASS} -p${MYSQL_PASS}"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')";
DBLIST=""
for DB in `mysql ${MYSQL_CONN} -ANe"${SQL}"` ; do DBLIST="#{DBLIST} ${DB}" ; done
TBL_PHYBYTES=0
for DB in `echo "${DBLIST}"`
do
    cd /var/lib/mysql/${DB}
    for X in `ls -l *.[iM][bY][dDI] | awk '{print $9}' ; do (( TBL_OSBYTES += X )) ; done
done
SQL="SELECT data_length+index_length"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')";
TBL_LOGBYTES=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBL_FRGBYTES = TBL_PHYBYTES - TBL_LOGBYTES ))
echo "Table Stats for ${DB}.${TB}"
echo "Physical Size : ${TBL_PHYBYTES}"
echo "Logical Bytes : ${TBL_LOGBYTES}"
echo "Fragmentation : ${TBL_FRGBYTES}"