MySQL伺服器效能最佳化

標籤：mysql 最佳化

一:設定檔讀取位置,不同系統my.cnf設定檔位置不同.

例如debian位置:/etc/mysql/my.cnf找到mysqld二進位檔案: find   /  -name  mysqld/usr/bin/mysqld   --verbose  --help  | grep  -A 1  "Default options"

二:全域緩衝

(key_buffer_size(預設值：384M)innodb_buffer_pool_sizeinnodb_additional_mem_pool_sizeinnodb_log_buffer_size(預設值：8M)query_cache_size(預設值：32M)

1.innodb_buffer_pool_size(預設值：128M)

innodb_buffer_pool_size=24G

優點:緩衝索引,緩衝行資料,自適應雜湊索引,插入緩衝,鎖,內部資料結構

缺點:Innodb緩衝過大,預熱和關閉會花費大量時間.該時間由髒頁數量決定的.因為在關閉之前會把髒頁的寫回資料檔案.強制關閉後,恢復會很長.

解決缺點:  若要關閉mysql資料庫,可以事先講髒頁的數量(innodb_max_dirty_pages_pct)調的小一點,值改小後,等待新的線程清理緩衝池,然後在髒頁數量小得時候,再關閉資料庫.

但是innodb_max_dirty_pages_pct越小,並不能保證髒頁的數量會很小.

配置:記憶體的80%(前提是該伺服器只跑了mysql一個消耗記憶體型,IO型的資料庫)

監控:show  status   或者innotop工具

2.innodb_additional_mem_pool_size（預設值：8M）

innodb_additional_mem_pool_size=16M

存放資料字典資訊以及一些內部資料結構的記憶體空間大小,當mysql執行個體資料庫物件很多時,調大該值.判斷是否足夠,查看mysql的error日誌中,看是否有warnning資訊.

3.innodb_log_buffer_size(預設8M)

innodb_log_buffer_size=8Minnodb_flush_log_trx_commit=2

臨時存放交易記錄.InnoDB在寫交易記錄的時候,為了提高效能,先講交易記錄寫到innodb_log_buffer中,當滿足innodb_flush_log_trx_commit參數所設定的相應條件(或者日誌緩衝區寫滿)之後，才會將日誌寫到檔案 (或者同步到磁碟)中.

理想值為 1M 至 8M,一般不要超過32M.

註：innodb_flush_log_trx_commit參數對InnoDB Log的寫入效能有非常關鍵的影響,預設值為1。該參數可以設定為0，1，2.

4.innodb_flush_log_trx_commit(預設值為1)

innodb_flush_log_trx_commit=2

該值對資料庫的寫入效能影響非常大.MySQL官方建議將插入操作合并成一個事務，這樣可以大幅提高速度

實際測試發現，設定為2時插入10000條記錄只需要2秒

            設定為0時插入10000條記錄只需要1秒，

            設定為1時插入10000條記錄需要229秒。在存在丟失最近部分事務的危險的前提下，可以把該值設為0。

事務重新整理.可配置為0,1,2

0:表示log buffer中的資料會以每秒一次重新整理的頻率重新整理到log file中.同時也會觸發檔案系統到磁碟的同步操作.

1:表示每次有新事務提交都會將log buffer中的資料重新整理到log file中.同時也會觸發檔案系統到磁碟的同步操作.

2:表示每次有新事務提交都會將log buffer中的資料重新整理到log file中,但是檔案系統會以每秒一次的重新整理頻率到磁碟上.

5.query_cache_size(預設為32M)

query_cache_size=256Mquery_cache_type=ON

緩衝select語句的執行結果.單不是全部緩衝,條件是查詢到的結果集大小必須小於等於query_cache_size的大小.

注意:

該值得使用或者不用,取決於查詢表中的資料是否經常變化,例如我公司的訂單表.該表的資料時時刻刻都在變化,因此不能應用此選項.這是一個致命的配置.因為表中的資料一旦變化,那麼存在查詢快取中的結果都會失效.

多個參數配合使用該選項.

query_cache_size  緩衝結果集大小

query_cache_type ={0|1|2}

0:表示不用查詢快取

1:表示不使用緩衝.1(ON)或者2(DEMOND)，分別表示完全不使用query cache，除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，

配置:256M足夠

Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進行調整

6.key_buffer_size

key_buffer_size=128M

優點:緩衝索引資料,並只緩衝索引資料

缺點:mysql5.0上限4G

三:局部緩衝(read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size )

這些局部記憶體在需要的時候才會分配,然後等操作完成之後就回立即釋放佔用的記憶體.

1.read_buffer_size（預設值：2M）

read_buffer_size=4M

順序讀緩衝區,對錶進行順序掃描所分配的一塊緩衝區.

如果程式定時去掃描資料表,應該增大該值來提高效能.

2.read_rnd_buffer_size(預設值：8M)

read_rnd_buffer_size=8M

隨機讀緩衝區,對錶隨機讀取資料時分配的一塊緩衝區.

3.sort_buffer_size(預設值: 2M)

sort_buffer_size=4M

用於oder by語句存放排序查詢

4.tmp_table_size(預設值：16M)

tmp_table_size=16M

聯集查詢緩衝大小

5.表緩衝

table_open_cache

table_open_cache=4096

解釋:儲存物件是資料表.

監控:如果Opend_tables狀態變數很大或者在增長,可能是表緩衝不夠大,應該增大.

缺點:當資料庫中的表MyISAM表很多時,可能會導致關機時間很長.因為關機前索引塊必須完成重新整理.表都被標記為不再開啟.

監控:如果發現open_tables等於table_open_cache，並且opened_tables在不斷增長，那麼你就需要增加table_open_cache的值了.

該值計算:max_connections*n

n表示查詢語句中最大的表.

四:線程緩衝(thread_cache_size)

thread_cache_size=64

解釋:為建立mysql的串連而準備的線程.thread_cache_size保證緩衝中的線程數.一般無需配置此值,除非資料庫有大量的串連請求.當一個串連建立時,如果緩衝中有線程存在,MYSQL從緩衝

刪除一個線程,並且把它分配給這個新的串連,當串連關閉時,若緩衝中還有空間,MYSQL又會把這個串連放到緩衝中.若沒有空間,MYSQL會銷毀這個線程.

監控:檢查線程緩衝是否夠用,查看Threads_connectd狀態變數.

例子:

Threads_connected在100-200之間,則thread_cache_size=20,足夠.

Threads_connected在500-700之間,則thread_cache_size=200,足夠.

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

 (Connections -  Threads_created) / Connections * 100 %

 thread_concurrency

 解釋:該值應該為CPU核心數的2倍.

 例子:2個物理cpu,每個CPU8核心,那麼

 thread_concurrency=2*8*2=32

5.InnoDB並發限制

innodb_thread_concurrency=24

解釋:它會限制一次性可以有多少個線程進入核心.0表示不限制.在任何架構和業務壓力下,設定好這個值很重要.

innodb_thread_concurrency=CPU數量*磁碟數量*2

例子:2個物理cpu,每個CPU8核心,那麼

thread_concurrency=2*6*2=32

6.資料庫檔案描述符(open_files_limit)

open_files_limit=65535

7.請求隊列(back_log)

back_log=500

mysql建立請求隊列,只定max_connections達到最大時,還可以接受多少的請求,先放到隊列中.每個

五:其他

1.

innodb_data_file_path = ibdata1:1G:autoextend 

innodb_log_file_size = 512M

注意:以上參數在啟動資料庫前必須配置好,否則報錯如下:

2015-12-23 17:03:06 16182 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (relevant if non-zero) pages!2015-12-23 17:03:06 16182 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!2015-12-23 17:03:06 16182 [ERROR] Plugin ‘InnoDB‘ init function returned error.2015-12-23 17:03:06 16182 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed.2015-12-23 17:03:06 16182 [ERROR] Unknown/unsupported storage engine: INNODB2015-12-23 17:03:06 16182 [ERROR] Aborting

2.innodb_log_files_in_group = 2

本文出自 “不求最好,只求更好” 部落格，請務必保留此出處http://yujianglei.blog.51cto.com/7215578/1727646

MySQL伺服器效能最佳化