《Mysql應用mysql性能優化 my.cnf配置文件詳解》要點：
本文介紹了Mysql應用mysql性能優化 my.cnf配置文件詳解，希望對您有用。如果有疑問，可以聯系我們。

MYSQL實例本節內容：
mysql配置文件my.cnf用法詳解
硬件：內存16g

MYSQL實例一,my.cnf配置參考：
?

MYSQL實例二,my.cnf配置文件介紹

MYSQL實例back_log = 600?
#在mysql暫時停止響應新請求之前,短時間內的多少個請求可以被存在堆棧中.如果系統在短時間內有很多連接,則需要增大該參數的值,該參數值指定到來的tcp/ip連接的監聽隊列的大小.默認值50.

MYSQL實例max_connections = 3000?
#mysql允許最大的進程連接數,如果經常出現too many connections的錯誤提示,則需要增大此值.

MYSQL實例max_connect_errors = 6000?
#設置每個主機的連接請求異常中斷的最大次數,當超過該次數,mysql服務器將禁止host的連接請求,直到mysql服務器重啟或通過flush hosts命令清空此host的相關信息.

MYSQL實例table_cache = 614
#指示表調整緩沖區大小.

MYSQL實例# table_cache 參數設置表高速緩存數目.每個連接進來,都會至少打開一個表緩存.因此, table_cache 的大小應與 max_connections 的設置有關.例如,對于 200 個并行運行的連接,應該讓表的緩存至少有 200 × n ,這里 n 是應用可以執行的查詢的一個聯接中表的最大數量.此外,還需要為臨時表和文件保留一些額外文件描述符.
# 當 mysql 訪問一個表時,如果該表在緩存中已經被打開,則可以直接訪問緩存；如果沒有被緩存,但是在 mysql 表緩沖區中還有空間,那么這個表就被打開并放入表緩沖區；如果表緩存滿了,則會按照一定的規則將當前未用的表釋放,或者臨時擴大表緩存來存放,使用表緩存的好處是可以更快速地訪問表中的內容.

MYSQL實例執行 flush tables 會清空緩存的內容.

MYSQL實例一般來說,可以通過查看數據庫運行峰值時間的狀態值 open_tables 和 opened_tables ,判斷是否需要增加 table_cache 的值(其中 open_tables 是當前打開的表的數量, opened_tables 則是已經打開的表的數量).即如果open_tables接近table_cache的時候,并且opened_tables這個值在逐步增加,那就要考慮增加table_cache這個值的大小了.還有就是table_locks_waited比較高的時候,也需要增加table_cache.

MYSQL實例external-locking = false?
#使用–skip-external-locking mysql選項以避免外部鎖定.該選項默認開啟

MYSQL實例max_allowed_packet = 32m?
#設置在網絡傳輸中一次消息傳輸量的最大值.系統默認值 為1mb,最大值是1gb,必須設置1024的倍數.

MYSQL實例sort_buffer_size = 2m?
# sort_buffer_size 是一個connection級參數,在每個connection(session)第一次需要使用這個buffer的時候,一次性分配設置的內存.
#sort_buffer_size 并不是越大越好,由于是connection級的參數,過大的設置+高并發可能會耗盡系統內存資源.例如：500個連接將會消耗 500*sort_buffer_size(8m)=4g內存
#sort_buffer_size 超過2kb的時候,就會使用mmap() 而不是 malloc() 來進行內存分配,導致效率降低.

MYSQL實例join_buffer_size = 2m?
#用于表間關聯緩存的大小,和sort_buffer_size一樣,該參數對應的分配內存也是每個連接獨享.

MYSQL實例thread_cache_size = 300?
# 服務器線程緩存這個值表示可以重新利用保存在緩存中線程的數量,當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求,那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求,那么這個線程將被重新創建,如果有很多新的線程,增加這個值可以改善系統性能.通過比較 connections 和 threads_created 狀態的變量,可以看到這個變量的作用.設置規則如下：1gb 內存配置為8,2gb配置為16,3gb配置為32,4gb或更高內存,可配置更大.

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

MYSQL實例query_cache_size = 64m?
#在myisam引擎優化中,這個參數也是一個重要的優化參數.但也爆露出來一些問題.機器的內存越來越大,習慣性把參數分配的值越來越大.這個參數加大后也引發了一系列問題.我們首先分析一下 query_cache_size的工作原理：一個select查詢在db中工作后,db會把該語句緩存下來,當同樣的一個sql再次來到db里調用時,db在該表沒發生變化的情況下把結果從緩存中返回給client.這里有一個關建點,就是db在利用query_cache工作時,要求該語句涉及的表在這段時間內沒有發生變更.那如果該表在發生變更時,query_cache里的數據又怎么處理呢?首先要把query_cache和該表相關的語句全部置為失效,然后在寫入更新.那么如果query_cache非常大,該表的查詢結構又比較多,查詢語句失效也慢,一個更新或是insert就會很慢,這樣看到的就是update或是insert怎么這么慢了.所以在數據庫寫入量或是更新量也比較大的系統,該參數不適合分配過大.而且在高并發,寫入量大的系統,建議把該功能禁掉.

MYSQL實例query_cache_limit = 4m???
#指定單個查詢能夠使用的緩沖區大小,缺省為1m

MYSQL實例query_cache_min_res_unit = 2k?
#默認是4kb,設置值大對大數據查詢有好處,但如果你的查詢都是小數據查詢,就容易造成內存碎片和浪費
#查詢緩存碎片率 = qcache_free_blocks / qcache_total_blocks * 100%
#如果查詢緩存碎片率超過20%,可以用flush query cache整理緩存碎片,或者試試減小query_cache_min_res_unit,如果你的查詢都是小數據量的話.
#查詢緩存利用率 = (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%

MYSQL實例default-storage-engine = myisam
#default_table_type = innodb

MYSQL實例thread_stack = 192k?
#設置mysql每個線程的堆棧大小,默認值足夠大,可滿足普通操作.可設置范圍為128k至4gb,默認為192kb.

MYSQL實例transaction_isolation = read-committed?
# 設定默認的事務隔離級別.可用的級別如下:
read uncommitted-讀未提交

MYSQL實例read committe-讀已提交

MYSQL實例repeatable read -可重復讀

MYSQL實例serializable -串行

MYSQL實例tmp_table_size = 256m?
# tmp_table_size 的默認大小是 32m.如果一張臨時表超出該大小,mysql產生一個 the table tbl_name is full 形式的錯誤,如果你做很多高級 group by 查詢,增加 tmp_table_size 值.如果超過該值,則會將臨時表寫入磁盤.
max_heap_table_size = 256m
long_query_time = 2
log_long_format
log-slow-queries=/data/3306/slow-log.log
#log-bin = /data/3306/mysql-bin
log-bin
binlog_cache_size = 4m
max_binlog_cache_size = 8m
max_binlog_size = 512m

MYSQL實例expire_logs_days = 7
key_buffer_size = 2048m
#批定用于索引的緩沖區大小,增加它可以得到更好的索引處理性能,對于內存在4gb左右的服務器來說,該參數可設置為256mb或384mb.

MYSQL實例read_buffer_size = 1m?
# mysql讀入緩沖區大小.對表進行順序掃描的請求將分配一個讀入緩沖區,mysql會為它分配一段內存緩沖區.read_buffer_size變量控制這一緩沖區的大小.如果對表的順序掃描請求非常頻繁,并且你認為頻繁掃描進行得太慢,可以通過增加該變量值以及內存緩沖區大小提高其性能.和sort_buffer_size一樣,該參數對應的分配內存也是每個連接獨享.

MYSQL實例read_rnd_buffer_size = 16m?
# mysql的隨機讀(查詢操作)緩沖區大小.當按任意順序讀取行時(例如,按照排序順序),將分配一個隨機讀緩存區.進行排序查詢時,mysql會首先掃描一遍該緩沖,以避免磁盤搜索,提高查詢速度,如果需要排序大量數據,可適當調高該值.但mysql會為每個客戶連接發放該緩沖空間,所以應盡量適當設置該值,以避免內存開銷過大.

MYSQL實例bulk_insert_buffer_size = 64m?
#批量插入數據緩存大小,可以有效提高插入效率,默認為8m

MYSQL實例myisam_sort_buffer_size = 128m?
# myisam表發生變化時重新排序所需的緩沖

MYSQL實例myisam_max_sort_file_size = 10g?
# mysql重建索引時所允許的最大臨時文件的大小 (當 repair, alter table 或者 load data infile).
# 如果文件大小比此值更大,索引會通過鍵值緩沖創建(更慢)

MYSQL實例myisam_max_extra_sort_file_size = 10g
myisam_repair_threads = 1?
# 如果一個表擁有超過一個索引, myisam 可以通過并行排序使用超過一個線程去修復他們.
# 這對于擁有多個cpu以及大量內存情況的用戶,是一個很好的選擇. www.jbxue.com

MYSQL實例myisam_recover?
#自動檢查和修復沒有適當關閉的 myisam 表
skip-name-resolve
lower_case_table_names = 1

MYSQL實例server-id = 1

MYSQL實例innodb_additional_mem_pool_size = 16m?
#這個參數用來設置 innodb 存儲的數據目錄信息和其它內部數據結構的內存池大小,類似于oracle的library cache.這不是一個強制參數,可以被突破.

MYSQL實例innodb_buffer_pool_size = 2048m?
# 這對innodb表來說非常重要.innodb相比myisam表對緩沖更為敏感.myisam可以在默認的 key_buffer_size 設置下運行的可以,然而innodb在默認的 innodb_buffer_pool_size 設置下卻跟蝸牛似的.由于innodb把數據和索引都緩存起來,無需留給操作系統太多的內存,因此如果只需要用innodb的話則可以設置它高達 70-80% 的可用內存.一些應用于 key_buffer 的規則有 — 如果你的數據量不大,并且不會暴增,那么無需把 innodb_buffer_pool_size 設置的太大了

MYSQL實例innodb_data_file_path = ibdata1:1024m:autoextend?
#表空間文件 重要數據

MYSQL實例innodb_file_io_threads = 4?
#文件io的線程數,一般為 4,但是在 windows 下,可以設置得較大.

MYSQL實例innodb_thread_concurrency = 8
#服務器有幾個cpu就設置為幾,建議用默認設置,一般為8.

MYSQL實例innodb_flush_log_at_trx_commit = 2?
# 如果將此參數設置為1,將在每次提交事務后將日志寫入磁盤.為提供性能,可以設置為0或2,但要承擔在發生故障時丟失數據的風險.設置為0表示事務日志寫入日志文件,而日志文件每秒刷新到磁盤一次.設置為2表示事務日志將在提交時寫入日志,但日志文件每次刷新到磁盤一次.

MYSQL實例innodb_log_buffer_size = 16m?
#此參數確定些日志文件所用的內存大小,以m為單位.緩沖區更大能提高性能,但意外的故障將會丟失數據.mysql開發人員建議設置為1－8m之間

MYSQL實例innodb_log_file_size = 128m?
#此參數確定數據日志文件的大小,以m為單位,更大的設置可以提高性能,但也會增加恢復故障數據庫所需的時間

MYSQL實例innodb_log_files_in_group = 3?
#為提高性能,mysql可以以循環方式將日志文件寫到多個文件.推薦設置為3m

MYSQL實例innodb_max_dirty_pages_pct = 90?
# buffer_pool中dirty_page所占的數量,直接影響innodb的關閉時間.參數innodb_max_dirty_pages_pct 可以直接控制了dirty_page在buffer_pool中所占的比率,而且幸運的是innodb_max_dirty_pages_pct是可以動態改變的.所以,在關閉innodb之前先將innodb_max_dirty_pages_pct調小,強制數據塊flush一段時間,則能夠大大縮短 mysql關閉的時間.

MYSQL實例innodb_lock_wait_timeout = 120?
# innodb 有其內置的死鎖檢測機制,能導致未完成的事務回滾.但是,如果結合innodb使用myisam的lock tables 語句或第三方事務引擎,則innodb無法識別死鎖.為消除這種可能性,可以將innodb_lock_wait_timeout設置為一個整數值,指示 mysql在允許其他事務修改那些最終受事務回滾的數據之前要等待多長時間(秒數) www.jbxue.com

MYSQL實例innodb_file_per_table = 0?
#獨享表空間(關閉)

MYSQL實例[mysqldump]
quick
max_allowed_packet = 32m

MYSQL實例[mysqld_safe]
log-error=/data/3306/mysql_oldboy.err
pid-file=/data/3306/mysqld.pid

MYSQL實例#補充
#wait_timeout = 10?
#指定一個請求的最大連接時間,對于4gb左右的內存服務器來說,可以將其設置為5-10.
#skip_networking?
#開啟該選可以徹底關閉mysql的tcp/ip連接方式,如果web服務器是以遠程連接的方式訪問mysql數據庫服務器的,則不要開啟該選項,否則將無法正常連接.

MYSQL實例#log-queries-not-using-indexes
將沒有使用索引的查詢也記錄下來

MYSQL實例mysql的監控方法大致分為兩類：
1.連接到mysql數據庫內部,使用show status,show variables,flush status 來查看mysql的各種性能指標.
2. 直接使用mysqladmin查看其性能指標

MYSQL實例mysqladmin兩個參數,status,extended-status
shell > mysqladmin? -uroot -ppassword? variables status
uptime: 4557887????? #mysql運行的秒數
threads: 1?????????? #連接數
questions: 1684130??? #the number of questions (queries) from clients since the server was started.
slow queries: 0????? #the number of queries that have taken more than long_query_time seconds
opens: 221872???? #the number of tables the server has opened.
flush tables: 1???? #the number of flush-*, refresh, and reload commands the server has executed.
open tables: 64???? #the number of tables that currently are open.
queries per second avg: 0.369? #從上次運行開始計算,每秒鐘平均查詢次數

MYSQL實例最完整的信息
shell > mysqladmin? -uroot -ppassword? variables extended-status

MYSQL實例注：value 值的單位是byte ,要得到m ,需除以2次1024
uptime??? 4405546???? mysql服務器已經運行的秒數

MYSQL實例auto_increment_increment????? 1??? 控制列中的值的增量值(步進量)???????????????????????????????????
auto_increment_offset???????? 1??? 確定auto_increment列值的初始值.
兩個變量值只能為1到65,535之間的整數值.設置為非整數值,則會給出錯誤.這兩個變量影響auto_increment列.

MYSQL實例一般不去更改.更改方法：mysql> set @auto_increment_offset=5;
?

MYSQL實例幾個參數的關系：
table_cache * 2 + max_connections＝max_open_files
max_connections 默認為100
mysql>show full processlist;

MYSQL實例max_open_files 由 open_files_limit 參數決定.
mysql打開的最大文件數,受兩個參數的影響：系統打開的最大文件數(ulimit -n)和?? open_files_limit .
加大max_open_files的值
-------------------------------
在/etc/my.cnf加入open_files_limit=8192
在/etc/security/limits.conf添加
?

MYSQL實例最好用sysctl或者修改/etc/sysctl.conf文件,同時還要在配置文件中把open_files_limit這個參數增大,對于4g內存服務器,open_files_limit至少要增大到4096,非特殊情況,設置成8192就可以了.

MYSQL實例mysql>show status;?? (服務器狀態變量,運行服務器的統計和狀態指標)
mysql>show variables;(服務器系統變量,實際上使用的變量的值)
或者
mysql>show status like? '%變量名% ' ;

MYSQL實例例如：
?

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/6462.html