MySQL 5.5.x my.cnf參數設置裝備擺設優化詳解

MySQL 5.5.x my.cnf參數設置裝備擺設優化詳解。本站提示廣大學習愛好者：（MySQL 5.5.x my.cnf參數設置裝備擺設優化詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL 5.5.x my.cnf參數設置裝備擺設優化詳解正文

一向有耳聞MySQL5.5的機能異常NB，所以近期盤算測試一下，便利的時刻就把bbs.kaoyan.com進級到這個版本的數據庫。明天正悅目到一篇有關my.cnf優化的總結，固然還沒經由我本身的理論磨練，但從文章內容來講曾經寫的很具體了(固然，現實高低面這篇文章許多處所只是翻譯了my.cnf原始設置裝備擺設文件的解釋，呵呵)，所以專程轉載珍藏一下，年夜家在對mysql辦事器停止優化的時刻可以作為參考，並依據現實情形對個中的一些參數停止調劑。（特殊備注：以下原文中有些參數現實上不實用於mysql5.5，不曉得原作者能否有經由現實測試，好比log-slow-queries應當寫成slow-query-log，而log_long_format這個爽性就直接不支撐了，回頭我再從新整頓一份靠譜點的參數解釋吧）

以下是原文作者對my.cnf的具體解讀：

PS：本設置裝備擺設文件針對Dell R710，雙至強E5620、16G內存的硬件設置裝備擺設。CentOS 5.6 64位體系，MySQL 5.5.x 穩固版。實用於日IP 50-100w，PV 100-300w的站點，重要應用InnoDB存儲引擎。其他運用情況請依據現實情形來設置優化。

# 以下選項會被MySQL客戶端運用讀取。
# 留意只要MySQL附帶的客戶端運用法式包管可以讀取這段內容。
# 假如你想你本身的MySQL運用法式獲得這些值。
# 須要在MySQL客戶端庫初始化的時刻指定這些選項。

# [client] #password = [your_password]
 port = @MYSQL_TCP_PORT@
 socket = @MYSQL_UNIX_ADDR@

# *** 運用定制選項 ***

# # MySQL 辦事端
# [mysqld]

# 普通設置裝備擺設選項
port = @MYSQL_TCP_PORT@
 socket = @MYSQL_UNIX_ADDR@

# back_log 是操作體系在監聽隊列中所能堅持的銜接數,
 # 隊列保留了在MySQL銜接治理器線程處置之前的銜接.
 # 假如你有異常高的銜接率而且湧現”connection refused” 報錯,
 # 你就應當增長此處的值. # 檢討你的操作體系文檔來獲得這個變量的最年夜值.
 # 假如將back_log設定到比你操作體系限制更高的值,將會沒有用果 back_log = 300

# 不在TCP/IP端口長進行監聽.
 # 假如一切的過程都是在統一台辦事器銜接到當地的mysqld,
 # 如許設置將是加強平安的辦法
# 一切mysqld的銜接都是經由過程Unix sockets 或許定名管道停止的.
 # 留意在windows下假如沒有翻開定名管道選項而只是用此項
# (經由過程 “enable-named-pipe” 選項) 將會招致mysql辦事沒有任何感化!
 #skip-networking

# MySQL 辦事所許可的同時會話數的下限
# 個中一個銜接將被SUPER權限保存作為治理員登錄.
 # 即使曾經到達了銜接數的下限.
max_connections = 3000
 # 每一個客戶端銜接最年夜的毛病許可數目,假如到達了此限制.
 # 這個客戶端將會被MySQL辦事阻攔直到履行了”FLUSH HOSTS” 或許辦事重啟
# 不法的暗碼和其他在鏈接時的毛病會增長此值.
 # 檢查 “Aborted_connects” 狀況來獲得全局計數器.
max_connect_errors = 30
 # 一切線程所翻開表的數目.
 # 增長此值就增長了mysqld所須要的文件描寫符的數目
# 如許你須要確認在[mysqld_safe]中 “open-files-limit” 變量設置翻開文件數目許可至多4096
 table_cache = 4096

# 許可內部文件級其余鎖. 翻開文件鎖會對機能形成負面影響
# 所以只要在你在異樣的文件上運轉多個數據庫實例時才應用此選項(留意仍會有其他束縛!)
 # 或許你在文件層面上應用了其他一些軟件依附來鎖定MyISAM表
#external-locking

# 辦事所能處置的要求包的最年夜年夜小和辦事所能處置的最年夜的要求年夜小(當與年夜的BLOB字段一路任務時相當需要)
 # 每一個銜接自力的年夜小.年夜小靜態增長
max_allowed_packet = 32M

# 在一個事務中binlog為了記載SQL狀況所持有的cache年夜小
# 假如你常常應用年夜的,多聲明的事務,你可以增長此值來獲得更年夜的機能.
 # 一切從事務來的狀況都將被緩沖在binlog緩沖中然後在提交後一次性寫入到binlog中
# 假如事務比此值年夜, 會應用磁盤上的暫時文件來替換.
 # 此緩沖在每一個銜接的事務第一次更新狀況時被創立
binlog_cache_size = 4M

# 自力的內存表所許可的最年夜容量.
 # 此選項為了避免不測創立一個超年夜的內存表招致永盡一切的內存資本. max_heap_table_size = 128M

# 排序緩沖被用來處置相似ORDER BY和GROUP BY隊列所惹起的排序
# 假如排序後的數據沒法放入排序緩沖,
 # 一個用來替換的基於磁盤的歸並分類會被應用
# 檢查 “Sort_merge_passes” 狀況變量.
 # 在排序產生時由每一個線程分派 sort_buffer_size = 16M

# 此緩沖被應用來優化全結合(full JOINs 不帶索引的結合).
 # 相似的結合在極年夜多半情形下有異常蹩腳的機能表示,
 # 然則將此值設年夜可以或許加重機能影響.
 # 經由過程 “Select_full_join” 狀況變量檢查全結合的數目
# 當全結合產生時,在每一個線程平分配
join_buffer_size = 16M

# 我們在cache中保存若干線程用於重用
# 當一個客戶端斷開銜接後,假如cache中的線程還少於thread_cache_size,
 # 則客戶端線程被放入cache中.
 # 這可以在你須要年夜量新銜接的時刻極年夜的削減線程創立的開支
# (普通來講假如你有好的線程模子的話,這不會有顯著的機能晉升.)
thread_cache_size = 16

# 此許可運用法式賜與線程體系一個提醒在統一時光賜與盼望被運轉的線程的數目.
 # 此值只關於支撐 thread_concurrency() 函數的體系成心義( 例如Sun Solaris).
 # 你可可以測驗考試應用 [CPU數目]*(2..4) 來作為thread_concurrency的值
thread_concurrency = 8

# 查詢緩沖常被用來緩沖 SELECT 的成果而且鄙人一次異樣查詢的時刻不再履行直接前往成果.
 # 翻開查詢緩沖可以極年夜的進步辦事器速度, 假如你有年夜量的雷同的查詢而且很少修正表.
 # 檢查 “Qcache_lowmem_prunes” 狀況變量來檢討能否以後值關於你的負載來講能否足夠高.
 # 留意: 在你表常常變更的情形下或許假如你的查詢原文每次都分歧,
 # 查詢緩沖或許惹起機能降低而不是機能晉升.
query_cache_size = 128M

# 只要小於此設定值的成果才會被緩沖
# 此設置用來掩護查詢緩沖,避免一個極年夜的成果集將其他一切的查詢成果都籠罩.
query_cache_limit = 4M

# 被全文檢索索引的最小的字長.
 # 你或許願望削減它,假如你須要搜刮更短字的時刻. # 留意在你修正此值以後,
 # 你須要重建你的 FULLTEXT 索引
ft_min_word_len = 8

# 假如你的體系支撐 memlock() 函數,你或許願望翻開此選項用以讓運轉中的mysql在在內存高度重要的時刻,數據在內存中堅持鎖定而且避免能夠被swapping out
 # 此選項關於機能無益
#memlock

# 當創立新表時作為默許應用的表類型,
 # 假如在創立表現沒有特殊履行表類型,將會應用此值
default_table_type = MYISAM

# 線程應用的堆年夜小. 此容量的內存在每次銜接時被預留.
 # MySQL 自己常不會須要跨越64K的內存
# 假如你應用你本身的須要年夜量堆的UDF函數
# 或許你的操作體系關於某些操作須要更多的堆,
 # 你或許須要將其設置的更高一點.
thread_stack = 512K

# 設定默許的事務隔離級別.可用的級別以下:
 # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
 transaction_isolation = REPEATABLE-READ

# 外部(內存中)暫時表的最年夜年夜小
# 假如一個表增加到比此值更年夜,將會主動轉換為基於磁盤的表.
 # 此限制是針對單個表的,而不是總和.
tmp_table_size = 128M

# 翻開二進制日記功效.
 # 在復制(replication)設置裝備擺設中,作為MASTER主辦事器必需翻開此項
# 假如你須要從你最初的備份中做基於時光點的恢復,你也異樣須要二進制日記.
log-bin=mysql-bin

# 假如你在應用鏈式從辦事器構造的復制形式 (A->B->C),
 # 你須要在辦事器B上翻開此項.
 # 此選項翻開在從線程上重做過的更新的日記, # 並將其寫入從辦事器的二進制日記.
 #log_slave_updates

# 翻開全查詢日記. 一切的由辦事器吸收到的查詢 (乃至關於一個毛病語法的查詢)
 # 都邑被記載上去. 這關於調試異常有效, 在臨盆情況中經常封閉此項.
 #log

# 將正告打印輸入到毛病log文件. 假如你關於MySQL有任何成績
# 你應當翻開正告log而且細心審查毛病日記,查出能夠的緣由.
 #log_warnings

# 記載慢速查詢. 慢速查詢是指消費了比 “long_query_time” 界說的更多時光的查詢.
 # 假如 log_long_format 被翻開,那些沒有應用索引的查詢也會被記載.
 # 假如你常常增長新查詢到已有的體系內的話. 普通來講這是一個好主張。
log_slow_queries

# 一切的應用了比這個時光(以秒為單元)更多的查詢會被以為是慢速查詢.
 # 不要在這裡應用”1″, 不然會招致一切的查詢,乃至異常快的查詢頁被記載上去(因為MySQL 今朝時光的准確度只能到達秒的級別).
long_query_time = 6

# 在慢速日記中記載更多的信息. # 普通此項最好翻開.
 # 翻開此項會記載使得那些沒有應用索引的查詢也被作為到慢速查詢附加到慢速日記裡
log_long_format

# 此目次被MySQL用來保留暫時文件.例如,
 # 它被用來處置基於磁盤的年夜型排序,和外部排序一樣.
 # 和簡略的暫時表.
 # 假如你不創立異常年夜的暫時文件,將其放置到 swapfs/tmpfs 文件體系上或許比擬好
# 另外一種選擇是你也能夠將其放置在自力的磁盤上.
 # 你可使用”;”來放置多個途徑
# 他們會依照roud-robin辦法被輪詢應用.
 #tmpdir = /tmp

# *** 主從復制相干的設置

# 獨一的辦事辨識號,數值位於 1 到 2^32-1之間.
 # 此值在master和slave上都須要設置.
 # 假如 “master-host” 沒有被設置,則默許為1, 然則假如疏忽此選項,MySQL不會作為master失效.
server-id = 1

# 復制的Slave (去失落master段的正文來使其失效)
 # # 為了設置裝備擺設此主機作為復制的slave辦事器,你可以選擇兩種辦法:
 # # 1) 應用 CHANGE MASTER TO 敕令 (在我們的手冊中有完全描寫) –
#  語法以下:
 # #  CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
 #  MASTER_USER=, MASTER_PASSWORD= ;
 # #  你須要調換失落 , , 等被尖括號包抄的字段和應用master的端標語調換 (默許3306).
 # #  例子:
 # #  CHANGE MASTER TO MASTER_HOST='125.56.12.1′, MASTER_PORT=3306, #  MASTER_USER='joe', MASTER_PASSWORD='secret';
 # # 或許
# # 2) 設置以下的變量. 豈論若何, 在你選擇這類辦法的情形下, 然後第一次啟動復制(乃至不勝利的情形下,
 #   例如假如你輸出錯暗碼在master-password字段而且slave沒法銜接),
 #  slave會創立一個 master.info 文件,而且以後任何關於包括在此文件內的參數的變更都邑被疏忽
#  而且由 master.info 文件內的內容籠罩, 除非你封閉slave辦事, 刪除 master.info 而且重啟slave 辦事.
 #  因為這個緣由,你或許不想碰一下的設置裝備擺設(正文失落的) 而且應用 CHANGE MASTER TO (檢查下面) 來取代
# # 所須要的獨一id號位於 2 和 2^32 – 1之間
# (而且和master分歧)
 # 假如master-host被設置了.則默許值是2
 # 然則假如省略,則不會失效 #server-id = 2
 # # 復制構造中的master – 必需 #master-host =
 # # 當銜接到master上時slave所用來認證的用戶名 – 必需
#master-user =
 # # 當銜接到master上時slave所用來認證的暗碼 – 必需
#master-password =
 # # master監聽的端口.
 # 可選 – 默許是3306
 #master-port =

# 使得slave只讀.只要用戶具有SUPER權限和在下面的slave線程可以或許修正數據.
 # 你可使用此項去包管沒有運用法式會心外的修正slave而不是master上的數據
#read_only

#*** MyISAM 相干選項

# 症結詞緩沖的年夜小, 普通用來緩沖MyISAM表的索引塊.
 # 不要將其設置年夜於你可用內存的30%,
 # 由於一部門內存異樣被OS用來緩沖行數據
# 乃至在你其實不應用MyISAM 表的情形下, 你也須要仍然設置起 8-64M 內存因為它異樣會被外部暫時磁盤表應用. key_buffer_size = 128M

# 用來做MyISAM表全表掃描的緩沖年夜小.
 # 當全表掃描須要時,在對應線程平分配.
read_buffer_size = 8M

# 當在排序以後,從一個曾經排序好的序列中讀取行時,行數據將從這個緩沖中讀取來避免磁盤尋道.
 # 假如你增高此值,可以進步許多ORDER BY的機能.
 # 當須要時由每一個線程分派
read_rnd_buffer_size = 64M

# MyISAM 應用特別的相似樹的cache來使得突發拔出
# (這些拔出是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 和 LOAD DATA
 # INFILE) 更快. 此變量限制每一個過程中緩沖樹的字節數.
 # 設置為 0 會封閉此優化.
 # 為了最優化不要將此值設置年夜於 “key_buffer_size”.
 # 當突發拔出被檢測到時此緩沖將被分派.
bulk_insert_buffer_size = 256M

# 此緩沖當MySQL須要在 REPAIR, OPTIMIZE, ALTER 和 LOAD DATA INFILE 到一個空表中惹起重建索引時被分派.
 # 這在每一個線程中被分派.所以在設置年夜值時須要當心.
myisam_sort_buffer_size = 256M

# MySQL重建索引時所許可的最年夜暫時文件的年夜小 (當 REPAIR, ALTER TABLE 或許 LOAD DATA INFILE).
 # 假如文件年夜小比此值更年夜,索引會經由過程鍵值緩沖創立(更慢)
myisam_max_sort_file_size = 10G

# 假如被用來更快的索引創立索引所應用暫時文件年夜於制訂的值,那就應用鍵值緩沖辦法.
 # 這重要用來強迫在年夜表中長字串鍵去應用慢速的鍵值緩沖辦法來創立索引.
myisam_max_extra_sort_file_size = 10G

# 假如一個表具有跨越一個索引, MyISAM 可以經由過程並行排序應用跨越一個線程去修復他們.
 # 這關於具有多個CPU和年夜量內存情形的用戶,是一個很好的選擇. myisam_repair_threads = 1

# 主動檢討和修復沒有恰當封閉的 MyISAM 表.
myisam_recover

# 默許封閉 Federated skip-federated

# *** BDB 相干選項 ***

# 假如你運轉的MySQL辦事有BDB支撐然則你禁絕備應用的時刻應用此選項. 這會節儉內存而且能夠加快一些事.
skip-bdb

# *** INNODB 相干選項 ***

# 假如你的MySQL辦事包括InnoDB支撐然則其實不盤算應用的話,
 # 應用此選項會節儉內存和磁盤空間,而且加快某些部門
#skip-innodb

# 附加的內存池被InnoDB用來保留 metadata 信息
# 假如InnoDB為此目標須要更多的內存,它會開端從OS這裡請求內存.
 # 因為這個操作在年夜多半古代操作體系上曾經足夠快, 你普通不須要修正此值.
 # SHOW INNODB STATUS 敕令會顯示領先應用的數目.
innodb_additional_mem_pool_size = 64M

# InnoDB應用一個緩沖池來保留索引和原始數據, 不像 MyISAM.
 # 這裡你設置越年夜,你在存取內外面數據時所須要的磁盤I/O越少.
 # 在一個自力應用的數據庫辦事器上,你可以設置這個變量到辦事器物理內存年夜小的80%
 # 不要設置過年夜,不然,因為物理內存的競爭能夠招致操作體系的換頁波動.
 # 留意在32位體系上你每一個過程能夠被限制在 2-3.5G 用戶層面內存限制,
 # 所以不要設置的太高.
innodb_buffer_pool_size = 6G

# InnoDB 將數據保留在一個或許多個數據文件中成為表空間.
 # 假如你只要單個邏輯驅動保留你的數據,一個單個的自增文件就足夠好了.
 # 其他情形下.每一個裝備一個文件普通都是個好的選擇.
 # 你也能夠設置裝備擺設InnoDB來應用裸盤分區 – 請參考手冊來獲得更多相干內容
innodb_data_file_path = ibdata1:10M:autoextend

# 設置此選項假如你願望InnoDB表空間文件被保留在其他分區.
 # 默許保留在MySQL的datadir中.
 #innodb_data_home_dir =

# 用來同步IO操作的IO線程的數目.
 # 此值在Unix下被硬編碼為4,然則在Windows磁盤I/O能夠在一個年夜數值下表示的更好.
innodb_file_io_threads = 4

# 假如你發明InnoDB表空間破壞, 設置此值為一個非零值能夠贊助你導出你的表.
 # 從1開端而且增長此值曉得你可以或許勝利的導出表.
 #innodb_force_recovery=1

# 在InnoDb焦點內的許可線程數目.
 # 最優值依附於運用法式,硬件和操作體系的調劑方法. # 太高的值能夠招致線程的互斥波動.
innodb_thread_concurrency = 16

# 假如設置為1 ,InnoDB會在每次提交後刷新(fsync)事務日記到磁盤上,
 # 這供給了完全的ACID行動.
 # 假如你情願對事務平安調和, 而且你正在運轉一個小的食品, 你可以設置此值到0或許2來削減由事務日記惹起的磁盤I/O # 0代表日記只年夜約每秒寫入日記文件而且日記文件刷新到磁盤.
 # 2代表日記寫入日記文件在每次提交後,然則日記文件只要年夜約每秒才會刷新到磁盤上.
innodb_flush_log_at_trx_commit = 2 
（解釋：假如是游戲辦事器，建議此值設置為2；假如是對數據平安請求極高的運用，建議設置為1；設置為0機能最高，但假如產生毛病，數據能夠會有喪失的風險！默許值1的意思是每次事務提交或事務外的指令都須要把日記寫入（flush）硬盤，這是很費時的。特殊是應用電池供電緩存（Battery backed up cache）時。設成2關於許多應用，特殊是從MyISAM表轉過去的是可以的，它的意思是不寫入硬盤而是寫入體系緩存。日記依然會每秒flush到硬盤，所以你普通不會喪失跨越1-2秒的更新。設成0會更快一點，但平安方面比擬差，即便MySQL掛了也能夠會喪失事務的數據。而值2只會在全部操作體系掛了時才能夠丟數據。）

# 加快InnoDB的封閉. 這會阻攔InnoDB在封閉時做全消除和拔出緩沖歸並.
 # 這能夠極年夜增長關機時光, 然則取而代之的是InnoDB能夠鄙人次啟動時做這些操作.
 #innodb_fast_shutdown

# 用來緩沖日記數據的緩沖區的年夜小.
 # 當此值快滿時, InnoDB將必需刷新數據到磁盤上.
 # 因為根本上每秒都邑刷新一次,所以沒有需要將此值設置的太年夜(乃至關於長事務而言)

innodb_log_buffer_size = 16M

# 在日記組中每一個日記文件的年夜小.
 # 你應當設置日記文件總合年夜小到你緩沖池年夜小的25%~100%
 # 來防止在日記文件覆寫上不用要的緩沖池刷新行動.
 # 豈論若何, 請留意一個年夜的日記文件年夜小會增長恢復過程所須要的時光. innodb_log_file_size = 512M

# 在日記組中的文件總數.
 # 平日來講2~3是比擬好的.
innodb_log_files_in_group = 3

# InnoDB的日記文件地點地位. 默許是MySQL的datadir.
 # 你可以將其指定到一個自力的硬盤上或許一個RAID1卷下去進步其機能
#innodb_log_group_home_dir

# 在InnoDB緩沖池中最年夜許可的髒頁面的比例.
 # 假如到達限額, InnoDB會開端刷新他們避免他們妨害到清潔數據頁面.
 # 這是一個軟限制,不被包管相對履行.
innodb_max_dirty_pages_pct = 90

# InnoDB用來刷新日記的辦法.
 # 表空間老是應用兩重寫入刷新辦法 # 默許值是 “fdatasync”, 另外一個是 “O_DSYNC”.
 #innodb_flush_method=O_DSYNC

# 在被回滾前,一個InnoDB的事務應當期待一個鎖被同意多久.
 # InnoDB在其具有的鎖表中主動檢測事務逝世鎖而且回滾事務.
 # 假如你應用 LOCK TABLES 指令, 或許在異樣事務中應用除InnoDB之外的其他事務平安的存儲引擎
# 那末一個逝世鎖能夠產生而InnoDB沒法留意到.
 # 這類情形下這個timeout值關於處理這類成績就異常有贊助.
innodb_lock_wait_timeout = 120

[mysqldump]
 # 不要在將內存中的全部成果寫入磁盤之前緩存. 在導出異常偉大的表時須要此項 quick

max_allowed_packet = 32M

[mysql] no-auto-rehash

# 僅僅許可應用鍵值的 UPDATEs 和 DELETEs .
 #safe-updates

[isamchk]
 key_buffer = 2048M
 sort_buffer_size = 2048M
 read_buffer = 32M
 write_buffer = 32M

[myisamchk]
 key_buffer = 2048M
 sort_buffer_size = 2048M
 read_buffer = 32M
 write_buffer = 32M

[mysqlhotcopy]
 interactive-timeout

[mysqld_safe]
 # 增長每一個過程的可翻開文件數目.
 # 正告: 確認你曾經將全體系限制設定的足夠高!
 # 翻開年夜量表須要將此值設年夜
open-files-limit = 8192

from : http://www.ha97.com/4110.html