MySQL database size part2

3. MySQL metadata 的存放空間 information_schema 在database size上的應用

user:mysample 有權限的database有三個,其中information_schema是系統metadata的存放空間,
我們就可以存取他來獲得一些資訊.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysample           |
| test               |
+--------------------+

我們可以執行下面的指令:
select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| mysample     |            37750 |             38912 |   76662 |
| test         |              152 |              2048 |    2200 |
+--------------+------------------+-------------------+---------+
2 rows in set (0.01 sec)

結果均相符,因為來源都一樣. 這裡可以看到MySQL方便的地方,我們不需要將information_schema排除.
到目前都是以user:mysample來作例子.當我們要以整個instance為範圍來管理就需要用root權限登入了.
以root的視野來看:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+

mysql> select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
    -> from information_schema.tables
    -> where table_type='BASE TABLE'
    -> and engine='MyISAM'
    -> group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| auth         |              231 |              4096 |    4327 |
| bookmarks    |              152 |              3072 |    3224 |
| books        |             6094 |             10240 |   16334 |
| joomla       |           105223 |            118784 |  224007 |
| joomla2      |           108588 |            118784 |  227372 |
| mail         |             3545 |              4096 |    7641 |
| mysample     |            37750 |             38912 |   76662 |
| mysql        |           492362 |             72704 |  565066 |
| pureftpd     |               88 |              3072 |    3160 |
| simple_blog  |               44 |              3072 |    3116 |
| test         |              152 |              2048 |    2200 |
| test2        |              476 |              7168 |    7644 |
+--------------+------------------+-------------------+---------+


有沒有發覺到上面 show databases的結果扣掉 information_schema,共有14個databses;
而我們依據 information_schema.tables 計算的資料卻只有12筆.

因為有些database建立了,但是都沒有在裡面建立資料庫物件(table/view等),所以在
information_schema.tables 裡面就沒有資料.
接下來我們看一下 information_schema.schemata

mysql> select distinct schema_name
    -> from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
15 rows in set (0.00 sec)

mysql> select distinct table_schema
    -> from information_schema.tables;
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
13 rows in set (0.03 sec)

現在應該很清楚的看到了 information_schema.schemata, information_schema.tables
與show databases, show table status, show tables的關係了.

***********************************************************************************
4. 建立控制用database及其附屬table/view
接下來我們建立一個database 來作控制之用.

login as root

mysql> create database quotadb;

mysql> use quotadb;
Database changed

CREATE TABLE quota (
dbname CHAR(64) NOT NULL PRIMARY KEY,
limitbyte BIGINT NOT NULL,
exceeded ENUM('Y','N') DEFAULT 'N' NOT NULL);

insert into quota
select distinct schema_name,20971520,'N'
from information_schema.schemata
where schema_name != 'information_schema'
and schema_name != 'mysql'
and schema_name != 'quotadb';

mysql> select * from quota;
+-------------+-----------+----------+
| dbname      | limitbyte | exceeded |
+-------------+-----------+----------+
| auth        |  20971520 | N        |
| bookmarks   |  20971520 | N        |
| books       |  20971520 | N        |
| impexp_text |  20971520 | N        |
| joomla      |  20971520 | N        |
| joomla2     |  20971520 | N        |
| mail        |  20971520 | N        |
| mysample    |  20971520 | N        |
| pandora     |  20971520 | N        |
| pureftpd    |  20971520 | N        |
| simple_blog |  20971520 | N        |
| test        |  20971520 | N        |
| test2       |  20971520 | N        |
+-------------+-----------+----------+
13 rows in set (0.00 sec)

我們需要將mysql這個系統database排除,information_schema系統metadata也需要排除,
quotadb自己也要排除,以免限制住自己.

接下來建立一個view以方便獲取database size,並且在view中事先將mysql,quotadb排除.

create view v_dbsize as
select table_schema as dbname,sum(data_length) as data_size,sum(index_length) as index_size,sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema != 'mysql'
and table_schema != 'quotadb'
and table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;

mysql> select * from v_dbsize;
+-------------+-----------+------------+---------+
| dbname      | data_size | index_size | totsize |
+-------------+-----------+------------+---------+
| auth        |       231 |       4096 |    4327 |
| bookmarks   |       152 |       3072 |    3224 |
| books       |      6094 |      10240 |   16334 |
| joomla      |    105223 |     118784 |  224007 |
| joomla2     |    108588 |     118784 |  227372 |
| mail        |      3545 |       4096 |    7641 |
| mysample    |     37750 |      38912 |   76662 |
| pureftpd    |        88 |       3072 |    3160 |
| simple_blog |        44 |       3072 |    3116 |
| test        |       152 |       2048 |    2200 |
| test2       |       476 |       7168 |    7644 |
+-------------+-----------+------------+---------+
11 rows in set (0.03 sec)

ps:因為這個instance裡面有兩個database impexp_text與pandora目前都是沒有任何資料的,
所以quota table有13筆record, v_dbsize view裡面有11筆record,是正常的.
運用quota與v_dbsize作一下比較.

select q.dbname, q.limitbyte, d.totsize, q.limitbyte - d.totsize as diff, d.totsize / q.limitbyte as percent
from quota q, v_dbsize d
where q.dbname = d.dbname;
+-------------+-----------+---------+----------+---------+
| dbname      | limitbyte | totsize | diff     | percent |
+-------------+-----------+---------+----------+---------+
| auth        |  20971520 |    4327 | 20967193 |  0.0002 |
| bookmarks   |  20971520 |    3224 | 20968296 |  0.0002 |
| books       |  20971520 |   16334 | 20955186 |  0.0008 |
| joomla      |  20971520 |  224007 | 20747513 |  0.0107 |
| joomla2     |  20971520 |  227372 | 20744148 |  0.0108 |
| mail        |  20971520 |    7641 | 20963879 |  0.0004 |
| mysample    |  20971520 |   76662 | 20894858 |  0.0037 |
| pureftpd    |  20971520 |    3160 | 20968360 |  0.0002 |
| simple_blog |  20971520 |    3116 | 20968404 |  0.0001 |
| test        |  20971520 |    2200 | 20969320 |  0.0001 |
| test2       |  20971520 |    7644 | 20963876 |  0.0004 |
+-------------+-----------+---------+----------+---------+

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.