安装 MySQL 后,需要调整的 10 个性能配置项

| 选择喜欢的代码风格  
  • 一次只更改一个配置项!这是检验本次更改是否有利的途径。
  • 大多数配置项可以在运行时使用 SET GLOBAL 命令来修改。这种方式非常方便,并且如果修改后出现问题,还能马上恢复原设置。但到最后,仍然需要把这个改变写到配置文件中,使之永久生效。
  • 一个配置的调整即使重启了 MySQL 实例也没有生效?那么你是否使用了正确配置文件?你是否把这个选项写到了正确的节下面?(本文的所有选项都归属于 [mysqld] 节)
  • 服务器在配置调整之后启动失败:你是否使用了正确的单位?例如: innodb_buffer_pool_size 的单位是 byte,而 max_connection 是没有单位的。
  • 同一个配置文件里不要出现重复的配置项。如果你想追踪更改历史,请使用版本控制器。
  • 别做幼稚的运算,如“我的新服务器的 RAM 是旧的 2 倍,因此可以把所有的配置项的值都设置成之前的 2 倍”

MySQL 基础设置


innodb_buffer_pool_size:这是任何使用 InnoDB 存储引擎的 MySQL 在安装时第一个应该要查看的配置。Buffer pool 是用来缓存数据和索引的:尽可能地设置大一点,以确保在进行大多数读操作时是读内存而不是读磁盘。一般的经验是服务器内存的 2/3 左右,比如设置为 5-6GB(8GB RAM),20-25G(32GB RAM),100-120GB(128GB RAM)。

innodb_log_file_size:这个选项是设置 redo 日志(重做日志)的大小。redo 日志 是用来确保写入的数据能够快速地写入,并且持久化,还可以用于崩溃恢复(crash recovery)。MySQL 5.1 之前,这个选项很难去进行调整,因为你既想要加大 redo 日志来提高性能,又想要减小 redo 日志来进行快速的崩溃恢复。幸运的是,自 MySQL 5.5 之后,崩溃恢复的性能有了很大的提高,现在你可以拥有快速写入性能的同时,还能满足快速崩溃恢复。一直到 MySQL 5.5,redo 日志的总大小被限制在 4GB (默认有 2 个日志文件)。这个在 MySQL 5.6 中被增加了。

启动的时候设置 innodb_log_file_size = 512M(也就是 1GB 大小的 redo 日志),这样可以提供充足的写空间。如果你知道你的应用是频繁写入的,并且你使用的 MySQL 版本是 MySQL 5.6,你可以设置 innodb_log_file_size = 4G。

max_connections:如果你经常遇到 "Too many connections" 的错误,是因为 max_connections 太小了。这个错误很长见到,因为应用程序没有正确地关闭与数据库的连接,你需要设置连接数为比默认 151 更大的值。max_connections 设置过高(如 1000 或更高)的一个主要缺点是当服务器运行 1000 个或者更多的事务时,会响应缓慢甚至没有响应。在应用程序端使用连接池或者在 MySQL 端使用线程池有助于解决这个问题。

InnoDB 设置


从 MySQL 5.5 开始,InnoDB 成为了默认的存储引擎,并且它的使用频率比其他存储引擎的要多得多。这就是要小心配置它的原因。

innodb_file_per_table:这个配置项会决定 InnoDB 是使用共享表空间 (innodb_file_per_table = OFF) 来存储数据和索引,还是为每个表使用一个单独的 .ibd 文件 (innodb_file_per_table= ON)。对每个表使用一个文件的方式,在 drop、truncate, 或者重建表的时候,会回收这个表空间。在一些高级特性,如压缩的时候也需要开启使用独立表空间。然而这个选项却不能带来性能的提升。你不想使用独立表空间的一个主要场景是:有很多的表(例如:10000 以上张表)。

在 MySQL 5.6 中,这个配置项是默认开启的,因此多数情况下,你无需操作。对于早期的 MySQL 版本,需要在加载数据之前把它设置成 ON ,因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit:默认值为 1,表示 InnoDB 完全支持 ACID 特性。例如在在一个主节点上,你主要关注数据安全性,这是最好的设置值。然而它会对速度缓慢的磁盘系统造成很大的开销,因为每次将改变刷新到 redo 日志的时候,都需要额外的 fsync 操作。设置为 2,可靠性会差一点,因为已提交的事务只会 1 秒钟刷新一次到 redo 日志,但在某些情况下,对一个主节点而言,这仍然是可以接受的,而且对于复制关系的从库来说,这是一个很好的值。设置为 0,速度更快,但是在遇到崩溃的时候很可能会丢失一些数据,这只对从库是一个好的设置值。

innodb_flush_method:这个设置项决定了数据和日志刷新到磁盘的方式。当服务器硬件有 RAID 控制器、断电保护、采取 write-back 缓存机制的时候,最常用的值是 O_DIRECT;其他大多数场景使用默认值 fdatasync。sysbench 是一个帮助你在这两个值之间做出选择好工具

innodb_log_buffer_size:这个设置项用来设置缓存还没有提交的事务的缓冲区的大小。默认值(1MB) 一般是够用的,但一旦事务之中带有大 blob/text 字段,这个缓冲区会被很快填满,并引起额外的 I/O 负载。看看 innodb_log_waits 这个状态变量的值,如果不是 0 的话,需要增加 innodb_log_buffer_size

其它设置


query_cache_size:Query Cache(查询缓存)是一个众所周知的瓶颈位,即使在并发量不高的时候也会出现。最好的选择是从一开始就禁用它,通过设置 query_cache_size = 0 (MySQL 5.6 中现在已经默认禁用),并通过其它途径去提高读查询:合适的索引,增加从库去分散读压力,或者使用一个额外的缓存(例如 memcache 或者 redis)。如果你的 MySQL 已经开启了查询缓存,并且没有发现有任何错误,开启查询缓存可能是有利的,如果要禁用它,就需要谨慎了。

log_bin:如果要让一个节点做为复制关系中的主节点,启用二进制日志(binary log)是必须的。这样的话,同时需要设置全局唯一的 server_id。 对于单节点,在希望做基于时间点的恢复的时候,开启这个选项也是很有用的:恢复最新的备份和应用二进制日志。二进制日志一旦创建,会被永久保存。所以如果不想耗尽磁盘空间,应该使用 PURGE BINARY LOGS 清理旧的二进制日志文件,或者设置 expire_logs_days 选项指定多少天之后,自动清理过期的二进制日志。

记录二进制日志不是没有开销的。所以,例如是一个复制关系中的从节点,建议禁用二进制日志

skip_name_resolve:当一个客户端连接上来的时候,服务端会执行主机名解释操作,当 DNS 很慢时,建立的连接也会很慢。因此建议在启动的时候设置 skip_name_resolve 来禁用 DNS 查找。唯一的局限是 GRANT 语句仅且仅能使用 IP 地址,所以,在已有系统中添加这个选项时需要格外小心。

my.cnf 示例配置文件参考


[client]
#password                       = your_password
port                            = 3306
socket                          = /Data/apps/mysql/mysql.sock
#default-character-set          = utf8

[mysqld]
port                            = 3306
socket                          = /Data/apps/mysql/mysql.sock
datadir                         = /Data/apps/mysql/data
log_error                       = /Data/apps/mysql/data/error.log

explicit_defaults_for_timestamp = true

skip-external-locking
skip_name_resolve

character_set_server            = utf8
default_storage_engine          = InnoDB
#线程缓存
thread_cache_size               = 128
#innodb
innodb_buffer_pool_size         = 10G
innodb_buffer_pool_instances    = 1
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_data_file_path           = ibdata1:1G:autoextend
innodb_file_per_table           = on 
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8
innodb_io_capacity              = 100
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_open_files               = 2000
innodb_file_per_table           = on
innodb_autoextend_increment     = 256
innodb_print_all_deadlocks      = 1
innodb_thread_concurrency       = 16

max_allowed_packet              = 64M
max_connections                 = 300
table_open_cache                = 1024
#sort_buffer_size默认256k
sort_buffer_size                = 1M
#read_buffer_size默认128k
read_buffer_size                = 1M
#read_rnd_buffer_size默认256k
read_rnd_buffer_size            = 4M
#join_buffer_size默认256k
join_buffer_size                = 1M
#myisam_sort_buffer_size默认8M
myisam_sort_buffer_size         = 128M
open_files_limit                = 65535
max_heap_table_size             = 256M
tmp_table_size                  = 256M
#myisam
key_buffer_size                 = 512M
myisam_sort_buffer_size         = 128M
bulk_insert_buffer_size         = 64M
#关闭查询缓存
query_cache_type                = 0
query_cache_size                = 0
#慢查询
long_query_time                 = 3
slow_query_log                  = 1
slow_query_log_file             = /Data/apps/mysql/data/slow.log

#如果运行的SQL没有使用索引,不会将该SQL记录到慢查询日志中

log_queries_not_using_indexes   = OFF
#启用事件
event_scheduler                 = 1
sql_mode                        = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#表名不区分大小写
lower_case_table_names          = 1
#活动连接时间,默认8小时,28800秒
interactive_timeout             = 28800
#非活动连接时间,300秒
wait_timeout                    = 1800

log_bin                         = mysql-bin
binlog_cache_size               = 2M
sync_binlog                     = 60
relay_log                       = relay-log
relay_log_index                 = relay-log.index
#日志时间戳变量,默认UTC,为了方便查看改为SYSTEM
log_timestamps                  = SYSTEM
#log-bin 保留7天,7天前日志自动删除
expire_logs_days                = 7
default_password_lifetime       = 0
#启用远程表引擎
federated
server-id                       = 91
#启用GTID多线程复制
binlog_format                   = mixed
#log-slave-updates              = ON
#开启GTID
gtid_mode                       = ON
enforce_gtid_consistency        = TRUE
#开启并行复制MTS
slave_parallel_type             = LOGICAL_CLOCK
slave_parallel_workers          = 4
master_info_repository          = TABLE
relay_log_info_repository       = TABLE
relay_log_recovery              = ON
#切换完成后去掉
#replicate_wild_ignore_table     = flow.adsense_%
#read-only                       = ON
#服务器启动不启动从属线程
skip-slave-start

symbolic-links                  = 0

[mysqldump]
quick
max_allowed_packet              = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size                 = 128M
sort_buffer_size                = 128M
read_buffer                     = 2M
write_buffer                    = 2M

[mysqlhotcopy]
interactive-timeout

数据库扩展阅读:




发表评论