MYSQL最佳化三板斧

MYSQL最佳化三板斧

---

現在MySQL執行的大部分環境都是在Linux上的，如何在Linux作業系統上根據MySQL進行最佳化，我們這裡給出一些通用簡單的策略。這些方法都有助於改進MySQL的效能。
閒話少說，進入正題。
 

一、CPU

首先從CPU說起。
你仔細檢查的話，有些伺服器上會有的一個有趣的現象：你cat /proc/cpuinfo時，會發現CPU的頻率竟然跟它標稱的頻率不一樣：
    #cat /proc/cpuinfo
    processor : 5
    model name : Intel(R) Xeon(R) CPU E5-2620 0 @2.00GHz
    ...
    cpu MHz : 1200.000
這個是Intel E5-2620的CPU，他是2.00G * 24的CPU，但是，我們發現第5顆CPU的頻率為1.2G。
這是什麼原因列？
這些其實都源於CPU最新的技術：節能模式。作業系統和CPU硬體配合，系統不繁忙的時候，為了節約電能和降低溫度，它會將CPU降頻。這對環保人士和抵制地球變暖來說是一個福音，但是對MySQL來說，可能是一個災難。
為了保證MySQL能夠充分利用CPU的資源，建議設定CPU為最大效能模式。這個設定可以在BIOS和作業系統中設定，當然，在BIOS中設定該選項更好，更徹底。由於各種BIOS型別的區別，設定為CPU為最大效能模式千差萬別，我們這裡就不具體展示怎麼設定了。
 
二、記憶體

然後我們看看記憶體方面，我們有哪些可以最佳化的。
i)我們先看看numa
非一致儲存訪問結構 (NUMA ： Non-Uniform Memory Access) 也是最新的記憶體管理技術。它和對稱多處理器結構 (SMP ： Symmetric Multi-Processor) 是對應的。簡單的隊別如下：
Smp numa
如圖所示，詳細的NUMA資訊我們這裡不介紹了。但是我們可以直觀的看到：SMP訪問記憶體的都是代價都是一樣的；但是在NUMA架構下，本地記憶體的訪問和非本地記憶體的訪問代價是不一樣的。對應的根據這個特性，作業系統上，我們可以設定程式的記憶體分配方式。目前支援的方式包括：
--interleave=nodes
 --membind=nodes
 --cpunodebind=nodes
 --physcpubind=cpus
 --localalloc
 --preferred=node
簡而言之，就是說，你可以指定記憶體在本地分配，在某幾個CPU節點分配或者輪詢分配。除非是設定為--interleave=nodes輪詢分配方式，即記憶體可以在任意NUMA節點上分配這種方式以外。其他的方式就算其他NUMA節點上還有記憶體剩餘，Linux也不會把剩餘的記憶體分配給這個程式，而是採用SWAP的方式來獲得記憶體。有經驗的系統管理員或者DBA都知道SWAP導致的資料庫效能下降有多麼坑爹。
所以最簡單的方法，還是關閉掉這個特性。
關閉特性的方法，分別有：可以從BIOS，作業系統，啟動程式時臨時關閉這個特性。
a)由於各種BIOS型別的區別，如何關閉NUMA千差萬別，我們這裡就不具體展示怎麼設定了。
b)在作業系統中關閉，可以直接在/etc/grub.conf的kernel行最後新增numa=off，如下所示：
kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-root rd_NO_LUKS LANG=en_US.UTF-8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto rd_LVM_LV=VolGroup/swap rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM  numa=off  

另外可以設定 vm.zone_reclaim_mode=0儘量回收記憶體。
c)啟動MySQL的時候，關閉NUMA特性：
  numactl --interleave=all  mysqld &
當然，最好的方式是在BIOS中關閉。
 
ii)我們再看看vm.swappiness。
vm.swappiness是作業系統控制實體記憶體交換出去的策略。它允許的值是一個百分比的值，最小為0，最大執行100，該值預設為60。vm.swappiness設定為0表示儘量少swap，100表示儘量將inactive的記憶體頁交換出去。
具體的說：當記憶體基本用滿的時候，系統會根據這個引數來判斷是把記憶體中很少用到的inactive 記憶體交換出去，還是釋放資料的cache。cache中快取著從磁碟讀出來的資料，根據程式的區域性性原理，這些資料有可能在接下來又要被讀取；inactive 記憶體顧名思義，就是那些被應用程式對映著，但是“長時間”不用的記憶體。
我們可以利用vmstat看到inactive的記憶體的數量：
#vmstat -an 1
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r b swpd free  inact  active si so bi bo in cs us sy id wa st
 1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0
 0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0
 0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0
 0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0
透過/proc/meminfo 你可以看到更詳細的資訊：
#cat /proc/meminfo | grep -i inact
 Inactive: 326972 kB
 Inactive(anon): 248 kB
 Inactive(file): 326724 kB
這裡我們對不活躍inactive記憶體進一步深入討論。Linux中，記憶體可能處於三種狀態：free，active和inactive。眾所周知，Linux Kernel在內部維護了很多LRU列表用來管理記憶體，比如LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE, LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON, LRU_ACTIVE_ANON用來管理匿名頁，LRU_INACTIVE_FILE , LRU_ACTIVE_FILE用來管理page caches頁快取。系統核心會根據記憶體頁的訪問情況，不定時的將活躍active記憶體被移到inactive列表中，這些inactive的記憶體可以被交換到swap中去。
一般來說，MySQL，特別是InnoDB管理記憶體快取，它佔用的記憶體比較多，不經常訪問的記憶體也會不少，這些記憶體如果被Linux錯誤的交換出去了，將浪費很多CPU和IO資源。 InnoDB自己管理快取，cache的檔案資料來說佔用了記憶體，對InnoDB幾乎沒有任何好處。
所以，我們在MySQL的伺服器上最好設定vm.swappiness=0。
我們可以透過在sysctl.conf中新增一行：
echo "vm.swappiness = 0" >>/etc/sysctl.conf
並使用sysctl -p來使得該引數生效。
 
三、檔案系統

最後，我們看一下檔案系統的最佳化
i)我們建議在檔案系統的mount引數上加上noatime，nobarrier兩個選項。
用noatime mount的話，檔案系統在程式訪問對應的檔案或者資料夾時，不會更新對應的access time。一般來說，Linux會給檔案記錄了三個時間，change time, modify time和access time。
我們可以透過stat來檢視檔案的三個時間：
stat libnids-1.16.tar.gz
 File: `libnids-1.16.tar.gz'
 Size: 72309 Blocks: 152 IO Block: 4096 regular file
 Device: 302h/770d Inode: 4113144 Links: 1
 Access: (0644/-rw-r--r--) Uid: ( 0/ root) Gid: ( 0/ root)
  Access  : 2008-05-27 15:13:03.000000000 +0800
 Modify: 2004-03-10 12:25:09.000000000 +0800
 Change: 2008-05-27 14:18:18.000000000 +0800
其中access time指檔案最後一次被讀取的時間，modify time指的是檔案的文字內容最後發生變化的時間，change time指的是檔案的inode最後發生變化(比如位置、使用者屬性、組屬性等)的時間。一般來說，檔案都是讀多寫少，而且我們也很少關心某一個檔案最近什麼時間被訪問了。
所以，我們建議採用noatime選項，這樣檔案系統不記錄access time，避免浪費資源。
現在的很多檔案系統會在資料提交時強制底層裝置重新整理cache，避免資料丟失，稱之為write barriers。但是，其實我們資料庫伺服器底層儲存裝置要麼採用RAID卡，RAID卡本身的電池可以掉電保護；要麼採用Flash卡，它也有自我保護機制，保證資料不會丟失。所以我們可以安全的使用nobarrier掛載檔案系統。設定方法如下：
對於ext3, ext4和 reiserfs檔案系統可以在mount時指定barrier=0；對於xfs可以指定nobarrier選項。
 
ii)檔案系統上還有一個提高IO的最佳化萬能鑰匙，那就是deadline。
在Flash技術之前，我們都是使用機械磁碟儲存資料的，機械磁碟的尋道時間是影響它速度的最重要因素，直接導致它的每秒可做的IO(IOPS)非常有限，為了儘量排序和合並多個請求，以達到一次尋道能夠滿足多次IO請求的目的，Linux檔案系統設計了多種IO排程策略，已適用各種場景和儲存裝置。
Linux的IO排程策略包括：Deadline scheduler，Anticipatory scheduler，Completely Fair Queuing(CFQ)，NOOP。每種排程策略的詳細排程方式我們這裡不詳細描述，這裡我們主要介紹CFQ和Deadline，CFQ是Linux核心2.6.18之後的預設排程策略，它聲稱對每一個 IO 請求都是公平的，這種排程策略對大部分應用都是適用的。但是如果資料庫有兩個請求，一個請求3次IO，一個請求10000次IO，由於絕對公平，3次IO的這個請求都需要跟其他10000個IO請求競爭，可能要等待上千個IO完成才能返回，導致它的響應時間非常慢。並且如果在處理的過程中，又有很多IO請求陸續傳送過來，部分IO請求甚至可能一直無法得到排程被“餓死”。而deadline兼顧到一個請求不會在佇列中等待太久導致餓死，對資料庫這種應用來說更加適用。
實時設定，我們可以透過
echo deadline >/sys/block/sda/queue/scheduler
來將sda的排程策略設定為deadline。
我們也可以直接在/etc/grub.conf的kernel行最後新增elevator=deadline來永久生效。
 
 
總結

CPU方面
    關閉電源保護模式
記憶體：
    vm.swappiness = 0
    關閉numa
檔案系統：
    用noatime，nobarrier掛載系統
    IO排程策略修改為deadline。