[TOC] http://tools.percona.com/wizard 這個網站根據信息填寫會給出相應的配置建議 # centos系統參數優化 ## 內核相關參數優化 ( /etc/sysctl.conf ) 通常服務器與數據庫之間通過tcp3次握手來建立網絡連接,同時有個端口會在監聽,監聽的端口會有多個監聽隊列 ~~~ net.core.somaxconn=65535 #每個端口最大監聽的長度 net.core.netdev_max_backlog=65535 #在每個網絡接收速率比內核接收快的時候,允許發送到隊列數據包的最大速率 net.ipv4.tcp_max_syn_backlog=65535 #允許未獲得對方請求可保存在隊列中的最大的連接數目 net.ipv4.tcp_fin_timeout=10 #用來控制tcp連接的等待時間,對于經常訪問,通常會有大量連接處于等待狀態的,而這個參數就是加快tcp等待時間,加快回收速度 net.ipv4.tcp_tw_reuse=1 net.ipv4.tcp_tw_recycle=1 #主要加快tcp回收速度 #tcp接收和發送數據的緩沖區默認值和最大值 net.core.wmem_default=87380 net.core.wmem_max=16777216 net.core.rmem_default=87380 net.core.rmem_max=16777216 #tcp失效連接占用系統資源,加快資源回收 net.ipv4.tcp_keepalive_time=120 #表示tcp發送keepalive探測消息的時間間隔,單位為秒 net.ipv4.tcp_keepalive_intvl=30 #控制發送keepalive探測消息沒有消息時候,重發的間隔,單位為秒 net.ipv4.tcp_keepalive_probes=3 #失效之前重發的次數 #這個參數應該設置的足夠大,以便能在一個共享內存段下容納下整個Innodb緩沖池大小 #這個參數設置過低,會造成需要在系統中創建出多個共享內存段 #這個值的大小對于64位系統linux,可取的最大值為物理內存值-1byte,建議值為物理內存的一半,一般取值大于Innodb緩沖池的大小即可,可以取物理內存值-1byte kernel.shmmax=4294967295 #這個參數當內存不足時會對性能產生比較明顯的影響 #內存不足的時候會用到linux交換分區swap,將虛擬內存寫到磁盤的交換區,來發生內存交換 #當這對mysql性能會是嚴重下降(free -m 命名看交換分區),建議mysql配置參數不使用交換分區 #這個參數告訴系統除非內存滿了,否則不要用交換分區 vm.swappiness=0 ~~~ ## 增加資源限制 (/etc/security/limit.conf) 這個文件其實就是linux PAM也就是插入式認證模塊的配置文件 打開文件數的限制 打開最大文件數的配置 ~~~ #把這2個參數加到limit.conf文件的末尾就行了 * soft nofile 65535 * hard nofile 65535 *表示對所有用戶生效 soft 表示當前系統生效的設置,soft限制不能比hard高 hard 聲明系統中所能設定的最大值 nofile 表示所限制的資源是打開文件的最大數目 65535 就是限制的數量 # 注意需要重啟操作系統才可生效 ~~~ ## 磁盤調度策略 (/sys/block/devname/queue/scheduler) ~~~ cat /sys/block/sda/queue/scheduler noop anticipatory deadline[cfq] ~~~ linux系統下對磁盤塊設備的調度,實際上是塊設備發送到底層設備的順序,默認情況用的是cfq 上面命令顯示用的是cfq,cfq一般系統用是可以的,但是對mysql的話性能不是很好 noop(電梯式調度策略) NOOP實現了一個FIFO隊列,它像電梯的工作方法一樣對I/O請求進行組織,當有一個新的請求到來時,它將請求合并到最近的請求之后,以此來保證請求同一介質.NOOP傾向餓死讀而利于寫,因此NOOP對于閃存設備,RAM,嵌入式系統是最好的選擇 deadline(截止時間調度) Deadline保證了在一個截止時間內服務的請求,這個截止時間是可調整的,而默認讀期限短于寫期限.這樣就防止了寫操作因為不能被讀取而餓死的現象,Deadline對數據庫類應用是最好的選擇 anticipatory(預料I/O調度策略) 本質上與Deadline一樣,但在最后一次讀操作后,要等待6ms,才能繼續進行對其他I/O請求進行調度.它會在每個6ms中插入新的I/O操作,而會將一些小寫入流合并成一個大寫入流,用寫入延遲換取最大的寫入吞吐量.AS適合寫入較多的環境,比如文件服務器,AS對數據庫環境表現很差 我們可以通過這個命令把磁盤調度策略改為deadline ~~~ echo <schedulername > /sys/block/devname/queue/scheduler 如 echo deadline > /sys/block/devname/queue/scheduler ~~~ ## 文件系統對性能的影響 windows有FAT和NTFS,實際可選擇就只有NTFS linux有EXT3,EXT4,XFS,這3個都是有日志功能,這3個表現好的是XFS 如果是EXT3/4系統的掛載參數(/etc/fstab) ~~~ data=writeback | ordered | journal # 3個參數表示不同的日志策略 # writeback表示源數據寫入和數據寫入不是同步的,這是最快的日志 # ordered保證一致,在寫源數據的時候先寫數據 # journal 更安全,最慢的 還有2個參數,noatime, nodiratime 表示禁止訪問時間和讀取時間,禁用2個選項,有利于寫的速度 例子: /dev/sda1/ext4 noatime,nodiratime,data=writeback11 ~~~ # Mysql服務器參數 ## 內存配置參數 * 確定可以使用的內存的上限 * 確定Mysql每個連接使用的內存 ~~~ sort_buffer_size 排序緩沖區內存,規定了每個線程使用排序緩沖區大小,不是在連接初始化的時候就分配的,而是在有查詢要做排序操作的時候才會分配內存,需要注意的是當查詢做排序操作的時候就會立即分配該排序這么多內存,不管他是否需要這么大的內存,如果我們給他設置100M,當10個連接進來做排序的時候,mysql就會占用1G內存 join_buffer_size 關聯緩沖區大小,每個線程使用關聯緩沖區大小.當一個連接關聯多張表的時候,為每個關聯分配一個緩沖,所以一個關聯會有多個緩沖 read_buffer_size 每個線程當對myisam表全表掃描時,讀使用的緩沖區大小,有查詢需要的時候才會分配,這個參數的大小一定要是4K的倍數 read_rnd_buffer_size 排序操作時將表的row pointer讀入sort buffer，然后讀取sort buffer中的row pointer并排序，按排序后的row pointer將行讀入read_rnd_buffer中 每個線程當需要的時候,mysql會分配,不是全部給設置的大小 Innodb_buffer_pool_size innodb所使用的緩沖池的大小,不僅緩存索引還會緩存數據,插入緩沖,鎖等等,innodb還能利用緩存池來幫助寫入,然后一起順序的寫入磁盤 總內存-(每個線程所需要的內存*連接數)-系統保留內存 這樣就是這個大小了 需要注意的是,如果給他分配很大的內存,當關閉的時候,innodb也需要很長的時候把內存中的一些東西寫到磁盤上 key_buffer_size 這個是給myisam用的,用來緩存索引,myisam表只能緩存索引,數據是由操作系統緩存的,mysql的系統表還在使用myisam存儲引擎 ~~~ ## I/O相關的配置參數 這部分決定了,修改的數據如何同步到磁盤上,對數據做持久化保存,這部分參數對性能影響也是比較大的 ## innodb I/O相關配置 他是事務存儲引擎,他為了節省I/O開銷,他是每次事務都寫入事務日志中而不是每次修改都寫到磁盤中,為了提高I/O性能,因為每次事務都要把數據寫到對應表的隨機位置,這樣會產生大量的隨機I/O,而記錄事務日志是順序I/O相比刷新臟數據要快的多,寫到事務日志就算成功 即使服務器宕機,我們也能從事務日志中恢復以及提交的事務 ~~~ Innodb_log_file_size 控制單個事務日志的大小 Innodb_log_files_in_group 控制事務日志文件的個數 事務日志總大小 = Innodb_log_files_in_group*Innodb_log_file_size 事務日志是循環使用的,當這個使用滿了才會使用下一個 一般來說事務日志的總大小可以記錄服務器一個小時的事務總信息 事務不是先寫入到事務日志文件中的,而是先寫入事務日志的緩沖區,然后在刷新到磁盤中 Innodb_log_buffer_size 用來控制日志緩沖區大小,至少一秒就會刷新到磁盤,32M-128M不要設置太大,而刷新頻率影響還是比較大的 Innodb_flush_log_at_trx_commit 值有0和1,2 0:每秒進行一次log寫入cache,并flush log到磁盤,當mysql崩潰至少丟失1秒的數據 1 [默認]:在每次事務提交執行log寫入到cache,并flush log到磁盤 2[建議]:每次事務提交,執行log數據寫入到cache,每秒執行一次flush log到磁盤,mysql進程崩潰不會丟失數據,但是服務器宕機才可能丟失至少一秒數據 Innodb_flush_method=O_DIRECT innodb刷新方式,innodb的數據文件和日志文件如何和系統進行交互,影響innodb怎么寫數據和怎么讀數據,設置為這個值就是建議操作系統不要緩存數據也不要預讀,完全關閉操作系統緩存 Innodb_file_per_table=1 Innodb_doublewrite=1 控制innodb是否啟用雙寫緩存,避免沒有寫完整造成數據丟失,增加數據安全性,對性能有一些影響,影響不會太大 ~~~ ## MyISAM I/O相關配置 ~~~ delay_key_write 控制關鍵字緩沖的臟塊什么時候刷新到磁盤中 OFF:每次寫操作后刷新鍵緩沖中的臟塊到磁盤中,關閉延遲寫入,最安全的選項,性能比較差 ON:只對鍵表時指定了delay_key_write選項的表使用延遲刷新 ALL:對所有MYISAM表都使用延遲鍵寫入 注意:如果當mysql崩潰,臟塊中數據沒有寫入就會造成myisam索引的損壞,這樣就要對myisam表進行修復了 ~~~ ## 安全相關配置參數 ~~~ expire_logs_days指定自動清理binlog的天數 開啟了binlog就要開啟這個,清理舊的binlog日志,避免日志太大,設置至少2次全備的間隔 max_allowed_packet控制mysql可以接收的包的大小,也控制用戶定義變量最大的大小,配置了主從的話要保持一致,從小于主否則會導致主從失敗 skip_name_resolve 禁用DNS查找,但是禁用的話只能對ip或者ip段或者host文件中出現的進行授權了 sysdate_is_now確保了sysdate()返回確定性日期 read_only 禁止非super權限的用戶寫權限,建議在主從復制中的從庫使用,這樣就只接受主庫中的寫入 skip_slave_start 禁止slave自動恢復,在主從上從庫使用的,從庫崩潰會自動恢復,但是這個恢復一般是危險的,一般要我們檢查通過才允許恢復 sql_mode 設置mysql所使用的SQL模式 ~~~ ## 其他常用的配置參數 ~~~ sync_binlog 控制mysql如何向磁盤刷新binlog 默認為0,mysql不會寫,操作系統會根據情況來寫 1,就是每次事務提交都會寫到binlog,最安全的,建議主庫上使用 tmp_table_size和max_heap_table_size 一起設置的,控制內存臨時表大小,不要設置太大 max_connections控制允許的最大連接數,默認是100 ~~~