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

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

2、ash#default-character-set=utf8user=mysqlport=3306socket=/tmp/mysql.sockbasedir=/usr/local/webserver/mysqldatadir=/data0/mysql/3306/dataopen_files_limit=10240back_log=600max_connections=3000實際MySQL服務器允許的最大連接數(shù)16384；max_connect_errors=6000可以允許多少個錯誤連接table_cache=1024高速緩存的大小.table_cache的值在2G內存以下的機器中的值默I時

3、256到512,如果機器有4G內石，則默認這個值是2048,但這決意味著機器內存越大，這個值應該越大，因為table_cache加大后，使得mysql對SQL響應的速度更快了，不可避免的會產生更多的死鎖(deadlock),這樣反而使得數(shù)據庫整個一套操作慢了下來，嚴重影響性能。external-locking=FALSEmax_allowed_packet=32Msort_buffer_size=2M是一個connection級參數(shù)，在每個connection第一次需要使用這個buffer的時候，一次性分配設置的內存.并不是越大越好，由于是connection級的參數(shù)，過大的設置+高并發(fā)可能會

4、耗盡系統(tǒng)內存資源。join_buffer_size=2Mthread_cache_size=64重新利用保存在緩存中線程的數(shù)量，當斷開連接時如果緩存中還看空間，那么客戶端的線程將被放到緩存中，如果線程重新被請求，那么請求將從緩存中讀取，如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建，如果有很多新的線程，增加這個值可以改善系統(tǒng)性能根據物理內存設置規(guī)則如下：1G->82G->163G->32>3G->64thread_concurrency=16對mysql的性能影響很大，在多個cpu（或多核）的情況下,錯誤設置了thread_concurrency的值，會

5、導致mysql不能充分利用多cpu（或多核）,出現(xiàn)同一時刻只能一個cpu（或核）在工作的情況。應設為CPU核數(shù)的2倍.比如有一個雙核的CPU,那么thread_concurrency的應該為4;2個雙核的cpu,thread_concurrency的值應為8.query_cache_size=32M緩存機制簡單的說就是緩存sql文本及查詢結果，如果運行相同的sql,服務器直接從緩存中取到結果，而不需要再去解析和執(zhí)行sql。如果表更改了，那么使用這個表的所有緩沖查詢將不再有效，查詢緩存值的相關條目被清空query_cache_limit=2M單個查詢能夠使用的緩沖區(qū)大小.缺省為1Mquery_c

6、ache_min_res_unit=2k它指定分配緩沖區(qū)空間的最小單位，缺省為4K。檢查狀態(tài)值Qcache_free_blocks,如果該值非常大，則表明緩沖區(qū)中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unitdefault-storage-engine=MyISAMdefault_table_type=MyISAMthread_stack=192Ktransaction_isolation=READCOMMITTED設置所有連接的默認事務隔離級tmp_table_size=256M臨時HEAP數(shù)據表的最大長度max_heap_table_siz

7、e=256MHEA做據表（內存表）的最大長度（默認設置是16M）;long_query_time=1log_long_formatlog-bin=/data0/mysql/3306/binlogbinlog_cache_size=8M為binary10g指定在查詢請求處理過程中SQL查詢語句使用的熱大小一binlog_format=MIXEDmax_binlog_cache_size=8Mmax_binlog_size=512Mexpire_logs_days=7key_buffer_size=256M索引緩沖區(qū)的大小，嚴格說是它決定了數(shù)據庫索引處理的成度，無人是索引讀的速度read_buff

8、er_size=1M讀查詢操作所能使用的緩沖區(qū)大小，該參數(shù)對應的分配內存也是每連套獨享.read_rnd_buffer_size=16M針對按某種特定順序（如ORDERBY子句）輸出的查詢結果（默U256K）加速排序操作后的讀數(shù)據，提高讀分類行的速度。bulk_insert_buffer_size=64M指定MylSAM類型數(shù)據表表使用特殊的樹形結構的以f一一myisam_sort_buffer_size=128Mmyisam弓I擎的sort_buffer_sizemyisam_max_sort_file_size=10G#myisam_max_extra_sort_file_size=10G

9、這個參數(shù)已經不在MySQLmyisam_repair_threads=1myisam_recover自動檢查和修復無法正確關閉MyISAM表skip-name-resolvemaster-connect-retry=10slave-skip-errors=1032,1062,126,1114,1146,1048,1396server-id=1設定為masterinnodb_additional_mem_pool_size=16MInnoDB用來存儲數(shù)據字典和其他內部數(shù)據結構向內存池大小。應用程序里的表越多就應該分配越多的內存，如果innodb用光了這個內存就會向系統(tǒng)內存要。并且寫入警告日志，根

10、據MySQL手冊，對于2G內存的機器，推薦值是20M。缺省值是1M。通常不用太大，只要夠用就行，與表結構的復雜度有關系。innodb_status_fileInnoDB發(fā)送診斷輸出到stderr或文件，而不是到stdout或者固定尺寸內存緩沖，以避免底層緩沖溢出。作為一個副效果，SHOWINNODBSTATUS輸出每15秒鐘寫到一個狀態(tài)文件。這個文件的名字是innodb_status.pid,其中pid是服務器進程ID。這個文件在MySQL數(shù)據目錄里創(chuàng)建。正常關機之時，InnoDB刪除這個文件。如果發(fā)生不正常的關機，這些狀態(tài)文件的實例可能被展示，而且必須被手動刪除。在移除它們之前，你可能想要檢

11、查它們來看它們是否包含有關不正常關機的原因的有用信息。僅在配置選項innodb_status_file=1被設置之時，innodb_status.pid文件被倉建。innodb_buffer_pool_size=2048M指定大小的內存來緩沖數(shù)據和索引。對于亞獨的aySQL數(shù)據庫服務器，最大可以把該值設置成物理內存的80%。根據MySQL手冊，對于2G內存的機器，推薦值是1G（50%）innodb_data_file_path=ibdata1:1024M:autoextendinnodb_file_io_threads=4IO操作（硬盤寫操作）的最大線程個數(shù)（默認設置是4）。innodb_th

12、read_concurrency=16InnoDB驅動程序能夠同時使用的最大線程個數(shù)（默認區(qū)根是8）-oinnodb_flush_log_at_trx_commit=2InnoDB記錄日志的方式如果設置為1,則每個事務提交的時候，MySQL都會將事務日志寫入磁盤。如果設置為0或者2,則大概每秒中將日志寫入磁盤一次。實際測試發(fā)現(xiàn)，該值對插入數(shù)據的速度影響非常大設置為2時插入10000條記錄只需要2秒，設置為0時只需要1秒，而設置為1時則需要229秒。建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。在存在丟失最近部分事務的危險的前提下，可以把該值設為00innodb_log_buffer_

13、size=16M日志緩存的大小默認而設A在中樂強度寫入負載以及較短事務的情況下，一般可以滿足服務器的性能要求。如果更新操作峰值或者負載較大就應該加大這個值。8-16M即可。innodb_log_file_size=128M日志組中每個日志文件的大小在高寫入負載尤其是大數(shù)據集的補況下很重要。這個值越大性能就越高，但恢復時時間會加長。默認是5M。Javaeye推薦innodb_log_file_size=64Minnodb_log_files_in_group=3日志組中的日志文件數(shù)目，推薦使用3innodb_max_dirty_pages_pct=90最大臟頁的百分數(shù)innodb_lock_wa

14、it_timeout=120事務獲得資源超時設置，默認50sinnodb_file_per_table=0為每一個新數(shù)據表創(chuàng)建一個表空間文件而不是把數(shù)據表都集孑然力在中央表空間里quick不緩沖查詢，直接導出至stdoutmax_allowed_packet=32M停用mysqlM格驗證STRICT_TRANS_TABLES#把sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"中"STRICT_TRANS_TABLES",而啟MySQL服務，問題解決汗顏經過此

15、役，總結數(shù)京庫設計下的一個注意點，也即避免此現(xiàn)象的兩種方法：1、在表的設計過程中，對所有不能為空的字段設置默認值2、配置MySQL服務，如上所述將STRICT_TRANS_TABLES#開啟event#event_scheduler=on#myisam自動修復myisam-recover=BACKUJFORCE#在中巾俞入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#lo

16、g-update=/var/lib/mysql/update.log未能成功生成long_query_time=1log-slow-querieslog-queries-not-using-indexes是否啟用了日志mysql>showvariableslike'log_%'怎樣知道當前的日志一mysql>showmasterstatus;K示二迤制日志數(shù)目mysql>showmasterlogs;看二進制日志文件用mysqlbinlogshell>mysqlbinlogmai-bin.000001或者shell>mysqlbinlogmai-b

17、in.000001|tail#詳細說明log-bin=/var/lib/mysql/mysql-bin#二進制LOG,mysqlbinlog直接讀取二進制日志文件。二進制日志包含所有更新數(shù)據的語句，其目的是在恢復數(shù)據庫時用它來把數(shù)據盡可能恢復到最后的狀態(tài)。另外，如果做同步復制(Replication)的話，也需要使用二進制日志傳送修改情況。log_bin指定日志文件，如果不提供文件名，MySQL將自己產生缺省文件名。MySQL會在文件名后面自動添加數(shù)字索引,每次啟動服務時，都會重新生成一個新的二進制文件。時間長了logbin也會占很大的硬盤空間，所以在my.ini中加上對其的限制#expire

18、_logs_days=10#max_binlog_size=100M# showmasterstatus;#mysqlbinlogmysq-bin.000052咯式如下：flushprivileges;# at173end_log_pose=0# 09081410:09:22serverid1448Querythread_id=51exec_timerror_code=0usesq_gllutf8;SETTIMESTAMP=1250215762;SETsession.character_set_client=33,session.collation_connection=33,session.

19、collation_server=8;updategll_productssetsn_name='休閑褲',ch_name='H957-3',color='卡其',composition='60%馬棉44',huoqi='',p_remarks='',utime='2009-08-1410:09:22',uip='192.168.200.96'WHEREid='339'Jid1end_log_pose=0# at448#09081410:11:14se

20、rver556Querythread_id=59exec_timerrorcode=0SETTIMESTAMP=1250215874;deletefromgll_memberWHEREid='26JDELIMITER;# EndoflogfileROLLBACK;log=/var/lib/mysql/mysql.log#M所有執(zhí)行語句進行記錄哈式如下：09081410:11:1459Connectsq_szczkjlocalhoston59InitDBsq_gllutf859QuerySETNAMES'UTF8'59Querydeletefromgll_memberWH

21、EREid='26'59Quit09081410:11:1560Connectsq_szczkjlocalhoston60InitDBsq_gllutf860QuerySETNAMES'UTF8'60Queryselect*fromgll_memberORDERBYidDESC60Queryselect*fromgll_memberORDERBYidDESClimit0,5060Quit61Connectsq_szczkjlocalhoston61InitDBsq_gllutf861QuerySETNAMES'UTF8'61Queryselect

22、*fromgll_zoneORDERBYidDESC61Queryselect*fromgll_zoneORDERBYidDESClimit0,9961Quitlog-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log未能成功生成long_query_time=1log-slow-queries#-log-slow-queries=file_name的巾le_name參數(shù)可選，缺省值是host_name-slow.log,如果指定了file_name參數(shù)的話，mysql就會把慢查詢的日志記錄到file_name

23、所設定的文彳中，如果file_name提供的是一個相對路徑，mysql會把日志記錄到mysql的data目錄中。log-queries-not-using-indexes#在mysql的啟動配置文件或命令行參數(shù)中增加-log-queries-not-using-indexes參數(shù)就可以啟用未使用索引查詢語句了，日志記錄的文件就是log-slow-queries對應的文件。Mysql的log_slow_queries日志文件設置不當錯誤1,容易發(fā)生錯誤的設置:一一log_slow_queries=ONlog_slow_queries=/var/log/slow-queries.loglong_q

24、uery_time=5這種容易導致slow-queries.log無法讀取的錯誤(權限問題)。常見錯誤提示：Couldnotuse/var/log/slow-queries.logforlogging(error13).TurningloggingoffforthewholedurationoftheMySQLserverprocess.Toturnitonagain:fixthecause,shutdowntheMySQLserverandrestartit.2,比較妥當?shù)呐渲茫簂og_slow_queries=ONlog_slow_queries=slow-loglong_query_time=5直接在目錄/var/lib/mysql/下生成slow-log文件-測試慢日志mysql>selectsleep(1);mysql>selectsleep(3);mysql>select*fromgll_products;打開mysql.log和慢日志文件可以看到selectsleep(3);select*fromgll_products;3命令說明showvariableslike'long%'查詢long_query_time的值mysql>showvariablesli