MySQL 最佳化之 Linux系統層面調優

標籤：

http://www.cnblogs.com/digdeep/p/4885948.html

MySQL 一般運行於Linux系統中。對於MySQL的調優一般分為Linux作業系統層面的調優和MySQL層面的調優(當然還有架構層面、業務層面、應用程式層面的調優)。作業系統主要是管理和分配硬體資源，所以其實系統層面的調優包括了硬體的調優，也就是調整硬體參數。Linux系統層面的調優一般分為 CPU的調優、記憶體的調優、磁碟的調優、網路的調優、Linux後台service調優等等。

1. CPU 調優

1.1 CPU 的節能模式

在server環境的CPU一定要關閉節能模式，節能模式不適應於伺服器環境。因為他會自動給CPU降頻進入睡眠模式！一般膝上型電腦，手機為了續航時間，才需要。關閉CPU的節能模式有兩種方法：

1）在BIOS中進行設定，徹底關閉；

2）關閉Linux中的服務 cpuspeed 和 irqbalance；

[[email protected] ~]# chkconfig --level 35 cpuspeed off[[email protected] ~]# chkconfig | grep cpuspeedcpuspeed        0:off   1:on    2:off   3:off   4:off   5:off   6:off[[email protected] ~]# chkconfig --level 35 irqbalance off[[email protected] ~]# chkconfig | grep irqbalanceirqbalance      0:off   1:off   2:off   3:off   4:off   5:off   6:off

cpuspeed 就是負責CPU節能的後台服務；而irqbalance在cpuspeed將某個或某幾個CPU調節進入睡眠模式時，它負責將中斷髮送到沒有休眠的CPU。關閉irqbalance會將所有中斷均衡的發送到所有cpu.

1.2 關閉CPU的numa

numa的會導致mysqld產生swap，嚴重影響效能。因為numa架構的CPU和記憶體是bind的，如果CPU自己node中的記憶體不夠，就會導致swap的產生，即使此時其它node中有大量的空閑記憶體，它也不會去使用。這就是numa的一個缺陷。有多種方法關閉CPU的numa:

1）在BISO中進行配置；

2）numactl --interleave=all

[[email protected] ~]# numactl --interleave=all

interleave=all 其實是將NUMA架構的各個node中的記憶體，又重新虛擬成了一個共用的記憶體來進行分配，但是和SMP不同的是，因為每兩個node之間有 inter-connect ，所以又避免了SMP架構匯流排爭用的缺陷。

查看CPU是否被休眠導致降頻：

[[email protected] ~]# cat /proc/cpuinfoprocessor       : 0vendor_id       : GenuineIntelcpu family      : 6model           : 23model name      : Intel(R) Xeon(R) CPU           E5405  @ 2.00GHzstepping        : 10cpu MHz         : 1995.288

 看上面的 cpu MHz : 1995.288 和 實際是否一致。

具體參見：http://www.cnblogs.com/digdeep/p/4847484.html

2. 記憶體的調優

記憶體主要是要防止發生 swap。因為發生swap的話，從記憶體訪問直接下降為硬碟訪問，隨機訪問的速度下降10的6次方倍，也就是10萬倍。順序訪問下降10倍左右。

2.1 防止發生swap：

1）關閉CPU的numa，防止numa導致的swap；

2）設定 vm.swappiness=1; 在 /etc/sysctl.conf 中添加：vm.swappiness=1，然後 sysctl -p; 也可以 sysctl vm.swappiness=1臨時修改，然後sysctl -p

    注意：在RHEL/CentOS 6.4及更新的核心中 vm.swappiness = 0 的預設行為被修改了，如果繼續設定vm.swappiness = 0，

            有可能導致系統記憶體溢出，從而導致MySQL被意外kill掉。所以這裡我們設定為 1 而不是傳統的 0.

3）設定 /proc/$(pidof -s mysqld)/oom_adj為較小的值(-15,-16或者-17)來盡量避免MySQL由於記憶體不足而被關閉

[[email protected] ~]# echo -17 > /proc/$(pidof mysqld)/oom_adj[[email protected] ~]# cat /proc/$(pidof mysqld)/oom_adj-17

這個oom_adj中的變數的範圍為15到-16之間。越大越容易在記憶體不足時被kill。-17 則表示該進程不會被kill掉，當記憶體不足時，會kill其它進程。

4）使用 hugepage 可以避免swap out; 但是 huagepage也是有代價的(導致page爭用加劇)。

2.2 在BIOS 設定記憶體為最大效能模式；

2.3 調節 disk cache 重新整理到磁碟的行為

因為Linux預設會大量的進行檔案cache，也就是將大量記憶體用於disk cache。這樣的話，會影響mysql使用記憶體。所以我們可以調節disk cache在髒塊達到多大的百分比時，進行重新整理。vm.dirty_background_ratio=10; 預設值為10，表示disk cache中的髒頁數量達到10%時，pdflush核心線程會被調用，非同步重新整理disk cache; vm.dirty_ratio=20; 表示disk cache中的髒頁數量達到20%時，會進行同步的disk cache重新整理，從而會阻塞系統中應用進程的IO操作！我們可以調低vm.dirty_background_ratio來降低disk cache對mysql使用記憶體的影響，但是可能會增加磁碟IO，因為檔案cache減少了，增加其他進程的page fault；(vm.dirty_background_ratio / vm.dity_ratio 帶有backround表示非同步重新整理，沒有帶的是同步重新整理。)

具體參見：http://www.cnblogs.com/digdeep/p/4850460.html

3. 磁碟IO的調優

磁碟IO的調優涉及到檔案系統的調優和磁碟的調優。

3.1 檔案系統的調優

1）檔案系統的選擇：在rhel6.4之前ext4效能比xfs好，因為xfs有lock爭用的bug。但是6.4開始，xfs的bug被fix了。測試表明xfs效能比ext4好。

2）檔案掛載選項：檔案掛載時啟用noatime,nodiratime，可以在 /etc/fstab 中進行修改。man mount 手冊中有相關選項的說明。

4）檔案掛載選項：barrier/nobarrier(如果有電池保護，可以啟用nobarrier選項來提供效能)

     If your disks are battery-backed in one way or another,  disabling barriers may safely improve performance. The mount options

    "barrier" and "nobarrier" can also be used to enable or disable barriers, for consistency with other ext4 mount options.

5）檔案掛載選項：data={journal|ordered|writeback}，如果有電池保護，啟用 data=writeback可能會提升效能。

     journal：All data is committed into the journal prior to being written into the main filesystem.
     ordered：This  is  the default mode.  All data is forced directly out to the main file system prior to its meta-data being committed

                   to the journal.
   writeback: Data ordering is not preserved - data may be written into the main filesystem after its metadata has been committed to

                   the journal. This is rumoured to be the highest-throughput option. It guarantees internal filesystem integrity, however

                   it can allow old data to appear in files after a crash and journal recovery.

6）XFS掛載選項：inode64，如果採用的是XFS檔案系統，並且一個分區容量大於1T時，需要採用inode64選項掛載，不然可能會錯誤的報"disk full"

    參見：http://imysql.cn/2013/02/21/using-xfs-with-large-partition-for-backup.html

具體參見：http://www.cnblogs.com/digdeep/p/4857987.html

3.2 磁碟的調優

1）IO調度演算法：mysql伺服器一定不要使用預設的CFQ調度演算法。如果是SSD，那麼應該使用NOOP調度演算法，如果是磁碟，就應該使用Deadline調度算法。

修改方法：

[[email protected] ~]# cat /sys/block/sda/queue/schedulernoop anticipatory deadline [cfq][[email protected] ~]# echo noop >  /sys/block/sda/queue/scheduler[[email protected] ~]# cat /sys/block/sda/queue/scheduler[noop] anticipatory deadline cfq

這是臨時修改，重啟失效。永久修改，需要修改檔案 /boot/grub/menu.lst 中的elevator=deadline 或者noop：

# vi /boot/grub/menu.lst
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

2）加大記憶體，可以使mysql緩衝更大的內容，減少IO操作。

3）磁碟RAID10 或者 換SSD；

4）適當的採用Nosql(redis/mongdb/ssdb)等減輕mysql的負擔；也可以架構master-slave減輕master的IO壓力。

5）使用memcache或者reids在mysql前面加一層緩衝，減輕磁碟IO；

6）有陣列卡時，設定陣列寫策略為WB，甚至FORCE WB（若有雙電保護，或對資料安全性要求不是特別高的話）

具體參見：http://www.cnblogs.com/digdeep/p/4863502.html

4. 網路調優

網路調優分為硬體層面和TCP/IP軟體層面參數的調優。

4.1 網路硬體調優：

1）換延遲更小，throught更大的網卡；

2）雙網卡綁定，進行負載平衡和高可用；

4.2 TCP/IP參數調優：

1）socket buffer 參數調節:

1>/proc/sys/net/ipv4/tcp_mem TCP全域緩衝，單位為記憶體頁(4k);

對應的核心參數：net.ipv4.tcp_mem ,可以在 /etc/sysctl.conf 中進行修改；

2>/proc/sys/net/ipv4/tcp_rmem 接收buffer，單位為位元組

對應的核心參數：net.ipv4.tcp_rmem, 可以在 /etc/sysctl.conf 中進行修改；

3>/proc/sys/net/ipv4/tcp_wmem 接收buffer，單位為位元組

對應的核心參數：net.ipv4.tcp_wmem, 可以在 /etc/sysctl.conf 中進行修改；

4>/proc/sys/net/core/rmem_default 接收buffer預設大小，單位位元組

對應核心參數：net.core.rmem_default, 可以在 /etc/sysctl.conf 中進行修改；

5>/proc/sys/net/core/rmem_max 接收buffer最大大小，單位位元組

對應核心參數：net.core.rmem_max, 可以在 /etc/sysctl.conf 中進行修改；

6>/proc/sys/net/core/wmem_default 發送buffer預設大小，單位位元組

對應核心參數：net.core.rmem_default, 可以在 /etc/sysctl.conf 中進行修改；

7>/proc/sys/net/core/wmem_max 發送buffer最大大小，單位位元組

對應核心參數：net.core.rmem_max, 可以在 /etc/sysctl.conf 中進行修改；

2）offload配置：

將tso，checksum等功能交給網卡硬體來完成：

ethtool -K eth0 rx on|off

ethtool -K eth0 tx on|off

ethtool -K eth0 tso on|off

3）調大網卡的接收隊列和發送隊列：

1>接收隊列：/proc/sys/net/core/netdev_max_backlog 對應核心參數：net.core.netdev_max_backlog

2>發送隊列：

查看大小：ifconfig eth0 | grep txqueue

修改大小：ifconfig eth0 txqueuelen 20000

4）調大 SYN 半串連 tcp_max_syn_backlog 數量：

sysctl -w net.ipv4.tcp_max_syn_backlog=4096

也可以在/etc/sysctl.conf檔案中配置。

5）net.core.somaxconn ：

該參數為完成3次握手，已經建立了串連，等待被accept然後進行處理的數量。預設為128，我們可以調整到 65535，甚至更大。也就是尅有容納更多的等待處理的串連。

MTU 大小 調優：

如果TCP串連的兩端的網卡和網路介面層都支援大的 MTU，那麼我們就可以配置網路，使用更大的mtu大小，也不會導致被 切割重新組裝發送。

配置命令：ifconfig eth0 mtu 9000 up

6）TCP串連的 CLOSE_WAIT 和 TIME_WAIT

如果TCP串連的 CLOSE_WAIT 和 TIME_WAIT 狀態過多時，分別需要調優TCP的keepalive相關的參數和TCP的回收相關的參數。

TCP/IP的調優極其複雜，具體參見博文：http://www.cnblogs.com/digdeep/p/4869010.html

5. Linux 系統中的各種後台daemon的調優

Linux系統中存在各種各樣的後台daemon，也就是各種service，對於mysql伺服器來說很多沒有必要的service就可以通通關閉掉。

mysql的最小化的後台服務，可以只有：crond，sshd，rsyslog，network，sysstat

當然如果有需要可以在增加其他服務。

使用 chkconfig --level 35 servicename off; 可以進行關閉。35表示在runlevel的level =3 和 level =5層級進行關閉。

deamon的調優可以參考：http://wubx.net/category/optimize/

6. ulimit 資源限制的調優

ulimit provides control over the resources available to the shell and to processes started by it, on systems that allow such control.

The soft limit is the value that the kernel enforces for the corresponding resource. The hard limit acts as a ceiling for the soft limit.

ulimit命令可以控制 shell 可以獲得的資源的限制，同時也控制在 shell 中啟動的進程可以獲得的資源的限制。分為 soft limit 和 hard limit.

ulimit 的手冊：

User limits - limit the use of system-wide resources.Syntax      ulimit [-acdfHlmnpsStuv] [limit]Options   -S   Change and report the soft limit associated with a resource.    -H   Change and report the hard limit associated with a resource.    -a   All current limits are reported.    -c   The maximum size of core files created.    -d   The maximum size of a process‘s data segment.    -f   The maximum size of files created by the shell(default option)    -l   The maximum size that can be locked into memory.    -m   The maximum resident set size.    -n   The maximum number of open file descriptors.    -p   The pipe buffer size.    -s   The maximum stack size.    -t   The maximum amount of cpu time in seconds.    -u   The maximum number of processes available to a single user.    -v   The maximum amount of virtual memory available to the process. ulimit provides control over the resources available to the shell and to processes started by it, on systems that allow such control.The soft limit is the value that the kernel enforces for the corresponding resource. The hard limit acts as a ceiling for the soft limit.An unprivileged process may only set its soft limit to a value in the range from 0 up to the hard limit, and (irreversibly) lower its hard limit. A privileged process can make arbitrary changes to either limit value.If limit is given, it is the new value of the specified resource. Otherwise, the current value of the soft limit for the specified resource is printed, unless the `-H‘ option is supplied.When setting new limits, if neither `-H‘ nor `-S‘ is supplied, both the hard and soft limits are set.Restricting per user processes ( -u) can be useful for limiting the potential effects of a fork bomb.Values are in 1024-byte increments, except for `-t‘, which is in seconds, `-p‘, which is in units of 512-byte blocks, and `-n‘ and `-u‘, which are unscaled values.The return status is zero unless an invalid option is supplied, a non-numeric argument other than unlimited is supplied as a limit, or an error occurs while setting a new limit.ulimit is a bash built in command.

查看目前的所有的限制：

[[email protected] ~]# ulimit -acore file size          (blocks, -c) 0data seg size           (kbytes, -d) unlimitedscheduling priority             (-e) 0file size               (blocks, -f) unlimitedpending signals                 (-i) 7908max locked memory       (kbytes, -l) 64max memory size         (kbytes, -m) unlimitedopen files                      (-n) 1024pipe size            (512 bytes, -p) 8POSIX message queues     (bytes, -q) 819200real-time priority              (-r) 0stack size              (kbytes, -s) 10240cpu time               (seconds, -t) unlimitedmax user processes              (-u) 7908virtual memory          (kbytes, -v) unlimitedfile locks                      (-x) unlimited

一般而言，我們很容易在 open files 上被限制，從而報錯。我們可以臨時修改和永久修改：

ulimit -n 8192

永久修改，需要修改檔案 /etc/security/limits.conf, 加上：

* soft nofile 10240* hard nofile 20480

修改之後，還需要在 vi /etc/pam.d/login 最後加上一行：

session required pam_limits.so

7. 總結：

Linux系統和硬體的調優，除了一些通用的調優之外。其它比如TCP/IP的調優，我們首先是要使用相關的各種命令查清楚瓶頸在哪裡，然後才好對症下藥。

 

參考：

http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=207641943&idx=1&sn=d124286ea8811950be5a872d57a27357

http://wubx.net/category/optimize/

http://imysql.cn/2015/05/24/mysql-optimization-reference-1.shtml

http://imysql.cn/2013/02/21/using-xfs-with-large-partition-for-backup.html

MySQL 最佳化之 Linux系統層面調優