写在前面
即使是有经验的人也会犯一些会造成许多麻烦的错误。因此,在应用本文的建议之前,请记住以下内容:
1.每次更改一个设置!这是验证设置是否有效的唯一方法。
2.大多数设置可以使用 set global命令进行更改。这是非常方便的,这样的话如果您的更改产生问题,他可以快速的进行配置恢复。但最后,希望在my.cnf配置文件中永久地调整设置。
3.有时候即使MySQL重启后,配置文件中的设置也不生效。这时候你需要考虑:你使用正确的配置文件了吗?你把这个设置放在正确的地方了吗?(在这篇文章中的所有设置都属于【mysqld】部分)
4.如在更改配置后数据库无法启动,需要检查是否使用正确的单位?例如,innodb_buffer_pool_size应设置为字节,max_connection设置为数值等等。
5.在配置文件中不允许重复设置。如果要跟踪配置的更改,请使用版本控制。
6.不要做幼稚的数学算法,比如“我的新服务器有2倍的RAM,我仅仅会让所有的值比以前的值提高2倍”。
基本设置
这里主要讲解3个非常重要的MySQL性能调优设置,您应该始终查看这些参数。如果你不这样做,你很可能很快遇到问题。
innodb_buffer_pool_size:
该参数是设置InnoDB存储引擎的缓存池。缓冲池是缓存数据和索引的地方:分配尽可能大的内存将确保大多数读取操作中使用内存而不是磁盘。典型的设置值是5-6gb(8GB内存),20-25gb(32GB内存),100-120gb(128GB内存)。
innodb_log_file_size:
该参数是设置重做日志. 重做日志可以加速写入性能,同时会用于数据库Crash Recover. 在MySQL5.1之前,该参数很难去调整, 因为大的Redo Log可以增加写入性能,而小的Redo Log可以提高Crash Recover速度。 幸运的是,自从MySQL 5.5以来,崩溃恢复性能有了很大提高,所以现在加大Redo Log可以拥有良好的写入性能和快速崩溃恢复。在MySQL 5.5中总的重做日志的大小是有限的4GB(默认的是有2个日志文件),这在MySQL 5.6中得到了改进。
默认参数innodb_log_file_size = 512M(默认1GB的重做日志),如果你知道你的应用程序是写密集型,并且你使用的是MySQL 5.6,你可以设置innodb_log_file_size = 4G。
max_connections:
如果你经常遇到 ‘Too many connections’ 错误, 那么是max_connections设置太低. 最有可能的原因是应用程序没有正确的关闭数据库连接, 你需要的连接比默认的151多的多. max_connections设置很高的缺点 (比如1000或更多) 是可能运行1000个活动会话将会使数据库变的缓慢,在应用程序级别使用连接池或在MySQL级别上的线程池可以有助于解决这个问题。
INNODB设置
InnoDB自MySQL 5.5是默认的存储引擎,它比其他任何存储引擎使用的更加频繁。所以我们要仔细配置它。