對mysql的高并發(fā)優(yōu)化配置的一些思考-創(chuàng)新互聯(lián)

對mysql的高并發(fā)優(yōu)化配置的一些思考

站在用戶的角度思考問題，與客戶深入溝通，找到蒲城網(wǎng)站設計與蒲城網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：做網(wǎng)站、網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名申請、網(wǎng)頁空間、企業(yè)郵箱。業(yè)務覆蓋蒲城地區(qū)。

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

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

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

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

圖1-1 基于redis實現(xiàn)mysql讀寫分離

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

1.1、redis響應請求過程。

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

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

1.2、redis配置優(yōu)化

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

1.3、mysql優(yōu)化配置（主從復制）

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

1.4、mysql監(jiān)控系統(tǒng)

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

圖1.2 mysql資源數(shù)據(jù)圖

圖1.3 mysql資源數(shù)據(jù)圖

由以上圖可以發(fā)現(xiàn)，我們可以大體的可以看到mysql的資源配置情況，根據(jù)數(shù)值我們可以適當?shù)膬?yōu)化，并調(diào)整一些mysql的自身的參數(shù)，這就是pmm-sever的監(jiān)控的好處了。

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

圖1.4 zabbix的mysql模板監(jiān)控項

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

2.、mysql自身的優(yōu)化

總的來說還是自身因素影響的比較多，我們可以通過修改my.cnf配置文件來對mysql進行進一步的優(yōu)化。我們可以通過修改mysql的參數(shù)使得mysql擁有更可靠的性能。下面是我的數(shù)據(jù)庫配置，自己通過百度谷歌，找很多配置選項的解析（配置適合mysql5.5以上的版本），然后總結(jié)。希望對你有幫助。（注意一下優(yōu)化配置均在【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：該參數(shù)的值表示在MySql的連接數(shù)據(jù)達到#max_connections時，在它暫時停止響應新請求之前的短時間內(nèi)有300個請求可以被存在堆棧中，即新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，等 mysql處理完其他請求之后會對其作出響應，如果等待連接的數(shù)量超過#back_log，將不被授予連接資源。你可以合理的設置你的back_log，但是該值不要高于操作系統(tǒng)的限制的值。系統(tǒng)的默認值為50。Linux系統(tǒng)一般設置小于512的整數(shù)。

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

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

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

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

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

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

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

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

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

系統(tǒng)CPU查看命令如下：

查看邏輯CPU個數(shù)：

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

查看物理CPU個數(shù)：

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

              

查看每個物理CPU內(nèi)核個數(shù)：

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

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

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

join_buffer_size = 8M：表示#聯(lián)合查詢操作所能使用的緩沖區(qū)大小。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

sort_buffer_size = 8M：表示查詢排序時所能使用的緩沖區(qū)大小。它直接與實時連接的個數(shù) 有關，實時連接的個數(shù)乘以sort_buffer_size的大小就是實際分配的總共排序緩沖區(qū)大小。所以，對于內(nèi)存在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高速緩存的數(shù)量。由于每個客戶端連接都會至少訪問一個表，因此此參數(shù)的值與max_connections有關。你可以通過命令show variables like '%open%';查看open_files_limit參數(shù),大量使用MyISAM的環(huán)境里，應該保證open_files_limit表類型至少是table_cache的二到三倍，調(diào)到512-1024最佳。

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

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

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

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

3、總結(jié)

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

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

當前題目：對mysql的高并發(fā)優(yōu)化配置的一些思考-創(chuàng)新互聯(lián)
當前地址：http://bm7419.com/article18/gjodp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、ChatGPT、響應式網(wǎng)站、網(wǎng)站策劃、軟件開發(fā)、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)