MySQL在Linux系統下設定檔詳解

標籤：

在日常的的開發過程中接觸到了SQLServer和MySQL資料庫的操作性問題，可能是以前接觸的都是SQL Server，才開始接觸MySQL，總感覺使用MySQL沒有使用SQLserver那麼順手，一些關鍵的系統函數，比如說開窗、行轉列、列轉行、自增欄位等一系列的問題，雖然最後都找到了替代的方案，但是解決效能問題還是頗費了一些功夫的。對比了一下SQL Server、MySQL在Windows環境下、MySQL在linux環境下的效能，同樣的一個預存程序，在預存程序中建立了八個暫存資料表，並迴圈向每個暫存資料表裡插入了十萬條資料，欄位大概有十二個左右，最後查詢出資料，在SQL Server下用了6秒時間，在Linux下的MySQL中用了3秒多的時間，而在Windows環境下的MySQL改為了一千條資料耗費了六十多秒的時間。總的來說效能上的差別還是很大的，下面就來談談Linux下的MySQL的設定檔。

設定慢查詢時間的長度，以及慢查詢日誌存放的位置:

long_query_time=1
slow_query_log_file = /var/log/mysql/mysql-slow.log

避免Mysql的外部鎖定，減少出錯幾率，增強穩定性:
skip-external-locking

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

back_log參數的值指出在MySQL暫時停止回應新請求之前，短時間內的多少個請求可以被存,在對堆棧中，如果系統短時間內有很多串連，則需>要增大該參數的值，該參數值指定到來的TCP/IP串連的監聽隊列的大小。不同的作業系統在這個隊列的大小有自己的限制，將back_log設定得高於作業系統的限制將是無效的，其預設值為50，對於LINUX系統而言，推薦設定為小於512的整數:

back_log=1200 binlog_cache_size=64M

索引緩衝大小，增加它可得到更好的索引處理效能，對於記憶體在4GB左右的伺服器，該參數可設定為256M或384M。如果該參數值設定的過大>反而會使伺服器的整體效率降低:

key_buffer_size=512M

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

max_allowed_packet=64M

設定MySQL每個線程的堆棧大小，預設值足夠大，可滿足普通操作。可設定範圍為128KB至4GB，預設192K:

thread_stack=64M sort_buffer_size=64M

max_connect_errors = 6000 open_files_limit = 65535

table_open_cache=256 max_heap_table_size=16M

設定Thread Cache池中可以緩衝的串連池線程最大數量，可設定為0-16384,預設為0。1GB記憶體我們配置為8，2GB記憶體我們配置為16，4GB或4GB以上內在我們配置為64:

thread_cache_size=128

指定Mysql查詢緩衝區的大小，可以通過在Mysql控制台觀察，如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果 Qcache_hits的值非常大，則表明查詢緩衝使用的非常頻繁 :

query_cache_size=64M

query_cache_limit=64M

設定內在暫存資料表最大值，如果超過該值，則會將暫存資料表寫入磁碟，其範圍為1KB至4GB:

tmp_table_size=64M

指定MYSQL允許的最大串連進程數，如果在訪問程式時經常出現TOO MANY CONNECTIONS的錯誤提示，則需要增大該參數值:

max_connections=1500

max_user_connections=1500

指定一個請求的最大連線時間，對於4GB左右內在的伺服器來說，可以將其設定為5－10:

wait_timeout=100

該參數取值為伺服器邏輯CPU數量*2，比如，伺服器有兩個物理CPU，每個物理CPU支援HT超執行緒，所以實際取值4*2=8，這也是目前雙四核主流 伺服器的配置:

join_buffer_size = 64M

開啟該選項可以徹底關閉MYSQL的TCP/IP串連方式，如果WEB伺服器是以遠端連線的方式訪問MYSQL的資料庫伺服器，則不要開啟該選項，否則>將無法正常串連。 skip-networking   抱怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。預設值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush） 硬碟，這是很費時的。特別是使用電池供電緩衝（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM錶轉過來的是可以的>，它的意思是不寫入硬碟而是寫入系統緩衝。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安>全方面比較差，即使MySQL掛了也可能會丟失事務的資料。而值2隻會在整個操作系#統 掛了時才可能丟資料:

innodb_flush_log_at_trx_commit=2

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

innodb_log_buffer_size=64M

innodb_buffer_pool_size=64M

innodb_log_file_size=1G

innodb_file_per_table=1

innodb_read_io_threads=10

innodb_write_io_threads=10

innodb_flush_method=O_DIRECT

innodb_io_capacity=1000

innodb_io_capacity_max=1000

innodb_lru_scan_depth=500

innodb_thread_concurrency=0  

innodb_autoinc_lock_mode=2

innodb_log_files_in_group=3

innodb_max_dirty_pages_pct=90

innodb_lock_wait_timeout=100

bulk_insert_buffer_size = 16M  

innodb_thread_concurrency = 8

innodb_purge_threads = 1

tmp_table_size 的預設大小是 32M。如果一張暫存資料表超出該大小，MySQL產生一個 The table tbl_name is full 形式的錯誤，如果你做很多 #進階 GROUP BY 查詢，增加 tmp_table_size 值:

tmp_table_size=512M

隨機讀取資料緩衝區使用記憶體(read_rnd_buffer_size)：和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）資料區塊的時候，會利用>這個緩衝區暫存讀取的資料。如根據索引資訊讀取表資料，根據排序後的結果集與表進行Join等等。總的來說，就是當資料區塊的讀取需要滿足>一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到 read_rnd_buffer_size 參數所設定的記憶體緩衝區:

read_rnd_buffer_size=128M

你最好在定義資料庫命名規則的時候就全部採用小寫字母加底線的組合，而不使用任何的大寫字母:

lower_case_table_names=1

設定校正模式 :

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

預設配置沒開查詢快取:

explicit_defaults_for_timestamp

需要記錄進位日誌的資料庫.如果有多個資料庫可用逗號分隔,或者使用多個binlog-do-db選項:

binlog-do-db=showeedb

不需要記錄進位日誌的資料庫.如果有多個資料庫可用逗號分隔,或者使用多個binlog-do-db選項  :

binlog-ignore-db=mysql 

需要進行同步的資料庫.如果有多個資料庫可用逗號分隔,或者使用多個replicate-do-db選項:

replicate-do-db=showeedb 

不需要同步的資料庫.如果有多個資料庫可用逗號分隔,或者使用多個replicate-ignore-db選項:

replicate-ignore-db=mysql,test 

同步參數:

server-id=1

log_bin=/var/log/mysql/mysql-bin

保證slave掛在任何一台master上都會接收到另一個master的寫入資訊:

log-slave-updates

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

過濾掉一些沒什麼大問題的錯誤:

slave-skip-errors=all

MySQL在Linux系統下設定檔詳解