Mysql配置參數(shù)詳解以及優(yōu)化配置分析

1、mysql有以下幾種日志：  錯(cuò)誤日志：    log-err  查詢(xún)?nèi)罩荆?#160;   log  慢查詢(xún)?nèi)罩?  log-slow-queries  更新日志:    log-update  二進(jìn)制日志： log-bin 要把日志生成在 /var/log 目錄下(是系統(tǒng)日志存放的地方，只有 root 賬號(hào)有寫(xiě)權(quán)限)，需要 MySQL 進(jìn)程對(duì)這個(gè)目錄有讀寫(xiě)權(quán)限，一般是不這么做的，也考慮到安全問(wèn)題，包括 MySQL 本身的數(shù)據(jù)安全，因?yàn)閷?duì) MySQL 的所有操作，都會(huì)記錄到

2、常規(guī)查詢(xún)?nèi)罩尽ySQL 的日志就不要用 /var/log/ 目錄下。-2 fdefault-character-set = utf8port    = 3306socket  = /tmp/mysql.sockno-auto-rehash#default-character-set = utf8user    = mysqlport    = 3306socket  = /tmp/mysql.sockbasedir = /usr/local/webserver/mysqldatadir = /data0/mysq

3、l/3306/dataopen_files_limit    = 10240back_log = 600max_connections = 3000 實(shí)際MySQL服務(wù)器允許的最大連接數(shù)16384；max_connect_errors = 6000  可以允許多少個(gè)錯(cuò)誤連接table_cache = 1024  高速緩存的大小.table_cache的值在2G內(nèi)存以下的機(jī)器中的值默認(rèn)時(shí)256到 512，如果機(jī)器有4G內(nèi)存,則默認(rèn)這個(gè)值是2048，但這決意味著機(jī)器內(nèi)存越大，這個(gè)值應(yīng)該越大，因?yàn)閠able_cache加大后，使得mysql對(duì) SQL響應(yīng)的速度

4、更快了，不可避免的會(huì)產(chǎn)生更多的死鎖（dead lock），這樣反而使得數(shù)據(jù)庫(kù)整個(gè)一套操作慢了下來(lái)，嚴(yán)重影響性能。external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2M 是一個(gè)connection級(jí)參數(shù)，在每個(gè)connection第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存.并不是越大越好，由于是connection級(jí)的參數(shù)，過(guò)大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)內(nèi)存資源。join_buffer_size = 2Mthread_cache_size = 64  重新利用保存在緩存中線(xiàn)程的數(shù)量,當(dāng)

5、斷開(kāi)連接時(shí)如果緩存中還有空間,那么客戶(hù)端的線(xiàn)程將被放到緩存中,如果線(xiàn)程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線(xiàn)程將被重新創(chuàng)建,如果有很多新的線(xiàn)程，增加這個(gè)值可以改善系統(tǒng)性能  根據(jù)物理內(nèi)存設(shè)置規(guī)則如下：    1G  -> 8    2G  -> 16    3G  -> 32    >3G  -> 64thread_concurrency = 16 對(duì)mysql的性能影響很大, 在多個(gè)cpu(或多

6、核)的情況下，錯(cuò)誤設(shè)置了thread_concurrency的值, 會(huì)導(dǎo)致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時(shí)刻只能一個(gè)cpu(或核)在工作的情況。  應(yīng)設(shè)為CPU核數(shù)的2倍. 比如有一個(gè)雙核的CPU, 那么thread_concurrency的應(yīng)該為4; 2個(gè)雙核的cpu, thread_concurrency的值應(yīng)為8.query_cache_size = 32M 緩存機(jī)制簡(jiǎn)單的說(shuō)就是緩存sql文本及查詢(xún)結(jié)果，如果運(yùn)行相同的sql，服務(wù)器直接從緩存中取到結(jié)果，而不需要再去解析和執(zhí)行sql。如果表更改了，那么使用這個(gè)表的所有緩沖查詢(xún)將不再有效，查詢(xún)緩存值的相關(guān)條目

7、被清空query_cache_limit = 2M 單個(gè)查詢(xún)能夠使用的緩沖區(qū)大小.缺省為1Mquery_cache_min_res_unit = 2k 它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢(xún)結(jié)果都比較小，此時(shí)需要減小query_cache_min_res_unitdefault-storage-engine = MyISAMdefault_table_type = MyISAMthread_stack = 192Ktransaction_isolation = READ-COMMITTED

8、  設(shè)置所有連接的默認(rèn)事務(wù)隔離級(jí)tmp_table_size = 256M 臨時(shí)HEAP數(shù)據(jù)表的最大長(zhǎng)度max_heap_table_size = 256M HEAP數(shù)據(jù)表(內(nèi)存表)的最大長(zhǎng)度(默認(rèn)設(shè)置是16M);long_query_time = 1log_long_formatlog-bin = /data0/mysql/3306/binlogbinlog_cache_size = 8M 為binary log指定在查詢(xún)請(qǐng)求處理過(guò)程中SQL 查詢(xún)語(yǔ)句使用的緩存大小binlog_format = MIXEDmax_binlog_cache_size = 8Mmax_binlog_s

9、ize = 512Mexpire_logs_days = 7key_buffer_size = 256M  索引緩沖區(qū)的大小，嚴(yán)格說(shuō)是它決定了數(shù)據(jù)庫(kù)索引處理的速度，尤其 是索引讀的速度read_buffer_size = 1M  讀查詢(xún)操作所能使用的緩沖區(qū)大小，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)享.read_rnd_buffer_size = 16M 針對(duì)按某種特定順序(如ORDER BY子句)輸出的查詢(xún)結(jié)果(默認(rèn)256K) 加速排序操作后的讀數(shù)據(jù)，提高讀分類(lèi)行的速度。bulk_insert_buffer_size = 64M 指定 MyISAM 類(lèi)型數(shù)據(jù)表表使用特殊的樹(shù)形結(jié)

10、構(gòu)的緩存myisam_sort_buffer_size = 128M myisam引擎的sort_buffer_sizemyisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G  這個(gè)參數(shù)已經(jīng)不在MySQLmyisam_repair_threads = 1myisam_recover  自動(dòng)檢查和修復(fù)無(wú)法正確關(guān)閉MyISAM表skip-name-resolvemaster-connect-retry = 10slave-skip-errors = 1032,1062,126,1114,1146

11、,1048,1396server-id = 1 設(shè)定為masterinnodb_additional_mem_pool_size = 16M InnoDB用來(lái)存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。  應(yīng)用程序里的表越多就應(yīng)該分配越多的內(nèi)存，如果innodb用光了這個(gè)內(nèi)存就會(huì)向系統(tǒng)內(nèi)存要。  并且寫(xiě)入警告日志,根據(jù)MySQL手冊(cè)，對(duì)于2G內(nèi)存的機(jī)器，推薦值是20M。  缺省值是1M。通常不用太大，只要夠用就行，與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。innodb_status_fileInnoDB發(fā)送診斷輸出到stderr或文件，而不是到stdout或者固定尺寸內(nèi)存緩沖，以避

12、免底層緩沖溢出。作為一個(gè)副效果，SHOW INNODB STATUS 的輸出每15秒鐘寫(xiě)到一個(gè)狀態(tài)文件。這個(gè)文件的名字是innodb_status.pid，其中pid是服務(wù)器進(jìn)程ID。這個(gè)文件在MySQL數(shù)據(jù)目錄里創(chuàng)建。正常關(guān)機(jī)之時(shí)，InnoDB刪除這個(gè)文件。如果發(fā)生不正常的關(guān)機(jī)，這些狀態(tài)文件的實(shí)例可能被展示，而且必須被手動(dòng)刪除。在移除它們之前，你可能想要檢查它們來(lái)看它們是否包含有關(guān)不正常關(guān)機(jī)的原因的有用信息。僅在配置選項(xiàng)innodb_status_file=1被設(shè)置之時(shí)，innodb_status.pid文件被創(chuàng)建。innodb_buffer_pool_size = 2048M 指定大小的內(nèi)

13、存來(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_data_file_path = ibdata1:1024M:autoextendinnodb_file_io_threads = 4 IO操作(硬盤(pán)寫(xiě)操作)的最大線(xiàn)程個(gè)數(shù)(默認(rèn)設(shè)置是4)。innodb_thread_concurrency = 16 InnoDB驅(qū)動(dòng)程序能夠同時(shí)使用的最大線(xiàn)程個(gè)數(shù)(默認(rèn)設(shè)置是8)。innodb_flush_log_at_trx_commit = 2  Inn

14、oDB記錄日志的方式  如果設(shè)置為1，則每個(gè)事務(wù)提交的時(shí)候，MySQL都會(huì)將事務(wù)日志寫(xiě)入磁盤(pán)。  如果設(shè)置為0或者2，則大概每秒中將日志寫(xiě)入磁盤(pán)一次。  實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大    設(shè)置為2時(shí)插入10000條記錄只需要2秒，設(shè)置為0時(shí)只需要1秒，而設(shè)置為1時(shí)則需要229秒。  建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。  在存在丟失最近部分事務(wù)的危險(xiǎn)的前提下，可以把該值設(shè)為0。innodb_log_buffer_size = 16M 日志緩存的大小  默認(rèn)的設(shè)置在中等強(qiáng)度寫(xiě)入負(fù)

15、載以及較短事務(wù)的情況下，一般可以滿(mǎn)足服務(wù)器的性能要求。  如果更新操作峰值或者負(fù)載較大就應(yīng)該加大這個(gè)值。8-16M即可。innodb_log_file_size = 128M 日志組中每個(gè)日志文件的大小在高寫(xiě)入負(fù)載尤其是大數(shù)據(jù)集的情況下很重要。  這個(gè)值越大性能就越高，但恢復(fù)時(shí)時(shí)間會(huì)加長(zhǎng)。默認(rèn)是5M。Javaeye推薦innodb_log_file_size = 64Minnodb_log_files_in_group = 3 日志組中的日志文件數(shù)目,推薦使用3innodb_max_dirty_pages_pct = 90 最大臟頁(yè)的百分?jǐn)?shù)innodb_lock_wait_

16、timeout = 120 事務(wù)獲得資源超時(shí)設(shè)置,默認(rèn)50sinnodb_file_per_table = 0  為每一個(gè)新數(shù)據(jù)表創(chuàng)建一個(gè)表空間文件而不是把數(shù)據(jù)表都集中保存在中央表空間里quick  不緩沖查詢(xún)，直接導(dǎo)出至stdoutmax_allowed_packet = 32M停用mysql的嚴(yán)格驗(yàn)證STRICT_TRANS_TABLES#把sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 中"STRICT_TRANS_TABLES,"

17、;去掉，重啟MySQL服務(wù)，問(wèn)題解決汗顏經(jīng)過(guò)此役，總結(jié)數(shù)據(jù)庫(kù)設(shè)計(jì)中的一個(gè)注意點(diǎn)，也即避免此現(xiàn)象的兩種方法：1、在表的設(shè)計(jì)過(guò)程中，對(duì)所有不能為空的字段設(shè)置默認(rèn)值2、配置MySQL服務(wù)，如上所述將STRICT_TRANS_TABLES去掉#開(kāi)啟event#event_scheduler = on#myisam自動(dòng)修復(fù)myisam-recover=BACKUP,FORCE# 在 中輸入log-bin=/var/lib/mysql/mysql-bin#expire_logs_days = 10#max_binlog_size = 100Mlog=/var/lib/mysql/mysql.loglog-

18、error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log  未能成功生成long_query_time=1log-slow-querieslog-queries-not-using-indexes是否啟用了日志mysql>show variables like 'log_%'怎樣知道當(dāng)前的日志mysql> show master status;顯示二進(jìn)制日志數(shù)目mysql> show master logs;看二進(jìn)制日志文件用mysqlbinlogshell>mysq

19、lbinlog mail-bin.000001或者shell>mysqlbinlog mail-bin.000001 | tail#詳細(xì)說(shuō)明log-bin=/var/lib/mysql/mysql-bin #二進(jìn)制LOG,mysqlbinlog直接讀取二進(jìn)制日志文件。二進(jìn)制日志包含所有更新數(shù)據(jù)的語(yǔ)句 ，其目的是在恢復(fù)數(shù)據(jù)庫(kù)時(shí)用它來(lái)把數(shù)據(jù)盡可能恢復(fù)到最后的狀態(tài)。另外，如果做同步復(fù)制( Replication )的話(huà)，也需要使用二進(jìn)制日志傳送修改情況。log_bin指定日志文件，如果不提供文件名，MySQL將自己產(chǎn)生缺省文件名。MySQL會(huì)在文件名后面自動(dòng)添加數(shù)字索引，每次啟動(dòng)服務(wù)時(shí)，都會(huì)重

20、新生成一個(gè)新的二進(jìn)制文件。時(shí)間長(zhǎng)了log bin也會(huì)占很大的硬盤(pán)空間，所以在my.ini中加上對(duì)其的限制。#expire_logs_days = 10#max_binlog_size = 100M-# show master status;#mysqlbinlog mysql-bin.000052-格式如下：flush privileges;# at 173#090814 10:09:22 server id 1  end_log_pos 448  Query  thread_id=51    exec_tim   

21、60;                                       e=0    error_code=0use sq_gllutf8;SET TIMESTAMP=1250215762;SET session.character_set_client=33,session.collation_connection=33,sessio     

22、                                       n.collation_server=8;update gll_products set  sn_name='休閑褲'  ,ch_name='H957-3'  ,color='卡其'  ,co       

23、;                                     mposition='60%馬棉44'  ,huoqi=''  ,p_remarks=''  ,utime='2009-08-14 10:09:22'  ,           

24、                                uip='192.168.200.96'  WHERE id = '339'# at 448#090814 10:11:14 server id 1  end_log_pos 556  Query  thread_id=59    exec_tim    

25、0;                                       e=0    error_code=0SET TIMESTAMP=1250215874;delete from gll_member  WHERE id = '26'DELIMITER ;# End of log fileROLLBACK ;-log=/var/lib/mysql/

26、mysql.log  #對(duì)所有執(zhí)行語(yǔ)句進(jìn)行記錄-格式如下：090814 10:11:14      59 Connect    sq_szczkjlocalhost on                    59 Init DB    sq_gllutf8                    59 Query

27、60;     SET NAMES 'UTF8'                    59 Query      delete from gll_member  WHERE id = '26'                    59 Quit090814 10:11:15    &

28、#160; 60 Connect    sq_szczkjlocalhost on                    60 Init DB    sq_gllutf8                    60 Query      SET NAMES 'UTF8'      &

29、#160;             60 Query      select * from gll_member  ORDER BY id DESC                    60 Query      select * from gll_member  ORDER BY id DESC  limit  0,50 

30、                  60 Quit                    61 Connect    sq_szczkjlocalhost on                    61 Init DB    sq_gllutf8  

31、0;                 61 Query      SET NAMES 'UTF8'                    61 Query      select * from gll_zone  ORDER BY id DESC            &#

32、160;       61 Query      select * from gll_zone  ORDER BY id DESC  limit  0,99                    61 Quit-log-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log  未能成功生成long_query_time

33、=1log-slow-queries#-log-slow-queries=file_name 的file_name參數(shù)可選，缺省值是host_name-slow.log ，如果指定了file_name參數(shù)的話(huà)，mysql就會(huì)把慢查詢(xún)的日志記錄到file_name所設(shè)定的文件中，如果file_name提供的是一個(gè)相對(duì)路徑，mysql會(huì)把日志記錄到mysql的data目錄中。log-queries-not-using-indexes #在mysql的啟動(dòng)配置文件或命令行參數(shù)中增加-log-queries-not-using-indexes 參數(shù)就可以啟用未使用索引查詢(xún)語(yǔ)句了，日志記錄的文件就是lo

34、g-slow-queries對(duì)應(yīng)的文件。-Mysql的log_slow_queries日志文件設(shè)置不當(dāng)錯(cuò)誤1，容易發(fā)生錯(cuò)誤的設(shè)置：log_slow_queries = ONlog_slow_queries=/var/log/slow-queries.loglong_query_time=5這種容易導(dǎo)致slow-queries.log無(wú)法讀取的錯(cuò)誤（權(quán)限問(wèn)題）。常見(jiàn)錯(cuò)誤提示：Could not use /var/log/slow-queries.log forlogging (error 13). Turning logging off for the whole duration of the

35、MySQL server process. To turn it on again: fix the cause, shutdown theMySQL server and restart it.2，比較妥當(dāng)?shù)呐渲茫簂og_slow_queries = ONlog_slow_queries=slow-loglong_query_time=5直接在目錄/var/lib/mysql/ 下生成 slow-log 文件-測(cè)試慢日志mysql>select sleep(1);mysql>select sleep(3);mysql>select * from gll_products;打

36、開(kāi)mysql.log和慢日志文件可以看到select sleep(3);select * from gll_products;-3 命令說(shuō)明show variables like 'long%' 查詢(xún) long_query_time 的值mysql>show variables like 'log_%'  是否啟用了日志mysql> show variables like 'log_%'+-+-+| Variable_name              &

37、#160;   | Value                    |+-+-+| log_bin                        | ON                      | log_bin_trust_function_creators | OFF                      | log_error