mysql伺服器配置與並發能力,mysql集群性能

本文目錄一覽：

• 1、MySQL性能調優 – 你必須了解的15個重要變數
• 2、超詳細MySQL資料庫優化
• 3、mysql資料庫最大能支持多少並發量

MySQL性能調優 – 你必須了解的15個重要變數

前言:

MYSQL 應該是最流行了 WEB 後端資料庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構師還是會選擇 MYSQL 來做數據存儲。本文作者總結梳理MySQL性能調優的15個重要變數，又不足需要補充的還望大佬指出。

1.DEFAULT_STORAGE_ENGINE

如果你已經在用MySQL 5.6或者5.7，並且你的數據表都是InnoDB，那麼表示你已經設置好了。如果沒有，確保把你的錶轉換為InnoDB並且設置default_storage_engine為InnoDB。

為什麼？簡而言之，因為InnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲引擎 – 它支持事務，高並發，有著非常好的性能表現(當配置正確時)。這裡有詳細的版本介紹為什麼

2.INNODB_BUFFER_POOL_SIZE

這個是InnoDB最重要變數。實際上，如果你的主要存儲引擎是InnoDB，那麼對於你，這個變數對於MySQL是最重要的。

基本上，innodb_buffer_pool_size指定了MySQL應該分配給InnoDB緩衝池多少內存，InnoDB緩衝池用來存儲緩存的數據，二級索引，臟數據(已經被更改但沒有刷新到硬碟的數據)以及各種內部結構如自適應哈希索引。

根據經驗，在一個獨立的MySQL伺服器應該分配給MySQL整個機器總內存的80%。如果你的MySQL運行在一個共享伺服器，或者你想知道InnoDB緩衝池大小是否正確設置，詳細請看這裡。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日誌文件的設置在MySQL社區也叫做事務日誌。直到MySQL 5.6.8事務日誌默認值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認值提升到48M,但對於許多稍繁忙的系統，還遠遠要低。

根據經驗，你應該設置的日誌大小能在你伺服器繁忙時能存儲1-2小時的寫入量。如果不想這麼麻煩，那麼設置1-2G的大小會讓你的性能有一個不錯的表現。這個變數也相當重要，更詳細的介紹請看這裡。

當然，如果你有大量的大事務更改，那麼，更改比默認innodb日誌緩衝大小更大的值會對你的性能有一定的提高，但是你使用的是autocommit，或者你的事務更改小於幾k，那還是保持默認的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默認下，innodb_flush_log_at_trx_commit設置為1表示InnoDB在每次事務提交後立即刷新同步數據到硬碟。如果你使用autocommit，那麼你的每一個INSERT, UPDATE或DELETE語句都是一個事務提交。

同步是一個昂貴的操作(特別是當你沒有寫回緩存時)，因為它涉及對硬碟的實際同步物理寫入。所以如果可能，並不建議使用默認值。

兩個可選的值是0和2:

* 0表示刷新到硬碟，但不同步(提交事務時沒有實際的IO操作)

* 2表示不刷新和不同步(也沒有實際的IO操作)

所以你如果設置它為0或2，則同步操作每秒執行一次。所以明顯的缺點是你可能會丟失上一秒的提交數據。具體來說，你的事務已經提交了，但伺服器馬上斷電了，那麼你的提交相當於沒有發生過。

顯示的，對於金融機構，如銀行，這是無法忍受的。不過對於大多數網站，可以設置為innodb_flush_log_at_trx_commit=0|2，即使伺服器最終崩潰也沒有什麼大問題。畢竟，僅僅在幾年前有許多網站還是用MyISAM，當崩潰時會丟失30s的數據(更不要提那令人抓狂的慢修復進程)。

那麼，0和2之間的實際區別是什麼？性能明顯的差異是可以忽略不計，因為刷新到操作系統緩存的操作是非常快的。所以很明顯應該設置為0，萬一MySQL崩潰(不是整個機器)，你不會丟失任何數據，因為數據已經在OS緩存，最終還是會同步到硬碟的。

5.SYNC_BINLOG

已經有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關係，下面我們來簡單的介紹下：

a) 如果你的伺服器沒有設置從伺服器，而且你不做備份，那麼設置sync_binlog=0將對性能有好處。

b) 如果你有從伺服器並且做備份，但你不介意當主伺服器崩潰時在二進位日誌丟失一些事件，那麼為了更好的性能還是設置為sync_binlog=0.

c) 如果你有從伺服器並且備份，你非常在意從伺服器的一致性，以及能及時恢復到一個時間點(通過使用最新的一致性備份和二進位日誌將資料庫恢復到特定時間點的能力)，那麼你應該設置innodb_flush_log_at_trx_commit=1，並且需要認真考慮使用sync_binlog=1。

問題是sync_binlog=1代價比較高 – 現在每個事務也要同步一次到硬碟。你可能會想為什麼不把兩次同步合併成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經能合併提交，那麼在這種情況下sync_binlog=1的操作也不是這麼昂貴了，但在舊的mysql版本中仍然會對性能有很大影響。

6.INNODB_FLUSH_METHOD

將innodb_flush_method設置為O_DIRECT以避免雙重緩衝.唯一一種情況你不應該使用O_DIRECT是當你操作系統不支持時。但如果你運行的是Linux，使用O_DIRECT來激活直接IO。

不用直接IO，雙重緩衝將會發生，因為所有的資料庫更改首先會寫入到OS緩存然後才同步到硬碟 – 所以InnoDB緩衝池和OS緩存會同時持有一份相同的數據。特別是如果你的緩衝池限制為總內存的50%，那意味著在寫密集的環境中你可能會浪費高達50%的內存。如果沒有限制為50%，伺服器可能由於OS緩存的高壓力會使用到swap。

簡單地說，設置為innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了緩衝實例作為減小內部鎖爭用來提高MySQL吞吐量的手段。

在5.5版本這個對提升吞吐量幫助很小，然後在MySQL 5.6版本這個提升就非常大了，所以在MySQL5.5中你可能會保守地設置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設置為8-16個緩衝池實例。

你設置後觀察會覺得性能提高不大，但在大多數高負載情況下，它應該會有不錯的表現。

對了，不要指望這個設置能減少你單個查詢的響應時間。這個是在高並發負載的伺服器上才看得出區別。比如多個線程同時做許多事情。

8.INNODB_THREAD_CONCURRENCY

InnoDB有一種方法來控制並行執行的線程數 – 我們稱為並發控制機制。大部分是由innodb_thread_concurrency值來控制的。如果設置為0，並發控制就關閉了，因此InnoDB會立即處理所有進來的請求(儘可能多的)。

在你有32CPU核心且只有4個請求時會沒什麼問題。不過想像下你只有4CPU核心和32個請求時 – 如果你讓32個請求同時處理，你這個自找麻煩。因為這些32個請求只有4 CPU核心，顯然地會比平常慢至少8倍(實際上是大於8倍)，而然這些請求每個都有自己的外部和內部鎖，這有很大可能堆積請求。

下面介紹如何更改這個變數，在mysql命令行提示符執行：

對於大多數工作負載和伺服器，設置為8是一個好開端，然後你可以根據伺服器達到了這個限制而資源使用率利用不足時逐漸增加。可以通過show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：

9.SKIP_NAME_RESOLVE

這一項不得不提及，因為仍然有很多人沒有添加這一項。你應該添加skip_name_resolve來避免連接時DNS解析。

大多數情況下你更改這個會沒有什麼感覺，因為大多數情況下DNS伺服器解析會非常快。不過當DNS伺服器失敗時，它會出現在你伺服器上出現「unauthenticated connections」 ，而就是為什麼所有的請求都突然開始慢下來了。

所以不要等到這種事情發生才更改。現在添加這個變數並且避免基於主機名的授權。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用來當刷新臟數據時，控制MySQL每秒執行的寫IO量。

* innodb_io_capacity_max: 在壓力下，控制當刷新臟數據時MySQL每秒執行的寫IO量

首先，這與讀取無關 – SELECT查詢執行的操作。對於讀操作，MySQL會盡最大可能處理並返回結果。至於寫操作，MySQL在後台會循環刷新，在每一個循環會檢查有多少數據需要刷新，並且不會用超過innodb_io_capacity指定的數來做刷新操作。這也包括更改緩衝區合併（在它們刷新到磁碟之前，更改緩衝區是輔助臟頁存儲的關鍵）。

第二，我需要解釋一下什麼叫「在壓力下」，MySQL中稱為」緊急情況」，是當MySQL在後台刷新時，它需要刷新一些數據為了讓新的寫操作進來。然後，MySQL會用到innodb_io_capacity_max。

那麼，應該設置innodb_io_capacity和innodb_io_capacity_max為什麼呢？

最好的方法是測量你的存儲設置的隨機寫吞吐量，然後給innodb_io_capacity_max設置為你的設備能達到的最大IOPS。innodb_io_capacity就設置為它的50-75%，特別是你的系統主要是寫操作時。

通常你可以預測你的系統的IOPS是多少。例如由8 15k硬碟組成的RAID10能做大約每秒1000隨機寫操作，所以你可以設置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價企業SSD可以做4,000-10,000 IOPS等。

這個值設置得不完美問題不大。但是，要注意默認的200和400會限制你的寫吞吐量，因此你可能偶爾會捕捉到刷新進程。如果出現這種情況，可能是已經達到你硬碟的寫IO吞吐量，或者這個值設置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認值，因為它已經設置正確了。

不過在MySQL 5.5或5.1，強烈建議關閉這個變數 – 如果是開啟，像命令show table status會立即查詢INFORMATION_SCHEMA而不是等幾秒再執行，這會使用到額外的IO操作。

從5.1.32版本開始，這個是動態變數，意味著你不需要重啟MySQL伺服器來關閉它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個變數與性能無關，不過如果你偶爾重啟mysql伺服器(如生效配置)，那麼就有關。當兩個都激活時，MySQL緩衝池的內容(更具體地說，是緩存頁)在停止MySQL時存儲到一個文件。當你下次啟動MySQL時，它會在後台啟動一個線程來載入緩衝池的內容以提高預熱速度到3-5倍。

兩件事：

第一，它實際上沒有在關閉時複製緩衝池內容到文件，僅僅是複製表空間ID和頁面ID – 足夠的信息來定位硬碟上的頁面了。然後它就能以大量的順序讀非常快速的載入那些頁面，而不是需要成千上萬的小隨機讀。

第二，啟動時是在後台載入內容，因為MySQL不需要等到緩衝池內容載入完成再開始接受請求(所以看起來不會有什麼影響)。

從MySQL 5.7.7開始，默認只有25%的緩衝池頁面在mysql關閉時存儲到文件，但是你可以控制這個值 – 使用innodb_buffer_pool_dump_pct，建議75-100。

這個特性從MySQL 5.6才開始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你運行著一個大量SELECT查詢的MySQL伺服器(並且已經儘可能優化)，那麼自適應哈希索引將下你的下一個瓶頸。自適應哈希索引是InnoDB內部維護的動態索引，可以提高最常用的查詢模式的性能。這個特性可以重啟伺服器關閉，不過默認下在mysql的所有版本開啟。

這個技術非常複雜，在大多數情況下它會對大多數類型的查詢直到加速的作用。不過，當你有太多的查詢往資料庫，在某一個點上它會花過多的時間等待AHI鎖和閂鎖。

如果你的是MySQL 5.7，沒有這個問題 – innodb_adaptive_hash_index_parts默認設置為8，所以自適應哈希索引被切割為8個分區，因為不存在全局互斥。

不過在mysql 5.7前的版本，沒有AHI分區數量的控制。換句話說，有一個全局互斥鎖來保護AHI，可能導致你的select查詢經常撞牆。

所以如果你運行的是5.1或5.6，並且有大量的select查詢，最簡單的方案就是切換成同一版本的Percona Server來激活AHI分區。

14.QUERY_CACHE_TYPE

如果人認為查詢緩存效果很好，肯定應該使用它。好吧，有時候是有用的。不過這個只在你在低負載時有用，特別是在低負載下大多數是讀取，小量寫或者沒有。

如果是那樣的情況，設置query_cache_type=ON和query_cache_size=256M就好了。不過記住不能把256M設置更高的值了，否則會由於查詢緩存失效時，導致引起嚴重的伺服器停頓。

如果你的MySQL伺服器高負載動作，建議設置query_cache_size=0和query_cache_type=OFF，並重啟伺服器生效。那樣Mysql就會停止在所有的查詢使用查詢緩存互斥鎖。

15.TABLE_OPEN_CACHE_INSTANCES

從MySQL 5.6.6開始，表緩存能分割到多個分區。

表緩存用來存放目前已打開表的列表，當每一個表打開或關閉互斥體就被鎖定 – 即使這是一個隱式臨時表。使用多個分區絕對減少了潛在的爭用。

從MySQL 5.7.8開始，table_open_cache_instances=16是默認的配置。

歡迎做Java的工程師朋友們私信我資料免費獲取免費的Java架構學習資料（裡面有高可用、高並發、高性能及分散式、Jvm性能調優、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點的架構資料）

其中覆蓋了互聯網的方方面面，期間碰到各種產品各種場景下的各種問題，很值得大家借鑒和學習，擴展自己的技術廣度和知識面。

超詳細MySQL資料庫優化

資料庫優化一方面是找出系統的瓶頸,提高MySQL資料庫的整體性能,而另一方面需要合理的結構設計和參數調整,以提高用戶的相應速度,同時還要儘可能的節約系統資源,以便讓系統提供更大的負荷.

1. 優化一覽圖

2. 優化

筆者將優化分為了兩大類,軟優化和硬優化,軟優化一般是操作資料庫即可,而硬優化則是操作伺服器硬體及參數設置.

2.1 軟優化

2.1.1 查詢語句優化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執行信息.

2.例:

顯示:

其中會顯示索引和查詢數據讀取數據條數等信息.

2.1.2 優化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統開銷,而連接查詢不會創建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高資料庫查詢速度最重要的方法之一,關於索引可以參高筆者MySQL資料庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

對於欄位較多的表,如果某些欄位使用頻率較低,此時應當,將其分離出來從而形成新的表,

2.1.5 中間表

對於將大量連接查詢的表可以創建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗餘欄位

類似於創建中間表,增加冗餘也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優化表

分析表主要是分析表中關鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優化表主要是消除刪除或更新造成的表空間浪費.

1. 分析表: 使用 ANALYZE 關鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數值:

3. 優化表:使用OPTIMIZE關鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日誌.,優化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執行過程中會加上只讀鎖.

2.2 硬優化

2.2.1 硬體三件套

1.配置多核心和頻率高的cpu,多核心可以執行多個線程.

2.配置大內存,提高內存,即可提高緩存區容量,因此能減少磁碟I/O時間,從而提高響應速度.

3.配置高速磁碟或合理分布磁碟:高速磁碟提高I/O,分布磁碟能提高並行操作的能力.

2.2.2 優化資料庫參數

優化資料庫參數可以提高資源利用率,從而提高MySQL伺服器性能.MySQL服務的配置參數都在my.cnf或my.ini,下面列出性能影響較大的幾個參數.

2.2.3 分庫分表

因為資料庫壓力過大，首先一個問題就是高峰期系統性能可能會降低，因為資料庫負載過高對性能會有影響。另外一個，壓力過大把你的資料庫給搞掛了怎麼辦？所以此時你必須得對系統做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個資料庫服務上，這時作為主庫承載寫入請求。然後每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機器，比如說系統層面不停加機器，就可以承載更高的並發請求。然後資料庫層面如果寫入並發越來越高，就擴容加資料庫伺服器，通過分庫分表是可以支持擴容機器的，如果資料庫層面的讀並發越來越高，就擴容加更多的從庫。但是這裡有一個很大的問題：資料庫其實本身不是用來承載高並發請求的，所以通常來說，資料庫單機每秒承載的並發就在幾千的數量級，而且資料庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高並發架構里通常都有緩存這個環節，緩存系統的設計就是為了承載高並發而生。所以單機承載的並發量都在每秒幾萬，甚至每秒數十萬，對高並發的承載能力比資料庫系統要高出一到兩個數量級。所以你完全可以根據系統的業務特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫資料庫的時候同時寫一份數據到緩存集群里，然後用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的並發。

一個完整而複雜的高並發系統架構中，一定會包含：各種複雜的自研基礎架構系統。各種精妙的架構設計.因此一篇小文頂多具有拋磚引玉的效果,但是資料庫優化的思想差不多就這些了.

mysql資料庫最大能支持多少並發量

MySQL伺服器的最大並發連接數是16384。

受伺服器配置，及網路環境等制約，實際伺服器支持的並發連接數會小一些。主要決定因素有：

1、伺服器CPU及內存的配置。

2、網路的帶寬。互聯網連接中上行帶寬的影響尤為明顯。

擴展資料：

優化資料庫結構：

組織資料庫的schema、表和欄位以降低I/O的開銷，將相關項保存在一起，並提前規劃，以便隨著數據量的增長，性能可以保持較高的水平。

設計數據表應盡量使其佔用的空間最小化，表的主鍵應儘可能短。·對於InnoDB表，主鍵所在的列在每個輔助索引條目中都是可複製的，因此如果有很多輔助索引，那麼一個短的主鍵可以節省大量空間。

僅創建需要改進查詢性能的索引。索引有助於檢索，但是會增加插入和更新操作的執行時間。

InnoDB的ChangeBuffering特性：

InnoDB提供了changebuffering的配置，可減少維護輔助索引所需的磁碟I/O。大規模的資料庫可能會遇到大量的表操作和大量的I/O，以保證輔助索引保持最新。當相關頁面不在緩衝池裡面時，InnoDB的changebuffer將會更改緩存到輔助索引條目。

從而避免因不能立即從磁碟讀取頁面而導致耗時的I/O操作。當頁面被載入到緩衝池時，緩衝的更改將被合併，更新的頁面之後會刷新到磁碟。這樣做可提高性能，適用於MySQL5.5及更高版本。

參考資料來源：百度百科-MySQL資料庫