基本配置:

你需要经常察看以下3个配置项。不然&＃xff0c;可能很快就会出问题。

1、innodb_buffer_pool_size

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

2、innodb_log_file_size

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

3、max_connections

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

InnoDB配置&＃xff1a;

从MySQL 5.5版本开始&＃xff0c;InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。

1、innodb_file_per_table

这项设置告知InnoDB是否需要将所有表的数据和索引存放在 共享表空间里(innodb_file_per_table &＃61; OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table &＃61; ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的&＃xff0c;比如数据压缩。但是它 不会带来任何性能收益。你不想让每张表一个文件的主要场景是&＃xff1a;有非常多的表(比如10k&＃43;)。MySQL 5.6中&＃xff0c;这个属性默认值是ON&＃xff0c;因此大部分情况下你什么都不需要做。对于之前的版本你必须在加载数据之前将这个属性设置为ON&＃xff0c;因为它只对新创建的表有影响。

2、innodb_flush_log_at_trx_commit

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

3、innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式。一般来说&＃xff0c;如果你有硬件RAID控制器&＃xff0c;并且其独立缓存采用write-back机制&＃xff0c;并有着电池断电保护&＃xff0c;那 么应该设置配置为O_DIRECT&＃xff1b;否则&＃xff0c;大多数情况下应将其设为fdatasync(默认值)。sysbench是一个可以帮助你决定这个选项的好工 具。

4、innodb_log_buffer_size

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

其他设置&＃xff1a;

1、query_cache_size

query cache(查询缓存)是一个众所周知的瓶颈&＃xff0c;甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用&＃xff0c;设置query_cache_size &＃61; 0(现在MySQL 5.6的默认值)并利用其他方法加速查询&＃xff1a;优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。如果你已经为你的应用启 用了query cache并且还没有发现任何问题&＃xff0c;query cache可能对你有用。这是如果你想停用它&＃xff0c;那就得小心了。

2、log_bin

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

10、skip_name_resolve

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

总结&＃xff1a;

当然还有其他的设置 可以起作用&＃xff0c;取决于你的负载或硬件&＃xff1a;在慢内存和快磁盘、高并发和写密集型负载情况下&＃xff0c;你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个稳健 的MySQL配置&＃xff0c;而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些设置对你来说很重要。