企業環境下MySQL5.5調優

參照 騰訊雲 和ucloud my.cnf 以及網上找的資料 整理出來的 my.cnf , 以後修改任何引數都會繼續更新 也是在測試階段

物理機 ： ubuntu14.04 配置 8core 32G
mysql ： 5.5.62

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer             = 256M
max_allowed_packet     = 1073741824
thread_stack           = 512K
thread_cache_size      = 50
max_connections        = 5000
max_connect_errors     = 1000000
table_cache            = 4096
max_heap_table_size    = 128M
open_files_limit       = 102400
back_log               = 2000
thread_concurrency     = 20
sort_buffer_size       = 524288
ft_min_word_len        = 4
query_cache_size       = 0
log_error                          = /var/log/mysql/error.log
log_slow_queries                   = /var/log/mysql/mysql-slow.log
long_query_time                    = 3
server-id                          = 1
log_bin                            = /var/log/mysql/mysql-bin.log
expire_logs_days                   = 2
tmp_table_size                     = 4294967295
#default_table_type                = InnoDB
#loose-skip-bdb
default-storage-engine             =InnoDB
innodb_file_format=barracuda
#ROW_FORMAT=COMPRESSED
innodb_additional_mem_pool_size    = 64M
innodb_buffer_pool_size            = 6G
innodb_data_file_path              = ibdata1:128M:autoextend
innodb_read_io_threads             = 8
innodb_write_io_threads            = 8
innodb_thread_concurrency          = 20
innodb_flush_log_at_trx_commit     = 2
innodb_log_buffer_size             = 8388608
innodb_log_file_size               = 512M
innodb_log_files_in_group          = 3
innodb_max_dirty_pages_pct         = 90
innodb_lock_wait_timeout           = 120
[mysqldump]
quick
quote-names
max_allowed_packet                 = 1073741824
[mysql]
default-character-set              = utf8
[isamchk]
key_buffer                         = 256M
!includedir /etc/mysql/conf.d/

附一些引數的簡單解釋

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
open-files-limit = 8192   #增加每個程式的可開啟檔案數量
[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp          #記錄msyql臨時檔案
#back_log        = 300   
lc-messages-dir = /usr/share/mysql
skip-external-locking    #允許外部檔案級別的鎖. 開啟檔案鎖會對效能造成負面影響
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 0.0.0.0   #控制可連線資料庫的 客戶端
#
# * Fine Tuning
#
key_buffer      = 16M   #可變大 待查詢  用於索引塊緩衝區大小
max_allowed_packet  = 16M    
thread_stack        = 192K  #執行緒使用的堆大小. 此容量的記憶體在每次連線時被預留
thread_cache_size       = 8     #cache中保留多少執行緒用於重用
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 100     #mysql 允許 同時會話的上限
max_connect_errors     = 30      #mysql 允許最大的錯誤連線上限
table_cache            = 64      #mysql所有執行緒開啟表的數量 ，需對應的open_files_limit 允許
max_heap_table_size    = 128M    #獨立的記憶體表所允許的最大容量（防止意外建立一個超大的記憶體表導致用盡所有的記憶體資源）
open_files_limit       = 10240   #mysql允許開啟最大檔案數
back_log               = 300     #作業系統在監聽佇列中所保持的連線數
thread_concurrency     = 10      #應用程式給予執行緒系統一個提示在同一時間給予渴望被執行的執行緒的數量
#
# * Query Cache Configuration
#
sort_buffer_size        = 16M    #排序發生時每個執行緒分配
join_buffer_size        = 16M    #當全聯合發生時,在每個執行緒中分配
query_cache_limit   = 1M     #只有小於此設定值的結果才會被緩衝
query_cache_size        = 16M    #查詢緩衝常被用來緩衝 SELECT 的結果並且在下一次同樣查詢的時候不再執行直接返回結果
ft_min_word_len         = 8      #被全文檢索索引的最小的字長
#memlock                         #如果你的系統支援 memlock() 函式,你也許希望開啟此選項用以讓執行中的mysql在在記憶體高度緊張的時候,資料在記憶體中保持鎖定並且防止可能被swapping out
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
#log                   #開啟全查詢日誌. 所有的由伺服器接收到的查詢 (甚至對於一個錯誤語法的查詢） 用於除錯環境
#log_warnings          #將警告列印輸出到錯誤log檔案
log_error = /var/log/mysql/error.log
# Here you can see queries with especially long duration
log_slow_queries    = /var/log/mysql/mysql-slow.log   #開啟慢日誌
long_query_time         = 2                               #慢日誌時間界限
log_long_format                                           #在慢速日誌中記錄更多的資訊 
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id       = 1                                   #主從id
log_bin         = /var/log/mysql/mysql-bin.log        #開啟二進位制
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_cache_size       = 4M   #在一個事務中binlog 為了記錄sql狀態所持有的cache大小
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
log_slave_updates        #主從日誌
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_table_type = MYISAM                #當建立新表時作為預設使用的表型別, 
transaction_isolation = REPEATABLE-READ    #設定預設的事務隔離級別.可用的級別如下: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
tmp_table_size = 128M                      #內部(記憶體中)臨時表的最大大小
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
# MyISAM 相關選項
#read_buffer_size = 8M                     #用來做MyISAM表全表掃描的緩衝大小
#read_rnd_buffer_size = 64M                #當在排序之後,從一個已經排序好的序列中讀取行時,行資料將從這個緩衝中讀取來防止磁碟尋道
#bulk_insert_buffer_size = 256M            #MyISAM 使用特殊的類似樹的cache來使得突發插入
#myisam_sort_buffer_size = 256M            #此緩衝當MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一個空表中引起重建索引時被分配.
#myisam_max_sort_file_size = 10G           #MySQL重建索引時所允許的最大臨時檔案的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
#myisam_max_extra_sort_file_size = 10G     #如果被用來更快的索引建立索引所使用臨時檔案大於制定的值,那就使用鍵值緩衝方法.
#myisam_repair_threads = 1                 #如果一個表擁有超過一個索引, MyISAM 可以透過並行排序使用超過一個執行緒去修復他們
#myisam_recover                            #自動檢查和修復沒有適當關閉的 MyISAM 表
#INNODB 相關選項
#skip-innodb                               #如果你的MySQL服務包含InnoDB支援但是並不打算使用的話,
#innodb_additional_mem_pool_size = 64M     #附加的記憶體池被InnoDB用來儲存 metadata 資訊
#innodb_buffer_pool_size = 6G              #InnoDB使用一個緩衝池來儲存索引和原始資料, 不像 MyISAM
#innodb_data_file_path = ibdata1:10M:autoextend  #InnoDB 將資料儲存在一個或者多個資料檔案中成為表空間
#innodb_data_home_dir =                          #設定此選項如果你希望InnoDB表空間檔案被儲存在其他分割槽
#innodb_file_io_threads = 4                      #用來同步IO操作的IO執行緒的數量.
#innodb_force_recovery=1                         #如果你發現InnoDB表空間損壞, 設定此值為一個非零值可能幫助你匯出你的表
#innodb_thread_concurrency = 16                  #在InnoDb核心內的允許執行緒數量
#innodb_flush_log_at_trx_commit = 2              #如果設定為1 ,InnoDB會在每次提交後重新整理(fsync)事務日誌到磁碟上 如果是遊戲伺服器，建議此值設定為2；如果是對資料安全要求極高的應用，建議設定為1
#innodb_fast_shutdown                            #加速InnoDB的關閉. 這會阻止InnoDB在關閉時做全清除以及插入緩衝合併.
#innodb_log_buffer_size = 16M                    #用來緩衝日誌資料的緩衝區的大小.
#innodb_log_file_size = 512M                     #在日誌組中每個日誌檔案的大小.   
#innodb_log_files_in_group = 3                   #在日誌組中的檔案總數.
#innodb_log_group_home_dir                       #InnoDB的日誌檔案所在位置. 預設是MySQL的datadir
#innodb_max_dirty_pages_pct = 90                 #在InnoDB緩衝池中最大允許的髒頁面的比例.
#innodb_flush_method=O_DSYNC                     #InnoDB用來重新整理日誌的方法 表空間總是使用雙重寫入重新整理方法 # 預設值是 “fdatasync”, 另一個是 “O_DSYNC”
#innodb_lock_wait_timeout = 120                  #在被回滾前,一個InnoDB的事務應該等待一個鎖被批准多久
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M   ## 服務所能處理的請求包的最大大小以及服務所能處理的最大的請求大小(當與大的BLOB欄位一起工作時相當必要)
#safe-updates                    #僅僅允許使用鍵值的 UPDATEs 和 DELETEs 
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition 
[isamchk]
key_buffer      = 16M
sort_buffer_size        = 2048M
read_buffer             = 32M
write_buffer            = 32M
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
[myisamchk]
key_buffer             = 2048M
sort_buffer_size       = 2048M
read_buffer            = 32M
write_buffer           = 32M
[mysqlhotcopy]
interactive-timeout
!includedir /etc/mysql/conf.d/