安裝MySQL后,配置文件my.cnf在MySQL安裝目錄/share/mysql目錄中,該目錄中還包含多個(gè)配置文件可供參考,有my-large.cnf ,my-huge.cnf, ?my-medium.cnf,my-small.cnf,分別對(duì)應(yīng)大中小型數(shù)據(jù)庫(kù)應(yīng)用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件。
下面列出了對(duì)性能優(yōu)化影響較大的主要變量,主要分為連接請(qǐng)求的變量和緩沖區(qū)變量。
一、連接請(qǐng)求的變量
max_connections
MySQL的最大連接數(shù),增加該值增加mysqld要求的文件描述符的數(shù)量。如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大,建議調(diào)高此值,以增加并行連接數(shù)量,當(dāng)然這建立在機(jī)器能支撐的情況下,因?yàn)槿绻B接數(shù)越多,介于MySQL會(huì)為每個(gè)連接提供連接緩沖區(qū),就會(huì)開(kāi)銷越多的內(nèi)存,所以要適當(dāng)調(diào)整該值,不能盲目提高設(shè)值。數(shù)值過(guò)小會(huì)經(jīng)常出現(xiàn)ERROR?1040:?Too?many?connections錯(cuò)誤,可以過(guò)‘conn%’通配符查看當(dāng)前狀態(tài)的連接數(shù)量,以定奪該值的大小。show?variables?like?’max_connections‘????查看最大連接數(shù)
show??status?like?‘max_used_connections’???查看響應(yīng)的連接數(shù)
如下:
mysql>?show?variables?like?‘max_connections‘;+———————–+——-+|?Variable_name |?Value?|+———————–+——-+|?max_connections?|?256 |+———————–+——-+mysql>?show?status?like?‘max%connections‘;+———————–+——-+|?Variable_name ??????|?Value?|+—————————-+——-+|?max_used_connections?|?256|+—————————-+——-+max_used_connections?/?max_connections?*?100%?(理想值≈?85%)?
如果max_used_connections跟max_connections相同?那么就是max_connections設(shè)置過(guò)低或者超過(guò)服務(wù)器負(fù)載上限了,低于10%則設(shè)置過(guò)大。
back_log
MySQL能暫存的連接數(shù)量。當(dāng)主要MySQL線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求,這就起作用。如果MySQL的連接數(shù)據(jù)達(dá)到max_connections時(shí),新來(lái)的請(qǐng)求將會(huì)被存在堆棧中,以等待某一連接釋放資源,該堆棧的數(shù)量即back_log,如果等待連接的數(shù)量超過(guò)back_log,將不被授予連接資源。back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在堆棧中。只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接,你需要增加它,換句話說(shuō),這值對(duì)到來(lái)的TCP/IP連接的偵聽(tīng)隊(duì)列的大小。當(dāng)觀察你主機(jī)進(jìn)程列表(mysql>?show?full?processlist),發(fā)現(xiàn)大量264084?|?unauthenticated?user?|?xxx.xxx.xxx.xxx?|?NULL?|?Connect?|?NULL?|?login?|?NULL?的待連接進(jìn)程時(shí),就要加大back_log?的值了。默認(rèn)數(shù)值是50,可調(diào)優(yōu)為128,對(duì)于Linux系統(tǒng)設(shè)置范圍為小于512的整數(shù)。
?interactive_timeout
一個(gè)交互連接在被服務(wù)器在關(guān)閉前等待行動(dòng)的秒數(shù)。一個(gè)交互的客戶被定義為對(duì)mysql_real_connect()使用CLIENT_INTERACTIVE?選項(xiàng)的客戶。?默認(rèn)數(shù)值是28800,可調(diào)優(yōu)為7200。
二、緩沖區(qū)變量
全局緩沖:
key_buffer_size
key_buffer_size指定索引緩沖區(qū)的大小,它決定索引處理的速度,尤其是索引讀的速度。通過(guò)檢查狀態(tài)值Key_read_requests和Key_reads,可以知道key_buffer_size設(shè)置是否合理。比例key_reads?/?key_read_requests應(yīng)該盡可能的低,至少是1:100,1:1000更好(上述狀態(tài)值可以使用SHOW?STATUS?LIKE?‘key_read%’獲得)。key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表,但是內(nèi)部的臨時(shí)磁盤表是MyISAM表,也要使用該值。可以使用檢查狀態(tài)值created_tmp_disk_tables得知詳情。
舉例如下:
mysql>?show?variables?like?‘key_buffer_size‘;+——————-+————+|?Variable_name?|?Value??????|+———————+————+|?key_buffer_size?|?536870912?|+————?———-+————+key_buffer_size為512MB,我們?cè)倏匆幌耴ey_buffer_size的使用情況:mysql>?show?global?status?like?‘key_read%‘;+————————+————-+|?Variable_name ??|?Value????|+————————+————-+|?Key_read_requests|?27813678764?||?Key_reads |??6798830??????|+————————+————-+一共有27813678764個(gè)索引讀取請(qǐng)求,有6798830個(gè)請(qǐng)求在內(nèi)存中沒(méi)有找到直接從硬盤讀取索引,計(jì)算索引未命中緩存的概率:key_cache_miss_rate?=Key_reads?/?Key_read_requests?*?100%,設(shè)置在1/1000左右較好默認(rèn)配置數(shù)值是8388600(8M),主機(jī)有4GB內(nèi)存,可以調(diào)優(yōu)值為268435456(256MB)。
query_cache_size
使用查詢緩沖,MySQL將查詢結(jié)果存放在緩沖區(qū)中,今后對(duì)于同樣的SELECT語(yǔ)句(區(qū)分大小寫(xiě)),將直接從緩沖區(qū)中讀取結(jié)果。通過(guò)檢查狀態(tài)值Qcache_*,可以知道query_cache_size設(shè)置是否合理(上述狀態(tài)值可以使用SHOW?STATUS?LIKE?‘Qcache%’獲得)。如果Qcache_lowmem_prunes的值非常大,則表明經(jīng)常出現(xiàn)緩沖不夠的情況,如果Qcache_hits的值也非常大,則表明查詢緩沖使用非常頻繁,此時(shí)需要增加緩沖大小;如果Qcache_hits的值不大,則表明你的查詢重復(fù)率很低,這種情況下使用查詢緩沖反而會(huì)影響效率,那么可以考慮不用查詢緩沖。此外,在SELECT語(yǔ)句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。與查詢緩沖有關(guān)的參數(shù)還有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查詢緩沖,可以設(shè)置為0、1、2,該變量是SESSION級(jí)的變量。query_cache_limit指定單個(gè)查詢能夠使用的緩沖區(qū)大小,缺省為1M。query_cache_min_res_unit是在4.1版本以后引入的,它指定分配緩沖區(qū)空間的最小單位,缺省為4K。檢查狀態(tài)值Qcache_free_blocks,如果該值非常大,則表明緩沖區(qū)中碎片很多,這就表明查詢結(jié)果都比較小,此時(shí)需要減小query_cache_min_res_unit。
舉例如下:
mysql>?show?global?status?like?‘qcache%‘;+——————————-+—————–+|?Variable_name??????????????????|?Value ???????|+——————————-+—————–+|?Qcache_free_blocks ???????|?22756 ??????||?Qcache_free_memory ????|?76764704????||?Qcache_hits ??????|?213028692?||?Qcache_inserts ?????|?208894227???||?Qcache_lowmem_prunes???|?4010916??????||?Qcache_not_cached |?13385031????||?Qcache_queries_in_cache?|?43560 ||?Qcache_total_blocks??????????|?111212 ?????|+——————————-+—————–+mysql>?show?variables?like?‘query_cache%‘;+————————————–+————–+|?Variable_name ???????|?Value ?????|+————————————–+———–+|?query_cache_limit ????|?2097152?????||?query_cache_min_res_unit ?????|?4096 ??||?query_cache_size ????|?203423744?||?query_cache_type ???|?ON ??????????||?query_cache_wlock_invalidate?|?OFF ??|+————————————–+—————+查詢緩存碎片率=?Qcache_free_blocks?/?Qcache_total_blocks?*?100%如果查詢緩存碎片率超過(guò)20%,可以用FLUSH?QUERY?CACHE整理緩存碎片,或者試試減小query_cache_min_res_unit,如果你的查詢都是小數(shù)據(jù)量的話。查詢緩存利用率=?(query_cache_size?–?Qcache_free_memory)?/?query_cache_size?*?100%查詢緩存利用率在25%以下的話說(shuō)明query_cache_size設(shè)置的過(guò)大,可適當(dāng)減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes?>?50的話說(shuō)明query_cache_size可能有點(diǎn)小,要不就是碎片太多。查詢緩存命中率=?(Qcache_hits?–?Qcache_inserts)?/?Qcache_hits?*?100%示例服務(wù)器查詢緩存碎片率=20.46%,查詢緩存利用率=62.26%,查詢緩存命中率=1.94%,命中率很差,可能寫(xiě)操作比較頻繁吧,而且可能有些碎片。
每個(gè)連接的緩沖:
record_buffer_size
每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區(qū)。如果你做很多順序掃描,你可能想要增加該值。默認(rèn)數(shù)值是131072(128K),可改為16773120?(16M)
read_rnd_buffer_size
隨機(jī)讀緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如,按照排序順序),將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí),MySQL會(huì)首先掃描一遍該緩沖,以避免磁盤搜索,提高查詢速度,如果需要排序大量數(shù)據(jù),可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間,所以應(yīng)盡量適當(dāng)設(shè)置該值,以避免內(nèi)存開(kāi)銷過(guò)大。一般可設(shè)置為16M
sort_buffer_size
每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速ORDER?BY或GROUP?BY操作。默認(rèn)數(shù)值是2097144(2M),可改為16777208?(16M)。
join_buffer_size
聯(lián)合查詢操作所能使用的緩沖區(qū)大小record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size為每個(gè)線程獨(dú)占,也就是說(shuō),如果有100個(gè)線程連接,則占用為16M*100
table_cache
表高速緩存的大小。每當(dāng)MySQL訪問(wèn)一個(gè)表時(shí),如果在表緩沖區(qū)中還有空間,該表就被打開(kāi)并放入其中,這樣可以更快地訪問(wèn)表內(nèi)容。通過(guò)檢查峰值時(shí)間的狀態(tài)值Open_tables和Opened_tables,可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache,并且opened_tables在不斷增長(zhǎng),那么你就需要增加table_cache的值了(上述狀態(tài)值可以使用SHOW?STATUS?LIKE?‘Open%tables’獲得)。注意,不能盲目地把table_cache設(shè)置成很大的值。如果設(shè)置得太高,可能會(huì)造成文件描述符不足,從而造成性能不穩(wěn)定或者連接失敗。1G內(nèi)存機(jī)器,推薦值是128-256。內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。
max_heap_table_size
用戶可以創(chuàng)建的內(nèi)存表(memory?table)的大小。這個(gè)值用來(lái)計(jì)算內(nèi)存表的最大行數(shù)值。這個(gè)變量支持動(dòng)態(tài)改變,即set?@max_heap_table_size=#這個(gè)變量和tmp_table_size一起限制了內(nèi)部?jī)?nèi)存表的大小。如果某個(gè)內(nèi)部heap(堆積)表大小超過(guò)tmp_table_size,MySQL可以根據(jù)需要自動(dòng)將內(nèi)存中的heap表改為基于硬盤的MyISAM表。
tmp_table_size
通過(guò)設(shè)置tmp_table_size選項(xiàng)來(lái)增加一張臨時(shí)表的大小,例如做高級(jí)GROUP?BY操作生成的臨時(shí)表。如果調(diào)高該值,MySQL同時(shí)將增加heap表的大小,可達(dá)到提高聯(lián)接查詢速度的效果,建議盡量?jī)?yōu)化查詢,要確保查詢過(guò)程中生成的臨時(shí)表在內(nèi)存中,避免臨時(shí)表過(guò)大導(dǎo)致生成基于硬盤的MyISAM表。mysql>?show?global?status?like?‘created_tmp%‘;+——————————–+———+|?Variable_name ???????????|?Value |+———————————-+———+|?Created_tmp_disk_tables?|?21197??||?Created_tmp_files |?58 ||?Created_tmp_tables |?1771587?|+——————————–+———–+每次創(chuàng)建臨時(shí)表,Created_tmp_tables增加,如果臨時(shí)表大小超過(guò)tmp_table_size,則是在磁盤上創(chuàng)建臨時(shí)表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù),比較理想的配置是:Created_tmp_disk_tables?/?Created_tmp_tables?*?100%?<=?25%比如上面的服務(wù)器Created_tmp_disk_tables?/?Created_tmp_tables?*?100%?=1.20%,應(yīng)該相當(dāng)好了默認(rèn)為16M,可調(diào)到64-256最佳,線程獨(dú)占,太大可能內(nèi)存不夠I/O堵塞
thread_cache_size
可以復(fù)用的保存在中的線程的數(shù)量。如果有,新的線程從緩存中取得,當(dāng)斷開(kāi)連接的時(shí)候如果有空間,客戶的線置在緩存中。如果有很多新的線程,為了提高性能可以這個(gè)變量值。通過(guò)比較?Connections和Threads_created狀態(tài)的變量,可以看到這個(gè)變量的作用。默認(rèn)值為110,可調(diào)優(yōu)為80。
thread_concurrency
推薦設(shè)置為服務(wù)器?CPU核數(shù)的2倍,例如雙核的CPU,?那么thread_concurrency的應(yīng)該為4;2個(gè)雙核的cpu,?thread_concurrency的值應(yīng)為8。默認(rèn)為8
wait_timeout
指定一個(gè)請(qǐng)求的最大連接時(shí)間,對(duì)于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。
三、配置InnoDB的幾個(gè)變量
innodb_buffer_pool_size
對(duì)于InnoDB表來(lái)說(shuō),innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。對(duì)于單獨(dú)的MySQL數(shù)據(jù)庫(kù)服務(wù)器,最大可以把該值設(shè)置成物理內(nèi)存的80%。根據(jù)MySQL手冊(cè),對(duì)于2G內(nèi)存的機(jī)器,推薦值是1G(50%)。
innodb_flush_log_at_trx_commit
主要控制了innodb將log?buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤的時(shí)間點(diǎn),取值分別為0、1、2三個(gè)。0,表示當(dāng)事務(wù)提交時(shí),不做日志寫(xiě)入操作,而是每秒鐘將log?buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤一次;1,則在每秒鐘或是每次事物的提交都會(huì)引起日志文件寫(xiě)入、flush磁盤的操作,確保了事務(wù)的ACID;設(shè)置為2,每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作,但每秒鐘完成一次flush磁盤操作。實(shí)際測(cè)試發(fā)現(xiàn),該值對(duì)插入數(shù)據(jù)的速度影響非常大,設(shè)置為2時(shí)插入10000條記錄只需要2秒,設(shè)置為0時(shí)只需要1秒,而設(shè)置為1時(shí)則需要229秒。因此,MySQL手冊(cè)也建議盡量將插入操作合并成一個(gè)事務(wù),這樣可以大幅提高速度。根據(jù)MySQL手冊(cè),在允許丟失最近部分事務(wù)的危險(xiǎn)的前提下,可以把該值設(shè)為0或2。
innodb_log_buffer_size
log緩存大小,一般為1-8M,默認(rèn)為1M,對(duì)于較大的事務(wù),可以增大緩存大小。可設(shè)置為4M或8M。
innodb_additional_mem_pool_size
該參數(shù)指定InnoDB用來(lái)存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大,只要夠用就行,應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用,MySQL會(huì)在錯(cuò)誤日志中寫(xiě)入一條警告信息。根據(jù)MySQL手冊(cè),對(duì)于2G內(nèi)存的機(jī)器,推薦值是20M,可適當(dāng)增加。
innodb_thread_concurrency=8
推薦設(shè)置為?2*(NumCPUs+NumDisks),默認(rèn)一般為8
?原文作者:破修電腦的
分享原文地址:http://www.cnblogs.com/Bozh/archive/2013/01/22/2871545.html
轉(zhuǎn)載于:https://blog.51cto.com/asaderas/1706679
總結(jié)
以上是生活随笔為你收集整理的Mysql配置优化浅谈的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問(wèn)題。
如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò),歡迎將生活随笔推薦給好友。