首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫

MySQL 5.5.49 大內存優化配置文件深度解析與實戰調優

     編輯:MySQL 資料庫相關知識

官方預設配置文件對照

  • my-small.cnf:≤64M 內存
  • my-medium.cnf:32-128M
  • my-large.cnf:512M
  • my-huge.cnf:1-2G
  • my-innodb-heavy-4G.cnf:4G 內存、純 InnoDB、ACID、少連接重查詢

5.6+ 取消預設配置,改為極簡默認配置,需自行按需調優。

my-innodb-heavy-4G.cnf 關鍵參數詳解

文中逐行註釋:back_log、max_connections、table_open_cache、binlog_cache_size、max_heap_table_size、read_buffer_size、read_rnd_buffer_size、sort_buffer_size、join_buffer_size、thread_cache_size、thread_concurrency、query_cache_size/limit、ft_min_word_len、default-storage-engine、transaction_isolation、tmp_table_size、log-bin/binlog_format、slow_query_log/long_query_time、server-id、key_buffer_size、bulk_insert_buffer_size、myisam_sort_buffer_size、innodb_buffer_pool_size、innodb_data_file_path、innodb_write_io_threads、innodb_thread_concurrency、innodb_flush_log_at_trx_commit、innodb_log_buffer_size、innodb_log_file_size、innodb_log_files_in_group、innodb_max_dirty_pages_pct、innodb_lock_wait_timeout 等。

實戰優化配置(16G 內存、日 IP 100-200W)

[mysqld]
back_log = 512
max_connections = 3000
table_open_cache = 4096
max_allowed_packet = 32M
max_heap_table_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
log_warnings
slow_query_log
long_query_time = 10
server-id = 1
key_buffer_size = 128M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
open_files_limit = 10240

調優原則

配置優化必須基於線上實際負載,不可盲目套用;以 SHOW STATUS、SHOW VARIABLES、SHOW ENGINE INNODB STATUS 持續觀測調整。

  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式碼之家 All Rights Reserved