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