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 |
+-------------+-----------+---------+----------+---------+
沒有留言:
張貼留言