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

1、mysql有以下幾種日志： 錯誤日志： log-err 查詢?nèi)罩荆?log 慢查詢?nèi)罩? log-slow-queries 更新日志: log-update 二進(jìn)制日志： log-bin要把日志生成在 /var/log 目錄下(是系統(tǒng)日志存放的地方，只有 root 賬號有寫權(quán)限)，需要 MySQL 進(jìn)程對這個目錄有讀寫權(quán)限，一般是不這么做的，也考慮到安全問題，包括 MySQL 本身的數(shù)據(jù)安全，因?yàn)閷?MySQL 的所有操作，都會記錄到常規(guī)查詢?nèi)罩?。MySQL 的日志就不要用 /var/log/ 目錄下。-2 fdefault-character-set = utf8port = 3306soc

2、ket = /tmp/mysql.sockno-auto-rehash#default-character-set = utf8user = mysqlport = 3306socket = /tmp/mysql.sockbasedir = /usr/local/webserver/mysqldatadir = /data0/mysql/3306/dataopen_files_limit = 10240back_log = 600max_connections = 3000 實(shí)際MySQL服務(wù)器允許的最大連接數(shù)16384；max_connect_errors = 6000 可以允許多少個錯誤連

3、接table_cache = 1024 高速緩存的大小.table_cache的值在2G內(nèi)存以下的機(jī)器中的值默認(rèn)時256到 512，如果機(jī)器有4G內(nèi)存,則默認(rèn)這個值是2048，但這決意味著機(jī)器內(nèi)存越大，這個值應(yīng)該越大，因?yàn)閠able_cache加大后，使得mysql對 SQL響應(yīng)的速度更快了，不可避免的會產(chǎn)生更多的死鎖（dead lock），這樣反而使得數(shù)據(jù)庫整個一套操作慢了下來，嚴(yán)重影響性能。external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2M 是一個connection級參數(shù)，在每個connection第

4、一次需要使用這個buffer的時候，一次性分配設(shè)置的內(nèi)存.并不是越大越好，由于是connection級的參數(shù)，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。join_buffer_size = 2Mthread_cache_size = 64 重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程，增加這個值可以改善系統(tǒng)性能 根據(jù)物理內(nèi)存設(shè)置規(guī)則如下： 1G - 8 2G - 16 3G - 32 3G - 64thread_concur

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

6、么使用這個表的所有緩沖查詢將不再有效，查詢緩存值的相關(guān)條目被清空query_cache_limit = 2M 單個查詢能夠使用的緩沖區(qū)大小.缺省為1Mquery_cache_min_res_unit = 2k 它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks，如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結(jié)果都比較小，此時需要減小query_cache_min_res_unitdefault-storage-engine = MyISAMdefault_table_type = MyISAMthread_stack = 192Ktransacti

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

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

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

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

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

12、QL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%。 根據(jù)MySQL手冊，對于2G內(nèi)存的機(jī)器，推薦值是1G（50%）innodb_data_file_path = ibdata1:1024M:autoextendinnodb_file_io_threads = 4 IO操作(硬盤寫操作)的最大線程個數(shù)(默認(rèn)設(shè)置是4)。innodb_thread_concurrency = 16 InnoDB驅(qū)動程序能夠同時使用的最大線程個數(shù)(默認(rèn)設(shè)置是8)。innodb_flush_log_at_trx_commit = 2 InnoDB記錄日志的方式 如果設(shè)置為1，則每個事務(wù)提交的時候，MySQL都會將

13、事務(wù)日志寫入磁盤。 如果設(shè)置為0或者2，則大概每秒中將日志寫入磁盤一次。 實(shí)際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大 設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒。 建議盡量將插入操作合并成一個事務(wù)，這樣可以大幅提高速度。 在存在丟失最近部分事務(wù)的危險的前提下，可以把該值設(shè)為0。innodb_log_buffer_size = 16M 日志緩存的大小 默認(rèn)的設(shè)置在中等強(qiáng)度寫入負(fù)載以及較短事務(wù)的情況下，一般可以滿足服務(wù)器的性能要求。 如果更新操作峰值或者負(fù)載較大就應(yīng)該加大這個值。8-16M即可。innodb_log_file_size = 128M

14、 日志組中每個日志文件的大小在高寫入負(fù)載尤其是大數(shù)據(jù)集的情況下很重要。 這個值越大性能就越高，但恢復(fù)時時間會加長。默認(rèn)是5M。Javaeye推薦innodb_log_file_size = 64Minnodb_log_files_in_group = 3 日志組中的日志文件數(shù)目,推薦使用3innodb_max_dirty_pages_pct = 90 最大臟頁的百分?jǐn)?shù)innodb_lock_wait_timeout = 120 事務(wù)獲得資源超時設(shè)置,默認(rèn)50sinnodb_file_per_table = 0 為每一個新數(shù)據(jù)表創(chuàng)建一個表空間文件而不是把數(shù)據(jù)表都集中保存在中央表空間里quick

15、不緩沖查詢，直接導(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,去掉，重啟MySQL服務(wù)，問題解決汗顏經(jīng)過此役，總結(jié)數(shù)據(jù)庫設(shè)計中的一個注意點(diǎn)，也即避免此現(xiàn)象的兩種方法：1、在表的設(shè)計過程中，對所有不能為空的字段設(shè)置默認(rèn)值2、配置MySQL服務(wù)，如上所述將STRICT_TRANS_TABLES去掉#開啟event#event_sc

16、heduler = on#myisam自動修復(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-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log 未能成功生成long_query_time=1log-slow-querieslog-queries-not-using-index

17、es是否啟用了日志mysqlshow variables like log_%;怎樣知道當(dāng)前的日志mysql show master status;顯示二進(jìn)制日志數(shù)目mysql show master logs;看二進(jìn)制日志文件用mysqlbinlogshellmysqlbinlog mail-bin.000001或者shellmysqlbinlog mail-bin.000001 | tail#詳細(xì)說明log-bin=/var/lib/mysql/mysql-bin #二進(jìn)制LOG,mysqlbinlog直接讀取二進(jìn)制日志文件。二進(jìn)制日志包含所有更新數(shù)據(jù)的語句 ，其目的是在恢復(fù)數(shù)據(jù)庫時用它來

18、把數(shù)據(jù)盡可能恢復(fù)到最后的狀態(tài)。另外，如果做同步復(fù)制( Replication )的話，也需要使用二進(jìn)制日志傳送修改情況。log_bin指定日志文件，如果不提供文件名，MySQL將自己產(chǎn)生缺省文件名。MySQL會在文件名后面自動添加數(shù)字索引，每次啟動服務(wù)時，都會重新生成一個新的二進(jìn)制文件。時間長了log bin也會占很大的硬盤空間，所以在my.ini中加上對其的限制。#expire_logs_days = 10#max_binlog_size = 100M-# show master status;#mysqlbinlog mysql-bin.000052-格式如下：flush privileg

19、es;# at 173#090814 10:09:22 server id 1 end_log_pos 448 Query thread_id=51 exec_tim e=0 error_code=0use sq_gllutf8;SET TIMESTAMP=1250215762;SET session.character_set_client=33,session.collation_connection=33,sessio n.collation_server=8;update gll_products set sn_name=休閑褲 ,ch_name=H957-3 ,color=卡其 ,c

20、o mposition=60%馬棉44 ,huoqi= ,p_remarks= ,utime=2009-08-14 10:09:22 , 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 e=0 error_code=0SET TIMESTAMP=1250215874;delete from gll_member WHERE id = 26;DELIMITER ;# End of log fileROLLBACK ;

21、-log=/var/lib/mysql/mysql.log #對所有執(zhí)行語句進(jìn)行記錄-格式如下：090814 10:11:14 59 Connect sq_szczkjlocalhost on 59 Init DB sq_gllutf8 59 Query SET NAMES UTF8 59 Query delete from gll_member WHERE id = 26 59 Quit090814 10:11:15 60 Connect sq_szczkjlocalhost on 60 Init DB sq_gllutf8 60 Query SET NAMES UTF8 60 Query

22、select * from gll_member ORDER BY id DESC 60 Query select * from gll_member ORDER BY id DESC limit 0,50 60 Quit 61 Connect sq_szczkjlocalhost on 61 Init DB sq_gllutf8 61 Query SET NAMES UTF8 61 Query select * from gll_zone ORDER BY id DESC 61 Query select * from gll_zone ORDER BY id DESC limit 0,99

23、61 Quit-log-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log 未能成功生成long_query_time=1log-slow-queries#-log-slow-queries=file_name 的file_name參數(shù)可選，缺省值是host_name-slow.log ，如果指定了file_name參數(shù)的話，mysql就會把慢查詢的日志記錄到file_name所設(shè)定的文件中，如果file_name提供的是一個相對路徑，mysql會把日志記錄到mysql的data目錄中。log-queries-

24、not-using-indexes #在mysql的啟動配置文件或命令行參數(shù)中增加-log-queries-not-using-indexes 參數(shù)就可以啟用未使用索引查詢語句了，日志記錄的文件就是log-slow-queries對應(yīng)的文件。-Mysql的log_slow_queries日志文件設(shè)置不當(dāng)錯誤1，容易發(fā)生錯誤的設(shè)置：log_slow_queries = ONlog_slow_queries=/var/log/slow-queries.loglong_query_time=5這種容易導(dǎo)致slow-queries.log無法讀取的錯誤（權(quán)限問題）。常見錯誤提示：Could not u

25、se /var/log/slow-queries.log forlogging (error 13). Turning logging off for the whole duration of theMySQL 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 文件-測試慢日志mysqlselect sleep(1);mysqlselect sleep(3);mysqlselect * from gll_products;打開mysql.log和慢日志文件可以看到select sleep(3);select * from gll_products;-3 命令說明show variables like long