MySQL 記憶體交換區引起的一場“血案”

                 MySQL Performance murder -- SWAP

故事情節：

最近公司上了一個新的項目，關於搜尋一塊的項目。其程式會調用大量的SQL，包括各種條件的搜尋，模糊的匹配，聯動的效果，etc。其目的，是提高百度orGoogle的爬蟲的量；其效果，確實有了，從上線了，據SEO相關部門的統計爬蟲抓取的量大概翻了5倍。各個都Happy，唯獨我Happy不起來，因為MySQL的負載極其不穩定。當晚上線時，就發現Slave的負載有點猛，CTO一聲令下，再加一台Slave，點頭做事就OK了，呵呵。另加了一台Slave，發現Slave的負載，確實都迴歸正常了，本以為息事寧人呢，，，誰知，大概隔了3,4個小時吧，新添加的Slave的負載暴增，大概160+，麼情況？原來的slave到是顯得平靜無事。大概過了1個小時，新增加的Slave的負載迴歸正常，大概也就是零點幾的樣子；隔幾個小時，負載又是直接飆升100+。沒得說，身為DBA，就是要Troubleshooting。

Troubleshooting的過程：

1：因為是間接性抽風，所以認為肯定和前台的訪問的頻率，及調用的scope相關；就結合前台的日誌，發現確實是這樣。都是百度的orGoogle的爬蟲瘋狂的訪問新的項目(搜尋)時，新增的Slave的負載就會瘋一樣的增長。可是為何原來的Slave為何沒有太大的波動呢？

2：考慮到新增的server，因為記憶體，CPU等硬體的配置和原來資料庫的server都不一樣(其實是新增的Slave比原來的Slave的記憶體少了一半)，必然配置參數的值也會不同。所以就從MySQl的設定檔查起，如:sort_buffer_size的大小(因為考慮到有許多SQL包含排序)，join_buffer_size(用於串連的緩衝的大小)，max_connections(最大串連數，可是通過show processlist;發現也沒有超過設定的值)，innodb_buffer_pool_size(確認是否為實體記憶體的合適比例)等等。實際自己也犯2了，呵呵，身為DBA，這都是基本的配置，按照正常的邏輯判斷也不該有多大的問題，只要把這些參數設定在合理的範圍內，不會引起負載那麼大的波動。那為何呢？考慮到是新裝的系統，莫非是系統的參數引起的？

3：於是開始尋找關於一些TCP/IP，開啟檔案最大數等等，按照網友的最優調整，只能說希望越大失望越大吧，不起任何作用。這尼瑪到底怎麼回事呢？要是兩台Slave出現相同的情況，也好解決了，可是就這台新增加的Slave有問題，，，沒得選，靜下心來，恍然大悟，才考慮到系統的CPU，記憶體，IO的波動。

4：通過排查，CPU正常，但是問題出來了，發現free -m查看記憶體時，發現SWAP既然使用了300MB，怎麼會使用這麼多，所以一口咬定就死SWAP搗的蛋。通過vmsata，發現si和so的值不斷的變化，所以更加的肯定是發生了記憶體交換。既然找到問題了，那就解決吧。

記憶體交換區：

當作業系統因為沒有足夠的記憶體而將一些虛擬記憶體寫到磁碟就會發生記憶體交換。

記憶體交換對MySQL效能影響是極其糟糕的。它破壞了緩衝在記憶體的目的，並且相對於使用很小的記憶體做緩衝，使用交換區的效能更差。MySQL和儲存引擎有很多演算法來區別對待記憶體中的資料和硬碟上的資料，因為一般都是假設記憶體資料訪問代價更低。

因為記憶體交換對使用者進程不可見，MySQL(或儲存引擎)並不知道資料實際上已經移動到磁碟，還會意味在內才能中。

結果會導致很差的效能。例如。若儲存引擎認為資料依然在記憶體，可能覺得為"短暫"的記憶體伺服器用戶端檔案鎖一個全域互斥變數(例如，InnoDB緩衝池Mutex)是OK的。如果這個操作實際上引起了硬碟I/O，直到I/O操作完成前任何操作都會被掛起。這意味著記憶體交換比直接做硬碟I/O操作還要糟糕。

在GNU/Linux上，可以用vmstat來監控記憶體交換。最好查看si和so列報告的記憶體交換I/O活動，這比看swapd列報告的交換區利用率更重要。我們都喜歡si和so列的值為0，並且一定要保證它們低於每秒10塊。

可以通過正確地配置MySQL緩衝來解決大部分記憶體交換問題，但是有時作業系統的虛擬記憶體系統還是會決定交換MySQL記憶體。這通常發生在作業系統看到MySQL發出了大量I/O，因此嘗試增加檔案快取來儲存更多資料時。如果沒有足夠的記憶體，有些東西就必須交換出去，有些可能就是MySQL本身。

有些人主張完全禁用分頁檔。這樣做是很危險的，因為禁用記憶體交換就相當於給虛擬記憶體設定了一個不可動搖的限制。如果MySQL需要臨時使用很大一塊記憶體，或者有很耗記憶體的進程運行在同一台server上(如夜間的批量任務)，MySQL可能會記憶體溢出，崩潰，或者被作業系統kill掉。

作業系統通常允許對虛擬記憶體和I/O進行一些控制。最基本的方法就是修改/proc/sys/vm/swappiness為一個很小的值，如0或1。這等同於告訴核心除非虛擬記憶體完全滿了，否則不要使用交換區。下面是如何檢查這個值的例子：

$ cat /proc/sys/vm/swappiness60

這個值顯示為60，這是預設的設定(範圍是0~100)。對於伺服器而言這是個很糟糕的預設值。伺服器應該設定為0：

$ echo 0 > /proc/sys/vm/swappiness

另一個選項是修改儲存引擎怎麼讀取和寫入資料。使用innodb_flush_method=O_DIRECT，減輕I/O壓力。DIRECT I/O並不緩衝，因此作業系統並不能把MySQL視為增加檔案快取的原因。這個參數只對InnoDB有效。你也可以使用大頁，不參與換入換出，這對MyISAM和InnoDB都有效。

另一個選擇是使用MySQL的memlock配置項，可以把MySQL鎖定在記憶體。這可以避免交換，但是也可能帶來危險：如果沒有足夠的可鎖定記憶體，MySQL在嘗試分配更多記憶體時就會崩潰。

解決問題：

第一種方法：修改系統對虛擬記憶體的控制

$ echo 0 > /proc/sys/vm/swappiness#要想永久生效，將其配置寫入/etc/sysctl.conf檔案中$ echo "vm.swappiness=0" >> /etc/sysctl.conf#令其立即生效$ sysctl -p

第二種方法：修改innodb_flush_method參數

#注意innodb_flush_method是個全域變數，並且不支援動態修改，所以修改設定檔，重啟MySQL$ vi /etc/my.cnfinnodb_flush_method=O_DIRECT#添加其參數的配置，如果線上正在啟動並執行資料庫，就要先:mysql> stop slave;#然後重啟MySQL$ /etc/init.d/mysqld restart

對於修改memlock配置項，不推薦。

結果：

新增加的server，運行正常，爬蟲照常抓，負載很穩定在4以下。swap的使用也降到了8MB的樣子。呵呵，終於可以小Happy下了。

總結：

1：troubleshooting的經驗不足吧，自我評定。

2：swap對於作業系統，對於MySQL的效能影響很大。

3：要對swap的監控重視起來。

分享蝦米米：

可能有的同學對於如何查看那個進程佔用swap的大小比較感興趣，如下方法：

第一步：$ top第二步：按大寫的O第三步：輸入小寫字母p第四步：斷行符號

顯示的結果圖如：

650) this.width=650;" title="QQ20130822004459.png" src="http://www.bkjia.com/uploads/allimg/131229/125IG5a-0.png" />

希望這場swap的“血案”，讓我們共同成長！！！

本文出自 “Focus on the database” 部落格，請務必保留此出處http://lgdvsehome.blog.51cto.com/3360656/1280247