Linux系統(tǒng)中MySQL優(yōu)化技巧有哪些

今天小編給大家分享一下Linux系統(tǒng)中MySQL優(yōu)化技巧有哪些的相關(guān)知識點，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

1.禁止操作系統(tǒng)更新文件的atime屬性

atime是Linux/UNIX系統(tǒng)下的一個文件屬性，每當(dāng)讀取文件時，操作系統(tǒng)都會將讀操作時間回寫到磁盤上。對于讀寫頻繁的數(shù)據(jù)庫文件來說，記錄文件的訪問時間一般沒有任何用處，卻會增加磁盤系統(tǒng)的負(fù)擔(dān)，影響I/O性能！因此，可以通過設(shè)置文件系統(tǒng)的mount熟悉，阻止操作系統(tǒng)寫atime信息，減輕磁盤I/O負(fù)擔(dān)。方法如下：

（1）修改文件系統(tǒng)配置文件/etc/fstab，指定noatime選項：

 UUID=33958004-e8a7-4135-844f-707a5537e86a /data                   ext4  noatime     0    1

（2）重新mount文件系統(tǒng)使其修改生效：

 [root@MySQL-01 ~]# mount -o remount /data

2.調(diào)整I/O調(diào)度算法

詳細(xì)說明請參考前面文章提到的I/O調(diào)度算法的選擇

（1）查看當(dāng)前系統(tǒng)支持的I/O調(diào)度算法：

 [root@MySQL-01 ~]# dmesg | grep -i scheduler
 io scheduler noop registered
 io scheduler anticipatory registered
 io scheduler deadline registered
 io scheduler cfq registered (default)
 [root@MySQL-01 ~]#

（2）查看當(dāng)前設(shè)備（/dev/sda）使用的I/O調(diào)度算法：

 [root@MySQL-01 ~]# cat /sys/block/sda/queue/scheduler    
 noop anticipatory deadline [cfq]
 [root@MySQL-01 ~]#

（3）修改當(dāng)前設(shè)備使用的I/O調(diào)度算法，普通磁盤可以選擇Deadline，SSD我們可以選擇使用NOOP或者Deadline

 [root@MySQL-01 ~]# echo "deadline" >> /sys/block/sda/queue/scheduler
 [root@MySQL-01 ~]# cat /sys/block/sda/queue/scheduler                
 noop anticipatory [deadline] cfq
 [root@MySQL-01 ~]#

永久修改I/O調(diào)度算法，可以通過修改內(nèi)核引導(dǎo)參數(shù)，增加elevator=調(diào)度算法名

 [root@MySQL-01 ~]# vim /boot/grub/menu.lst

更改后的內(nèi)容：

 [root@MySQL-01 ~]# grep "deadline" /boot/grub/menu.lst      
         kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=UUID=c0618639-a967-4601-bca7-cc3b99c5c332 elevator=deadline rd_NO_LUKS rd_NO_LVM.UTF-8 rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM
 [root@MySQL-01 ~]#

3.NUMA架構(gòu)優(yōu)化

從系統(tǒng)架構(gòu)來看，目前的商用服務(wù)器大體可以分為三類：

（1）對稱多處理器架構(gòu)（Symmetric Multi-Processor，SMP）

（2）非一致存儲訪問架構(gòu)（Non-Uniform Memory Access，NUMA）

（3）海量并行處理架構(gòu)(Massive Parallel Processing，MPP）

一般服務(wù)器是SMP或者NUMA架構(gòu)的較多。我這里只詳細(xì)說明NUMA架構(gòu)，至于其他的童鞋們可以自行查閱資料_

NUMA把一臺計算機分成多個節(jié)點（Node），每個節(jié)點內(nèi)部擁有多個CPU，節(jié)點內(nèi)部使用共有的內(nèi)存控制器，節(jié)點之間是通過互聯(lián)模塊進行連接和信息交互，因此節(jié)點的所有內(nèi)存對于本節(jié)點所有的CPU是等同的，而對于其他節(jié)點中的所有CPU都是不同的。因此每個CPU可以訪問整個系統(tǒng)內(nèi)存，但是訪問本地節(jié)點的內(nèi)存速度最快（不需要經(jīng)過互聯(lián)模塊），訪問非本地節(jié)點的內(nèi)存速度較慢（需要經(jīng)過互聯(lián)模塊），即CPU訪問內(nèi)存的速度與節(jié)點的距離有關(guān)，距離稱為Node Distance。如下圖：

顯示當(dāng)前NUMA的節(jié)點情況:

 [root@localhost ~]# numactl --hardware
 available: 2 nodes (0-1)
 node 0 cpus: 0 2 4 6
 node 0 size: 16338 MB
 node 0 free: 136 MB
 node 1 cpus: 1 3 5 7
 node 1 size: 16384 MB
 node 1 free: 66 MB
 node distances:
 node   0   1
   0:  10  20
   1:  20  10
 [root@localhost ~]# free -m
              total       used       free     shared    buffers     cached
 Mem:         32060      31856        204          0        362      13016
 -/+ buffers/cache:      18477      13582
 Swap:         7999          6       7993
 [root@localhost ~]#

當(dāng)前服務(wù)器上有兩個節(jié)點Node 0和Node 1，Node 0的本地內(nèi)存約為16GB，Node 1的本地內(nèi)存約為16GB，可以看出系統(tǒng)一共有32GB內(nèi)存

節(jié)點之間的距離（Node Distance）是指節(jié)點1訪問節(jié)點0上的內(nèi)存需要付出的代價的一種表現(xiàn)形式。在上述例子中，Linux節(jié)點本地內(nèi)存聲明距離為10，非本地內(nèi)存聲明距離20.

NUMA的內(nèi)存分配策略分為以下4種：

（1）缺省default：總是在本地節(jié)點分配（分配在當(dāng)前進程運行的節(jié)點上）

（2）綁定bind：強制分配到指定節(jié)點上

（3）交叉interleave：在所有節(jié)點或者指定節(jié)點上交叉分配內(nèi)存

（4）優(yōu)先preferred：在指定節(jié)點上分配，失敗則在其他節(jié)點分配

顯示當(dāng)前系統(tǒng)NUMA策略：

 [root@localhost ~]# numactl --show
 policy: default
 preferred node: current
 physcpubind: 0 1 2 3 4 5 6 7
 cpubind: 0 1
 nodebind: 0 1
 membind: 0 1
 [root@localhost ~]#

因為NUMA默認(rèn)的內(nèi)存分配策略是優(yōu)先在進程所在CPU的本地內(nèi)存中分配，會導(dǎo)致CPU節(jié)點之間內(nèi)存分配不均衡，當(dāng)某個CPU節(jié)點內(nèi)存不足時，會導(dǎo)致SWAP發(fā)生，而不是從遠(yuǎn)程節(jié)點分配內(nèi)存，這就是Swap Insanity現(xiàn)象。

MySQL是單進程多線程架構(gòu)的數(shù)據(jù)庫，當(dāng)NUMA采用默認(rèn)的內(nèi)存分配策略時，MySQL進程會被并且僅僅會被分配到NUMA的一個節(jié)點上去。假設(shè)MySQL進程被分配到Node 1運行，這個節(jié)點的本地內(nèi)存是8GB，而MySQL配置了14GB內(nèi)存，MySQL分配的14GB內(nèi)存中，超過節(jié)點本地內(nèi)存部分（14GB-8GB=6GB）Linux系統(tǒng)寧愿使用Swap也不會使用其他節(jié)點的物理內(nèi)存。在這種情況下，能觀察到系統(tǒng)雖然總共可用的物理內(nèi)存還很多，但是MySQL進程已經(jīng)開始使用Swap了。

MySQL對NUMA的特性支持不好，如果單機只運行一個MySQL實例，可以選擇關(guān)閉NUMA，關(guān)閉的方式有兩種：

（1）硬件層，在BIOS中設(shè)置關(guān)閉

（2）OS內(nèi)核層，啟動時設(shè)置numa=off

修改/etc/grub.conf，添加numa=off

 [root@MySQL-01 ~]# vim /etc/grub.conf
 [root@MySQL-01 ~]# grep 'numa' /etc/grub.conf
         kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=UUID=c0618639-a967-4601-bca7-cc3b99c5c332 elevator=deadline rd_NO_LUKS rd_NO_LVM.UTF-8 rd_NO_MD quiet SYSFONT=latarcyrheb-sun16 rhgb crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM numa=off
 [root@MySQL-01 ~]#

或者通過numactl命令將NUMA的內(nèi)存分配策略修改為interleave

 /usr/bin/numactl --interleave=all /usr/local/mysql-5.1.66/bin/mysqld_safe --defaults-file=/usr/local/mysql-5.1.66/my.cnf

這樣就指定了MySQL啟動時內(nèi)存的分配策略是interleave

如果單機運行多個MySQL實例，可以將不同MySQL實例綁定到不同的CPU節(jié)點上，同時配置合適的MySQL內(nèi)存參數(shù)，并且采用綁定的內(nèi)存分配測試，強制在本地節(jié)點分配內(nèi)存。

4.vm.swappiness調(diào)整

swappiness是操作系統(tǒng)控制物理內(nèi)存交換出去的策略。它允許的值是一個百分比的值，最小為0，最大運行100，該值默認(rèn)為60。vm.swappiness設(shè)置為0表示盡量少swap，100表示盡量將inactive的內(nèi)存頁交換出去。 具體的說：當(dāng)內(nèi)存基本用滿的時候，系統(tǒng)會根據(jù)這個參數(shù)來判斷是把內(nèi)存中很少用到的inactive 內(nèi)存交換出去，還是釋放數(shù)據(jù)的cache。cache中緩存著從磁盤讀出來的數(shù)據(jù)，根據(jù)程序的局部性原理，這些數(shù)據(jù)有可能在接下來又要被讀?。籭nactive 內(nèi)存顧名思義，就是那些被應(yīng)用程序映射著，但是“長時間”不用的內(nèi)存。

我們可以利用vmstat看到inactive的內(nèi)存的數(shù)量：

 [root@MySQL-01 ~]# vmstat -an 1 5
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
  r  b   swpd   free  inact active   si   so    bi    bo   in   cs us sy id wa st
  0  0   4892 1194972 234208 492404    0    0     5    38   32   83  0  1 99  0  0
  0  0   4892 1194964 234208 492420    0    0     0     0   24   29  0  0 100  0  0
  0  0   4892 1194964 234208 492420    0    0     0     0   12   21  0  0 100  0  0
  0  0   4892 1194964 234208 492420    0    0     0     0   20   24  0  0 100  0  0
  0  0   4892 1194964 234208 492420    0    0     0     0   13   21  0  0 100  0  0
 [root@MySQL-01 ~]#

通過/proc/meminfo 你可以看到更詳細(xì)的信息：

 [root@MySQL-01 ~]# cat /proc/meminfo | grep -i inact
 Inactive:         234188 kB
 Inactive(anon):     3228 kB
 Inactive(file):   230960 kB
 [root@MySQL-01 ~]#

Linux中，內(nèi)存可能處于三種狀態(tài)：free，active和inactive。眾所周知，Linux Kernel在內(nèi)部維護了很多LRU列表用來管理內(nèi)存，比如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頁緩存。系統(tǒng)內(nèi)核會根據(jù)內(nèi)存頁的訪問情況，不定時的將活躍active內(nèi)存被移到inactive列表中，這些inactive的內(nèi)存可以被交換到swap中去。 一般來說，MySQL，特別是InnoDB管理內(nèi)存緩存，它占用的內(nèi)存比較多，不經(jīng)常訪問的內(nèi)存也會不少，這些內(nèi)存如果被Linux錯誤的交換出去了，將浪費很多CPU和IO資源。InnoDB自己管理緩存，cache的文件數(shù)據(jù)來說占用了內(nèi)存，對InnoDB幾乎沒有任何好處。 所以，我們在MySQL的服務(wù)器上最好設(shè)置vm.swappiness=0。

我們可以通過在sysctl.conf中添加一行（如果你的內(nèi)核版本是2.6.32-303.el6及以后，請設(shè)置vm.swappiness = 1）：

 [root@MySQL-01 ~]# echo "vm.swappiness = 0" >>/etc/sysctl.conf
 [root@MySQL-01 ~]# sysctl -p

另外一種做法是innodb啟用大內(nèi)存頁，也和上述方法有相同的效果。具體請參考前面文章提到的InnoDB啟用大內(nèi)存頁。

5.CPU優(yōu)化

檢查CPU是否開啟了節(jié)能選項

 [root@localhost ~]# grep -E '^model name|^cpu MHz' /proc/cpuinfo
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 model name      : Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz
 cpu MHz         : 2266.602
 [root@localhost ~]#

如果發(fā)現(xiàn)CPU的頻率跟它標(biāo)稱的頻率不一樣，那么就是開啟了節(jié)能模式。

可以使用下面命令關(guān)閉：

 for CPUFREQ in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do [ -f $CPUFREQ ] || continue; echo -n performance > $CPUFREQ; done

節(jié)能模式：操作系統(tǒng)和CPU硬件配合，系統(tǒng)不繁忙的時候，為了節(jié)約電能和降低溫度，它會將CPU降頻。對MySQL來說，可能是一個災(zāi)難。 為了保證MySQL能夠充分利用CPU的資源，建議設(shè)置CPU為最大性能模式。這個設(shè)置可以在BIOS和操作系統(tǒng)中設(shè)置，當(dāng)然，在BIOS中設(shè)置該選項更好。

以上就是“Linux系統(tǒng)中MySQL優(yōu)化技巧有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注億速云行業(yè)資訊頻道。