iT邦幫忙

DAY 25
4

MySQL漫談,由使用Python撰寫之MySQL工具程式出發系列 第 25 篇

MySQL漫談,由使用Python撰寫之MySQL工具程式出發(25)

linux mysql python
6712 瀏覽

  • 分享至 

  • xImage
    •  

今天將探討如何獲取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,再繪圖.
展示一個小而具體的測試-觀察的情境.


上一篇
MySQL漫談,由使用Python撰寫之MySQL工具程式出發(24)
下一篇
MySQL漫談,由使用Python撰寫之MySQL工具程式出發(26)
系列文
MySQL漫談,由使用Python撰寫之MySQL工具程式出發 共 30 篇
  1. 26
    MySQL漫談,由使用Python撰寫之MySQL工具程式出發(26)
  2. 27
    MySQL漫談,由使用Python撰寫之MySQL工具程式出發(27)
  3. 28
    MySQL漫談,由使用Python撰寫之MySQL工具程式出發(28)
  4. 29
    MySQL漫談,由使用Python撰寫之MySQL工具程式出發(29)
  5. 30
    MySQL漫談,由使用Python撰寫之MySQL工具程式出發(30)
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 則留言

0
patrickcheng
iT邦新手 4 級 ‧ 2012-10-28 15:08:36

恆逸怎麼沒找你去當講師 ?

登入發表回應
0
ted99tw
iT邦高手 1 級 ‧ 2012-10-28 20:15:39

恆逸的講師應該都是樓主訓練出來的吧！！！

讚讚讚

patrickcheng iT邦新手 4 級 ‧ 2012-10-29 09:04:58 檢舉

泰大愛說笑！

登入發表回應

我要留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22196 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙