MySQL 效能調校至關重要，本文介紹如何利用 Python 與相關工具自動化此流程。首先，我們使用 Python 的 psutil 函式庫擷取系統硬體資訊，例如 CPU 核心數、實體記憶體及虛擬記憶體使用量，這些資訊有助於判斷效能瓶頸來源。接著，我們示範如何透過 Python 外掛檢查 MySQL 版本，確保資料函式庫版本保持最新以獲得最佳效能和安全性。此外，文章也涵蓋 Key Buffer 大小的自動化檢查與建議，Key Buffer 作為 MyISAM 索引快取，其大小設定會直接影響查詢效能。最後，我們將探討如何自動化監控慢查詢比率，找出並解決效能問題。
自動化MySQL資料函式庫效能調校
查詢伺服器狀態變數
伺服器狀態變數透過提供內部計數器來深入瞭解伺服器的執行情況。所有變數都是唯讀的，無法被修改。
注意：您可以透過MySQL官方檔案（http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html）找到有關每個MySQL伺服器狀態變數的詳細資訊。
SHOW 命令的語法是 SHOW STATUS。與沒有修飾符的 SHOW VARIABLES 命令類別似，此命令傳回適用於當前會話的狀態，等同於 SHOW LOCAL STATUS 命令。如果要檢索伺服器範圍的狀態，請使用 SHOW GLOBAL STATUS 命令。
這種行為僅適用於MySQL伺服器的5.0及更高版本。在此版本之前的版本中，SHOW STATUS 假設為全域性狀態，如果要檢索特定於會話的計數器，則需要明確執行 SHOW LOCAL STATUS。如果您正在開發可能在各種版本的MySQL伺服器上執行的外掛，這可能會成為問題。不過，有一個簡單的解決方案：在您的 SHOW 陳述式中指定版本選擇器。以下查詢正確使用了適當的命令修飾符，可以在所有版本的MySQL伺服器上使用：
SHOW /*!50000 GLOBAL */ STATUS
您也可以使用 LIKE 和 WHERE 資料集修飾符，如下例所示：
>>> cursor.execute("SHOW GLOBAL STATUS WHERE variable_name LIKE '%innodb%' AND value > 0")
16L
>>> for r in cursor.fetchmany(10): print r
...
('Innodb_buffer_pool_pages_data', '19')
('Innodb_buffer_pool_pages_free', '493')
('Innodb_buffer_pool_pages_total', '512')
('Innodb_buffer_pool_read_ahead_rnd', '1')
('Innodb_buffer_pool_read_requests', '77')
('Innodb_buffer_pool_reads', '12')
('Innodb_data_fsyncs', '3')
('Innodb_data_read', '2494464')
('Innodb_data_reads', '25')
('Innodb_data_writes', '3')
>>>
內容解密：
這段程式碼展示瞭如何查詢MySQL伺服器的狀態變數。首先，執行 SHOW GLOBAL STATUS 命令來檢索伺服器範圍的狀態。然後，使用 WHERE 子句過濾出變數名稱包含 “innodb” 且值大於 0 的結果。這樣可以幫助我們瞭解InnoDB儲存引擎的相關狀態。
擷取系統狀態變數的外掛
列表 13-4 展示了一個用於檢索系統狀態變數的外掛類別。這個外掛類別與查詢系統組態設定的類別相似。
class ServerStatusVariables(Plugin):
def __init__(self, **kwargs):
self.keywords = ['provider']
print(self.__class__.__name__, 'initialising...')
def generate(self, **kwargs):
cursor = kwargs['connection'].cursor()
cursor.execute('SHOW /*!50000 GLOBAL */ STATUS')
result = {}
for k, v in cursor.fetchall():
result[k] = v
cursor.close()
return result
內容解密：
這個外掛類別 ServerStatusVariables 繼承自 Plugin。在 generate 方法中，它執行 SHOW /*!50000 GLOBAL */ STATUS 命令來檢索伺服器的狀態變數，並將結果儲存在字典中傳回。這樣可以讓我們在不修改原始碼的情況下，根據不同的MySQL版本執行正確的命令。
收集主機組態資料
為了使收集到的MySQL組態和狀態資料有實際意義，我們需要將這些資料放在作業系統環境的背景下進行分析。
例如，key_buffer_size 變數決定了分配給MyISAM表索引的記憶體量。這個設定對MySQL伺服器的效能有著重大影響。如果設定得太小，索引就不會被快取在記憶體中，每次查詢都需要進行磁碟讀取操作，這比從記憶體讀取要慢得多。
如果為這個緩衝區分配太多記憶體，就會限制其他操作（如檔案系統快取）可用的記憶體。如果檔案系統快取太小，所有讀寫操作都不會被快取，從而對磁碟I/O產生負面影響。
對於這個緩衝區變數，標準的建議是使用伺服器上可用總記憶體的30%到40%。因此，要做出這個判斷，您實際上需要知道系統上的實體記憶體量！
我們將使用 psutil 函式庫來查詢系統記憶體讀取。這個函式庫提供了查詢執行程式資訊並執行一些基本程式操作的功能。它不包含在基本的Python模組集中，但廣泛可用於大多數Linux發行版。例如，在Fedora系統上，您可以使用以下命令安裝此函式庫：
$ sudo yum install python-psutil
原始碼以及完整的檔案可在專案網站上找到：https://github.com/giampaolo/psutil。
不幸的是，這個函式庫沒有提供有關可用CPU核心數量的資訊。我們需要查詢Linux的 /proc/ 檔案系統來取得有關可用CPU的報告。這很容易做到。我們只需要計算 /proc/cpuinfo 檔案中以關鍵字 “processor” 開頭的行數。
列表 13-5 展示了收集系統記憶體讀取和CPU資訊的外掛程式碼。
import psutil
class HostProperties(Plugin):
def __init__(self, **kwargs):
self.keywords = ['provider']
print(self.__class__.__name__, 'initialising...')
def _get_total_cores(self):
f = open('/proc/cpuinfo', 'r')
c_cpus = 0
for line in f.readlines():
if line.startswith('processor'):
c_cpus += 1
f.close()
return c_cpus
def generate(self, **kwargs):
result = {
'mem_phys_total': psutil.TOTAL_PHYMEM,
'mem_phys_avail': psutil.avail_phymem(),
'mem_phys_used': psutil.used_phymem(),
'mem_virt_total': psutil.total_virtmem(),
'mem_virt_avail': psutil.avail_virtmem(),
'mem_virt_used': psutil.used_virtmem(),
'cpu_cores': self._get_total_cores(),
}
return result
內容解密：
這個外掛類別 HostProperties 用於收集系統的記憶體和CPU資訊。在 generate 方法中，它使用 psutil 函式庫來取得實體記憶體和虛擬記憶體的總量、可用量和使用量。同時，它透過讀取 /proc/cpuinfo 檔案來計算CPU核心的數量。這樣，我們就可以獲得有關系統硬體資源的重要資訊。
編寫消費者外掛
現在，我們準備開始編寫顧問外掛。這些外掛將根據從資訊生產者模組接收到的資訊提出建議。到目前為止，我們已經收集了有關資料函式庫設定和狀態的基本資訊，以及一些有關實體硬體和作業系統的資訊。
儘管資訊集並不詳盡，但它包含了做出一些有根據的結論所需的關鍵細節。在這裡，我們將檢視三個例子，這些例子應該足以讓您上手，以便您可以開始開發自己的顧問外掛。
檢查MySQL版本
您可能需要執行的第一個檢查是MySQL版本號。保持您的伺服器安裝是最新的非常重要。每個新版本都會修復伺服器軟體錯誤，並可能引入效能改進。
自動化MySQL資料函式庫效能調校
檢查MySQL版本
在進行MySQL資料函式庫的效能調校時，首先需要檢查目前的MySQL版本是否為最新的一般可用（GA）版本。這個檢查是透過一個外掛來實作的，該外掛會從MySQL的官方下載頁面提取最新的GA版本號，然後與目前伺服器上執行的MySQL版本進行比較。
檢查邏輯
提取最新GA版本號：使用Beautiful Soup HTML解析函式庫從MySQL官方下載頁面提取最新的GA版本號。頁面結構相對簡單，所需的資料包含在最後一個
<h1>標籤中。比較版本號：將提取到的最新GA版本號與目前MySQL伺服器的版本號進行比較。比較的結果會根據主版本號、次版本號和修補程式版本號的不同而有所不同。
- 如果主版本號不匹配，則視為嚴重問題，標記為CRITICAL。
- 如果主版本號匹配，但次版本號較低，則標記為WARNING，建議升級。
- 如果主、次版本號都匹配，但修補程式版本較低，則標記為NOTE，建議更新修補程式。
- 如果所有版本號都匹配，則視為目前安裝是最新的，標記為OK。
實作程式碼
class MySQLVersionAdvisor(Plugin):
def __init__(self, **kwargs):
self.keywords = ['consumer']
self.advices = []
self.installed_release = None
self.latest_release = None
def _check_latest_ga_release(self):
html = urllib2.urlopen('http://www.mysql.com/downloads/mysql/')
soup = BeautifulSoup(html)
tags = soup.findAll('h1')
version_str = tags[1].string.split()[-1]
(major, minor, release) = [int(i) for i in version_str.split('.')]
return (major, minor, release)
def process(self, **kwargs):
version = kwargs['env_vars']['ServerSystemVariables']['version'].split('-')[0]
(major, minor, release) = [int(i) for i in version.split('.')]
latest_major, latest_minor, latest_rel = self._check_latest_ga_release()
self.installed_release = (major, minor, release)
self.latest_release = (latest_major, latest_minor, latest_rel)
if major < latest_major:
self.advices.append(('CRITICAL', '有新的主要版本可供升級，建議升級'))
elif major == latest_major and minor < latest_minor:
self.advices.append(('WARNING', '有新的次要版本可供升級，考慮升級'))
elif major == latest_major and minor == latest_minor and release < latest_rel:
self.advices.append(('NOTE', '有新的修補程式版本可供升級，考慮更新'))
else:
self.advices.append(('OK', '目前安裝的版本是最新的'))
def report(self, **kwargs):
print self.__class__.__name__, '報告中...'
print "目前執行的伺服器版本是：%d.%d.%d" % self.installed_release
print "最新的GA版本是：%d.%d.%d" % self.latest_release
for rec in self.advices:
print "%10s: %s" % (rec[0], rec[1])
內容解密：
_check_latest_ga_release方法透過解析MySQL官方下載頁面來取得最新的GA版本號。process方法比較目前MySQL版本與最新GA版本的差異，並根據比較結果給出不同的建議。report方法輸出檢查結果和建議。
檢查Key Buffer大小設定
另一個重要的效能調校是檢查key_buffer_size的設定。這個引數影響MySQL的效能，因為它決定了索引快取的大小。
檢查邏輯
- 取得實體記憶體大小：取得目前系統的實體記憶體總量。
- 計算建議的Key Buffer大小：假設最佳的
key_buffer_size設定為實體記憶體的40%。 - 比較目前設定與建議值：如果目前的
key_buffer_size小於建議值，則建議增加；否則，建議減少。
實作程式碼
class KeyBufferSizeAdvisor(Plugin):
def __init__(self, **kwargs):
self.keywords = ['consumer']
self.physical_mem = 0
self.key_buffer = 0
self.ratio = 0.0
self.recommended_buffer = 0
self.recommended_ratio = 0.4
def process(self, **kwargs):
self.key_buffer = int(kwargs['env_vars']['ServerSystemVariables']['key_buffer_size'])
self.physical_mem = int(kwargs['env_vars']['HostProperties']['mem_phys_total'])
self.ratio = float(self.key_buffer) / self.physical_mem
self.recommended_buffer = int(self.physical_mem * self.recommended_ratio)
def report(self, **kwargs):
print self.__class__.__name__, '報告中...'
print "目前key buffer大小是：%d" % self.key_buffer
if self.ratio < self.recommended_ratio:
print "這個設定對於目前的記憶體大小來說太小了：%d" % self.physical_mem
print "建議設定key_buffer_size到%d，如果差異太大" % self.recommended_buffer
else:
print "你可能分配了太多的記憶體給key buffer"
內容解密：
process方法計算目前key_buffer_size與實體記憶體的比例，並計算出建議的key_buffer_size。report方法根據比例與建議值給出調整建議。
檢查慢查詢比率
慢查詢可能會對資料函式庫效能造成重大影響，因此需要檢查慢查詢的比率。
檢查邏輯
- 取得查詢總數和慢查詢數：從MySQL狀態變數中取得查詢總數和慢查詢數。
- 計算慢查詢比率：計算慢查詢佔總查詢的比例。
- 評估比率：如果比率超過一定的閾值（例如0.0001%），則視為問題。
實作程式碼
class SlowQueriesAdvisor(Plugin):
def __init__(self, **kwargs):
self.keywords = ['consumer']
self.log_slow = False
def process(self, **kwargs):
# 省略實作細節
pass
def report(self, **kwargs):
print self.__class__.__name__, '報告中...'
# 省略實作細節
pass
內容解密：
- 需要從MySQL狀態變數中讀取查詢總數和慢查詢數，並計算慢查詢比率。
- 根據比率是否超過閾值給出是否需要最佳化的建議。
