Mysql参数调优

innodb_buffer_pool_size

这是mysql/innodb调优中最重要的参数。mysql把磁盘中的数据缓存到内存中以节省访问时间，它使用这个参数来决定它自己可以使用多少内存。在高负载的环境中（如只读服务器），需要尽可能的把这个参数调整到最大来避免使用交换区。推荐值是服务器内存的90%。

在写负载很高的机器上或只负责备份的从服务器上这个参数远没有这么重要，可能设置能合适的值以节省内存。

innodb_flush_log_at_trx_commit

这个设置是用来在I/O性能与轻微数据丢失（1秒）之间取得平衡的一个参数。当参数设置为1时，mysql会立刻写入数据更改到硬盘中的InnoDB 日志文件。然而每次事务都写入日志到硬盘是很慢的，也会使用珍贵的IOPS（普通硬盘为200 IOPS）。

在主库上，这个参数应该始终设置为1，设置为其它值是有可能数据丢失。

在从库上，这个参数可以设置为2，mysql会立即写入数据变更到InnoDB日志文件，但允许系统缓存写入数据到内存中，每台进程每秒运行一次调用fsync写入数据到硬盘。这样从服务器上最多丢失一秒的数据。但这样换来的是从服务器上节省了很多的IOPS来处理数据查询。

innodb_flush_method 

这个参数有许多可选项，我们只需简单的设置为O_DIRECT即可。告诉系统文件系统无需缓存任何Mysql数据库的数据，因为

innodb_buffer_pool_size已经做了缓存的工作。如果不设置为O_DIRECT，会缓存两份数据，造成内存浪费。

sync_binlog 

此参数用来记录非SELECT查询日志。它是MySQL复制中非常重要的一个组合。在主库上，它应该被设置为1，用来空间里记录SQL执行日志，以防止数据丢失及数据库崩溃后没有记录日志造成从服务器上数据不全。

从库上此参数应该设置为0或移除，如果需要记录更新日志可以设置log_slave_updates参数。

innodb_log_file_size

此参数用来做数据存储时的缓冲。由于直接把数据写入mysql数据文件中需要计算过程和随机IO操作，不能立即响应用户，可以先把数据写入日志文件中，随后写入到数据文件。

如果日志文件过小，InnoDB不能有效的组合和排列要写入数据。如果文件过大，数据恢复时则需要很长的时间。

最佳值：数据写入高峰期时一个小时内的写入量。

show global status where variable_name like "%Innodb_os_log_written%"; 
#value: 234825649664
select sleep(60);
show global status where variable_name like "%Innodb_os_log_written%";
#value: 234828058624
	
#234828058624-234825649664=2408960bytes
#2.29MB*60minutes=137.4MB 
#innodb有两个独立的日志文件，每个日志文件大小大概可以设置为 	137.4/2 = 70M

更改参数后，停止mysql服务器。删除 ib_logfile0和ib_logfile1后开启mysql服务器。 

max_connections 

max_connections = pm.max_children * number of application servers

PS: Mysql为每个连接创建一个线程，大概占用192KB的内存，如果最大连接数据为2000时，大概需要400MB的内存。确保在最大连接时有足够的内存。总内存 - 系统所有内存 - innodb_buffer_pool_size > 最大连接所需内存

innodb_io_capacity

告诉innodb硬盘允许最大IOPS是多少, 防止IO阻塞或IO不能有效利用。

PS：IOPS测试，此处假如你的/mysql为你的mysql安装目录

apt-get instal fio
cd /mysql
mkdir /mysql/fio
vi random-rw.fio
[random_rw]
rw=randrw
size=128M（好磁盘可以做更改）
directory=/mysql/fio

fio random-rw.fio

mysql使用的IO类型

1. 连接IO用于InnnoDB 日志文件写入

2. 很多随机IO用于InnoDB 数据文件写入

3. 很多随机IO用于InnoDB 数据文件读取

innodb_read_io_threads 与 innodb_write_io_threads

InnoDB使用后台线程管理数据读取和写入工作。默认设置为4个。应该设置为4 x CPU内核数

query_cache_size

优点：缓存SQL解析、查询数据到内存中

缺点：

1. 由于不能智能的处理过期数据，数据库每次有变更时需要刷新缓存。在数据更改较多是影响性能。

2. 缓存刷新时使用唯一的全局锁，数据变更是影响性能。

PS：数据变更频繁时禁用缓存，把query_cache_size 和 query_cache_type 设置 0.因为InnoDB有自己内部的缓存机制，并且没有此限制。

XtraDB/InnoDB与 MyISAM比较

MyISAM缺点：在写入时锁定整张表，影响其他并发的读取和写入。

转载于:https://my.oschina.net/yeah11/blog/399344