mysql 整理之mysql 参数关注与优化

9.1innodb读写参数

（1）读取参数，globalbuffer pool以及localbuffer

Globalbuffer：

Innodb_buffer_pool_size

innodb_log_buffer_size

innodb_additional_mem_pool_size

localbuffer(下面的都是server层的session变量，不是innodb的)：

Read_buffer_size

Join_buffer_size

Sort_buffer_size

Key_buffer_size

Binlog_cache_size

（2）写入参数

insert_buffer_size innodb_buffer_pool_size

如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb_additional_pool_size 
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。

innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size 
默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit  （这个很管用） 
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

 

9.2     mysql有哪些global内存参数，有哪些local内存参数。

Global:

innodb_buffer_pool_size/innodb_additional_mem_pool_size/innodb_log_buffer_size/key_buffer_size/query_cache_size/table_open_cache/table_definition_cache/thread_cache_size

Local:

read_buffer_size/read_rnd_buffer_size/sort_buffer_size/join_buffer_size/binlog_cache_size/tmp_table_size/thread_stack/bulk_insert_buffer_size

sync_binlog设置为1，保证binlog的安全性。

innodb_flush_log_at_trx_commit：

0：事务提交时不将redo log buffer写入磁盘(仅每秒进行master thread刷新，安全性最差，性能最好)

1：事务提交时将redo log buffer写入磁盘(安全性最好，性能最差，推荐生产使用)

2：事务提交时仅将redo log buffer写入操作系统缓存(安全性和性能都居中，当mysql宕机但是操作系统不宕机则不丢数据，如果操作系统宕机，最多丢一秒数据)

innodb_io_capacity/innodb_io_capacity_max：看磁盘的性能来定。如果是HDD可以设置为200-几百不等。如果是SSD，推荐为4000左右。innodb_io_capacity_max更大一些。

innodb_flush_method设置为O_DIRECT。

（3） 读取的话，那几个全局的pool的值的设置，以及几个local的buffer的设置。

 

Global:

innodb_buffer_pool_size:设置为可用内存的50%-60%左右，如果不够，再慢慢上调。

innodb_additional_mem_pool_size：采用默认值8M即可。

innodb_log_buffer_size:默认值8M即可。

key_buffer_size:myisam表需要的buffer size，选择基本都用innodb，所以采用默认的8M即可。

9.3mysql 参数优化

（1）Mysql层面：

1. innodb_flush_log_at_trx_commit 设置为2

设置0是事务log（ib_logfile0、ib_logfile1）每秒写入到logbuffer,1是时时写，2是先写文件系统的缓存，每秒再刷进磁盘，和0的区别是选2即使mysql崩溃也不会丢数据。

 

2. innodb_write_io_threads=16 (该参数需要在配置文件中添加，重启mysql实例起效)

脏页写的线程数，加大该参数可以提升写入性能.mysql5.5以上才有。

 

3. innodb_max_dirty_pages_pct 最大脏页百分数

当系统中 脏页 所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。默认75，一般现在流行的SSD硬盘很难达到这个比例。可依据实际情况在75-80之间调节

 

4. innodb_io_capacity=5000

从缓冲区刷新脏页时，一次刷新脏页的数量。根据磁盘IOPS的能力一般建议设置如下：

 

SAS 200

SSD 5000

PCI-E 10000-50000

 

5. innodb_flush_method=O_DIRECT(该参数需要重启mysql实例起效)

控制innodb数据文件和redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

6. innodb_adaptive_flushing  设置为 ON （使刷新脏页更智能）

影响每秒刷新脏页的数目。规则由原来的“大于innodb_max_dirty_pages_pct时刷新100个脏页到磁盘”变为 “通过buf_flush_get_desired_flush_reate函数判断重做日志产生速度确定需要刷新脏页的最合适数目”；即使脏页比例小于innodb_max_dirty_pages_pct时也会刷新一定量的脏页。

 

7. innodb_adaptive_flushing_method  设置为 keep_average  

影响checkpoint，更平均的计算调整刷脏页的速度，进行必要的flush.（该变量为mysql衍生版本PerconaServer下的一个变量，原生mysql不存在）

 

8. innodb_stats_on_metadata=OFF  

关掉一些访问information_schema库下表而产生的索引统计。

 

当重启mysql实例后，mysql会随机的io取数据遍历所有的表来取样来统计数据，这个实际使用中用的不多，建议关闭.

 

9. innodb_change_buffering=all 

当更新/插入的非聚集索引的数据所对应的页不在内存中时（对非聚集索引的更新操作通常会带来随机IO），会将其放到一个insertbuffer中，当随后页面被读到内存中时，会将这些变化的记录merge到页中。当服务器比较空闲时，后台线程也会做merge操作。

 

由于主要用到merge的优势来降低io，但对于一些场景并不会对固定的数据进行多次修改，此处则并不需要把更新/插入操作开启change_buffering，如果开启只是多余占用了buffer_pool的空间和处理能力。这个参数要依据实际业务环境来配置。

 

10. innodb_old_blocks_time=1000

使Block在old sublist中停留时间长为1s，不会被转移到new sublist中,避免了BufferPool被污染BP可以被认为是一条长链表。被分成young和 old两个部分，其中old默认占37%的大小（由innodb_old_blocks_pct配置）。靠近顶端的Page表示最近被访问。靠近尾端的Page表示长时间未被访问。而这两个部分的交汇处成为midpoint。每当有新的Page需要加载到BP时，该page都会被插入到midpoint的位置，并声明为old-page。当old部分的page，被访问到时，该page会被提升到链表的顶端，标识为young。

 

由于table scan的操作是先load page，然后立即触发一次访问。所以当innodb_old_blocks_time=0 时，会导致tablescan所需要的page不读的作为youngpage被添加到链表顶端。而一些使用较为不频繁的page就会被挤出BP，使得之后的SQL会产生磁盘IO，从而导致响应速度变慢。

 

这时虽然mysqldump访问的page会不断加载在LRU顶端，但是高频度的热点数据访问会以更快的速度把page再次抢占到LRU顶端。从而导致mysqldump加载入的page会被迅速刷下，并立即被evict（淘汰）。因此，time=0或1000对这种压力环境下的访问不会造成很大影响，因为dump的数据根本抢占不过热点数据。不只dump，当大数据操作的时候也是如此。

（2）系统层面：

1. 关闭 numa=off, 或修改策略为interleave(交织分配内存)防止意外的swap 

numa策略引入了node的概念，每个物理CPU都被视为一个node，而每个node都有一个localmemory，相对这个node之外的其它node都属于外部访问。

 

NUMA的内存分配策略有localalloc（默认）、preferred、membind、interleave。

 

localalloc规定进程从当前node上请求分配内存；

preferred比较宽松地指定了一个推荐的node来获取内存，如果被推荐的node上没有足够内存，进程可以尝试别的node。

membind可以指定若干个node，进程只能从这些指定的node上请求分配内存。

interleave规定进程从指定的若干个node上以Round-roll算法交织地请求分配内存。

每个进程（或线程）都会分配一个优先node,对于系统默认的localalloc策略会有一个问题，对于mysql这种几乎占满整个系统内存的应用来说，很容就把某个node的资源给占满，若Linux又把一个大的资源分配到这个已经占满资源的node时，会资源不足，造成内存数据于磁盘进行交换，或者摒弃buffer_pool里的活跃数据。在实际测试中发现比如有node0、node1 两个物理node，当系统负载很高的时候，node0资源被占满，node1虽然仍有部分空闲内存，但是系统即使进行内存到磁盘交换也不会去利用node1上的空闲资源。

 

因此建议对于像mysql这样的单实例的庞大复杂的进程来说，关闭numa或者设置策略为交织分配内存更合理。但对于一个机器上有多少个实例，可以每个实例绑定一个CPU核上。然后就可以充分利用numa的特性，更高效。

 

2. 增加本地端口，以应对大量连接

echo ‘1024 65000′ >/proc/sys/net/ipv4/ip_local_port_range

 

该参数指定端口的分配范围，该端口是向外访问的限制。mysql默认监听的3306端口即使有多个请求链接，也不会有影响。但是由于mysql是属于高内存、高cpu、高io应用，不建议把多少应用于mysql混搭在同一台机器上。即使业务量不大，也可以通过降低单台机器的配置，多台机器共存来实现更好。

 

3. 增加队列的链接数

echo ‘1048576’ >/proc/sys/net/ipv4/tcp_max_syn_backlog

 

建立链接的队列的数越大越好，但是从另一个角度想，实际环境中应该使用连接池更合适，避免重复建立链接造成的性能消耗。使用连接池，链接数会从应用层面更可控些。

 

4. 设置链接超时时间

echo ’10’ >/proc/sys/net/ipv4/tcp_fin_timeout

 

该参数主要为了降低TIME_WAIT占用的资源时长。尤其针对http短链接的服务端或者mysql不采用连接池效果比较明显。

Local:

join_buffer_size：当sql有BNL和BKA的时候，需要用的buffer_size(plain index scans,range index scans的时候可能也会用到)。默认为256k，建议设置为16M-32M。

read_rnd_buffer_size：当使用mrr时，用到的buffer。默认为256k，建议设置为16-32M。

read_buffer_size:当顺序扫描一个myisam表，需要用到这个buffer。或者用来决定memory table的大小。或者所有的engine类型做如下操作：order by的时候用temporary file、SELECT INTO …OUTFILE 'filename' 、For caching results ofnested queries。默认为128K，建议为16M。

sort_buffer_size：sql语句用来进行sort操作(order by,group by)的buffer。如果buffer不够，则需要建立temporary file。如果在show global status中发现有大量的Sort_merge_passes值，则需要考虑调大sort_buffer_size。默认为256k，建议设置为16-32M。

binlog_cache_size：表示每个session中存放transaction的binlog的cachesize。默认32K。一般使用默认值即可。如果有大事务，可以考虑调大。

thread_stack：每个进程都需要有，默认为256K，使用默认值即可。

（4） 还有就是著名的query cache了，以及query cache的适用场景了，这里有一个陷阱，就是高并发的情况下，比如双十一的时候，query cache开还是不开，开了怎么保证高并发，不开又有何别的考虑？

 

建议关闭，上了性能反而更差。

innodb_double_write

innodb_write_io_thread

innodb_flush_method

（3）与IO相关的参数

Sync_binlog

Innodb_flush_log_at_trx_commit

Innodb_lru_scan_depth

Innodb_io_capacity

Innodb_io_capacity_max

innodb_log_buffer_size

innodb_max_dirty_pages_pct

（4）缓存参数以及缓存的适用场景

指的是查询缓存吗？？？使用于读多写少，如分析报表等等

query_cache_size

query_cache_type

query_cache_limit

maximumquery_cache_size