MySQL5.7数据库优化模板

8核16GMySQL数据库优化模板

[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
datadir     = /usr/local/mysql/var
skip-external-locking
# MyISAM
key_buffer_size = 64M  # MyISAM表索引缓冲区
myisam_sort_buffer_size = 128M  # MyISAM表索引创建缓冲区
# 缓冲区和缓存设置
max_allowed_packet = 128M  # 允许的最大数据包大小
table_open_cache = 4096  # 能打开的表数量
sort_buffer_size = 4M  # 排序操作分配的内存
net_buffer_length = 16K  # 网络通信缓冲区大小
read_buffer_size = 4M  # 顺序扫描缓冲区
read_rnd_buffer_size = 8M  # 随机读操作缓冲区
thread_cache_size = 256  # 缓存的线程数量
query_cache_size = 0  # 查询缓存大小（MySQL 8.0已废弃）
tmp_table_size = 256M  # 内存临时表的最大大小
performance_schema_max_table_instances = 6000  # performance_schema表实例最大数量

# 慢查询日志设置
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/var/mysql_slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  # 记录未使用索引的查询

# 其他设置
explicit_defaults_for_timestamp = true  # 显式默认时间戳
max_connections = 5000  # 最大连接数
max_connect_errors = 100000  # 最大连接错误数
open_files_limit = 65535  # 打开文件的限制

# 二进制日志设置
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
expire_logs_days = 10	# 二进制日志保留天数
early-plugin-load = ""
log_error = /usr/local/mysql/var/mysql_error.log  # 错误日志文件
general_log = 0  # 一般查询日志。设置为0表示禁用，设置为1表示启用。
general_log_file = /usr/local/mysql/var/mysql_general.log  # 一般查询日志文件

# InnoDB设置
default_storage_engine = InnoDB  # 默认存储引擎
innodb_data_home_dir = /usr/local/mysql/var  # InnoDB数据文件目录
innodb_data_file_path = ibdata1:10M:autoextend  # InnoDB数据文件路径
innodb_log_group_home_dir = /usr/local/mysql/var  # InnoDB日志组目录
innodb_buffer_pool_size = 12G  # InnoDB缓冲池大小
innodb_buffer_pool_instances = 8  # 缓冲池实例数，以减少内存争用和提高并发性能。一般为内存的一半以上，但不应超过CPU过多
innodb_log_file_size = 1024M  # 日志文件大小
innodb_log_buffer_size = 32M  # 日志缓冲区大小
innodb_flush_log_at_trx_commit = 2 # 设置InnoDB事务日志的刷新行为，1表示每次事务提交时写入磁盘，2表示每秒刷新一次
innodb_lock_wait_timeout = 20  # 锁等待超时时间（秒）
innodb_thread_concurrency = 0  # InnoDB线程并发数。表示自动调整，设置的话一般为CPU的两倍
# innodb_flush_method = O_DIRECT  # 直接IO，减少文件系统缓存压力。如果系统使用的是高性能的 SSD，且业务对 I/O 性能要求较高，建议尝试使用 O_DIRECT。
#innodb_file_per_table = 1  # 每个表使用单独的表空间文件。可以带来更灵活的管理、更高的并发性能和更高效的空间利用率，但同时也会增加文件系统管理的复杂性。
innodb_io_capacity = 2000  # 指定InnoDB每秒可以处理的I/O操作数量，用于限制后台I/O操作速率。SSD，可以设置为2000或更高
innodb_io_capacity_max = 4000  # 指定InnoDB在高负载时允许的最大I/O操作数量，用于突发性I/O操作。通常是innodb_io_capacity的2倍或更多。
innodb_flush_neighbors = 1  # 在SSD上禁用邻近页刷新，提高性能。在HDD上可以保持默认值1。
innodb_purge_threads = 4  # 指定用于清理已删除记录的后台线程数，加速清理过程。可以设置为4或更多。
innodb_read_io_threads = 8  # 指定用于读取操作的后台I/O线程数，提高并发读取性能。4到8，具体取决于硬件和负载。
innodb_write_io_threads = 8  # 指定用于写入操作的后台I/O线程数，提高并发写入性能。4到8，具体取决于硬件和负载。
# 启用表空间压缩（如果需要）
innodb_file_per_table = 1
innodb_compression_level = 6
# 启用查询性能分析
performance_schema = ON

# 查询优化
join_buffer_size = 4M  # 联接操作的缓冲区大小
query_cache_type = 0  # 禁用查询缓存
query_cache_size = 0  # 查询缓存大小
query_cache_limit = 1M  # 单个查询的最大缓存大小

# 字符集设置
character-set-server = utf8mb4  # 默认字符集
collation-server = utf8mb4_general_ci  # 默认排序规则

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer_size = 4M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

4核8GMySQL数据库优化模板

[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
datadir     = /usr/local/mysql/var
skip-external-locking

# MyISAM
key_buffer_size         = 64M  # 缓存索引块的大小
myisam_sort_buffer_size = 128M  # MyISAM索引重建时使用的排序缓冲区大小

# 缓冲区和缓存配置
max_allowed_packet      = 64M   # 单个包的最大大小
table_open_cache        = 2048  # 打开的表的缓存数量
sort_buffer_size        = 4M    # 排序操作的缓冲区大小
net_buffer_length       = 16K   # 网络通信缓冲区大小
read_buffer_size        = 2M    # 顺序扫描的缓冲区大小
read_rnd_buffer_size    = 16M   # 随机读取缓冲区大小
thread_cache_size       = 64    # 缓存的线程数量
query_cache_size        = 0     # 查询缓存大小，不推荐使用查询缓存
tmp_table_size          = 64M   # 临时表的最大大小
performance_schema_max_table_instances = 1000
# 慢查询日志
slow_query_log          = ON
slow_query_log_file     = /usr/local/mysql/var/mysql_slow.log
long_query_time         = 1     # 慢查询时间阈值

# 连接
max_connections         = 2000  # 最大连接数
max_connect_errors      = 5000  # 允许的最大连接错误数
open_files_limit        = 65535 # 打开文件的最大数量

# 二进制日志
log-bin                 = mysql-bin
binlog_format           = mixed
server-id               = 1
expire_logs_days        = 10    # 日志过期时间

# 存储引擎和InnoDB配置
default_storage_engine          = InnoDB
innodb_data_home_dir            = /usr/local/mysql/var
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /usr/local/mysql/var
innodb_buffer_pool_size         = 6G   # 缓冲池大小
innodb_log_file_size            = 512M # 日志文件大小
innodb_log_buffer_size          = 16M  # 日志缓冲区大小
innodb_flush_log_at_trx_commit  = 2    # 日志刷新策略，性能优先
innodb_lock_wait_timeout        = 50   # 锁等待超时时间
innodb_thread_concurrency       = 8    # 最大并发线程数

# 一些优化选项
join_buffer_size                = 4M   # 联接操作的缓冲区大小
innodb_flush_method             = O_DIRECT # 直接IO，减少文件系统缓存压力
innodb_file_per_table           = 1    # 每个表使用单独的表空间文件
innodb_stats_on_metadata        = 0    # 禁用统计信息自动更新
query_cache_type                = 0    # 禁用查询缓存

innodb_io_capacity = 1000  
innodb_io_capacity_max = 2000  
innodb_flush_neighbors = 0  
innodb_purge_threads = 2  
innodb_read_io_threads = 4  
innodb_write_io_threads = 4  

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer_size = 4M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout