Information about Disk Storage MySQL

Question

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate :

• used and free size of tablespace,
• used and free of data files,
• used and free of log files

Anyone know how to calculate their sizes or someone can give me articles or books can help me. And Thanks.

Answer 1

Here is a query I use to get all the used space of MySQL for all storage engines:

SELECT
    IFNULL(ENGINE,'Total') "Storage Engine",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
        FROM
        (SELECT
            ENGINE,
            SUM(data_length) DAT,
            SUM(index_length) NDX,
            SUM(data_length+index_length) TBL
        FROM
        (
           SELECT engine,data_length,index_length FROM
           information_schema.tables WHERE table_schema NOT IN
           ('information_schema','performance_schema','mysql')
           AND ENGINE IS NOT NULL
        ) AAA GROUP BY ENGINE WITH ROLLUP
) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database

SELECT
    IFNULL(DB,'Total') "Database",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database/Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

CAVEAT ON InnoDB FRAGMENTATION

To find the fragmentation of an individual InnoDB table (.ibd) you cannot fully trust the INFORMATION_SCHEMA database. You have to piggyback off the OS.

Let's say the table is mydb.mtable and datadir is /var/lib/mysql

Get the Size of the InnoDB Table from OS and subtract it from what INFORMATION_SCHEMA.tables says

SQLSTMT="SELECT SUM(data_length+index_length) size FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema='mydb' AND table_name='mytable'"
FILESIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
FILESIZE_DB=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( FILESIZE_FRAG = FILESIZE_OS - FILESIZE_DB ))

CAVEAT ON MyISAM FRAGMENTATION

For the MyISAM table mydb.myatble for the .MYD

SELECT data_length INTO @datsize
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SELECT table_rows*avg_row_length INTO @sizerows
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SET @tblfrag = @datsize - @sizerows;
SELECT @datsize,@sizerows,@tblfrag;

CAVEAT ON Tablespace file ibdata1

Here is a look at the InnoDB Architecture

Please note that ibdata1 has 5 moving parts

• Data Dictionary
• Double Write Buffer (support data consistency; used for Crash Recovery)
• Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
• Rollback Segments
• Undo Space (where the most uncontrolled growth can happen)

Since you have innodb_file_per_table enabled, the data and index pages for all InnoDB have been separated from these internal parts. Thus, there is no need to monitor ibdata1 internally.

For the benefit of those who have innodb_file_per_table disabled, then everything InnoDB and its grandmother is inside ibdata1. This is how you monitor ibdata1:

Let's say your innodb_data_fie_path is this

[mysqld]
innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend

You would have to add the file sizes of the two files and subtract the sum of the data and index pages. Perhaps like this:

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

See my additional suggestion on handling multiple ibdata files : How to solve "The table ... is full" with "innodb_file_per_table"?