MySQL配置最佳化選項

標籤：mysql配置最佳化

[mysqld]

port                    = 3306

預設為3306

user                    = mysql

datadir                 = /data/mysql

預設為/var/lib/mysql/

tmpdir                  = /dev/shm

預設為/tmp

slave-load-tmpdir       = /tmp

官方文檔中slave_load_tmpdir的解釋：從伺服器為複製LOAD DATA INFILE語句建立臨時檔案的目錄名。當資料恢複後，mysql會自動刪除對應的資料檔案。

預設不進行主從的操作所以沒有

socket                  = /var/lib/mysql/mysql.sock

預設值就為/var/lib/mysql/mysql.sock

如果是tar包安裝的話就為/tmp/mysql.sock

key_buffer              = 128M

指定索引緩衝的大小，它決定索引處理的速度

建議key_buffer設定為實體記憶體的1/4(針對MyISAM引擎)，甚至是實體記憶體的25%，如果key_buffer_size設定太大，系統就會頻繁的換頁，降低系統效能。因為MySQL使用作業系統的緩衝來快取資料，所以我們得為系統留夠足夠的記憶體；在很多情況下資料要比索引大得多。

預設8M

max_allowed_packet      = 128M

預設4M

table_open_cache        = 2048

當某一串連訪問一個表時，MySQL會檢查當前已緩衝表的數量。如果該表已經在緩衝中開啟，則會直接存取緩衝中的表已加快查詢速度；如果該表未被緩衝，則會將當前的表添加進緩衝並進行查詢。

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

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

預設2000

join_buffer_size        = 128MJoin操作使用記憶體

聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!

預設256KB

sort_buffer_size        = 128M排序使用記憶體

查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔!如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 6 = 

600MB。所以，對於記憶體在4GB左右的伺服器推薦設定為6-8M。

預設256KB

read_buffer_size        = 64M順序讀取資料緩衝區使用記憶體

讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!

預設256KB

read_rnd_buffer_size    = 64M隨機讀取資料緩衝區使用記憶體

和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）資料區塊的時候，會利用這個緩衝區暫存讀取的資料。如根據索引資訊讀取表資料，根據排序

後的結果集與表進行Join等等。總的來說，就是當資料區塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到read_rnd_buffer_size 參數所設定的記憶體緩衝區。

預設256KB

myisam_sort_buffer_size = 128M

在REPAIR TABLE過程中，或通過CREATE INDEX/ALTER TABLE為MyISAM表添加索引時為了對索引排序所能夠使用的緩衝空間大小。最小值為4，32位系統上所能夠使用的最大值為4294967295，即4G；64位系統上可以使用更大的空間。作用範圍為全域或會話層級，可用於設定檔，屬動態變數。

預設8M

query_cache_size        = 128M

指定查詢能夠使用的緩衝區大小如果設定它為 0 ，查詢緩衝將被禁止(預設值為 0 )

Qcache_queries_in_cache  在緩衝中登入的查詢數目  

Qcache_inserts  被加入到緩衝中的查詢數目  

Qcache_hits  緩衝採樣數數目  

Qcache_lowmem_prunes  因為缺少記憶體而被從緩衝中刪除的查詢數目  

Qcache_not_cached  沒有被緩衝的查詢數目 (不能被緩衝的，或由於 QUERY_CACHE_TYPE)  

Qcache_free_memory  查詢快取的空閑記憶體總數  

Qcache_free_blocks  查詢快取中的空閑記憶體塊的數目  

Qcache_total_blocks  查詢快取中的塊的總數目 

Qcache_queries_in_cache  在緩衝中登入的查詢數目  

Qcache_inserts  被加入到緩衝中的查詢數目  

Qcache_hits  緩衝採樣數數目  

Qcache_lowmem_prunes  因為缺少記憶體而被從緩衝中刪除的查詢數目  

Qcache_not_cached  沒有被緩衝的查詢數目 (不能被緩衝的，或由於 QUERY_CACHE_TYPE)  

Qcache_free_memory  查詢快取的空閑記憶體總數  

Qcache_free_blocks  查詢快取中的空閑記憶體塊的數目  

Qcache_total_blocks  查詢快取中的塊的總數目 

query_cache_limit       = 8M

指定單個查詢能夠使用的緩衝區大小，預設為1M

max_tmp_tables          = 512

單個用戶端串連能開啟的暫存資料表數目

預設32

tmp_table_size          = 128M

預設16M

max_heap_table_size     = 128M

這個變數定義了使用者可以建立的記憶體表(memory table)的大小.這個值用來計算記憶體表的最大行數值

預設16M

thread_cache            = 32線程棧資訊使用記憶體

主要用來存放每一個線程自身的標識資訊，如線程id，線程運行時基本資料等等，我們可以通過 thread_stack 參數來設定為每一個線程棧分配多大的內

存。

預設13

thread_concurrency      = 16

，錯誤設定了thread_concurrency的值, 會導致mysql不能充分利用多cpu(或多核), 出現同一時刻只能一個cpu(或核)在工作的情況。thread_concurrency應設為CPU核心數的2倍. 比如有一個雙核的CPU, 那麼thread_concurrency的應該為4; 2個雙核的cpu, thread_concurrency的值應為8.

預設為10

max_connect_errors      = 99999999

max_connect_errors是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼的情況。max_connect_errors的值與效能並無太大關係。

預設100

wait_timeout            = 300

預設28800 

interactive_timeout     = 300

在互動模式下，interactive_timeout取代wait_timeout。這樣，如果有的用戶端是互動模式方式串連mysql server。那麼用戶端的timeout受制於interactive_timeout。如果有的用戶端是非互動模式，長串連mysql server。那麼用戶端的timeout受制於wait_timeout。(是否是互動模式的串連，由用戶端決定)

預設28800

long_query_time         = 3

慢查詢區分多長時間為慢查詢

back_log                = 600

指定MySQL可能的串連數量。當MySQL主線程在很短的時間內接收到非常多的串連請求，該參數生效，主線程花費很短的時間檢查串連並且啟動一個新線程。 back_log參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。 如果系統在一個短時間內有很多串連，則需要增大該參數的值，該參數值指定到來的TCP/IP串連的偵聽隊列的大小。不同的作業系統在這個隊列大小上有它自己的限制。 試圖設定back_log高於你的作業系統的限制將是無效的。。對於Linux系統推薦設定為小於512的整數。

預設值為152

myisam_repair_threads   = 1

預設就是1

myisam-recover          = DEFAULT

自動檢查和修複無法正確關閉MyISAM表

default表示每次訪問表時會先判斷是否需要修複，但是不會強制修複（僅僅嘗試從key cache中修複），backup表示修複時會先將老的資料檔案先做個備份，force表示強制修複。

預設為off也就是不開啟此功能

expire_logs_days        = 10

二進位日誌儲存的天數

預設是0用不到期

default_tmp_storage_engine     = myisam

預設暫存資料表儲存引擎

預設為InnoDB的

###

plugin-load = thread_pool.so

thread pool包含數個thread groups,每個thread group管理一組用戶端串連。當串連建立以後,thread pool以輪詢的方式分配他們到thread group.

thread group的數量是通過thread_pool_size配置得到的,預設是16個,最大64個,最小1個。

每個thread group最大可以有4096個線程。

預設沒有載入任何外掛程式

collation_server = utf8_general_ci

預設latin1_swedish_ci

character_set_server = utf8

預設為latin1

character-set-client-handshake = false

skip-character-set-client-handshake = true

預設情況下是不開啟這樣的功能

innodb_lock_wait_timeout = 3600

指的是事務等待擷取資源等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗；參數的時間單位是秒，最小可設定為1s(此時需要考慮應用端的頻繁異常處理會消耗效能，不能設定過小)

預設50

group_concat_max_len = 4000

設定GROUP_CONCAT()函數傳回值的最大長度，有效取值範圍為4至“2^CPU字長”次方。作用範圍為全域或會話層級，用於設定檔，屬動態變數。於group by合并使用才會有效果就是將多個列合并的最大長度。

預設為1024。

#####

skip-slave-start

skip-slave-start 表示從mysql伺服器啟動時不啟動同步線程，這就要在啟動從伺服器之後，手工啟動同步線程，在mysql> 提示符下面運行“start slave”就可以

預設為空白

skip-name-resolve

大家都知道，當mysql的client連過來的時候，伺服器會主動去查client的網域名稱，當你設定了正確的DNS伺服器，而且此時DNS伺服器無異常，那麼你用戶端串連mysql伺服器就很快，如果此時你的DNS伺服器有異常，那麼串連mysql伺服器就比較慢

預設為off

max_user_connections   = 20000

某個使用者的最大串連數

預設為0表示隨便連只要伺服器不掛

max_connections   = 20000

整體的最大串連數

預設為151

read_only = 0

1. 只能對資料庫進行讀操作。

2. 對於擁有super許可權的使用者，可以ignore這個選項。

預設為off

log-slave-updates = 1

表示 如果一個MASTER 掛掉的話，另外一個馬上接管。

預設為空白

log-slave-updates 告訴Slave在二進位記錄中記錄從主要資料庫同步到的更新資料。如果只啟動log-bin 而沒有啟動log-slave-updates則Slave只記錄針對自己資料庫操作的更新。

預設為空白

bulk_insert_buffer_size = 32M

和key_buffer_size一樣，這個參數同樣也僅作用於使用 MyISAM儲存引擎，用來緩衝批量插入資料的時候臨時緩衝寫入資料。當我們使用如下幾種資料寫入語句的時候，會使用這個記憶體地區來緩衝批量結構的資料以協助批量寫入資料檔案：

insert … select …

insert … values (…) ,(…),(…)…

load data infile… into… (非空表)

預設8M

innodb_data_home_dir    = /data/mysql

此參數指定建立InnoDB資料表空間的路徑的公用部分,預設情況下，這是MySQL的預設資料，由MySQL參數datadir指定 

預設要看參數datadir指定

#innodb_data_file_path   = ibdata1:128M:autoextend

指定InnoDB的各個資料檔案及其大小，檔案多於一個時彼此間用分號隔開。資料檔案路徑可以為相對路徑，其相對於innodb_data_home_dir變數所指向的目錄；而檔案大小的表示可以以K(KB)、M(MB)、G(GB)為單位，但這些檔案的大小之和至少要達到10MB。在沒有顯式設定innodb_data_file_path變數的情況下，MySQL伺服器會在資料目錄中自動建立一個可自動成長、初始大小為10MB的名為ibdata1的資料檔案。單個資料檔案的大小上限取決於作業系統，這意味著可以使用作業系統所支援的最大單個檔案大小以為其資料檔案的體積上限。InnoDB還支援使用裸裝置作為資料檔案。作用範圍為全域，可用於選項檔案，屬非動態變數。

存放資料檔案內容

預設 ibdata1:12M:autoextend

innodb_log_group_home_dir = /data/mysql

此參數確定記錄檔組中的檔案的位置，日誌組中檔案的個數由innodb_log_files_in_group確定，此位置設定預設為MySQL的datadir 

預設為MySQL的datadir指定

innodb_file_per_table   = 1

可以修改InnoDB為獨立資料表空間模式，每個資料庫的每個表都會產生一個資料空間

設定InnoDB表是否使用每表資料表空間資料檔案(以.ibd結尾)分別儲存每個表的資料和索引。如果使用了每表資料表空間資料檔案，其將不再使用系統資料表空間(即共用資料表空間)。InnoDB表的某些特性，如壓縮表等僅對每表資料表空間生效。作用範圍為全域，可用於選項檔案，屬動態變數。

預設為1

innodb_buffer_pool_size = 64G

這個參數主要作用是緩衝innodb表的索引，資料，插入資料時的緩衝

專用mysql伺服器設定的大小： 作業系統記憶體的70%-80%最佳。

預設為128M

innodb_log_file_size    = 128M

如果對 Innodb 資料表有大量的寫入操作，那麼選擇合適的 innodb_log_file_size 值對提升MySQL效能很重要。然而設定太大了，就會增加恢複的時間，因此在MySQL崩潰或者突然斷電等情況會令MySQL伺服器花很長時間來恢複。通常依據伺服器的大小(server size)設定為64M到512M。

innodb_log_buffer_size  = 32M

這是 InnoDB 儲存引擎的交易記錄所使用的緩衝區。類似於 Binlog Buffer，InnoDB 在寫交易記錄的時候，為了提高效能，也是先將資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數所設定的相應條件(或者日誌緩衝區寫滿)之後，才會將日誌寫到檔案(或者同步到磁碟)中。

可以通過 innodb_log_buffer_size 參數設定其可以使用的最大記憶體空間。

預設8M

innodb_flush_log_at_trx_commit = 0

抱怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。預設值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush）硬碟，這是很費時的。特別是使用電 池供電緩衝（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM錶轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統緩衝。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的資料。而值2隻會在整個作業系統 掛了時才可能丟資料。 

預設為1

innodb_flush_method = O_DIRECT

O_DSYNC|O_DIRECT

設定innodb_flush_method變數的值為O_DIRECT時，InnoDB使用O_DIRECT標誌開啟資料檔案，而使用fsync()刷寫資料和記錄檔。O_DIRECT標誌會導致作業系統既不快取資料，也不預讀資料，它完全禁止了作業系統的緩衝並且使所有的讀寫動作直接至存放裝置，避免了雙緩衝。然而，其不能禁止硬體層面(如RAID卡)的緩衝和預讀功能，而且啟用硬體層面的緩衝和預讀功能也是保證InnoDB使用了O_DIRECT標誌時仍能保持良好效能的惟一途徑。

O_DSYNC會產生雙緩衝

預設為空白

server-id               = 281653306

預設為空白

log-bin                 = mysql-bin

預設為空白

relay-log               = relay-bin

預設為空白

log-error               = error.log

預設為空白

slow-query-log=1

預設為空白

slow-query-log-file        = slow-queries.log

啟動慢查詢日誌和慢查詢日誌記錄位置

預設為空白

US

[mysqld]

datadir=/data/mysql

tmpdir=/data/mysqltmp

預設為/tmp

plugin-load = thread_pool.so

# General server settings

# This setting ensures that temp tables are fast by being stored in memory. Add ENGINE = INNODB to create statements if a temp table 

is too big. See Task 186143 for pros/cons.

#default_tmp_storage_engine = MEMORY

預設為innodb

InnoDB寫入耗時大概是MyIsam和Memory的5倍左右，它的行鎖機制必然決定了寫入時的更多效能開銷，而它的強項在於多線程的並發處理，而本測試未能體現其優勢。

　　三種資料庫引擎在SELECT效能上差不多，Memory稍佔優，同樣高並發下的比較有待進一步測試。

表受限於mysqld變數max_heap_table_size(預設16M)

default_time_zone = SYSTEM

預設時區為系統

event_scheduler = off

可以讓你設定你的mysql資料庫再某段時間執行你想要的動作這與視圖是不相同的。

create event test1 

on schedule every 1 day starts  

‘2007-09-01 12:00:00‘ 

on completion not preserve  

do insert into yyy values(‘hhh‘,‘uuu‘);

使用這個功能之前必須確保event_scheduler已開啟，可執行 

SET GLOBAL event_scheduler = 1; 

預設為off

group_concat_max_len = 4000

# Matthew said that 5000 connections should be enough for a setup like ours for now.

max_connections = 5000

# Max heap and temp table size should always match. This setting keeps these tables from getting too big and using up all of memory.

max_heap_table_size = 64M

tmp_table_size = 64M

# Turning off the query cache gives a performance speed gain (a somewhat minor one). Since we don‘t use it, no reason to have it on.

query_cache_type = off

包括off，on，demand

預設off

query_cache_size = 0

預設為1M

skip_name_resolve = true

預設為off

# InnoDB engine settings

innodb_buffer_pool_instances = 8

  通過以上測試結果可知，在測試資料為200Warehouse時，資料大小大約佔innodb_buffer_pool_size的1/3。此時，innodb_buffer_pool_instances為2時，MySQL的效能相對較高，而隨著innodb_buffer_pool_instances的增大，MySQL的效能反而有所降低。但總體來看，對資料庫的效能影響不大。設定將InnoDB的buffer pool分隔為多少個地區。對於有著數GB空間的buffer pool來說，將其分隔為多個地區可以降低不同的線程對快取頁面面的讀寫操作時資源爭用係數，進行增強其並發能力。在buffer pool中，讀取或存入頁面時所選擇的地區是基於hash演算法隨機進行的。每個buffer pool管理自己的空閑列表、列表刷寫、LRU以及其它跟buffer pool相關的資料結構，並通過各自的互斥鎖進行保護。此變數僅在變數innodb_buffer_pool_size的值大於1G時才能發揮功用，緩衝池的整體空間將由各buffer pool執行個體分割使用。出於最佳效用的目的，建議配合使用innodb_buffer_pool_instances和innodb_buffer_pool_size變數以使得每個buffer pool執行個體的都至少有1G的空間。作用範圍為全域，可用於選項檔案，屬非動態變數。

預設為8

# This value should be 50%-80% of total system memory for a dedicated server

innodb_buffer_pool_size = 100GB

# These two settings help warm up the buffer pool, keeping server speed up after restarts. Increases shutdown and startup time.

innodb_buffer_pool_dump_at_shutdown = on

預設為off

innodb_buffer_pool_load_at_startup = on

預設為off

# This setting makes changes to data only get committed to disk (fsync) once per second, reducing disk I/O

innodb_flush_log_at_trx_commit = 0

# This setting skips unnecessary buffering when writing 

innodb_flush_method = O_DIRECT 

innodb_lock_wait_timeout = 3600

預設為50

# This was a recommended log size starting point from Matthew. Update this once we get better statistics.

innodb_log_file_size = 256MB

預設為48M

# Character set and collation settings

collation_server = utf8_general_ci

character_set_server = utf8

character-set-client-handshake = false

skip-character-set-client-handshake = true

# Replication settings 

# ROW format avoids problems with certain time based queries not being properly replicated, in addition to being faster in general 

than STATEMENT.

binlog_format = ROW

Row|Statement| Mixed

預設STATEMENT

log_bin

# This setting should be set to 1 for slaves to prevent writing data to the tables. Temp tables can still be written and created.

read_only = 0

# This setting needs to be an integer unique to the server. Zero means it does not replicate.

server_id = 0

# This setting can tell MySQL to look in specific directories for further config files. We can use this for slave replication to 

control what databases are replicated.

#!includedir /etc/my.cnf.d

本文出自 “wcf” 部落格，轉載請與作者聯絡！

MySQL配置最佳化選項