今天將探討 Key Cache.
所謂Key Cache,就是將Index 先讀入記憶體中,當有Query時,若有使用到Index,
會先到Key Cache中去尋找,若沒有則從Disk中再讀進來,並放置於Cache中.
相對的有讀也會有寫,相關的4個狀態值為Key_reads, Key_read_requests,
Key_writes, Key_write_requests.

接著我們建立rrd,獲取,配合產生測試資料並作select,繪圖.

#!/usr/bin/env python
# ------------------------
# Python RRDTool MySQL
# MySQL Key Cache
# create rrd file
# -----------------------

import rrdtool

rrdtool.create(
    'mysql3.rrd', '--step', '60',
    'DS:hit:GAUGE:120:0:U',
    'DS:write: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')

接著是獲取及儲存rrd的程式.

#!/usr/bin/env python
# -----------------------------
# MySQL Key Cache Hit Rate
# and Key Cache Write Rate
# Update RRD
# -----------------------------

import rrdtool
import mysql.connector
import time

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

stmt = 'SHOW GLOBAL STATUS LIKE "Key%"'


def safe_div(a, b):
    if b == 0 or b == 0.0:
        return 0.0
    else:
        return float(a) / float(b)
#
def update_key_rate(rrdfile):
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    
    cursor.execute(stmt)
    for item, val in cursor:
        if item == 'Key_read_requests':
            read_req = float(val)
        if item == 'Key_reads':
            reads = float(val)
        if item == 'Key_write_requests':
            write_req = float(val)
        if item == 'Key_writes':
            writes = float(val)
    #
    hit_rate = (1 - safe_div(reads, read_req)) * 100
    write_rate = safe_div(writes, write_req) * 100
    rrdtool.update(rrdfile, 'N:' + `hit_rate` + ':' + `write_rate`)
    cursor.close()  
    cnx.close()
#
if __name__ == '__main__':
    while 1:
        update_key_rate('mysql3.rrd')
        time.sleep(60)

放到背景執行,會每分鐘更新RRD.接著是建立Table,產生測試資料,以及進行select的程式.

#!/usr/bin/env python

import mysql.connector

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

drop_stmt = 'DROP TABLE bench1101'

create_stmt = (
    'CREATE TABLE bench1101('
    'region CHAR(1) NOT NULL,'
    'idn INT NOT NULL,'
    'rev_idn INT NOT NULL,'
    'grp INT NOT NULL,'
    'PRIMARY KEY(region, idn),'
    'UNIQUE(region, rev_idn),'
    'UNIQUE(region, grp, idn)'
    ') Engine=MyISAM'
)


insert_stmt = (
    'INSERT INTO bench1101 '
    '(region, idn, rev_idn, grp) '
    'VALUES (%s, %s, %s, %s)'
)


COUNT = 1000
opt_regions = 6
opt_groups = COUNT / 100


cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
cursor.execute(drop_stmt)
cursor.execute(create_stmt)
# -- INSERT DATA --
revi = COUNT
for i in range(COUNT):
    revi = revi - 1
    grp = i * 3 % opt_groups
    region = chr(65+i%opt_regions)
    data = (region, i, revi, grp)
    cursor.execute(insert_stmt, data)
    cnx.commit()

# -- SELECT --
select_stmt1 = 'SELECT SUM(idn), SUM(rev_idn) FROM bench1101'
select_stmt2 = 'SELECT SUM(idn+100), SUM(rev_idn-100) FROM bench1101'
cursor.execute(select_stmt1)
result = cursor.fetchall()
cursor.execute(select_stmt2)
result = cursor.fetchall()
#

for i in range(COUNT):
    select_stmt3 = 'SELECT SUM(idn+%s), SUM(rev_idn-%s) FROM bench1101' % (i, i)
    cursor.execute(select_stmt3)
    result = cursor.fetchall()
#

for i in range(COUNT):
    grp = i * 11 % opt_groups
    region = chr(65 + i % (opt_regions+1))
    select_stmt4 = 'SELECT idn FROM bench1101 WHERE region=%s' 
    select_stmt5 = 'SELECT idn FROM bench1101 WHERE region=%s AND idn=%s' 
    select_stmt6 = 'SELECT idn FROM bench1101 WHERE region=%s AND rev_idn=%s' 
    select_stmt7 = 'SELECT idn FROM bench1101 WHERE region=%s AND grp=%s' 
    select_stmt8 = 'SELECT idn FROM bench1101 WHERE grp=%s' %  grp
    cursor.execute(select_stmt4, region)
    result = cursor.fetchall()
    cursor.execute(select_stmt5, (region, i))
    result = cursor.fetchall()
    cursor.execute(select_stmt6, (region, i))
    result = cursor.fetchall()
    cursor.execute(select_stmt7, (region, grp))
    result = cursor.fetchall()
    cursor.execute(select_stmt8)
    result = cursor.fetchall()
#

cursor.close()  
cnx.close()

最後是繪圖的程式.

#!/usr/bin/env python
# ---------------------------
# Python RRDTool 
# MySQL Key Cache
# Create Image from rrd file
# ---------------------------

import rrdtool
import datetime

def mysql_graph3(rrdfile, period):
    timenow = datetime.datetime.now()
    disptime = datetime.datetime.strftime(timenow, '%Y-%m-%d %H-%M-%S')
    title = 'MySQL_Key_Cache_%s' % period
    filename = title + '.png'
    
    # -------------------
    
    hit = 'DEF:hit=%s:hit:AVERAGE' % rrdfile
    write = 'DEF:write=%s:write:AVERAGE' % rrdfile
    # -------------------
    if period == 'yesterday':
        start = 'end-1d'
        end = '00:00'
    if period == 'today':
        start = '00:00'
        end = '23:59'
    if period == '2h':
        start = '-2h'
        end = 'now'
    if period == '4h':
        start = '-4h'
        end = 'now'
        
    rrdtool.graph(
        filename,
        '--start', start,
        '--end', end,
        '--title', title,
        '-a', 'PNG',
        '-W', 'Hitomitanaka for ITHelp',
        '--slope-mode',
        '--vertical-label=Percent',
        '--rigid',
        '--upper-limit', '100',
        '--lower-limit', '0',
        '--width', '500',
        '--height', '150',
        '--x-grid', 'HOUR:1:HOUR:2:HOUR:2:0:%H',
        '--alt-y-grid',
        '--color', 'BACK#000000',
        '--color', 'CANVAS#000000',
        '--color', 'FONT#FFF978',
        '--font=LEGEND:7',
        '--font', 'TITLE:8:',
        '--font', 'UNIT:7:',
        '--font', 'WATERMARK:9',
        # ---------------------------------
        write,
        hit,
        #----------------------------------
        'LINE1:write#00FF00:Write Rate',
        'GPRINT:write:LAST:    Current\\: %.01lf',
        'GPRINT:write:AVERAGE:  Average\\: %.01lf',
        'GPRINT:write:MIN:  Min\\: %.01lf',
        'GPRINT:write:MAX:  Max\\: %.01lf\\n',
        'LINE1:hit#FF0000:Hit Rate',
        'GPRINT:hit:LAST:      Current\\: %.01lf',
        'GPRINT:hit:AVERAGE:  Average\\: %.01lf',
        'GPRINT:hit:MIN:  Min\\: %.01lf',
        'GPRINT:hit:MAX:  Max\\: %.01lf\\n',
        'COMMENT:\t\t\t\t\tUpdate Time %s' % disptime)
#
if __name__ == '__main__':
    mysql_graph3('mysql3.rrd', '4h')
    mysql_graph3('mysql3.rrd', '2h')

然後觀察圖形.

初期因為系統沒有進行操作,所以write rate是0.當使用程式產生資料以後,
就可以看到hit rate, write rate的變化,因為測試資料量不大,
Key Buffer Size足夠,故Hit Rate與Write Rate都保持漂亮的100%.