Mysql 優化(學習筆記二十)

https://blog.csdn.net/steven_liwen/article/details/53188411

運維角度mysql優化：https://www.cnblogs.com/shenjianyu/p/6405524.html

1、目的：

通過根據伺服器目前狀況，修改MySQL的系統引數，達到合理利用伺服器現有資源，最大合理的提高MySQL效能。

2、伺服器引數：

32G記憶體、4個CPU,每個CPU 8核。

3、MySQL目前安裝狀況。

    MySQL目前安裝，用的是MySQL預設的最大支援配置。拷貝的是my-huge.cnf.編碼已修改為UTF-8.具體修改及安裝MySQL,可以參考<<Linux系統上安裝MySQL 5.5>>幫助文件。

4、修改MySQL配置

開啟MySQL配置檔案my.cnf

vi  /etc/my.cnf

4.1 MySQL非快取引數變數介紹及修改

4.1.1修改back_log引數值:由預設的50修改為500.（每個連線256kb,佔用：125M）

          back_log=500

    back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆疊中。也就是說，如果MySql的連線資料達到max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log，如果等待連線的數量超過back_log，將不被授予連線資源。將會報：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連線程式時.

back_log值不能超過TCP/IP連線的偵聽佇列的大小。若超過則無效，檢視當前系統的TCP/IP連線的偵聽佇列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系統為1024。對於Linux系統推薦設定為小於512的整數。

修改系統核心引數，）http://www.51testing.com/html/64/n-810764.html

檢視mysql 當前系統預設back_log值，命令：

show variables like 'back_log'; 檢視當前數量

4.1.2修改wait_timeout引數值，由預設的8小時，修改為30分鐘。(本次不用)

          wait_timeout=1800（單位為妙）

我對wait-timeout這個引數的理解：MySQL客戶端的資料庫連線閒置最大時間值。

說得比較通俗一點，就是當你的MySQL連線閒置超過一定時間後將會被強行關閉。MySQL預設的wait-timeout  值為8個小時，可以通過命令show variables like 'wait_timeout'檢視結果值;。

設定這個值是非常有意義的，比如你的網站有大量的MySQL連結請求（每個MySQL連線都是要記憶體資源開銷的 ），由於你的程式的原因有大量的連線請求空閒啥事也不幹，白白佔用記憶體資源，或者導致MySQL超過最大連線數從來無法新建連線導致“Too many connections”的錯誤。在設定之前你可以檢視一下你的MYSQL的狀態（可用show processlist)，如果經常發現MYSQL中有大量的Sleep程式，則需要 修改wait-timeout值了。

interactive_timeout：伺服器關閉互動式連線前等待活動的秒數。互動式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。

wait_timeout:伺服器關閉非互動連線之前等待活動的秒數。線上程啟動時，根據全域性wait_timeout值或全域性 interactive_timeout值初始化會話wait_timeout值，取決於客戶端型別(由mysql_real_connect()的連線選項CLIENT_INTERACTIVE定義).

這兩個引數必須配合使用。否則單獨設定wait_timeout無效

4.1.3修改max_connections引數值，由預設的151，修改為3000（750M）。

    max_connections=3000

max_connections是指MySql的最大連線數，如果伺服器的併發連線請求量比較大，建議調高此值，以增加並行連線數量，當然這建立在機器能支撐的情況下，因為如果連線數越多，介於MySql會為每個連線提供連線緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。可以過'conn%'萬用字元檢視當前狀態的連線數量，以定奪該值的大小。

MySQL伺服器允許的最大連線數16384；

檢視系統當前最大連線數：

show variables like 'max_connections';

4.1..4修改max_user_connections值，由預設的0，修改為800

     max_user_connections=800

 max_user_connections是指每個資料庫使用者的最大連線

針對某一個賬號的所有客戶端並行連線到MYSQL服務的最大並行連線數。簡單說是指同一個賬號能夠同時連線到mysql服務的最大連線數。設定為0表示不限制。

目前預設值為：0不受限制。

這兒順便介紹下Max_used_connections:它是指從這次mysql服務啟動到現在，同一時刻並行連線數的最大值。它不是指當前的連線情況，而是一個比較值。如果在過去某一個時刻，MYSQL服務同時有1000個請求連線過來，而之後再也沒有出現這麼大的併發請求時，則Max_used_connections=1000.請注意與show variables 裡的max_user_connections的區別。預設為0表示無限大。

檢視max_user_connections值

show variables like 'max_user_connections';

4.1.5修改thread_concurrency值，由目前預設的8，修改為64

     thread_concurrency=64

thread_concurrency的值的正確與否,對mysql的效能影響很大,在多個cpu(或多核)的情況下，錯誤設定了thread_concurrency的值,會導致mysql不能充分利用多cpu(或多核),出現同一時刻只能一個cpu(或核)在工作的情況。

thread_concurrency應設為CPU核數的2倍.比如有一個雙核的CPU,那thread_concurrency  的應該為4; 2個雙核的cpu,thread_concurrency的值應為8.

比如：根據上面介紹我們目前系統的配置，可知道為4個CPU,每個CPU為8核，按照上面的計算規則，這兒應為:4*8*2=64

檢視系統當前thread_concurrency預設配置命令：

 show variables like 'thread_concurrency';

4.1.6新增skip-name-resolve，預設被註釋掉，沒有該引數。

skip-name-resolve

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

4.1.7 skip-networking，預設被註釋掉。沒有該引數。（本次無用）

 skip-networking建議被註釋掉，不要開啟

開啟該選項可以徹底關閉MySQL的TCP/IP連線方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項！否則將無法正常連線！

4.1.8  default-storage-engine(設定MySQL的預設儲存引擎)

default-storage-engine= InnoDB(設定InnoDB型別，另外還可以設定MyISAM型別)

設定建立資料庫及表預設儲存型別

show table status like ‘tablename’顯示錶的當前儲存狀態值

檢視MySQL有哪些儲存狀態及預設儲存狀態

 show engines;

建立表並指定儲存型別

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表儲存型別：

  Alter table tableName engine =engineName

備註：設定完後把以下幾個開啟：

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend（要註釋掉，否則會建立一個新的把原來的替換的。）

innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

設定完後一定記得把MySQL安裝目錄地址（我們目前是預設安裝所以地址/var/lib/mysql/）下的ib_logfile0和ib_logfile1刪除掉。否則重啟MySQL起動失敗。

4.2 MySQL快取變數介紹及修改

資料庫屬於IO密集型的應用程式，其主職責就是資料的管理及儲存工作。而我們知道，從記憶體中讀取一個資料庫的時間是微秒級別，而從一塊普通硬碟上讀取一個IO是在毫秒級別，二者相差3個數量級。所以，要優化資料庫，首先第一步需要優化的就是IO，儘可能將磁碟IO轉化為記憶體IO。本文先從MySQL資料庫IO相關引數(快取引數)的角度來看看可以通過哪些引數進行IO優化

4.2.1全域性快取

啟動MySQL時就要分配並且總是存在的全域性快取。目前有：key_buffer_size(預設值：402653184,即384M)、innodb_buffer_pool_size(預設值：134217728即：128M)、innodb_additional_mem_pool_size（預設值：8388608即：8M）、innodb_log_buffer_size(預設值：8388608即：8M)、query_cache_size(預設值：33554432即：32M)等五個。總共：560M.

這些變數值都可以通過命令如：show variables like '變數名';檢視到。

4.2.1.1：key_buffer_size,本系統目前為384M,可修改為400M

    key_buffer_size=400M

key_buffer_size是用於索引塊的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，對MyISAM(MySQL表儲存的一種型別，可以百度等檢視詳情)表效能影響最大的一個引數。如果你使它太大，系統將開始換頁並且真的變慢了。嚴格說是它決定了資料庫索引處理的速度，尤其是索引讀的速度。對於記憶體在4GB左右的伺服器該引數可設定為256M或384M.

怎麼才能知道key_buffer_size的設定是否合理呢，一般可以檢查狀態值Key_read_requests和Key_reads，比例key_reads / key_read_requests應該儘可能的低，比如1:100，1:1000，1:10000。其值可以用以下命令查得：show status like 'key_read%';

比如檢視系統當前key_read和key_read_request值為：

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Key_read_requests | 28535 |

| Key_reads         | 269   |

+-------------------+-------+

可知道有28535個請求，有269個請求在記憶體中沒有找到直接從硬碟讀取索引.

未命中快取的概率為：0.94%=269/28535*100%.一般未命中概率在0.1之下比較好。目前已遠遠大於0.1，證明效果不好。若命中率在0.01以下，則建議適當的修改key_buffer_size值。

http://dbahacker.com/mysql/innodb-myisam-compare(InnoDB與MyISAM的六大區別)

http://kb.cnblogs.com/page/99810/（檢視儲存引擎介紹）

MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive

4.2.1.2：innodb_buffer_pool_size(預設128M)

innodb_buffer_pool_size=1024M(1G)

innodb_buffer_pool_size:主要針對InnoDB表效能影響最大的一個引數。功能與Key_buffer_size一樣。InnoDB佔用的記憶體，除innodb_buffer_pool_size用於儲存頁面快取資料外，另外正常情況下還有大約8%的開銷，主要用在每個快取頁幀的描述、adaptive hash等資料結構，如果不是安全關閉，啟動時還要恢復的話，還要另開大約12%的記憶體用於恢復，兩者相加就有差不多21%的開銷。假設：12G的innodb_buffer_pool_size，最多的時候InnoDB就可能佔用到14.5G的記憶體。若系統只有16G，而且只執行MySQL，且MySQL只用InnoDB，

那麼為MySQL開12G，是最大限度地利用記憶體了。

另外InnoDB和 MyISAM 儲存引擎不同， MyISAM 的 key_buffer_size 只能快取索引鍵，而 innodb_buffer_pool_size 卻可以快取資料塊和索引鍵。適當的增加這個引數的大小，可以有效的減少 InnoDB 型別的表的磁碟 I/O 。

當我們操作一個InnoDB表的時候，返回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體區域中走一遭。

可以通過(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%計算快取命中率，並根據命中率來調整innodb_buffer_pool_size引數大小進行優化。值可以用以下命令查得：show status like 'Innodb_buffer_pool_read%';

比如檢視當前系統中系統中

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

+---------------------------------------+---------+

其命中率99.959%=（1283826-519）/1283826*100%命中率越高越好。

4.2.1.3：innodb_additional_mem_pool_size(預設8M)

  innodb_additional_mem_pool_size=20M

innodb_additional_mem_pool_size設定了InnoDB儲存引擎用來存放資料字典資訊以及一些內部資料結構的記憶體空間大小，所以當我們一個MySQL Instance中的資料庫物件非常多的時候，是需要適當調整該引數的大小以確保所有資料都能存放在記憶體中提高訪問效率的。

這個引數大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL會記錄Warning資訊到資料庫的error log中，這時候你就知道該調整這個引數大小了。

檢視當前系統mysql的error日誌cat  /var/lib/mysql/機器名.error發現有很多waring警告。所以要調大為20M.

根據MySQL手冊，對於2G記憶體的機器，推薦值是20M。

    32G記憶體的 100M

4.2.1.4：innodb_log_buffer_size(預設8M)

innodb_log_buffer_size=20M

innodb_log_buffer_size  這是InnoDB儲存引擎的事務日誌所使用的緩衝區。類似於Binlog Buffer，InnoDB在寫事務日誌的時候，為了提高效能，也是先將資訊寫入Innofb Log Buffer中，當滿足innodb_flush_log_trx_commit引數所設定的相應條件(或者日誌緩衝區寫滿)之後，才會將日誌寫到檔案 (或者同步到磁碟)中。可以通過innodb_log_buffer_size 引數設定其可以使用的最大記憶體空間。

InnoDB 將日誌寫入日誌磁碟檔案前的緩衝大小。理想值為 1M 至 8M。大的日誌緩衝允許事務執行時不需要將日誌儲存入磁碟而只到事務被提交(commit)。 因此，如果有大的事務處理，設定大的日誌緩衝可以減少磁碟I/O。 在my.cnf中以數字格式設定。

預設是8MB，系的如頻繁的系統可適當增大至4MB～8MB。當然如上面介紹所說，這個引數實際上還和另外的flush引數相關。一般來說不建議超過32MB

注：innodb_flush_log_trx_commit引數對InnoDB Log的寫入效能有非常關鍵的影響,預設值為1。該引數可以設定為0，1，2，解釋如下：

0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作;

1：在每次事務提交的時候將log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步;

2：事務提交會觸發log buffer到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。

實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄只需要2秒，設定為0時只需要1秒，而設定為1時則需要229秒。因此，MySQL手冊也建議儘量將插入操作合併成一個事務，這樣可以大幅提高速度。根據MySQL手冊，在存在丟失最近部分事務的危險的前提下，可以把該值設為0。

4.5.1.5：query_cache_size(預設32M)

query_cache_size=40M

query_cache_size:主要用來快取MySQL中的ResultSet，也就是一條SQL語句執行的結果集，所以僅僅只能針對select語句。當我們開啟了Query Cache功能，MySQL在接受到一條select語句的請求後，如果該語句滿足Query Cache的要求(未顯式說明不允許使用Query Cache，或者已經顯式申明需要使用Query Cache)，MySQL會直接根據預先設定好的HASH演算法將接受到的select語句以字串方式進行hash，然後到Query Cache中直接查詢是否已經快取。也就是說，如果已經在快取中，該select請求就會直接將資料返回，從而省略了後面所有的步驟(如SQL語句的解析，優化器優化以及向儲存引擎請求資料等)，極大的提高效能。根據MySQL使用者手冊，使用查詢緩衝最多可以達到238%的效率。

當然，Query Cache也有一個致命的缺陷，那就是當某個表的資料有任何任何變化，都會導致所有引用了該表的select語句在Query Cache中的快取資料失效。所以，當我們的資料變化非常頻繁的情況下，使用Query Cache可能會得不償失

Query Cache的使用需要多個引數配合，其中最為關鍵的是query_cache_size和query_cache_type，前者設定用於快取ResultSet的記憶體大小，後者設定在何場景下使用Query Cache。在以往的經驗來看，如果不是用來快取基本不變的資料的MySQL資料庫，query_cache_size一般256MB是一個比較合適的大小。當然，這可以通過計算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進行調整。query_cache_type可以設定為0(OFF)，1(ON)或者2(DEMOND)，分別表示完全不使用query cache，除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，只有顯示要求才使用query cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝.如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；

根據命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進行調整，一般不建議太大，256MB可能已經差不多了，大型的配置型靜態資料可適當調大.

可以通過命令：show status like 'Qcache_%';檢視目前系統Query catch使用大小

| Qcache_hits             | 1892463  |

| Qcache_inserts          | 35627  

命中率98.17%=1892463/(1892463 +35627 )*100

4.2.2區域性快取

除了全域性緩衝，MySql還會為每個連線發放連線緩衝。個連線到MySQL伺服器的執行緒都需要有自己的緩衝。大概需要立刻分配256K，甚至線上程空閒時，它們使用預設的執行緒堆疊，網路快取等。事務開始之後，則需要增加更多的空間。執行較小的查詢可能僅給指定的執行緒增加少量的記憶體消耗，然而如果對資料表做複雜的操作例如掃描、排序或者需要臨時表，則需分配大約read_buffer_size，

sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的記憶體空間.不過它們只是在需要的時候才分配，並且在那些操作做完之後就釋放了。有的是立刻分配成單獨的組塊。tmp_table_size可能高達MySQL所能分配給這個操作的最大記憶體空間了

。注意，這裡需要考慮的不只有一點——可能會分配多個同一種型別的快取，例如用來處理子查詢。一些特殊的查詢的記憶體使用量可能更大——如果在MyISAM表上做成批的插入

時需要分配bulk_insert_buffer_size大小的記憶體；執行ALTER TABLE，OPTIMIZE TABLE，REPAIR TABLE命令時需要分配myisam_sort_buffer_size大小的記憶體。

4.2.2.1：read_buffer_size（預設值：2097144即2M）

read_buffer_size=4M

read_buffer_size是MySql讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySql會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一

緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能.

4.2.2.2：sort_buffer_size（預設值：2097144即2M）

sort_buffer_size=4M

sort_buffer_size是MySql執行排序使用的緩衝大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變數的大小

4.2.2.3:  read_rnd_buffer_size(預設值：8388608即8M)

read_rnd_buffer_size=8M

read_rnd_buffer_size是MySql的隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀快取區。進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySql會為每個客戶連線發放該緩衝空間，所以應儘量適當設定該值，以避免記憶體開

銷過大。

4.2.2.4:  tmp_table_size(預設值：8388608即：16M)

tmp_table_size=16M

tmp_table_size是MySql的heap（堆積）表緩衝大小。所有聯合在一個DML指令內完成，並且大多數聯合甚至可以不用臨時表即可以完成。大多數臨時表是基於內

存的(HEAP)表。具有大的記錄長度的臨時表(所有列的長度的和)或包含BLOB列的表儲存在硬碟上。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自

動將記憶體中的heap表改為基於硬碟的MyISAM表。還可以通過設定tmp_table_size選項來增加臨時表的大小。也就是說，如果調高該值，MySql同時將增加heap表的大小，可達到提高

聯接查詢速度的效果。

4.2.2.5：record_buffer:（預設值：）

record_buffer每個進行一個順序掃描的執行緒為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。預設數值是131072

(128K)

4.2.3其它快取：

4.2.3.1：table_cache(預設值：512)

TABLE_CACHE(5.1.3及以後版本又名TABLE_OPEN_CACHE)

table_cache指定表快取記憶體的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了（上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者連線失敗。

SHOW STATUS LIKE 'Open%tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables   | 356   |

| Opened_tables | 0     |

+---------------+-------+

2 rows in set (0.00 sec)

open_tables表示當前開啟的表快取數，如果執行flush tables操作，則此係統會關閉一些當前沒有使用的表快取而使得此狀態值減小；

opend_tables表示曾經開啟的表快取數，會一直進行累加，如果執行flush tables操作，值不會減小。

在mysql預設安裝情況下，table_cache的值在2G記憶體以下的機器中的值預設時256到512，如果機器有4G記憶體,則預設這個值 是2048，但這決意味著機器記憶體越大，這個值應該越大，因為table_cache加大後，使得mysql對SQL響應的速度更快了，不可避免的會產生 更多的死鎖（dead lock），這樣反而使得資料庫整個一套操作慢了下來，嚴重影響效能。所以平時維護中還是要根據庫的實際情況去作出判斷，找到最適合你維護的庫的 table_cache值。

由於MySQL是多執行緒的機制,為了提高效能,每個執行緒都是獨自開啟自己需要的表的檔案描 述符,而不是通過共享已經開啟的.針對不同儲存引擎處理的方法當然也不一樣

在myisam表引擎中,資料檔案的描述符 (descriptor)是不共享的,但是索引檔案的描述符卻是所有執行緒共享的.Innodb中和使用表空間型別有關,假如是共享表空間那麼實際就一個數 據檔案,當然佔用的資料檔案描述符就會比獨立表空間少.

mysql手冊上給的建議大小 是:table_cache=max_connections*n

n表示查詢語句中最大表數, 還需要為臨時表和檔案保留一些額外的檔案描述符。

這個資料遭到很多質疑,table_cache夠用就好,檢查 Opened_tables值,如果這個值很大,或增長很快那麼你就得考慮加大table_cache了.

  table_cache：所有執行緒開啟的表的數目。增大該值可以增加mysqld需要的檔案描述符的數量。預設值是64.

4.2.3.2 thread_cache_size (伺服器執行緒快取)

thread_cache_size=64

預設的thread_cache_size=8，但是看到好多配置的樣例裡的值一般是32，64，甚至是128，感覺這個引數對優化應該有幫助，於是查了下：

根據調查發現以上伺服器執行緒快取thread_cache_size沒有進行設定，或者設定過小,這個值表示可以重新利用儲存在快取中執行緒的數量,當斷開連線時如果快取中還有空間,那麼客戶端的執行緒將被放到快取中,如果執行緒重新被請求，那麼請求將從快取中讀取,如果快取中是空的或者是新的請求，那麼這個執行緒將被重新建立,如果有很多新的執行緒，增加這個值可以改善系統效能.通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。(–>表示要調整的值)根據實體記憶體設定規則如下：

1G —> 8

2G —> 16

3G —> 32     >3G —> 64

mysql> show status like 'thread%';

+——————-+——-+

| Variable_name     | Value |

+——————-+——-+

| Threads_cached    | 0     |  <—當前被快取的空閒執行緒的數量

| Threads_connected | 1     |  <—正在使用（處於連線狀態）的執行緒

| Threads_created   | 1498  |  <—服務啟動以來，建立了多少個執行緒

| Threads_running   | 1     |  <—正在忙的執行緒（正在查詢資料，傳輸資料等等操作）

+——————-+——-+

檢視開機起來資料庫被連線了多少次？

mysql> show status like '%connection%';

+———————-+——-+

| Variable_name        | Value |

+———————-+——-+

| Connections          | 1504  |          –>服務啟動以來，歷史連線數

| Max_used_connections | 2     |

+———————-+——-+

通過連線執行緒池的命中率來判斷設定值是否合適？命中率超過90%以上,設定合理。

 (Connections -  Threads_created) / Connections * 100 %

1、MySQL引數優化

1:MySQL 預設的最大連線數為 100，可以在 mysql 客戶端使用以下命令檢視

mysql> show variables like 'max_connections';

2:檢視當前訪問Mysql的執行緒

mysql> show processlist;

3:設定最大連線數

mysql>set globle max_connections = 5000;

最大可設定16384,超過沒用

4:檢視當前被使用的connections

mysql>show globle status like 'max_user_connections'

1、磁碟尋道能力（磁碟I/O），我們現在用的都是SAS15000轉的硬碟，用6快這樣的硬碟作RAID1+0。MySQL每一秒鐘都在進行大量、複雜的查詢操作，對磁碟的讀寫量可想而知，所以，通常認為磁碟I/O是約制MySQL效能的最大因素之一。對於日均訪問量在100萬PV以上的論壇（Discuz）、部落格（Wordpress），如果效能不好，造成的直接後果就是MySQL的效能會非常低下！解決這一制約因素可以考慮解決訪問是：使用RAID1+0磁碟陣列，注意不要嘗試RAID5，MySQL在PAID5磁碟陣列上的效率不會像你期待的那樣快，如果資金允許，可以選擇固態硬碟SSD來替代SAS硬碟做RAID1+0。

3、對於一臺使用MySQL的Database Server來說，建議伺服器的記憶體不要小於2GB，推薦使用4GB以上的實體記憶體，不過記憶體對於現在的伺服器而言可以說是一個可以忽略的問題，如果是高階伺服器，基本上記憶體都超過了32GB，我們的資料流伺服器都是32GB記憶體。

MySQL配置檔案優化

[client]

port = 3306 #客戶端埠號為3306

socket = /data/3306/mysql.sock #

default-character-set = utf8 #客戶端字符集,(控制character_set_client、character_set_connection、character_set_results)

[mysql]

no-auto-rehash #僅僅允許使用鍵值的updates和deletes

[mysqld] #組包括了mysqld服務啟動的引數，它涉及的方面很多，其中有MySQL的目錄和檔案，通訊、網路、資訊保安，記憶體管理、優化、查詢快取區，還有MySQL日誌設定等。

user = mysql #mysql_safe指令碼使用MySQL執行使用者(編譯時–user=mysql指定),推薦使用mysql使用者。

port = 3306 #MySQL服務執行時的埠號。建議更改預設埠,預設容易遭受攻擊。

socket = /data/3306/mysql.sock #socket檔案是在Linux/Unix環境下特有的，使用者在Linux/Unix環境下客戶端連線可以不通過TCP/IP網路而直接使用unix socket連線MySQL。

basedir = /application/mysql #mysql程式所存放路徑,常用於存放mysql啟動、配置檔案、日誌等

datadir = /data/3306/data #MySQL資料存放檔案(極其重要)

character-set-server = utf8 #資料庫和資料庫表的預設字符集。(推薦utf8,以免導致亂碼)

log-error=/data/3306/mysql_xuliangwei.err #mysql錯誤日誌存放路徑及名稱(啟動出現錯誤一定要看錯誤日誌,百分之百都能通過錯誤日誌排插解決。)

pid-file=/data/3306/mysql_xuliangwei.pid #MySQL_pid檔案記錄的是當前mysqld程式的pid，pid亦即 ProcessID。

skip-locking #避免MySQL的外部鎖定，減少出錯機率，增強穩定性。

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

skip-networking #開啟該選項可以徹底關閉MySQL的TCP/IP連線方式，如果Web伺服器是以遠端連線的方式訪問MySQL資料庫伺服器的，則不要開啟該選項，否則無法正常連線！

open_files_limit = 1024 #MySQLd能開啟檔案的最大個數,如果出現too mant open files之類的就需要調整該值了。

back_log = 384 #back_log引數是值指出在MySQL暫時停止響應新請求之前，短時間內的多少個請求可以被存在堆疊中。如果系統在短時間內有很多連線，則需要增加該引數的值，該引數值指定到來的TCP/IP連線的監聽佇列的大小。不同的作業系統在這個佇列的大小上有自己的限制。如果試圖將back_log設定得高於作業系統的限制將是無效的，其預設值為50.對於Linux系統而言，推薦設定為小於512的整數。

max_connections = 800 #指定MySQL允許的最大連線程式數。如果在訪問部落格時經常出現

Too Many Connections

的錯誤提示，則需要增大該引數值。

max_connect_errors = 6000 #設定每個主機的連線請求異常中斷的最大次數，當超過該次數，MySQL伺服器將禁止host的連線請求，直到MySQL伺服器重啟或通過flush hosts命令清空此host的相關資訊。

wait_timeout = 120 #指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器來說，可以將其設定為5~10。

table_cache = 614K #table_cache指示表高速緩衝區的大小。當MySQL訪問一個表時，如果在MySQL緩衝區還有空間，那麼這個表就被開啟並放入表緩衝區，這樣做的好處是可以更快速地訪問表中的內容。一般來說，可以檢視資料庫執行峰值時間的狀態值Open_tables和Open_tables，用以判斷是否需要增加table_cache的值，即如果Open_tables接近table_cache的時候，並且Opened_tables這個值在逐步增加，那就要考慮增加這個值的大小了。

external-locking = FALSE #MySQL選項可以避免外部鎖定。True為開啟。

max_allowed_packet =16M #伺服器一次能處理最大的查詢包的值，也是伺服器程式能夠處理的最大查詢

sort_buffer_size = 1M #設定查詢排序時所能使用的緩衝區大小，系統預設大小為2MB。

注意：該引數對應的分配記憶體是每個連線獨佔的，如果有100個連線，那麼實際分配的總排序緩衝區大小為100 x6=600MB。所以，對於記憶體在4GB左右的伺服器來說，推薦將其設定為6MB~8MB

join_buffer_size = 8M #聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該引數對應的分配記憶體也是每個連線獨享。

thread_cache_size = 64 #設定Thread Cache池中可以快取的連線執行緒最大數量，可設定為0~16384，預設為0.這個值表示可以重新利用儲存在快取中執行緒的數量，當斷開連線時如果快取中還有空間，那麼客戶端的執行緒將被放到快取中;如果執行緒重新被請求，那麼請求將從快取中讀取,如果快取中是空的或者是新的請求，那麼這個執行緒將被重新建立，如果有很多執行緒，增加這個值可以改善系統效能。通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。我們可以根據實體記憶體設定規則如下:1GB記憶體我們配置為8,2GB記憶體我們配置為16,3GB我們配置為32,4GB或4GB以上我們給此值為64或更大的值。

thread_concurrency = 8 #該引數取值為伺服器邏輯CPU數量x 2，在本例中，伺服器有兩個物理CPU，而每個物理CPU又支援H.T超執行緒，所以實際取值為4 x 2 = 8。這也是雙四核主流伺服器的配置。

query_cache_size = 64M #指定MySQL查詢緩衝區的大小。可以通過在MySQL控制檯觀察，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況;如果Qcache_hits的值非常大，則表明查詢緩衝使用得非常頻繁。另外如果改值較小反而會影響效率，那麼可以考慮不用查詢緩衝。對於Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。

query_cache_limit = 2M #只有小於此設定值的結果才會被快取

query_cache_min_res_unit = 2k #設定查詢快取分配記憶體的最小單位，要適當第設定此引數，可以做到為減少記憶體快的申請和分配次數，但是設定過大可能導致記憶體碎片數值上升。預設值為4K，建議設定為1K~16K。

default_table_type = InnoDB #預設表的型別為InnoDB

thread_stack = 256K #設定MySQL每個執行緒的堆疊大小，預設值足夠大，可滿足普通操作。可設定範圍為128KB至4GB，預設為192KB

#transaction_isolation = Level #資料庫隔離級別 (READ UNCOMMITTED(讀取未提交內容) READ COMMITTED(讀取提交內容) REPEATABLE READ(可重讀) SERIALIZABLE(可序列化))

tmp_table_size = 64M #設定記憶體臨時表最大值。如果超過該值，則會將臨時表寫入磁碟，其範圍1KB到4GB。

max_heap_table_size = 64M #獨立的記憶體表所允許的最大容量。

table_cache = 614 #給經常訪問的表分配的記憶體，實體記憶體越大，設定就越大。調大這個值，一般情況下可以降低磁碟IO，但相應的會佔用更多的記憶體,這裡設定為614。

table_open_cache = 512  #設定表快取記憶體的數目。每個連線進來，都會至少開啟一個表快取。因此， table_cache 的大小應與max_connections 的設定有關。例如，對於 200 個並行執行的連線，應該讓表的快取至少有 200 × N ，這裡 N 是應用可以執行的查詢的一個聯接中表的最大數量。此外，還需要為臨時表和檔案保留一些額外的檔案描述符。

long_query_time = 1 #慢查詢的執行用時上限,預設設定是10s,推薦(1s~2s)

log_long_format #沒有使用索引的查詢也會被記錄。(推薦,根據業務來調整)

log-slow-queries = /data/3306/slow.log #慢查詢日誌檔案路徑(如果開啟慢查詢,建議開啟此日誌)

log-bin = /data/3306/mysql-bin #logbin資料庫的操作日誌,例如update、delete、create等都會儲存到binlog日誌,通過logbin可以實現增量恢復

relay-log = /data/3306/relay-bin #relay-log日誌記錄的是從伺服器I/O執行緒將主伺服器的二進位制日誌讀取過來記錄到從伺服器本地檔案,然後SQL執行緒會讀取relay-log日誌的內容並應用到從伺服器

relay-log-info-file = /data/3306/relay-log.info #從伺服器用於記錄中繼日誌相關資訊的檔案,預設名為資料目錄中的relay-log.info。

binlog_cache_size = 4M #在一個事務中binlog為了記錄sql狀態所持有的cache大小，如果你經常使用大的，多宣告的事務，可以增加此值來獲取更大的效能，所有從事務來的狀態都被緩衝在binlog緩衝中，然後再提交後一次性寫入到binlog中，如果事務比此值大，會使用磁碟上的臨時檔案來替代，此緩衝在每個連結的事務第一次更新狀態時被建立。

max_binlog_cache_size = 8M #最大的二進位制Cache日誌緩衝尺寸。

max_binlog_size = 1G #二進位制日誌檔案的最大長度(預設設定1GB)一個二進位制檔案資訊超過了這個最大長度之前,MySQL伺服器會自動提供一個新的二進位制日誌檔案接續上。

expire_logs_days = 7 #超過7天的binlog,mysql程式自動刪除(如果資料重要,建議不要開啟該選項)

key_buffer_size = 256M #指定用於索引的緩衝區大小，增加它可得到更好的索引處理效能。對於記憶體在4GB左右的伺服器來說，該引數可設定為256MB或384MB。

注意：如果該引數值設定得過大反而會使伺服器的整體效率降低！

read_buffer_size = 4M #讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該引數對應的分配記憶體也是每個連線獨享。

read_rnd_buffer_size = 16M #設定進行隨機讀的時候所使用的緩衝區。此引數和read_buffer_size所設定的Buffer相反，一個是順序讀的時候使用，一個是隨機讀的時候使用。但是兩者都是針對與執行緒的設定，每個執行緒都可以產生兩種Buffer中的任何一個。預設值256KB，最大值4GB。

bulk_insert_buffer_size = 8M #如果經常性的需要使用批量插入的特殊語句來插入資料,可以適當調整引數至16MB~32MB,建議8MB。

#myisam_sort_buffer_size = 8M #設定在REPAIR Table或用Create index建立索引或 Alter table的過程中排序索引所分配的緩衝區大小，可設定範圍4Bytes至4GB，預設為8MB

lower_case_table_names = 1 #實現MySQL不區分大小。(發開需求–建議開啟)

slave-skip-errors = 1032,1062 #從庫可以跳過的錯誤數字值(mysql錯誤以數字程式碼反饋,全的mysql錯誤程式碼大全,以後會發布至部落格)。

replicate-ignore-db=mysql #在做主從的情況下,設定不需要同步的庫。

server-id = 1 #表示本機的序列號為1,如果做主從，或者多例項,serverid一定不能相同。

myisam_sort_buffer_size = 128M #當需要對於執行REPAIR, OPTIMIZE, ALTER 語句重建索引時，MySQL會分配這個快取，以及LOAD DATA INFILE會載入到一個新表，它會根據最大的配置認真的分配的每個執行緒。

myisam_max_sort_file_size = 10G#當重新建索引（REPAIR，ALTER，TABLE，或者LOAD，DATA，TNFILE）時，MySQL被允許使用臨時檔案的最大值。

myisam_repair_threads = 1#如果一個表擁有超過一個索引, MyISAM 可以通過並行排序使用超過一個執行緒去修復他們.

myisam_recover#自動檢查和修復沒有適當關閉的 MyISAM 表.

innodb_additional_mem_pool_size = 4M #用來設定InnoDB儲存的資料目錄資訊和其他內部資料結構的記憶體池大小。應用程式裡的表越多，你需要在這裡面分配越多的記憶體。對於一個相對穩定的應用，這個引數的大小也是相對穩定的，也沒有必要預留非常大的值。如果InnoDB用廣了這個池內的記憶體，InnoDB開始從作業系統分配記憶體，並且往MySQL錯誤日誌寫警告資訊。預設為1MB，當發現錯誤日誌中已經有相關的警告資訊時，就應該適當的增加該引數的大小。

innodb_buffer_pool_size = 64M #InnoDB使用一個緩衝池來儲存索引和原始資料，設定越大，在存取表裡面資料時所需要的磁碟I/O越少。強烈建議不要武斷地將InnoDB的Buffer Pool值配置為實體記憶體的50%~80%，應根據具體環境而定。

innodb_data_file_path = ibdata1:128M:autoextend #設定配置一個可擴充套件大小的尺寸為128MB的單獨檔案，名為ibdata1.沒有給出檔案的位置，所以預設的是在MySQL的資料目錄內。

innodb_file_io_threads = 4 #InnoDB中的檔案I/O執行緒。通常設定為4，如果是windows可以設定更大的值以提高磁碟I/O

innodb_thread_concurrency = 8 #你的伺服器有幾個CPU就設定為幾，建議用預設設定，一般設為8。

innodb_flush_log_at_trx_commit = 1 #設定為0就等於innodb_log_buffer_size佇列滿後在統一儲存，預設為1，也是最安全的設定。

innodb_log_buffer_size = 2M #預設為1MB，通常設定為8~16MB就足夠了。

innodb_log_file_size = 32M #確定日誌檔案的大小，更大的設定可以提高效能，但也會增加恢復資料庫的時間。

innodb_log_files_in_group = 3 #為提高效能,MySQL可以以迴圈方式將日誌檔案寫到多個檔案。推薦設定為3。

innodb_max_dirty_pages_pct = 90 #InnoDB主執行緒重新整理快取池中的資料。

innodb_lock_wait_timeout = 120 #InnoDB事務被回滾之前可以等待一個鎖定的超時秒數。InnoDB在它自己的鎖定表中自動檢測事務死鎖並且回滾事務。InnoDB用locak tables 語句注意到鎖定設定。預設值是50秒。

innodb_file_per_table = 0 #InnoDB為獨立表空間模式，每個資料庫的每個表都會生成一個資料空間。0關閉，1開啟。

獨立表空間優點：

1、每個表都有自己獨立的表空間。

2、每個表的資料和索引都會存在自己的表空間中。

3、可以實現單表在不同的資料庫中移動。

4、空間可以回收（除drop table操作處，表空不能自己回收。）

[mysqldump]

quick

max_allowed_packet = 2M #設定在網路傳輸中一次訊息傳輸量的最大值。系統預設值為1MB，最大值是1GB，必須設定為1024的倍數。單位為位元組。

[mysqld_safe]

值得注意：

強烈建議不要武斷地將InnoDB的Buffer Pool值配置為實體記憶體的50%~80%，應根據具體環境而定。

如果key_reads太大，則應該把my.cnf中的key_buffer_size變大，保持key_reads/key_read_re-quests至少在1/100以上，越小越好。

如果qcache_lowmem_prunes很大，就要增加query_cache_size的值。

電商MySQL資料庫配置檔案

這是一份電子商務網站MySQL資料庫調整後所執行的配置檔案/etc/my.cnf(伺服器為DELL R710、16GB記憶體、RAID10)，大家可以根據實際的MySQL資料庫硬體情況進行調整配置檔案如下：

[client]

port = 3306

socket = /data/3306/mysql.sock

default-character-set = utf8

[mysqld]

user = mysql

port = 3306

character-set-server = utf8

socket = /data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

log-error=/data/3306/mysql_err.log

pid-file=/data/3306/mysql.pid

log_slave_updates = 1

log-bin = /data/3306/mysql-bin

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 90

binlog-ignore – db = mysql

binlog-ignore – db = information_schema

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

join_buffer_size = 2M

thread_cache_size = 8

query_cache_size = 32M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

thread_concurrency = 32

table_cache = 614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 256M

max_heap_table_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

slow_query_log

slow_query_log_file = /data/3306/slow.log

skip-name-resolv

skip-locking

skip-networking

server-id = 1

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no – auto – rehash