MySQL 5.5.13

參數(shù)說明：

[client]

character-set-server = utf8

port = 3306

socket = /data/mysql/3306/mysql.sock

[mysqld]

character-set-server = utf8

user = mysql

port = 3306

socket = /data/mysql/3306/mysql.sock

basedir = /usr/local/webserver/mysql

datadir = /data/mysql/3306/data

log-error = /data/mysql/3306/mysql_error.log

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

# table_cache 參數(shù)設置表高速緩存的數(shù)目。每個連接進來，都會至少打開一個表緩存。#因此， table_cache 的大小應與 max_connections 的設置有關(guān)。例如，對于 200 個#并行運行的連接，應該讓表的緩存至少有 200 × N ，這里 N 是應用可以執(zhí)行的查詢#的一個聯(lián)接中表的最大數(shù)量。此外，還需要為臨時表和文件保留一些額外的文件描述符。

#當 Mysql 訪問一個表時，如果該表在緩存中已經(jīng)被打開，則可以直接訪問緩存；如果#還沒有被緩存，但是在 Mysql 表緩沖區(qū)中還有空間，那么這個表就被打開并放入表緩#沖區(qū)；如果表緩存滿了，則會按照一定的規(guī)則將當前未用的表釋放，或者臨時擴大表緩存來存放，使用表緩存的好處是可以更快速地訪問表中的內(nèi)容。執(zhí)行 flush tables 會#清空緩存的內(nèi)容。一般來說，可以通過查看數(shù)據(jù)庫運行峰值時間的狀態(tài)值 Open_tables #和 Opened_tables ，判斷是否需要增加 table_cache 的值（其中 open_tables 是當#前打開的表的數(shù)量， Opened_tables 則是已經(jīng)打開的表的數(shù)量）。即如果open_tables接近table_cache的時候，并且Opened_tables這個值在逐步增加，那就要考慮增加這個#值的大小了。還有就是Table_locks_waited比較高的時候，也需要增加table_cache。

open_files_limit = 10240

table_cache = 512

#非動態(tài)變量，需要重啟服務

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

back_log = 600

#MySQL允許最大連接數(shù)

max_connections = 5000

#可以允許多少個錯誤連接

max_connect_errors = 6000

#使用–skip-external-locking MySQL選項以避免外部鎖定。該選項默認開啟

external-locking = FALSE

#設置最大包,限制server接受的數(shù)據(jù)包大小，避免超長SQL的執(zhí)行有問題默認值為16M，當MySQL客戶端或mysqld服務器收到大于max_allowed_packet字節(jié)的信息包時，將發(fā)出“信息包過大”錯誤，并關(guān)閉連接。對于某些客戶端，如果通信信息包過大，在執(zhí)行查詢期間，可能會遇到“丟失與MySQL服務器的連接”錯誤。默認值16M。

#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

max_allowed_packet = 32M

# Sort_Buffer_Size 是一個connection級參數(shù)，在每個connection（session）第一次需要使用這個buffer的時候，一次性分配設置的內(nèi)存。

#Sort_Buffer_Size 并不是越大越好，由于是connection級的參數(shù)，過大的設置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。例如：500個連接將會消耗 500*sort_buffer_size(8M)=4G內(nèi)存

#Sort_Buffer_Size 超過2KB的時候，就會使用mmap() 而不是 malloc() 來進行內(nèi)存分配，導致效率降低。

#技術(shù)導讀 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/

#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

#explain select*from table where order limit；出現(xiàn)filesort

#屬重點優(yōu)化參數(shù)

sort_buffer_size = 8M

#用于表間關(guān)聯(lián)緩存的大小

join_buffer_size = 1M

#服務器線程緩存這個值表示可以重新利用保存在緩存中線程的數(shù)量,當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程，增加這個值可以改善系統(tǒng)性能.通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個變量的作用

thread_cache_size = 300

#設置thread_concurrency的值的正確與否, 對mysql的性能影響很大, 在多個cpu(或多核)的情況下，錯誤設置了thread_concurrency的值, 會導致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時刻只能一個cpu(或核)在工作的情況。thread_concurrency應設為CPU核數(shù)的2倍. 比如有一個雙核的CPU, 那么thread_concurrency的應該為4; 2個雙核的cpu, thread_concurrency的值應為8

#屬重點優(yōu)化參數(shù)

thread_concurrency = 8

#對于使用MySQL的用戶，對于這個變量大家一定不會陌生。前幾年的MyISAM引擎優(yōu)化中，這個參數(shù)也是一個重要的優(yōu)化參數(shù)。但隨著發(fā)展，這個參數(shù)也爆露出來一些問題。機器的內(nèi)存越來越大，人們也都習慣性的把以前有用的參數(shù)分配的值越來越大。這個參數(shù)加大后也引發(fā)了一系列問題。我們首先分析一下query_cache_size的工作原理：一個SELECT查詢在DB中工作后，DB會把該語句緩存下來，當同樣的一個SQL再次來到DB里調(diào)用時，DB在該表沒發(fā)生變化的情況下把結(jié)果從緩存中返回給Client。這里有一個關(guān)建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內(nèi)沒有發(fā)生變更。那如果該表在發(fā)生變更時，Query_cache里的數(shù)據(jù)又怎么處理呢？首先要把Query_cache和該表相關(guān)的語句全部置為失效，然后在寫入更新。那么如果Query_cache非常大，該表的查詢結(jié)構(gòu)又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎么這么慢了。所以在數(shù)據(jù)庫寫入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過大。而且在高并發(fā)，寫入量大的系統(tǒng)，建系把該功能禁掉。

#重點優(yōu)化參數(shù)（主庫 增刪改-MyISAM）

query_cache_size = 512M

#指定單個查詢能夠使用的緩沖區(qū)大小，缺省為1M

query_cache_limit = 2M

#默認是4KB，設置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費

#查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

#如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。

#查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

#查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。

#查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

query_cache_min_res_unit = 2k

default-storage-engine = MyISAM

#限定用于每個數(shù)據(jù)庫線程的棧大小。默認設置足以滿足大多數(shù)應用

thread_stack = 192K

# 設定默認的事務隔離級別.可用的級別如下:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

# 1.READ UNCOMMITTED-讀未提交2.READ COMMITTE-讀已提交3.REPEATABLE READ -可重復讀4.SERIALIZABLE -串行

transaction_isolation = READ-COMMITTED

# tmp_table_size 的默認大小是 32M。如果一張臨時表超出該大小，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值。

tmp_table_size = 246M

max_heap_table_size = 246M

#索引緩存大小: 它決定了數(shù)據(jù)庫索引處理的速度，尤其是索引讀的速度

key_buffer_size = 512M

# MySql讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySql會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

read_buffer_size = 4M

# MySql的隨機讀（查詢操作）緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySql會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內(nèi)存開銷過大。

read_rnd_buffer_size = 16M

#批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認為8M

bulk_insert_buffer_size = 64M

# MyISAM表發(fā)生變化時重新排序所需的緩沖

myisam_sort_buffer_size = 128M

# MySQL重建索引時所允許的最大臨時文件的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

# 如果文件大小比此值更大,索引會通過鍵值緩沖創(chuàng)建(更慢)

myisam_max_sort_file_size = 10G

# 如果一個表擁有超過一個索引, MyISAM 可以通過并行排序使用超過一個線程去修復他們.

# 這對于擁有多個CPU以及大量內(nèi)存情況的用戶,是一個很好的選擇.

myisam_repair_threads = 1

#自動檢查和修復沒有適當關(guān)閉的 MyISAM 表

myisam_recover

interactive_timeout = 120

wait_timeout = 120

innodb_data_home_dir = /data/mysql/3306/data

#表空間文件 重要數(shù)據(jù)

innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

#這個參數(shù)用來設置 InnoDB 存儲的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小，類似于Oracle的library cache。這不是一個強制參數(shù)，可以被突破。

innodb_additional_mem_pool_size = 16M

#這對Innodb表來說非常重要。Innodb相比MyISAM表對緩沖更為敏感。MyISAM可以在默認的 key_buffer_size 設置下運行的可以，然而Innodb在默認的 innodb_buffer_pool_size 設置下卻跟蝸牛似的。由于Innodb把數(shù)據(jù)和索引都緩存起來，無需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話則可以設置它高達 70-80% 的可用內(nèi)存。一些應用于 key_buffer 的規(guī)則有 — 如果你的數(shù)據(jù)量不大，并且不會暴增，那么無需把 innodb_buffer_pool_size 設置的太大了

innodb_buffer_pool_size = 512M

#文件IO的線程數(shù)，一般為 4，但是在 Windows 下，可以設置得較大。

innodb_file_io_threads = 4

# 在InnoDb核心內(nèi)的允許線程數(shù)量.

# 最優(yōu)值依賴于應用程序,硬件以及操作系統(tǒng)的調(diào)度方式.

# 過高的值可能導致線程的互斥顛簸.

innodb_thread_concurrency = 8

#如果將此參數(shù)設置為1，將在每次提交事務后將日志寫入磁盤。為提供性能，可以設置為0或2，但要承擔在發(fā)生故障時丟失數(shù)據(jù)的風險。設置為0表示事務日志寫入日志文件，而日志文件每秒刷新到磁盤一次。設置為2表示事務日志將在提交時寫入日志，但日志文件每次刷新到磁盤一次。

innodb_flush_log_at_trx_commit = 2

#此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，但意外的故障將會丟失數(shù)據(jù).MySQL開發(fā)人員建議設置為1－8M之間

innodb_log_buffer_size = 16M

#此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設置可以提高性能，但也會增加恢復故障數(shù)據(jù)庫所需的時間

innodb_log_file_size = 128M

#為提高性能，MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設置為3M

innodb_log_files_in_group = 3

#推薦閱讀 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

# Buffer_Pool中Dirty_Page所占的數(shù)量，直接影響InnoDB的關(guān)閉時間。參數(shù)innodb_max_dirty_pages_pct可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸運的是innodb_max_dirty_pages_pct是可以動態(tài)改變的。所以，在關(guān)閉InnoDB之前先將innodb_max_dirty_pages_pct調(diào)小，強制數(shù)據(jù)塊Flush一段時間，則能夠大大縮短MySQL關(guān)閉的時間。

innodb_max_dirty_pages_pct = 90

# InnoDB 有其內(nèi)置的死鎖檢測機制，能導致未完成的事務回滾。但是，如果結(jié)合InnoDB使用MyISAM的lock tables 語句或第三方事務引擎,則InnoDB無法識別死鎖。為消除這種可能性，可以將innodb_lock_wait_timeout設置為一個整數(shù)值，指示 MySQL在允許其他事務修改那些最終受事務回滾的數(shù)據(jù)之前要等待多長時間(秒數(shù))

innodb_lock_wait_timeout = 120

#獨享表空間（關(guān)閉）

innodb_file_per_table = 0

#start mysqld with –slow-query-log-file=/data/mysql/3306/slow.log

slow_query_log

long_query_time = 1

replicate-ignore-db = mysql

replicate-ignore-db = test

replicate-ignore-db = information_schema

#配置從庫上的更新操作是否寫二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數(shù)，以便從庫的從庫能夠進行日志同步這個參數(shù)要和—logs-bin一起使用

log-slave-updates

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

binlog_cache_size = 4M

#STATEMENT,ROW,MIXED

#基于SQL語句的復制(statement-based replication, SBR)，基于行的復制(row-based replication, RBR)，混合模式復制(mixed-based replication, MBR)。相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。

binlog_format = MIXED

max_binlog_cache_size = 64M

max_binlog_size = 1G

relay-log-index = /data/mysql/3306/relaylog/relaylog

relay-log-info-file = /data/mysql/3306/relaylog/relaylog

relay-log = /data/mysql/3306/relaylog/relaylog

expire_logs_days = 30

skip-name-resolve

#master-connect-retry = 10

slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

server-id = 1

[mysqldump]

quick

max_allowed_packet = 32M

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

最新評論