對mysql的高并發優化配置的一些思考

對mysql的高并發優化配置的一些思考

mysql的高并發優化配置方案很多，但是適應你自己的就變得很少了，我們對數據庫的優化，無非就是為了應對mysql的高并發情況罷了。隨著大數據的時代的到來和網絡用戶的增多，很多企業中，可能每天應對的數量達百萬，千萬，甚至上億的pv量，這樣的量已經是超過普通配置的mysql所承受的量，所以應對日益增長的pv量，我們需要對mysql做出相應的對策，進一步優化mysql，達到我們所預期的效果，預防因為高并發所引起的mysql宕機，通過調試優化mysql，我們便可以有效的應對這一些情況。

 下面我們來談談關于mysql的一些優化方案，方案僅僅的參考，可能每個人的實際情況可能是有的不同，但是大體上可以嘗試這樣的優化。

1、基于redis做mysql讀寫分離。

 對于基于redis的做緩存處理優化，也不是很復雜，對于運維人員來說，你只要安裝redis和調試一下就可以了，關于redis如何調用mysql（好像是需要java寫個腳本），那是開發的事了。基于redis讀寫分離優化并不是很好講，在這里我先貼個圖，然后再講講。如圖1-1所示：

圖1-1 基于redis實現mysql讀寫分離

由上圖所示，我們可以發現其實redis調用就是那么回事，首先它是由用戶發送請求信息（讀或者寫），然而在有redis的情況下；根據圖的解析可以有以下。

1.1、redis響應請求過程。

讀：讀的過程可能復雜一點，用戶會直接先讀取redis數據庫，然后把請求結果返回到client；如果用戶在redis沒有讀取到想要請求結果，他會直接逃過redis直接讀取到mysql，然后redis會把數據復制一份到本地。

寫：寫的過程稍微簡單一點了，用戶會直接向redis寫入，然后redis在緩存到mysql上。可能你會發現，整個過程都基本是redis在工作，mysql好像沒它的事了，對的，我們就是要的是這種效果，任何請求都交給redis處理后，那你還怕mysql響應不過來了嗎。

1.2、redis配置優化

Redis配置，主要的是做持久化配置，主從復制，還有一些安全的配置，大概就是這樣，上圖我是有畫的，至于過程配置我就不寫了，我們大概有個思路就好了。

1.3、mysql優化配置（主從復制）

Mysql做主從復制，怎么么說呢，主要是為了安全，一般來說，在master有兩臺slave就可以了，已經足夠應對很多的意外情況了。做主從復制和備份，要注意得是，主從兩臺mysql配置一樣，對于備份的數據，不要放在mysql目錄下，要另起路徑，并給與mysql權限。配置可參考mysql主從復制配置

1.4、mysql監控系統

對于Mysql做監控，個人認為是很有必要的，首先我們可以在無人值守的情況之下，我們可以對mysql的狀態進行監控；通過監控，我們不但可以對mysql的負載情況進行告警，而且可以對mysql本身性能的一些優化處理，所以我在應用對mysql的監控中，使用了zabbix對負載情況做告警處理，并結合pmm-server對mysql系統的優化。對于pmm-sercer在這里先貼個圖，如圖1.2、1.3所示

圖1.2 mysql資源數據圖

圖1.3 mysql資源數據圖

由以上圖可以發現，我們可以大體的可以看到mysql的資源配置情況，根據數值我們可以適當的優化，并調整一些mysql的自身的參數，這就是pmm-sever的監控的好處了。

對于zabbix，我現在就是拿來做告警處理，在對mysql的監控中，zabbix本身自帶的模板和結合percona插件，基本實現對整個mysql的監控（配合可參考percona監控mysql數據），因為兩個結合基本比較全面的實現對mysql的監控了，如圖1.4所示，可看到模板所提供的監控項。

圖1.4 zabbix的mysql模板監控項

總結以上，我們可以發現，一個良好的mysql優化架構，需要做的包括redis做讀寫分離，mysql主從，監控系統完善等等。如果你還有跟好的方案，也記得分享一下；接下來，我們是針對mysql本身性能的優化，×××能的容錯率，在基礎上進一步提升mysql的性能。

2.、mysql自身的優化

總的來說還是自身因素影響的比較多，我們可以通過修改my.cnf配置文件來對mysql進行進一步的優化。我們可以通過修改mysql的參數使得mysql擁有更可靠的性能。下面是我的數據庫配置，自己通過百度谷歌，找很多配置選項的解析（配置適合mysql5.5以上的版本），然后總結。希望對你有幫助。（注意一下優化配置均在【mysqld】選項下配置，不要搞錯成【mysql】）

[mysqld]
back_log = 300
binlog_format = MIXED
character-set-server=utf8mb4
long_query_time = 1
log-bin=/databack/data_logbin/mysql_binlog
innodb_log_file_size=2G
innodb_log_buffer_size=4M
innodb_buffer_pool_size=4G
#innodb_file_per_table = ON
innodb_thread_concurrency=8
innodb_flush_logs_at_trx_commit=2
#innodb_additional_mem_pool_size=4M
join_buffer_size = 8M
key_buffer_size=256M
max_connections = 1000
max_allowed_packet = 4M
max_connect_errors = 10000
myisam_sort_buffer_size = 64M
port = 3306
query_cache_type=1
query_cache_size = 64M
read_buffer_size=4M
read_rnd_buffer_size=4M
server-id = 1
skip-external-locking
slow_query_log = 1 
#skip-name-resolve
#skip-networking
sort_buffer_size = 8M
socket = /tmp/mysql.sock
table_open_cache=1024
thread_cache_size = 64
thread_stack = 256K
tmp_table_size=64M
wait_timeout = 10

下面是對上面配置的解析：

back_log = 300：該參數的值表示在MySql的連接數據達到#max_connections 時，在它暫時停止響應新請求之前的短時間內有300個請求可以被存在堆棧中，即新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，等 mysql處理完其他請求之后會對其作出響應，如果等待連接的數量超過#back_log，將不被授予連接資源。你可以合理的設置你的back_log，但是該值不要高于操作系統的限制的值。系統的默認值為50。Linux系統一般設置小于512的整數。

binlog_format = MIXED：配置主從模式下，選取同步的模式，Mysql主從的復制可以有三種復制類型，分別是:語句的復制STATEMEN,行的復制ROW和混合類型的復制MIXED，語句的復制顧名思義就是在主服務器上執行的SQL語句，在從服務器上執行同樣的語句，行的復制就是把改變的內容復制過去，而不是把命令在從服務器上執行一遍。默認采用基于語句的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制,配置，復制類型可以通過binlog_format =在配置文件上配置

character-set-server=utf8mb4 ：utf-8編碼可能2個字節、3個字節、4個字節的字符，但是MySQL的utf8編碼只支持3字節的數據，而移動端的表情數據是4個字節的字符。如果直接往采用utf-8編碼的數據庫中插入表情數據，Java程序中將報SQL異常utf8mb4編碼是utf8編碼的超集，兼容utf8，并且能存儲4字節的表情字符。 采用utf8mb4編碼的好處是，存儲與獲取數據的時候，不用再考慮表情字符的編碼與解碼問題。

long_query_time = 1：設置慢查詢響應的時間，記錄超過1秒的SQL執行語句。

log-bin=/databack/data_logbin/mysql_binlog ：設置二進制日志的存放路徑，如果不設置系統會默認存放到mysql的目錄下，建議創建新的目錄來存放二進制日志，且該目錄不要同數據庫同個目錄，存放目錄擁有者為mysql。

innodb_log_file_size=2G ：在高寫入負載尤其是大數據集的情況下很重要。這個值越大則性能相對越高，跟據服務器大小而異。這是redo日志的大小。redo日志被用于確保寫操作快速而可靠并且在崩潰時恢復。在MySQL 5.5，redo日志的總尺寸被限定在4GB(默認可以有2個log文件)。而MySQL 5.6里可以設置允許大于4G。你可以一開始就把它設置成4G。這個值的設置其實是可以計算的 你可以通過命令SHOW GLOBAL STATUS的輸出看Innodb_os_log_written的值，把該值除以1024*1024 得到的結果是每分鐘處理的redo日志大小，然后再乘以60得到每小時處理的日志大小，因為在5.5以上版本都是默認有兩個日志重做日志文件ib_logfile0和ib_logfile1，所得到結果再除以2，再取整就是你的redo該設置大小了。

innodb_log_buffer_size=4M：默認為1M，在默認的設置在中等強度寫入負載以及較短事務的情況下，服務器性能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。在 InnoDB在事務提交前，并不將改變的日志寫入到磁盤中，因此在大事務中，可以減輕磁盤I/O的壓力。通常情況下，如果不是寫入大量的超大二進制數據，4MB-8MB已經足夠了。

innodb_buffer_pool_size=4G：這配置對Innodb表來說非常重要。該參數主要作用是緩存innodb表的索引，數據，插入數據時的緩沖由于Innodb把數據和索引都緩存起來，因此在配置該參數時，可以設置它高達60-80% 的可用內存（官網是建議的也是系統內存的80%左右）。緩沖池是數據和索引緩存的地方這能保證你在大多數的讀取操作時使用的是內存而不是硬盤。一般配置的值是5-6GB(8GB內存)，19-25GB(32GB內存)，38-50GB(64GB內存)僅供參考。

#innodb_file_per_table = ON：在5.6中，該選項屬性默認值是ON，由于對新建的表有影響，所以在之前的版本中你需要把它設置成ON。這項設置告知InnoDB是否需要將所有表的數據單獨放在一個.ibd文件，這樣做的好處是使得每個表都有自已獨立的表空間。每個表的數據和索引都會存在自已的表空間中。也實現單表在不同的數據庫中移動，且空間可以回收。

innodb_thread_concurrency=8：指服務器邏輯線程數可以設置成與系統一樣數量，參數可配置成邏輯CPU數量的兩倍。

系統CPU查看命令如下：

查看邏輯CPU個數：

#cat /proc/cpuinfo |grep "processor"|sort -u|wc –l

查看物理CPU個數：

# cat /proc/cpuinfo | grep "physical id" |sort -u|wc -l

查看每個物理CPU內核個數：

# cat /proc/cpuinfo |  grep "cpu cores" |uniq

innodb_flush_logs_at_trx_commit=2：系統默認值是 1，但是這樣設置會使得提交更新事務都會刷新到磁盤中，會造成資源耗費。所以需要值設置為 2，這樣就不用不把日志刷新到磁盤上，而只刷新到操作系統的緩存上。但然啦也可以設置為0， 這樣設置是很快，但也造成了相對的不安全，會導致MySQL服務器崩潰時就會丟失一些事務。而設置為 2 剛好尼補了。

#innodb_additional_mem_pool_size=4M：該參數默認為1M適當調整該參數的大小以確保所有數據都能存放在內存中提高訪問效率的，主要用來存放Innodb的內部目錄，這個值不用分配太大，系統可以自動調。在mysql5.6.3可以忽略。

join_buffer_size = 8M：表示#聯合查詢操作所能使用的緩沖區大小。

key_buffer_size=256M：指定索引緩沖區的大小，它決定索引處理的速度，你可以設置成系統的物理內存的1/4，它主要針對的是MyISAM引擎，但是設置大少不要超過4G，不然會出現問題。

max_connections = 1000：設置置MySQL的最大連接，按你實際情況適當設置就好。如果你經常看到‘Too many connections'錯誤，是因為max_connections的值太低了，所以需要設置更高的鏈接數，如果max_connection值被設高之后的缺陷是當服務器運行超過設置閾值或更高的活動事務時會變的沒有響應。

max_allowed_packet = 4M：這個參數mysql消息緩沖區的大小，如果這個過小可能會影響到部分操作，默認是1M，一般設置成4-16M就可以了。

max_connect_errors = 10000：表示如果有同一個主機訪問的參數值超出該參數值個數的中斷錯誤連接，則該主機將被禁止連接。如需對該主機進行解禁，執行：FLUSH HOST。

myisam_sort_buffer_size = 64M：這個參數默認是8M，表示MyISAM表發生變化時重新排序所需的緩沖，一般64M就已經足夠了。

port = 3306：表示使用3306來做mysql啟動端口

query_cache_type=1：表示控制緩存的類型，有三個參數可選（0、1、2）設置為0，表示緩存沒有應用，也就相當于禁用了，設置為1，表示緩存所有的結果，設置為2表示只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

query_cache_size=32M：參數表示mysql查詢結果的緩沖區大小，一般不建議設置太大，因為設置太大會增加開銷，一般設置成32M-256M左右即可，設置參數一般為2的倍數。

read_buffer_size=4M：表示按順序查詢操作包括讀、查詢等操作所能使用的緩沖區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享，一般不建議太大，對于4G到16G內存的服務器2M-8M就可以了。

read_rnd_buffer_size=4M：表示是MySQL的隨機讀緩沖區大小。當任意順序讀取行時將分配一個隨機讀取緩沖區，進行排序查詢時，便分配隨機緩沖作為該操作的緩沖區大小，同樣的對于4G到16G內存的服務器2M-8M就可以了。

server-id = 1：表示做主從同步所定義的serverid，作為master的server_id必須必slave端的要小，越小表示優先級越高，但是在同個網段內的mysql服務，不允許設置同樣的sever_id。參數可設參考范圍（1-200）。

skip-external-locking：開啟該選項表示避免MySQL的外部鎖定，減少出錯幾率增強穩定性，適用于單服務器環境。

slow_query_log = 1：開啟慢查詢日志，作用于慢查詢日志,顧名思義,就是查詢慢的日志。

skip-name-resolve：禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求。

skip-networking：開啟該選項可以徹底關閉MySQL的TCP/IP連接方式，如果WEB服務器是以遠程連接的方式訪問MySQL數據庫服務器則不要開啟該選項，否則將無法正常連接。

sort_buffer_size = 8M：表示查詢排序時所能使用的緩沖區大小。它直接與實時連接的個數 有關，實時連接的個數乘以sort_buffer_size的大小就是實際分配的總共排序緩沖區大小。所以，對于內存在4GB-8G左右的服務器可以設置為6-16M。

socket = /tmp/mysql.sock：mysql.sock 文件作用主要是server和client在同一臺服務器，當使用本地連接時，就會使用socket進行連接，該文件一般是放在/var/lib/mysql/mysql.sock下，也常常使用ln –s 在/tmp目錄下做軟連接。

table_open_cache=1024：table_cache主要用于設置table高速緩存的數量。由于每個客戶端連接都會至少訪問一個表，因此此參數的值與max_connections有關。你可以通過命令show variables like '%open%'; 查看open_files_limit參數,大量使用MyISAM的環境里，應該保證open_files_limit表類型至少是table_cache的二到三倍，調到512-1024最佳。

thread_cache_size = 64 ：這個變量值表示的是可以重新利用保存在緩存中線程的數量,當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創建,如果有很多新的線程，增加這個值可以改善系統性能.通過比較 Connections 和 Threads_created 狀態的變量，可以看到這個變量的作用 根據物理內存設置規則可以做以下配置2G-4G可以設置為16-64左右，當然大于4G的服務器，設置64也已經足夠了。

thread_stack = 256K：表示每個連接線程被創建時，MySQL給它分配的內存大小，對于8-16G的服務器設置成256K就可以了，再大一點的，可以適當增加呢。

tmp_table_size=64M：表示定義一個臨時表的大小，該值默認為16M，可調到64-256最佳，線程獨占，太大可能內存不夠造成I/O堵塞，如果動態頁面可以適當調大點。

wait_timeout = 100：表示指定一個請求的最大連接時間，該值過大會導致，MySQL里大量的SLEEP進程無法及時釋放，拖累系統性能，不過也不能把這個指設置的過小，否則你可能會遭遇到“MySQL has gone away”之類的問題。  系統默認是8個小時，感覺太大，可以設置小點。

3、總結

    預防Mysql病發的情況，是每個企業所要面對的事情，大數據的到來，更加使得mysql的性能要求更高，所以對mysql的優化升級，也是迫在眉睫。以上是本人總結，僅僅提供參考，希望能幫到你。