一、Innodb设置
innodb_buffer_pool_size:保存innodb数据和索引在该buffer中,对专用mysql服务器,推荐50-80%物理内存大小,如
5-6GB (8GB RAM),
20-25GB (32GB RAM), 100-120GB (128GB RAM)
。
innodb_log_file_size:控制redo日志的大小,redo日志可以加速写,持久性和crash恢复。通常使用两个文件,mysql 5.5总的redo日志限制为4GB,对写负载型的应用,应该增加该值,使后台检查点活动减少,提高性能,如4G左右,太大将使故障恢复需要更长的时间。
innodb_flush_method:控制数据和日志怎样刷新到磁盘,如果使用了RAID控制器,应该使用O_DIRECT。
innodb_flush_neighbors:如果是SSD可以关闭,对连续IO没有任何性能改善。
innodb_io_capacity和innodb_io_capacity_max:设置每秒有多少后台线程工作,根据硬件的能力进行设置。
innodb_lru_scan_depth:如果增加innodb_io_capacity,也要增加该值
innodb_file_per_table:设置为ON,每个表使用独立的.ibd文件存储数据和索引,允许当dropping、truncating或rebuilding表时回收再利用剩余空间,也能支持扩展的特性如压缩。
innodb_flush_log_at_trx_commit:默认为1,意味着Innodb是完全遵守ACID特性,刷新每个改变到redo日志;设置为2,每秒刷新一次;0更快,但是在crash时将丢失一些数据。
innodb_log_buffer_size:事务日志buffer,当有大的blob/text字段时,可以考虑提高该值,以免产生额外的I/O负载。通过观察Innodb_log_waits状态变量,如果非0,将增大该值。
二、复制
log-bin:启用二进制日志,默认不是crash安全的,根据业务情况进行设置,如果需要持久性,需要开启sync_binlog=1,sync_relay_log=1,relay-log-info-repository=TABLE和master-info-repository=TABLE或sync_relay_log_info=1和sync_master_info=1。
expire-log-days:推荐1-10天
server-id:在复制环境,每个服务器需要有唯一的server-id
binlog_format=ROW:使用基于行的复制,提高性能,减少锁。同时需要设置transaction-isolation=READ-COMMITIED,innodb_autoinc_lock_mode=2
三、混合参数
timezone=GMT:适合全球化
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci:utf8是更好的格式,也应该设置skip-character-set-client-handshake忽略应用程序设置的字符集
sql-mode:默认是宽容的,会静默的截断数据,应该更好的设置
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,
NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,
NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
skip-name-resolve:对近来的连接禁用域名反向查找,禁止使用主机名进行授权,必须使用IP地址进行授权
max_connect_errors:设置成100000,因使用防火墙阻止访问攻击
max-connections:建议300-500,推荐不要太高,建议使用应用连接池,或者mysql线程池
query_cache_size:关闭该选项,使用另外的方式,如好的索引、使用复制分担读负载,或使用外部cache机制(memcached、redis)提高性能。
参考:
1、"What to tune in MySQL 5.6 after installation":
http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html
2、"Improving MySQL's default configuration":
http://www.tocker.ca/2013/09/10/improving-mysqls-default-configuration.html
3、"Deciding whether or not to make MySQL durable":
http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-make-mysql-durable.html
4、<<Innodb_logs_ebook>>