今天將介紹使用Python讀取CPU效能資訊,將資訊存放在之前介紹的Archive
儲存引擎的Table,以節省空間,再配合昨天介紹的CSV儲存引擎,轉出來,並搭配
試算表來繪圖.
CPU效能資訊一向是監控機器運作的重要參考資料,系統也提供了許多指令讓我們
可以讀取;將其儲存起來,以利後續查詢與分析.

-- 首先建立 Table
CREATE TABLE cpudata(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
time TIMESTAMP NOT NULL DEFAULT NOW(),
usr DECIMAL(5,2) NOT NULL,
nice DECIMAL(5,2) NOT NULL,
sys DECIMAL(5,2) NOT NULL,
iowait DECIMAL(5,2) NOT NULL,
irq DECIMAL(5,2) NOT NULL,
soft_irq DECIMAL NOT NULL
) ENGINE=ARCHIVE;

-- 先寫讀取CPU效能資訊的函式, 存檔為 rep_cpu.py
#!/usr/bin/env python
# ----------------------------
# Get CPU Data with mpstat
# ----------------------------

import subprocess

def rep_cpu():
    output = subprocess.check_output(
        'mpstat', shell=True,)
    eachlines = output.splitlines()
    cpuall = eachlines[3]
    cpuall_data = cpuall.split('   ')
    usr  = float(cpuall_data[1])
    nice = float(cpuall_data[2])
    sys  = float(cpuall_data[3])
    iowait = float(cpuall_data[4])
    irq = float(cpuall_data[5])
    soft_irq = float(cpuall_data[6])
    cpu_item = ('usr', 'nice', 'sys', 'iowait', 'irq', 'soft_irq')
    cpu_val = (usr, nice, sys, iowait, irq, soft_irq)
    cpu_dict = dict(zip(cpu_item, cpu_val))
    return cpu_dict
    

----------------------------------
-- 使用 rep_cpu() 函式,獲取CPU 效能資訊,
-- 並將其寫入 Table: cpudata
#!/usr/bin/env python
# -----------------------------------
# Insert CPU data into Table:cpudata
# -----------------------------------
import mysql.connector
import time
import rep_cpu

config = {
    'user' : 'myperf',
    'password' : 'myperf',
    'host' : '127.0.0.1',
    'database' : 'myperf',
}

stmt = (
    'INSERT INTO cpudata'
    '(usr, nice, sys, iowait, irq, soft_irq) '
    'VALUES (%s, %s, %s, %s, %s, %s)'
)


try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    print 'Something went wrong-> %s' % format(err)
    print err.errno
else:
    cursor = cnx.cursor()
    while 1:
        cpu_dict = rep_cpu.rep_cpu()
        usr = cpu_dict['usr']
        nice = cpu_dict['nice']
        sys = cpu_dict['sys']
        iowait = cpu_dict['iowait']
        irq = cpu_dict['irq']
        soft_irq = cpu_dict['soft_irq']
        cpu_data = (usr, nice, sys, iowait, irq, soft_irq)
        cursor.execute(stmt, cpu_data)
        time.sleep(60)
---------------------------------
-- 存檔為 refresh_cpu.py 
-- 我們將其置於背景執行
$ ./refresh_cpu.py &
[1] 9796

-- 建立使用 CSV引擎的Table
CREATE TABLE csvcpu(
id INT UNSIGNED NOT NULL,
time TIMESTAMP NOT NULL DEFAULT NOW(),
usr DECIMAL(5,2) NOT NULL,
nice DECIMAL(5,2) NOT NULL,
sys DECIMAL(5,2) NOT NULL,
iowait DECIMAL(5,2) NOT NULL,
irq DECIMAL(5,2) NOT NULL,
soft_irq DECIMAL NOT NULL
) ENGINE=CSV;

-- 將cpudata的資料轉至csvcpu
INSERT INTO csvcpu
SELECT * 
FROM cpudata;

-- 到檔案系統中取出csvcpu
-- 至於放置於檔案系統中何處? 還記得前面介紹的 datadir嗎?
-- 到那個路徑中 裡面會有各資料庫的子目錄, 資料檔就會放在該處
-- 接著就是使用試算表繪製圖表