Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is this all about InnoDB, MyISAM, or both ??? –  RolandoMySQLDBA 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 –  FÔx Gênki Jul 3 '13 at 21:07
    
Are you using innodb_file_per_table ??? –  RolandoMySQLDBA Jul 3 '13 at 21:07
    
Yes I use innodb_file_per_table :) –  FÔx Gênki Jul 3 '13 at 21:09
    
Both about innodb and MyISAM –  FÔx Gênki Jul 3 '13 at 21:09
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
+100 that's great, These statistics can help me a lot. Thanks. if I want do this statistics like this : docs.google.com/file/d/0B3q2DOPnNwNhaUZEVXJZeDRMTm8/edit What is the formula to make it and Thanks again. –  FÔx Gênki Jul 3 '13 at 21:31
    
I have to leave work now. I can look at this in about 4.5 - 5 hours... –  RolandoMySQLDBA Jul 3 '13 at 21:33
    
ok, I wish you good luck and thank you for all –  FÔx Gênki Jul 3 '13 at 21:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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