How to monitor mysql percentage disk utilization?

Question

I am new to mysql world and not sure if there is a way to calculate mysql TableSpace/DiskSpace utilization in percentage. I am aware mysql does not provide upper limit or free space like Oracle DB, but just space consumed by Index tables and Data Tables. I have decided to add these two values and divide with total space available for mountpoint mentioned in "datadir" attribute of mysql. My queries are :

1. Is this right way of doing this ??
2. What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?

Answer 1

Four years ago, I addressed this type of situation in StackOverflow : How can you determine how much disk space a particular MySQL table is taking up?. My post supplies queries to find out how much logical space a table consumes.

For InnoDB, data and index pages for an individual are lumped together in a single tablespace file (if innodb_file_per_table is enabled) or lumped together with the data and index pages of other InnoDB tables inside ibdata1 (if innodb_file_per_table is disabled). For the sake of optimism, I'll assume you have innodb_file_per_table enabled.

In order to look for percentage usage of a table you would have to check the physical usage of the table in OS, query the database for the logical usage, and divide the logical by physical and multiply by 100.

Let's assume the following situation

• Your datadir is /var/lib/mysql
• You have a table called mydb.mytable
• You have innodb_file_per_table enabled
• You don't know if the table is MyISAM or InnoDB
• You don't use other storage engines

Here is what you need for an individual table's space usage

GIVEN_DB=mydb
GIVEN_TB=mytable
DATADIR=/var/lib/mysql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Get Logical Space of the Table
#
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${GIVEN_DB}' AND table_name='${GIVEN_TB}'"
LS=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Get Physical Space of Table
# Whether InnoDB or MyISAM
#
cd ${DATADIR}/${GIVEN_DB}
PS=0
for X in `ls -l ${GIVEN_TB}.[Mi][Yb][DId]|awk '{print $5}'` ; do (( PS += X )) ; done
(( DF = PS - LS ))
NUM="${LS}00"
(( PCT = NUM / PS ))
echo "Unused Bytes for ${GIVEN_DB}.${GIVEN_TB} : ${DF}"
echo "Pct(%) for ${GIVEN_DB}.${GIVEN_TB} : ${PCT}"

UPDATE 2015-08-14 13:14 EDT

Your second question

What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?

My script can monitor an individual table's space usage regardless of its mount point.

What you are asking for requires two things

• List of databases
• Script to cycle through each database and get physical and logical sizes

Let's say you have a file called /root/dbmounts that looked like this

db1
db2
db3

Each entry in this file is assumed to be on a different mount point.

You need the script to cycle through them:

DBMOUNT_FILE=/root/dbmounts
DATADIR=/var/lib/mysql
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBMOUNT_FILE}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done

If every database is on a different mount point, you do not need the dbmounts file

DBLIST=/tmp/dblist.txt
DATADIR=/var/lib/mysql
SQL="SELECT schame_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql',performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBLIST}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done