溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
|
參照 騰訊云 和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/
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
