今天將探討如何獲取MySQL裡某一Database的空間使用情況.
在前面討論儲存引擎時,就有使用SQL指令來計算某Database的空間
使用情況,但是MySQL是有權限管理的,當我們使用外部程式來獲取相關
資訊時,能夠用最少的權限來登入是比較好的,不要都用root來登入.
這時候我們可以寫一個MySQL的函式,權限是以建立者的權限來執行,
使用root來建立,讓他回報指定Database的空間使用量,這樣就可以
用權限較小的使用者來呼叫,獲得資訊.
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `dbsize`(`in_dbname` VARCHAR(64))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
RETURN(select (sum(data_length)+sum(index_length))/1048576
from information_schema.tables
where table_schema = in_dbname
and table_type = 'BASE TABLE');
END
接著就是建立rrd的程式
#!/usr/bin/env python
# ------------------------
# Python RRDTool MySQL
# Database Space Usage
# create rrd file
# -----------------------
import rrdtool
rrdtool.create(
'dbspace.rrd', '--step', '60',
'DS:myperf:GAUGE:120:0:U',
'DS:bunko:GAUGE:120:0:U',
'RRA:AVERAGE:0.5:1:2880',
'RRA:AVERAGE:0.5:30:672',
'RRA:AVERAGE:0.5:60:744',
'RRA:AVERAGE:0.5:720:732',
'RRA:MAX:0.5:1:2880',
'RRA:MAX:0.5:30:672',
'RRA:MAX:0.5:60:744',
'RRA:MAX:0.5:720:732',
'RRA:MIN:0.5:1:2880',
'RRA:MIN:0.5:30:672',
'RRA:MIN:0.5:60:744',
'RRA:MIN:0.5:720:732',
'RRA:LAST:0.5:1:2880',
'RRA:LAST:0.5:30:672',
'RRA:LAST:0.5:60:744',
'RRA:LAST:0.5:720:732')
使用UDF dbsize()來獲取兩個database: myperf 與 bunko的 size,
存到rrd的程式.
#!/usr/bin/env python
# ------------------------------
# Python RRDTool MySQL
# Database Space Usage
# Update RRD File
# Using MySQL UDF dbsize(dbname)
# ------------------------------
import mysql.connector
import rrdtool
import time
config = {
'user' : 'myperf',
'password' : 'myperf',
'host' : '127.0.0.1',
'database' : 'myperf',
}
def update_dbspace_rrd(rrdfile):
query_stmt = (
'SELECT dbsize(%s), dbsize(%s)'
)
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
cursor.execute(query_stmt, ('myperf', 'bunko'))
dbsizet = cursor.fetchone()
myperf = float(dbsizet[0])
bunko = float(dbsizet[1])
rrdtool.update(rrdfile, 'N:' + `myperf` + ':' + `bunko`)
cursor.close()
cnx.close()
#
if __name__ == '__main__':
while 1:
update_dbspace_rrd('dbspace.rrd')
time.sleep(60)
放到背景執行,每分鐘會更新rrd檔.
然後是繪圖程式.
#!/usr/bin/env python
# ---------------------------
# Python RRDTool MySQL
# Database Space Usage
# Create Image from rrd file
# ---------------------------
import rrdtool
import datetime
def dbspace_graph(rrdfile, dbname, period):
timenow = datetime.datetime.now()
disptime = datetime.datetime.strftime(timenow, '%Y-%m-%d %H-%M-%S')
title = '%s_Space_Usage_%s' % (dbname, period)
filename = title + '.png'
# --------------------
used = 'DEF:used=%s:%s:AVERAGE' % (rrdfile, dbname)
# -------------------
if period == 'yesterday':
start = 'end-1d'
end = '00:00'
xgrid = 'HOUR:1:HOUR:2:HOUR:2:0:%H'
if period == 'today':
start = '00:00'
end = '23:59'
xgrid = 'HOUR:1:HOUR:2:HOUR:2:0:%H'
if period == '2h':
start = '-2h'
end = 'now'
xgrid = 'MINUTE:10:HOUR:1:HOUR:1:0:%H'
if period == '4h':
start = '-4h'
end = 'now'
xgrid = 'MINUTE:10:HOUR:1:HOUR:1:0:%H'
rrdtool.graph(
filename,
'--start', start,
'--end', end,
'--title', title,
'-a', 'PNG',
'-W', 'Hitomitanaka for ITHelp',
'--slope-mode',
'--vertical-label=Mega Bytes',
'--rigid',
'--lower-limit', '0',
'--width', '500',
'--height', '150',
'--x-grid', xgrid,
'--alt-y-grid',
'--color', 'BACK#000000',
'--color', 'CANVAS#000000',
'--color', 'FONT#FFF978',
'--font=LEGEND:7',
'--font', 'TITLE:8:',
'--font', 'UNIT:7:',
'--font', 'WATERMARK:9',
# ---------------------------------
used,
'AREA:used#4444EE:Used',
'GPRINT:used:LAST:Current\\: %.01lf',
'GPRINT:used:AVERAGE:Average\\: %.01lf',
'GPRINT:used:MIN:Min\\: %.01lf',
'GPRINT:used:MAX:Max\\: %.01lf\\n',
'COMMENT:\t\t\t\tUpdate Time %s' % disptime)
#
if __name__ == '__main__':
dbspace_graph('dbspace.rrd', 'myperf', '2h')
dbspace_graph('dbspace.rrd', 'bunko', '2h')
接著我們將利用 MySQL的Event 來對增加myperf的空間使用量.
首先建立Table
CREATE TABLE bigfatone(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(65535)
)ENGINE=MyISAM;
建立產生資料用的Table,可以參考前面第五天關於Memory儲存引擎以及第七天
的使用情形.
call prc_filler(1024);
建立 Stroed Procedure
CREATE DEFINER=`myperf`@`localhost` PROCEDURE `FeedFatBoy`()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO bigfatone(data)
SELECT LPAD('', 65520, '*')
FROM filler;
END
建立Event
CREATE EVENT feed10min
ON SCHEDULE
EVERY 1 MINUTE
STARTS '2012-10-27 18:00:00' ENDS '2012-10-27 18:10:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'This event for ITHelp Ironmen'
DO CALL FeedFatBoy();
觀察Event的情形
myperf@[myperf]>show events\G
*************************** 1. row ***************************
Db: myperf
Name: feed10min
Definer: myperf@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2012-10-27 18:00:00
Ends: 2012-10-27 18:10:00
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
在18:11時檢查Event狀況
myperf@[myperf]>select now();
+---------------------+
| now() |
+---------------------+
| 2012-10-27 18:11:32 |
+---------------------+
1 row in set (0.00 sec)
myperf@[myperf]>show events\G
*************************** 1. row ***************************
Db: myperf
Name: feed10min
Definer: myperf@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2012-10-27 18:00:00
Ends: 2012-10-27 18:10:00
Status: DISABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
狀態變成 DISABLED, 停止了,這時候看一下圖形.
從原本的52.3M 一路增加到 756.4M, 每次約增加 70.41M,
因為是用MyISAM,資料頗佔空間.
接著我們將bigfatone 使用 TRUNCATE TABLE 指令清空.
myperf@[myperf]>select now();
+---------------------+
| now() |
+---------------------+
| 2012-10-27 18:19:33 |
+---------------------+
1 row in set (0.00 sec)
myperf@[myperf]>TRUNCATE TABLE bigfatone;
Query OK, 0 rows affected (0.10 sec)
再觀察圖形
可以看見圖形急劇下降,所以會出現鋸齒狀的誤差.
在此範例中,我們應用了MySQL 的Stored Procedure,配合Event
來,結合Memory 引擎,產生大量測試資料;再透過User Define Function
由Python呼叫獲得Database空間使用情況,存放到RRD,再繪圖.
展示一個小而具體的測試-觀察的情境.
iThome鐵人賽
看影片追技術