MySQL必须调整的几项配置

 innodb_buffer_pool_size:这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方:这个 值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存), 20-25GB(32GB内存) 

 

innodb_log_file_size:这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到 MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。幸 运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能 了。一直到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。

 

max_connections:如果你经常看到‘Too many connections’错误,是因为max_connections的值太低了。这非 常常见因为应用程序没有正确的关闭数据库连接,你需要比默认的151连接数更大的值。max_connection值被设高了 (例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连 接池或者在MySQL里使用进程池有助于解决这一问题。

 

InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。 

innodb_file_per_table:告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里 (innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON,mysql5.6的默认配置)。每 张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的,比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是:有非常多的表(比如10k+)。 

 

innodb_flush_log_at_trx_commit:默认值为1,表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的 时候这个值是最合适的,比如在一个主节点上。但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为 每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠(unreliable)因为提交的事务仅 仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的。如 果值为0速度就更快了,但在系统崩溃时可能丢失一些数据:只适用于备份节点 

 

innodb_log_buffer_size:这项配置决定了为尚未执行的事务分配的缓存。其默认值(1MB)一般来说已经够用了, 但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看 Innodb_log_waits状态变量,如果它不是0,增加innodb_log_buffer_size。

 

query_cache_size:query cache(查询缓存)是一个众所周知的瓶颈,甚至在并发并不多的时候也是如此。 最佳选 项是将其从一开始就停用,设置query_cache_size = 0(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索 引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。 

 

log_bin:如果你想让数据库服务器充当主节点的备份节点,那么开启二进制日志是必须的。如果这么做了之后,还别 忘了设置server_id为一个唯一的值。就算只有一个服务器,如果你想做基于时间点的数据恢复,这(开启二进制日志) 也是很有用的:从你最近的备份中恢复(全量备份),并应用二进制日志中的修改(增量备份)。二进制日志一旦创建 就将永久保存。所以如果你不想让磁盘空间耗尽,你可以用 PURGE BINARY LOGS 来清除旧文件,或者设置 expire_logs_days 来指定过多少天日志将被自动清除。 

 

skip_name_resolve:当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会 很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只 能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。