0

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.

5
  • Is this all about InnoDB, MyISAM, or both ??? Jul 3 '13 at 21:02
  • No, about all the server because I monitor the server but it's possible !? or their sizes just concerned the InnoDB. Thanks
    – dardar.moh
    Jul 3 '13 at 21:07
  • Are you using innodb_file_per_table ??? Jul 3 '13 at 21:07
  • Yes I use innodb_file_per_table :)
    – dardar.moh
    Jul 3 '13 at 21:09
  • Both about innodb and MyISAM
    – dardar.moh
    Jul 3 '13 at 21:09
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

enter image description here

Please note that ibdata1 has 5 moving parts

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"?

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.