标签： mysql

• innodb_buffer_pool

• innodb_additional_mem_pool_size

• innodb_log_buffer_size

这3个内存参数是可见的，可控的

还有一些是看不见，不可控的靠mysql内部管理的：

• adaptive index hash

• system dictionary hash

• locking system

• sync_array

• os_events

Innodb内存结构

innodb在5.6以前undo 是放在共享表空间里面的

在高并发的时候，可能会导致ibdata1文件持续增长

ibdata1这个共享表空间又是不能自动释放的 

如果在高并发一直没有提交有大量事务等待回滚的时候会把大量脏数据放在undo里面

所以会导致ibdata1特别庞大 特别是32位的系统下

需要我们及时提交事务 不要把大量事务堆积在innodb redo

或者在初始化的时候把ibdata1初始化大一些 最好在1G以上

5.6可以有独立undo 但是很麻烦 不建议使用

5.7可以设置undo大小和路径、

1.innodb_buffer_pool

• Innodb高速缓冲（简称IBP），是Innodb最重要的组成部分

• InnoDB不依赖OS,而自己缓存了所有数据，把索引和数据（包括索引数据，行数据，等等）放在内存里面。这点跟MyISAM有差别MyISAM只是把索引放在key buffer里面，数据还是用OS的page cache进行缓存

• 应该把innodb_buffer_pool_size设置得大一些，建议设置为可用RAM的50%~80%

• 如果前端使用连接池 那么设置能70%会相对安全一点，因为前端要有连接池 就不会有特别大的连接连接进来，PGA分配的内存相对就没那么大，所以我们可以留更多的内存给SGA

• 但是前端如果有大量连接并发的时候，我们建议innodb_buffer_pool_size设置小一点 50%

• 查询或更新的时候会对IBP加锁，影响并发，innodb会把内存分成多分，来缓解并发带来的影响

• IBP有一块buffer用于插入缓冲，在插入的时候，先写入内存，之后再合并后顺序写入磁盘。在合并到磁盘上的时候，会引发较大的IO操作，对实时操作造成影响（看上去是抖动，tps变低）

• show global status like 'innodb_buffer_pool_%' 查看IBP状态们单位是page(16kb)

• Innodb_buffer_pool_wait_free 如果较大，需要加大IBP设置

2. innodb_buffer_pool_instances 

        如果内存不超过8G，就没有必要分多个instance

        分多个instance时，最好每个instance至少2G以上8G以下

        innodb_buffer_pool_instances也不要太多，一般不超过8

innodb_buffer_pool 现在默认值是128M（老版本默认8M，坑很多）

[MySQL FAQ]系列 — 数据不算大，备份却非常慢

http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html

3.Adaptive Hash Index

自适应哈希索引，用来管理buffer pool的哈希索引

• adaotive index hash, size= innodb_buffer_pool / 64, 随着buffer的频繁更新，会随之上升

• system dictionary hash, size=innodb_buffer_size / 256，基本固定

• memory for sync_array, which is used for syncronization primitives, size=OS_THREADS（当前线程数） * 152

• memory for os_events, which are also used for syncronization primitives, size=OS_THREADS * 216

• memory for locking system, size = 5*4*NBLOCKS(NBLOCK, innodb buffer pool的block数量)，随着并发、行锁增加，会随之上升

概念：

• OS_THREADS=如果innodb_buffer_pool_size >= 1000Mb, 则为:50000, 否则如果innodb_buffer_pool_size >= 8Mb,则为:10000, 否则为:1000（*nixes平台下通用）

• NBLOCKS = innodb_buffer_pool_size/8192

如果innodb_buffer_pool > 1000MB, OS_THREADS*368=17.5MB

否则如果innodb_buffer_pool > 8MB , 3,5m

我们只能设置innodb_buffer_pool_size为一个合适值 因为其他几个我们没有办法控制

4.innodb_additional_mem_pool_size

数据字典以及内部数据结构缓存，表数据量越多，相应的内容需要越大。

默认8M，通常设置为8~32M足够，一般建议设置为16M，如果确实不够，那么会从系统中请求增加分配内存，并且错误日志中会提醒，目前至少还未发生过。

5.innodb_log_buffer_size

show global status查看Innodb_log_waits是否大于0， 是的话，就需要提高innodb_buffer_pool_size，否则维持原样。

show global status查看30~60秒钟Innodb_os_log_written 的间隔差异值，即可计算出innodb_buffer_pool_size设置多大合适。

每个客户端连接时，用以维持连接缓冲以及读取结果缓冲。

初试分配预设值，在有需要时，则会自动扩大到max_allowed_packet大小，然后再回收到预设的net_buffer_length大小。

最小1K， 最大1M，默认是16K。

mysqldump时，没个extended insert 的行长度，不能超过net_buffer_length的值

专用于MyISAM引擎

用一个特别的类似树型结构体缓存

用于提高INSERT…SELECT, INSERT…VALUES(…),(…)以及LOAD DATA写数据到非空表的情景

每个线程分配，默认8M，可以设置为0来关闭这个buffer

从MySQL5.1开始，数据表文件描述符呗分开为数据文件及数据表定义文件两部分。

表定义文件缓存可以放在专属的table_definition_cache中。

表定义文件缓存比表文件描述符缓存消耗的内存更小，其默认值是400。

状态值：

Open_table_definition:表定义文件.frm被缓存的数量

Opened_table_definition:历史上总共被缓存过的.frm文件数量。

.frm文件只能同时打开一次

.MYD .MYI文件，则是没个线程都打开一次

innodb也是一样

table_cache就是缓存文件打开的描述符 使得我们打开数据文件更快

几个关于table_cache的状态值

Open_tables:当前打开的表的数量

Opened_tables:历史上全部已经打开的表总数，如果Open_tables较大，table_open_cache值可能需要加大

在短连接的应用Thread_Cache的功效非常明显，因为在应用中数据库的连接和创建是非常频繁的，如果不使用Thread_Cache那么消耗的资源是非常可观的！

在长连接中虽然带来的改善没有短连接的那么明显，但是好处是显而易见的。

当一个连接处理完之后 不是立即销毁 而是把连接cache住

当有新连接连接时 用cache住的连接来处理

主要用来处理短连接请求

MySQL 企业版开始支持thread pool， MariaDB和Percona也免费支持

当连接数满了 管理员都连不上去 这个时候可以用extra_port功能

用这个端口进行处理一些事情

日常使用和监控还是继续使用正常的业务端口，但管理员救急时用extra_port连接mysqld 把连接释放出来

需要有对应的授权才可以

一般管理员都是本地管理 （root@localhost）

因为extra_port是个额外的端口不能采用socket的方式连接进去

只能使用TCP的方式连接

那么这个时候就需要新增一个授权(root@本机IP)

Thread Cache命中率： Thread_Cache_Hit=(Connections-Thread_created)/Connections*100%

binlog_cache_size 默认值是32k 一般设置成1,2M就够了 一般sql也没这么大

在事务过程中容纳二进制日志SQL 语句的缓存大小

二进制日志缓存是服务器支持事物存储引擎并且服务器启用了二进制日志（–log-bin选项）的前提下为没个客户端分配的内存

如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能

事物表 在事物没有结束之前 需要把数据放在cache里面 然后整个事物完成之后 

我们再把整个事物相关的sql文件写会文件 这些sql我们就把他放在binlog_cache里面

主要是给事物引擎产生的sql 作为cache 提高sql写入的效率

当然，我们可以通过MySQL的一下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use

binlog hit ratio = (Binlog_cache_use)/(Binlog_cache_use + Binlog_cache_disk_use)

当然我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误

比如，我们开启binlog时，执行load data infile，将一个大文件导入时可能会出现这个错误提示 需要注意一下

临时执行大文件的话我们可以这样

set sql_log_bin=0;

load data infile…

然后再slave 再次执行load data

一般大数据批量导入的时候（维护的时候，不是工作时间） 是把binlog关掉然后再master上导一下 在slave上导一下

tmp_table_size 是MySql的临时表缓冲大小

大多数临时表是基于内存的（HEAP）表

但是，具有大的记录长度的临时表（所有列的长度的和）或包含BLOB列的表存储在硬盘上

如果某个内部heap（堆积）表大小超过tmp_table_size， MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表

如果执行计划中显示有Using temporary，那么就意味着会用到临时表了

这个变量定义了用户可以创建的内存表(memory table)的大小

这个值用来计算内存表的最大行数

ex:已有一个memory表，总数据占用内存10M，如果动态将max_heap_table_size改成1M

这个变量支持动态改变，即set @max_heap_table_size=#，但是对于已经存在的内存表就咩有什么影响

除非这个表被重新创建（create table）或者修改（alert table）或者truncate table

服务器重启也会设置已经存在的内存表为全局max_heap_table_size的值（配置文件里的值）

这个变量和tmp_table_size一起限制了内部内存表的大小