[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示æ¯æ¬æºçåºå·ä¸º1,ä¸è¬æ¥è®²å°±æ¯masterçææ
skip-name-resolve
# ç¦æ¢MySQL对å¤é¨è¿æ¥è¿è¡DNS解æï¼ä½¿ç¨è¿ä¸é项å¯ä»¥æ¶é¤MySQLè¿è¡DNS解æçæ¶é´ãä½éè¦æ³¨æï¼å¦æå¼å¯è¯¥é项ï¼
# åææè¿ç¨ä¸»æºè¿æ¥ææé½è¦ä½¿ç¨IPå°åæ¹å¼ï¼å¦åMySQLå°æ æ³æ£å¸¸å¤çè¿æ¥è¯·æ±
#skip-networking
back_log = 600
# MySQLè½æçè¿æ¥æ°éãå½ä¸»è¦MySQL线ç¨å¨ä¸ä¸ªå¾çæ¶é´å
å¾å°é常å¤çè¿æ¥è¯·æ±ï¼è¿å°±èµ·ä½ç¨ï¼
# ç¶å主线ç¨è±äºæ¶é´(尽管å¾ç)æ£æ¥è¿æ¥å¹¶ä¸å¯å¨ä¸ä¸ªæ°çº¿ç¨ãback_logå¼æåºå¨MySQLææ¶åæ¢åçæ°è¯·æ±ä¹åççæ¶é´å
å¤å°ä¸ªè¯·æ±å¯ä»¥è¢«åå¨å æ ä¸ã
# å¦æææå¨ä¸ä¸ªçæ¶é´å
æå¾å¤è¿æ¥ï¼ä½ éè¦å¢å å®ãä¹å°±æ¯è¯´ï¼å¦æMySQLçè¿æ¥æ°æ®è¾¾å°max_connectionsæ¶ï¼æ°æ¥ç请æ±å°ä¼è¢«åå¨å æ ä¸ï¼
# 以çå¾
æä¸è¿æ¥éæ¾èµæºï¼è¯¥å æ çæ°éå³back_logï¼å¦æçå¾
è¿æ¥çæ°éè¶
è¿back_logï¼å°ä¸è¢«æäºè¿æ¥èµæºã
# å¦å¤ï¼è¿å¼ï¼back_logï¼éäºæ¨çæä½ç³»ç»å¯¹å°æ¥çTCP/IPè¿æ¥ç侦å¬éåç大å°ã
# ä½ çæä½ç³»ç»å¨è¿ä¸ªéå大å°ä¸æå®èªå·±çéå¶ï¼å¯ä»¥æ£æ¥ä½ çOSææ¡£æ¾åºè¿ä¸ªåéçæ大å¼ï¼ï¼è¯å¾è®¾å®back_logé«äºä½ çæä½ç³»ç»çéå¶å°æ¯æ æçã
max_connections = 1000
#
MySQLçæ大è¿æ¥æ°ï¼å¦ææå¡å¨ç并åè¿æ¥è¯·æ±éæ¯è¾å¤§ï¼å»ºè®®è°é«æ¤å¼ï¼ä»¥å¢å 并è¡è¿æ¥æ°éï¼å½ç¶è¿å»ºç«å¨æºå¨è½æ¯æçæ
åµä¸ï¼å 为å¦æè¿æ¥æ°è¶å¤ï¼
ä»äºMySQLä¼ä¸ºæ¯ä¸ªè¿æ¥æä¾è¿æ¥ç¼å²åºï¼å°±ä¼å¼éè¶å¤çå
åï¼æ以è¦éå½è°æ´è¯¥å¼ï¼ä¸è½ç²ç®æé«è®¾å¼ãå¯ä»¥è¿'conn%'éé
符æ¥çå½åç¶æçè¿æ¥
æ°éï¼ä»¥å®å¤ºè¯¥å¼ç大å°ã
max_connect_errors = 6000
# 对äºåä¸ä¸»æºï¼å¦ææè¶
åºè¯¥åæ°å¼ä¸ªæ°çä¸æé误è¿æ¥ï¼å该主æºå°è¢«ç¦æ¢è¿æ¥ãå¦é对该主æºè¿è¡è§£ç¦ï¼æ§è¡ï¼FLUSH HOSTã
open_files_limit = 65535
# MySQLæå¼çæ件æ述符éå¶ï¼é»è®¤æå°1024;å½open_files_limit没æ被é
ç½®çæ¶åï¼æ¯è¾max_connections*5åulimit -nçå¼ï¼åªä¸ªå¤§ç¨åªä¸ªï¼
# å½open_file_limit被é
ç½®çæ¶åï¼æ¯è¾open_files_limitåmax_connections*5çå¼ï¼åªä¸ªå¤§ç¨åªä¸ªã
table_open_cache = 128
# MySQLæ¯æå¼ä¸ä¸ªè¡¨ï¼é½ä¼è¯»å
¥ä¸äºæ°æ®å°table_open_cacheç¼åä¸ï¼å½MySQLå¨è¿ä¸ªç¼åä¸æ¾ä¸å°ç¸åºä¿¡æ¯æ¶ï¼æä¼å»ç£çä¸è¯»åãé»è®¤å¼64
# åå®ç³»ç»æ200个并åè¿æ¥ï¼åéå°æ¤åæ°è®¾ç½®ä¸º200*N(N为æ¯ä¸ªè¿æ¥æéçæ件æ述符æ°ç®)ï¼
# å½ætable_open_cache设置为å¾å¤§æ¶ï¼å¦æç³»ç»å¤çä¸äºé£ä¹å¤æ件æ述符ï¼é£ä¹å°±ä¼åºç°å®¢æ·ç«¯å¤±æï¼è¿æ¥ä¸ä¸
max_allowed_packet = 4M
# æ¥åçæ°æ®å
大å°ï¼å¢å 该åéçå¼ååå®å
¨ï¼è¿æ¯å 为ä»
å½éè¦æ¶æä¼åé
é¢å¤å
åãä¾å¦ï¼ä»
å½ä½ ååºé¿æ¥è¯¢æMySQLdå¿
é¡»è¿å大çç»æè¡æ¶MySQLdæä¼åé
æ´å¤å
åã
# 该åéä¹æ以åè¾å°é»è®¤å¼æ¯ä¸ç§é¢é²æªæ½ï¼ä»¥æè·å®¢æ·ç«¯åæå¡å¨ä¹é´çé误信æ¯å
ï¼å¹¶ç¡®ä¿ä¸ä¼å å¶ç¶ä½¿ç¨å¤§çä¿¡æ¯å
è导è´å
å溢åºã
binlog_cache_size = 1M
# ä¸ä¸ªäºå¡ï¼å¨æ²¡ææ交çæ¶åï¼äº§ççæ¥å¿ï¼è®°å½å°Cacheä¸ï¼çå°äºå¡æ交éè¦æ交çæ¶åï¼åææ¥å¿æä¹
åå°ç£çãé»è®¤binlog_cache_size大å°32K
max_heap_table_size = 8M
# å®ä¹äºç¨æ·å¯ä»¥å建çå
å表(memory table)ç大å°ãè¿ä¸ªå¼ç¨æ¥è®¡ç®å
å表çæ大è¡æ°å¼ãè¿ä¸ªåéæ¯æå¨ææ¹å
tmp_table_size = 16M
# MySQLçheapï¼å 积ï¼è¡¨ç¼å²å¤§å°ãææèåå¨ä¸ä¸ªDMLæ令å
å®æï¼å¹¶ä¸å¤§å¤æ°èåçè³å¯ä»¥ä¸ç¨ä¸´æ¶è¡¨å³å¯ä»¥å®æã
# 大å¤æ°ä¸´æ¶è¡¨æ¯åºäºå
åç(HEAP)表ãå
·æ大çè®°å½é¿åº¦ç临æ¶è¡¨ (ææåçé¿åº¦çå)æå
å«BLOBåç表åå¨å¨ç¡¬çä¸ã
#
å¦ææ个å
é¨heapï¼å 积ï¼è¡¨å¤§å°è¶
è¿tmp_table_sizeï¼MySQLå¯ä»¥æ ¹æ®éè¦èªå¨å°å
åä¸çheap表æ¹ä¸ºåºäºç¡¬ççMyISAM表ã
è¿å¯ä»¥éè¿è®¾ç½®tmp_table_sizeé项æ¥å¢å 临æ¶è¡¨ç大å°ãä¹å°±æ¯è¯´ï¼å¦æè°é«è¯¥å¼ï¼MySQLåæ¶å°å¢å heap表ç大å°ï¼å¯è¾¾å°æé«èæ¥æ¥
询é度çææ
read_buffer_size = 2M
# MySQL读å
¥ç¼å²åºå¤§å°ã对表è¿è¡é¡ºåºæ«æç请æ±å°åé
ä¸ä¸ªè¯»å
¥ç¼å²åºï¼MySQLä¼ä¸ºå®åé
ä¸æ®µå
åç¼å²åºãread_buffer_sizeåéæ§å¶è¿ä¸ç¼å²åºç大å°ã
# å¦æ对表ç顺åºæ«æ请æ±é常é¢ç¹ï¼å¹¶ä¸ä½ 认为é¢ç¹æ«æè¿è¡å¾å¤ªæ
¢ï¼å¯ä»¥éè¿å¢å 该åéå¼ä»¥åå
åç¼å²åºå¤§å°æé«å
¶æ§è½
read_rnd_buffer_size = 8M
# MySQLçéæºè¯»ç¼å²åºå¤§å°ãå½æä»»æ顺åºè¯»åè¡æ¶(ä¾å¦ï¼æç
§æåºé¡ºåº)ï¼å°åé
ä¸ä¸ªéæºè¯»ç¼ååºãè¿è¡æåºæ¥è¯¢æ¶ï¼
# MySQLä¼é¦å
æ«æä¸é该ç¼å²ï¼ä»¥é¿å
ç£çæç´¢ï¼æé«æ¥è¯¢é度ï¼å¦æéè¦æåºå¤§éæ°æ®ï¼å¯éå½è°é«è¯¥å¼ãä½MySQLä¼ä¸ºæ¯ä¸ªå®¢æ·è¿æ¥åæ¾è¯¥ç¼å²ç©ºé´ï¼æ以åºå°½ééå½è®¾ç½®è¯¥å¼ï¼ä»¥é¿å
å
åå¼éè¿å¤§
sort_buffer_size = 8M
# MySQLæ§è¡æåºä½¿ç¨çç¼å²å¤§å°ãå¦ææ³è¦å¢å ORDER BYçé度ï¼é¦å
çæ¯å¦å¯ä»¥è®©MySQL使ç¨ç´¢å¼èä¸æ¯é¢å¤çæåºé¶æ®µã
# å¦æä¸è½ï¼å¯ä»¥å°è¯å¢å sort_buffer_sizeåéç大å°
join_buffer_size = 8M
# èåæ¥è¯¢æä½æè½ä½¿ç¨çç¼å²åºå¤§å°ï¼åsort_buffer_sizeä¸æ ·ï¼è¯¥åæ°å¯¹åºçåé
å
åä¹æ¯æ¯è¿æ¥ç¬äº«
thread_cache_size = 8
# è¿ä¸ªå¼ï¼é»è®¤8ï¼è¡¨ç¤ºå¯ä»¥éæ°å©ç¨ä¿åå¨ç¼åä¸çº¿ç¨çæ°éï¼å½æå¼è¿æ¥æ¶å¦æç¼åä¸è¿æ空é´ï¼é£ä¹å®¢æ·ç«¯ç线ç¨å°è¢«æ¾å°ç¼åä¸ï¼
# å¦æ线ç¨éæ°è¢«è¯·æ±ï¼é£ä¹è¯·æ±å°ä»ç¼åä¸è¯»å,å¦æç¼åä¸æ¯ç©ºçæè
æ¯æ°ç请æ±ï¼é£ä¹è¿ä¸ªçº¿ç¨å°è¢«éæ°å建,å¦ææå¾å¤æ°ç线ç¨ï¼
# å¢å è¿ä¸ªå¼å¯ä»¥æ¹åç³»ç»æ§è½.éè¿æ¯è¾ConnectionsåThreads_createdç¶æçåéï¼å¯ä»¥çå°è¿ä¸ªåéçä½ç¨ã(â>表示è¦è°æ´çå¼)
# æ ¹æ®ç©çå
å设置è§åå¦ä¸ï¼
# 1G â> 8
# 2G â> 16
# 3G â> 32
# 大äº3G â> 64
query_cache_size = 8M
#MySQLçæ¥è¯¢ç¼å²å¤§å°ï¼ä»4.0.1å¼å§ï¼MySQLæä¾äºæ¥è¯¢ç¼å²æºå¶ï¼ä½¿ç¨æ¥è¯¢ç¼å²ï¼MySQLå°SELECTè¯å¥åæ¥è¯¢ç»æåæ¾å¨ç¼å²åºä¸ï¼
# ä»å对äºåæ ·çSELECTè¯å¥ï¼åºå大å°åï¼ï¼å°ç´æ¥ä»ç¼å²åºä¸è¯»åç»æãæ ¹æ®MySQLç¨æ·æåï¼ä½¿ç¨æ¥è¯¢ç¼å²æå¤å¯ä»¥è¾¾å°238%çæçã
# éè¿æ£æ¥ç¶æå¼'Qcache_%'ï¼å¯ä»¥ç¥équery_cache_size设置æ¯å¦åçï¼å¦æQcache_lowmem_prunesçå¼é常大ï¼å表æç»å¸¸åºç°ç¼å²ä¸å¤çæ
åµï¼
# å¦æQcache_hitsçå¼ä¹é常大ï¼å表ææ¥è¯¢ç¼å²ä½¿ç¨é常é¢ç¹ï¼æ¤æ¶éè¦å¢å ç¼å²å¤§å°ï¼å¦æQcache_hitsçå¼ä¸å¤§ï¼å表æä½ çæ¥è¯¢éå¤çå¾ä½ï¼
# è¿ç§æ
åµä¸ä½¿ç¨æ¥è¯¢ç¼å²åèä¼å½±åæçï¼é£ä¹å¯ä»¥èèä¸ç¨æ¥è¯¢ç¼å²ãæ¤å¤ï¼å¨SELECTè¯å¥ä¸å å
¥SQL_NO_CACHEå¯ä»¥æ确表示ä¸ä½¿ç¨æ¥è¯¢ç¼å²
query_cache_limit = 2M
#æå®å个æ¥è¯¢è½å¤ä½¿ç¨çç¼å²åºå¤§å°ï¼é»è®¤1M
key_buffer_size = 4M
#æå®ç¨äºç´¢å¼çç¼å²åºå¤§å°ï¼å¢å å®å¯å¾å°æ´å¥½å¤ççç´¢å¼(对ææ读åå¤éå)ï¼å°ä½ è½è´æ
å¾èµ·é£æ ·å¤ãå¦æä½ ä½¿å®å¤ªå¤§ï¼
# ç³»ç»å°å¼å§æ¢é¡µå¹¶ä¸ççåæ
¢äºã对äºå
åå¨4GBå·¦å³çæå¡å¨è¯¥åæ°å¯è®¾ç½®ä¸º384Mæ512Mãéè¿æ£æ¥ç¶æå¼Key_read_requestsåKey_readsï¼
# å¯ä»¥ç¥ékey_buffer_size设置æ¯å¦åçãæ¯ä¾key_reads/key_read_requestsåºè¯¥å°½å¯è½çä½ï¼
# è³å°æ¯1:100ï¼1:1000æ´å¥½(ä¸è¿°ç¶æå¼å¯ä»¥ä½¿ç¨SHOW STATUS LIKE 'key_read%'è·å¾)ã注æï¼è¯¥åæ°å¼è®¾ç½®çè¿å¤§åèä¼æ¯æå¡å¨æ´ä½æçéä½
ft_min_word_len = 4
# åè¯è¯æ±æå°é¿åº¦ï¼é»è®¤4
transaction_isolation = REPEATABLE-READ
# MySQLæ¯æ4ç§äºå¡é离级å«ï¼ä»ä»¬åå«æ¯ï¼
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# å¦æ²¡ææå®ï¼MySQLé»è®¤éç¨çæ¯REPEATABLE-READï¼ORACLEé»è®¤çæ¯READ-COMMITTED
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #è¶
è¿30天çbinlogå é¤
log_error = /data/mysql/mysql-error.log #é误æ¥å¿è·¯å¾
slow_query_log = 1
long_query_time = 1 #æ
¢æ¥è¯¢æ¶é´ è¶
è¿1ç§å为æ
¢æ¥è¯¢
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 #ä¸åºå大å°å
skip-external-locking #MySQLé项以é¿å
å¤é¨éå®ã该é项é»è®¤å¼å¯
default-storage-engine = InnoDB #é»è®¤åå¨å¼æ
innodb_file_per_table = 1
# InnoDB为ç¬ç«è¡¨ç©ºé´æ¨¡å¼ï¼æ¯ä¸ªæ°æ®åºçæ¯ä¸ªè¡¨é½ä¼çæä¸ä¸ªæ°æ®ç©ºé´
# ç¬ç«è¡¨ç©ºé´ä¼ç¹ï¼
# 1ï¼æ¯ä¸ªè¡¨é½æèªå·²ç¬ç«ç表空é´ã
# 2ï¼æ¯ä¸ªè¡¨çæ°æ®åç´¢å¼é½ä¼åå¨èªå·²ç表空é´ä¸ã
# 3ï¼å¯ä»¥å®ç°å表å¨ä¸åçæ°æ®åºä¸ç§»å¨ã
# 4ï¼ç©ºé´å¯ä»¥åæ¶ï¼é¤drop tableæä½å¤ï¼è¡¨ç©ºä¸è½èªå·²åæ¶ï¼
# 缺ç¹ï¼
# å表å¢å è¿å¤§ï¼å¦è¶
è¿100G
# ç»è®ºï¼
# å
±äº«è¡¨ç©ºé´å¨Insertæä½ä¸å°æä¼å¿ãå
¶å®é½æ²¡ç¬ç«è¡¨ç©ºé´è¡¨ç°å¥½ãå½å¯ç¨ç¬ç«è¡¨ç©ºé´æ¶ï¼è¯·åçè°æ´ï¼innodb_open_files
innodb_open_files = 500
# éå¶Innodbè½æå¼ç表çæ°æ®ï¼å¦æåºéç表ç¹å«å¤çæ
åµï¼è¯·å¢å è¿ä¸ªãè¿ä¸ªå¼é»è®¤æ¯300
innodb_buffer_pool_size = 64M
# InnoDB使ç¨ä¸ä¸ªç¼å²æ± æ¥ä¿åç´¢å¼ååå§æ°æ®, ä¸åMyISAM.
# è¿éä½ è®¾ç½®è¶å¤§,ä½ å¨åå表éé¢æ°æ®æ¶æéè¦çç£çI/Oè¶å°.
# å¨ä¸ä¸ªç¬ç«ä½¿ç¨çæ°æ®åºæå¡å¨ä¸,ä½ å¯ä»¥è®¾ç½®è¿ä¸ªåéå°æå¡å¨ç©çå
å大å°ç80%
# ä¸è¦è®¾ç½®è¿å¤§,å¦å,ç±äºç©çå
åçç«äºå¯è½å¯¼è´æä½ç³»ç»çæ¢é¡µé¢ ç°¸.
# 注æå¨32ä½ç³»ç»ä¸ä½ æ¯ä¸ªè¿ç¨å¯è½è¢«éå¶å¨ 2-3.5G ç¨æ·å±é¢å
åéå¶,
# æ以ä¸è¦è®¾ç½®ç太é«.
innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使ç¨åå°çº¿ç¨å¤çæ°æ®é¡µä¸ç读å I/O(è¾å
¥è¾åº)请æ±,æ ¹æ®ä½ ç CPU æ ¸æ°æ¥æ´æ¹,é»è®¤æ¯4
# 注:è¿ä¸¤ä¸ªåæ°ä¸æ¯æå¨ææ¹å,éè¦æ该åæ°å å
¥å°my.cnféï¼ä¿®æ¹å®åéå¯MySQLæå¡,å
许å¼çèå´ä» 1-64
innodb_thread_concurrency = 0
# é»è®¤è®¾ç½®ä¸º 0,表示ä¸éå¶å¹¶åæ°ï¼è¿éæ¨è设置为0ï¼æ´å¥½å»åæ¥CPUå¤æ ¸å¤çè½åï¼æé«å¹¶åé
innodb_purge_threads = 1
# InnoDBä¸çæ¸
é¤æä½æ¯ä¸ç±»å®æåæ¶æ ç¨æ°æ®çæä½ãå¨ä¹åçå 个çæ¬ä¸ï¼æ¸
é¤æä½æ¯ä¸»çº¿ç¨çä¸é¨åï¼è¿æå³çè¿è¡æ¶å®å¯è½ä¼å µå¡å
¶å®çæ°æ®åºæä½ã
# ä»MySQL5.5.Xçæ¬å¼å§ï¼è¯¥æä½è¿è¡äºç¬ç«ç线ç¨ä¸,并æ¯ææ´å¤ç并åæ°ãç¨æ·å¯éè¿è®¾ç½®innodb_purge_threadsé
ç½®åæ°æ¥éæ©æ¸
é¤æä½æ¯å¦ä½¿ç¨å
# ç¬çº¿ç¨,é»è®¤æ
åµä¸åæ°è®¾ç½®ä¸º0(ä¸ä½¿ç¨åç¬çº¿ç¨),设置为 1 æ¶è¡¨ç¤ºä½¿ç¨åç¬çæ¸
é¤çº¿ç¨ã建议为1
innodb_flush_log_at_trx_commit = 2
# 0ï¼å¦æinnodb_flush_log_at_trx_commitçå¼ä¸º0,log bufferæ¯ç§å°±ä¼è¢«å·åæ¥å¿æ件å°ç£çï¼æ交äºå¡çæ¶åä¸åä»»ä½æä½ï¼æ§è¡æ¯ç±mysqlçmaster thread线ç¨æ¥æ§è¡çã
# 主线ç¨ä¸æ¯ç§ä¼å°éåæ¥å¿ç¼å²åå
¥ç£ççéåæ¥å¿æ件(REDO LOG)ä¸ãä¸è®ºäºå¡æ¯å¦å·²ç»æ交ï¼é»è®¤çæ¥å¿æ件æ¯ib_logfile0,ib_logfile1
# 1ï¼å½è®¾ä¸ºé»è®¤å¼1çæ¶åï¼æ¯æ¬¡æ交äºå¡çæ¶åï¼é½ä¼å°log bufferå·åå°æ¥å¿ã
# 2ï¼å¦æ设为2,æ¯æ¬¡æ交äºå¡é½ä¼åæ¥å¿ï¼ä½å¹¶ä¸ä¼æ§è¡å·çæä½ãæ¯ç§å®æ¶ä¼å·å°æ¥å¿æ件ãè¦æ³¨æçæ¯ï¼å¹¶ä¸è½ä¿è¯100%æ¯ç§ä¸å®é½ä¼å·å°ç£çï¼è¿è¦åå³äºè¿ç¨çè°åº¦ã
# æ¯æ¬¡äºå¡æ交çæ¶åå°æ°æ®åå
¥äºå¡æ¥å¿ï¼èè¿éçåå
¥ä»
æ¯è°ç¨äºæ件系ç»çåå
¥æä½ï¼èæ件系ç»æ¯æ ç¼åçï¼æ以è¿ä¸ªåå
¥å¹¶ä¸è½ä¿è¯æ°æ®å·²ç»åå
¥å°ç©çç£ç
# é»è®¤å¼1æ¯ä¸ºäºä¿è¯å®æ´çACIDãå½ç¶ï¼ä½ å¯ä»¥å°è¿ä¸ªé
置项设为1以å¤çå¼æ¥æ¢åæ´é«çæ§è½ï¼ä½æ¯å¨ç³»ç»å´©æºçæ¶åï¼ä½ å°ä¼ä¸¢å¤±1ç§çæ°æ®ã
# 设为0çè¯ï¼mysqldè¿ç¨å´©æºçæ¶åï¼å°±ä¼ä¸¢å¤±æå1ç§çäºå¡ã设为2,åªæå¨æä½ç³»ç»å´©æºæè
æçµçæ¶åæä¼ä¸¢å¤±æå1ç§çæ°æ®ãInnoDBå¨åæ¢å¤çæ¶åä¼å¿½ç¥è¿ä¸ªå¼ã
# æ»ç»
# 设为1å½ç¶æ¯æå®å
¨çï¼ä½æ§è½é¡µæ¯æå·®çï¼ç¸å¯¹å
¶ä»ä¸¤ä¸ªåæ°èè¨ï¼ä½ä¸æ¯ä¸è½æ¥åï¼ãå¦æ对æ°æ®ä¸è´æ§åå®æ´æ§è¦æ±ä¸é«ï¼å®å
¨å¯ä»¥è®¾ä¸º2ï¼å¦æåªææ±æ§è½ï¼ä¾å¦é«å¹¶ååçæ¥å¿æå¡å¨ï¼è®¾ä¸º0æ¥è·å¾æ´é«æ§è½
innodb_log_buffer_size = 2M
# æ¤åæ°ç¡®å®äºæ¥å¿æ件æç¨çå
å大å°ï¼ä»¥M为åä½ãç¼å²åºæ´å¤§è½æé«æ§è½ï¼ä½æå¤çæ
éå°ä¼ä¸¢å¤±æ°æ®ãMySQLå¼å人å建议设置为1ï¼8Mä¹é´
innodb_log_file_size = 32M
# æ¤åæ°ç¡®å®æ°æ®æ¥å¿æ件ç大å°ï¼æ´å¤§ç设置å¯ä»¥æé«æ§è½ï¼ä½ä¹ä¼å¢å æ¢å¤æ
éæ°æ®åºæéçæ¶é´
innodb_log_files_in_group = 3
# 为æé«æ§è½ï¼MySQLå¯ä»¥ä»¥å¾ªç¯æ¹å¼å°æ¥å¿æ件åå°å¤ä¸ªæ件ãæ¨è设置为3
innodb_max_dirty_pages_pct = 90
# innodb主线ç¨å·æ°ç¼åæ± ä¸çæ°æ®ï¼ä½¿èæ°æ®æ¯ä¾å°äº90%
innodb_lock_wait_timeout = 120
# InnoDBäºå¡å¨è¢«åæ»ä¹åå¯ä»¥çå¾
ä¸ä¸ªéå®çè¶
æ¶ç§æ°ãInnoDBå¨å®èªå·±çéå®è¡¨ä¸èªå¨æ£æµäºå¡æ»é并ä¸åæ»äºå¡ãInnoDBç¨LOCK TABLESè¯å¥æ³¨æå°éå®è®¾ç½®ãé»è®¤å¼æ¯50ç§
bulk_insert_buffer_size = 8M
# æ¹éæå
¥ç¼å大å°ï¼ è¿ä¸ªåæ°æ¯é对MyISAMåå¨å¼ææ¥è¯´çãéç¨äºå¨ä¸æ¬¡æ§æå
¥100-1000+æ¡è®°å½æ¶ï¼ æé«æçãé»è®¤å¼æ¯8Mãå¯ä»¥é对æ°æ®éç大å°ï¼ç¿»åå¢å ã
myisam_sort_buffer_size = 8M
# MyISAM设置æ¢å¤è¡¨ä¹æ¶ä½¿ç¨çç¼å²åºç尺寸ï¼å½å¨REPAIR TABLEæç¨CREATE INDEXå建索å¼æALTER TABLEè¿ç¨ä¸æåº MyISAMç´¢å¼åé
çç¼å²åº
myisam_max_sort_file_size = 10G
# å¦æ临æ¶æ件ä¼åå¾è¶
è¿ç´¢å¼ï¼ä¸è¦ä½¿ç¨å¿«éæåºç´¢å¼æ¹æ³æ¥å建ä¸ä¸ªç´¢å¼ã注éï¼è¿ä¸ªåæ°ä»¥åèçå½¢å¼ç»åº
myisam_repair_threads = 1
# å¦æ该å¼å¤§äº1ï¼å¨Repair by sortingè¿ç¨ä¸å¹¶è¡å建MyISAM表索å¼(æ¯ä¸ªç´¢å¼å¨èªå·±ç线ç¨å
)
interactive_timeout = 28800
# æå¡å¨å
³é交äºå¼è¿æ¥åçå¾
æ´»å¨çç§æ°ã交äºå¼å®¢æ·ç«¯å®ä¹ä¸ºå¨mysql_real_connect()ä¸ä½¿ç¨CLIENT_INTERACTIVEé项ç客æ·ç«¯ãé»è®¤å¼ï¼28800ç§ï¼8å°æ¶ï¼
wait_timeout = 28800
# æå¡å¨å
³éé交äºè¿æ¥ä¹åçå¾
æ´»å¨çç§æ°ãå¨çº¿ç¨å¯å¨æ¶ï¼æ ¹æ®å
¨å±wait_timeoutå¼æå
¨å±interactive_timeoutå¼åå§åä¼è¯wait_timeoutå¼ï¼
# åå³äºå®¢æ·ç«¯ç±»å(ç±mysql_real_connect()çè¿æ¥é项CLIENT_INTERACTIVEå®ä¹)ãåæ°é»è®¤å¼ï¼28800ç§ï¼8å°æ¶ï¼
# MySQLæå¡å¨ææ¯æçæ大è¿æ¥æ°æ¯æä¸éçï¼å 为æ¯ä¸ªè¿æ¥ç建ç«é½ä¼æ¶èå
åï¼å æ¤æ们å¸æ客æ·ç«¯å¨è¿æ¥å°MySQL Serverå¤çå®ç¸åºçæä½åï¼
# åºè¯¥æå¼è¿æ¥å¹¶éæ¾å ç¨çå
åãå¦æä½ çMySQL Serveræ大éçé²ç½®è¿æ¥ï¼ä»ä»¬ä¸ä»
ä¼ç½ç½æ¶èå
åï¼èä¸å¦æè¿æ¥ä¸ç´å¨ç´¯å èä¸æå¼ï¼
# æç»è¯å®ä¼è¾¾å°MySQL Serverçè¿æ¥ä¸éæ°ï¼è¿ä¼æ¥'too many connections'çé误ã对äºwait_timeoutçå¼è®¾å®ï¼åºè¯¥æ ¹æ®ç³»ç»çè¿è¡æ
åµæ¥å¤æã
# å¨ç³»ç»è¿è¡ä¸æ®µæ¶é´åï¼å¯ä»¥éè¿show processlistå½ä»¤æ¥çå½åç³»ç»çè¿æ¥ç¶æï¼å¦æåç°æ大éçsleepç¶æçè¿æ¥è¿ç¨ï¼å说æ该åæ°è®¾ç½®çè¿å¤§ï¼
# å¯ä»¥è¿è¡éå½çè°æ´å°äºãè¦åæ¶è®¾ç½®interactive_timeoutåwait_timeoutæä¼çæã
[mysqldump]
quick
max_allowed_packet = 16M #æå¡å¨åéåæ¥åçæ大å
é¿åº¦
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
温馨提示:答案为网友推荐,仅供参考