数据库优化——优化MySQL Server

摘要

本文为数据库优化系列文章的第四篇文章 :《数据库优化——优化MySQL Server》

更多文章参见

数据库优化 : http://blog.csdn.net/leyounger/article/details/70157087

优化MySQL Server

1. 查看MySQL Server参数

//使用SHOW VARIABLES 和 SHOW STATUS命令查看MySQL的服务器静态参数值和动态运行状态信息

//若想要了解每个参数的详细定义，使用如下命令
mysql> mysqld --verbose --help|more

2. 影响MySQL性能的重要参数

(1) key_buffer_size

这个参数用来设置索引块缓存的大小，被所有线程共享，只适用于MyISAM引擎

具体的设置优化可以百度一下

(2) table_cache

这个参数用来表示数据库用户打开表的缓存数量。

每个连接进来，都会至少打开一个表缓存。
因此table_cache和max_connections有关。
例如，对于200个并行运行的连接，应该让表的缓存至少为200*N。
（这里N是可以执行的查询的一个联结中表的最大数量）

可以通过检查mysqld的状态变量open_tables和opened_tables来确定这个参数是否过小
open_tables   => 当前打开的表缓存数
opened_tables => 曾经打开的表缓存数

如果执行FLUSH TABLES操作，只有前者会减少，因此open_tables对于决定table_cache 的值至关重要

(3) innodb_buffer_pool_size

这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小。

MyISAM的key_buffer_size只缓存索引键，而innodb_buffer_pool_size却是同时为数据块和索引块做缓存。
这个值越高，访问表中数据需要的磁盘时间IO就越少。

在一个专用的数据库服务器上，这个值可以设置到服务器物理内存的80%。
但是还是建议不要设的太大，以免造成不必要的内存调度

(4)innodb_flush_log_at_trx_commit

这个参数是用来控制缓冲区中的数据写入到日志文件，以及日志文件数据刷新到磁盘的操作时机。

这个参数的设置可以对数据库在性能和数据安全之间进行折中。


a) 当这个参数为0的时候，日志缓冲每秒一次地被写到日志文件，并且对日志文件做向磁盘刷新的操作，但是在一个事务提交不做任何操作。

b) 当这个参数为1的时候，在每个事务提交时，日志缓冲被写到日志文件，并且对日志文件做磁盘刷新的操作。

c) 当这个参数是2的时候，在每个事务提交时，日志缓冲被写到日志文件，但不对日志文件做向磁盘刷新的操作，对日志文件每秒向磁盘做一次刷新操作。

该参数默认值为1，也是最安全的设置

(5)innodb_additional_mem_pool_size

这个参数是InnoDB存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小，默认值为1MB。

应用程序里的表越多，则需要在这里分配越多的内存。

如果InnoDB用光了这个池内的内存，则InnoDB开始从操作系统分配内存，并且往MySQL错误日志写警告。

其实没有必要给这个缓冲池分配非常大的空间，相对稳定即可

(6)innodb_lock_wait_timeout

MySQL可以自动地监测行锁导致的死锁并进行相应的处理，

但是对于表锁导致的死锁不能自动的检测，

所以该参数主要用于在出现这类情况的时候等待指定的时间后回滚。

系统默认值是50秒，用户可以根据应用的需要进行调整。

(7)innodb_support_xa

通过该参数设置是否支持分布式事务，默认值是1，表示支持分布式事务。

若确认应用不需要分布式事务，可以关闭这个参数，减少磁盘读写的次数并获得更高的性能。

(8)innodb_log_buffer_size

该参数是日志缓存的大小。

默认的设置在中等强度写入负载以及较短事务的情况下，一般都可满足服务器的性能要求。

如果存在更新操作峰值或者负载较大，就应该考虑加大它的值。

如果它设置的太高，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。

通常设置为8-16MB，默认为1MB

(9)innodb_log_file_size

该参数含义是一个日志组中每个日志文件的大小。

此参数在高写入负载尤其是大数据集的情况下很重要。

这个值越大则性能相对越高，但是副作用是当系统灾难时的回复时间会加大，系统默认值是5MB