MySQL database size part1

MySQL Database size limit
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912

所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.

然後我們寫了以下的php 程式:
----------------------------------------------------------------
#!/usr/bin/php -q
mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample;");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
   $sum += $total;
    print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234

因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.

-------------------------------------------------------------------
#!/usr/bin/php -q
# MySQL database size summary version 2
# Only count MyISAM tables

mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM';");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
    $sum += $total;
      print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234

與上面計算檔案的方式結果相同.

小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.

*************************************************************************************
2. 使用function來計算

剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.

將mysample各個MyISAM的Table列出table_name,data_length,index_length

select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';


計算總和:

select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

執行結果:
+---------+
| totsize |
+---------+
|   76662 |
+---------+

跟上面的資料相符.


接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
    from information_schema.tables
    where table_schema = in_dbname
    and table_type = 'BASE TABLE'
    and engine = 'MyISAM';

open c;
fetch c into rtnSize;
close c;

RETURN rtnSize;

END$$

DELIMITER ;
-----------------------------------------------------------

這個測試用的user: mysample,除了對database:mysample有權限外,對database:test也有權限.
我們測試一下

mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
|              76662 |
+--------------------+
1 row in set (0.07 sec)

mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
|           2200 |
+----------------+
1 row in set (0.00 sec)

可以看到可以依照輸入的database name計算出使用的size.