作為開發人員,平時和MySQL數據庫打交道時,更加關注的是如何寫出滿足業務需求的SQL,而作為MySQL DBA除了關心開發人員寫的業務SQL語句之外,對MySQL實例的整體運行狀態也要十分關注,MySQL實例就像是一部精密的“機器”,如果某個方面出現短板,則將會對這部“機器”整體運行性能造成負面影響,影響MySQL數據庫運行性能的方面有很多。
本文將主要從內存方面、IO與文件句柄限制方面、以及像慢查詢、Binary日誌和連接數方面來展開談談如何對MySQL數據庫進行狀態監控並根據經驗值進行優化。從而使MySQL這部“機器”更高效的運轉。
【“工欲善其事,必先利其器” 】
▪ 如何獲取MySQL狀態值以便於開展後續的診斷調優工作?
查看mysql參數的命令,在mysql客戶端輸入"show variables"
如果想要查看某個具體的值,可以使用如下命令:show variables like "%具體變量%";
例如:查看最大連接數: show variables like '%max_connections%';如圖1:
(圖1)
查看mysql狀態的命令,在mysql客戶端輸入"show status"之後將會看到如下輸出;如圖2:
(圖2)
如果想要查看某個具體的值,可以使用如下命令:show status like "%具體變量%";例如:查看歷史最大連接數: show status like '%max_used_ connections%';如圖3:
(圖3)
【內存方面監控及優化 】
目前MySQL數據庫默認使用的存儲引擎為InnoDB引擎,該引擎的存儲機制同Oracle很相似,因此這就意味著內存的合理設置對於使用InnoDB存儲引擎的MySQL數據庫影響十分巨大。
一、查看當前數據庫實例配置的內存大小
show variables like '%innodb_buffer_pool_ size%';
一般來講,在使用InnoDB引擎的MySQL數據庫需要將innodb_buffer_pool_size設置為實際物理內存的1/2或2/3,原因是通過提高查詢的內存命中率來減少對磁盤IO操作。
二、查詢緩存
作為加速查詢的重要手段,其大小設置的是否合理也關係到MySQL數據庫的性能好壞。啟用查詢緩存,可以極大地減低數據庫服務器的CPU使用率。
查看查詢緩存設置情況:show variables like '%query_cache%';
查看查詢緩存使用情況:show status like 'qcache%';
對於某些不想使用緩存的語句,可以這樣使用:
三、排序操作監控(sort opera-tions)
根據如下公式,評估參數sort_buffer_size設置是否合理 :
Sort_merge_passes/(Sort_scan+Sort_ range),如果其值大於2的話,則說明sort_buffer_size設置偏小,需要將其調大。
四、連接操作監控(join opera-tions)
當監控到這兩個狀態值為0時,說明數據庫中索引被合理的利用,當其中有一項不為0時,則說明join_buffer_size的尺寸偏小,需要將其調大。
本文我們從MYSQL的狀態值和內存方面來介紹MYSQL性能調優,下文我們會從表對象和文件方面使用監控和慢查詢、Binary日誌和連接數方面來介紹影響數據庫性能的因素。