MySQL 内存参数总结

MySQL 内存参数总结

Mysql内存可以分为全局共享内存类似ORACLE的系统全局区SGA，线程独享内存类似ORACLE的进程全局区PGA。

一、线程独享内存
在MySQL中，线程独享内存主要用于各客户端连接线程存储各种操作的独享数据，如线程栈信息，分组排序操作，数据读写缓冲，结果集暂存等等。


* 线程栈信息使用内存(thread_stack)
主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等。
通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。
Global,No Dynamic,Default 192K(32bit), 256K(32bit),
推荐配置：默认


* 排序使用内存(sort_buffer_size)：
MySQL 用此内存区域进行排序操作(filesort)，完成客户端的排序请求。
当我们设置的排序区缓存大小无法满足排序实际所需内存的时候，MySQL会将数据写入磁盘文件来完成排序。
Global Session,Dynamic,Default 2M(32bit), 2M(32bit),
推荐配置：8M（内存足够的情况下），默认（内存紧张的情况）


* Join操作使用内存(join_buffer_size)：
应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些Join需求的时候（all/index join），
为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到Join Buffer来协助完成Join操作。
Global Session,Dynamic,Default 128K 各版本平台最大值不一样
推荐配置：8M（内存足够的情况下），默认（内存紧张的情况）


* 顺序读取数据缓冲区使用内存(read_buffer_size)：
这部分内存主要用于当需要顺序读取数据的时候，如无法使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，
当buffer空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。
Global Session,Dynamic,Default 128K
推荐配置：4M/8M


* 随机读取数据缓冲区使用内存(read_rnd_buffer_size)：
和顺序读取相反，当MySQL进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。如根据索引信息读取表数据，根据排序后的结果集与表进行Join等等。
总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL就需要产生随机读取，进而使用到read_rnd_buffer_size 参数所设置的内存缓冲区。
Global Session,Dynamic,Default 256K
推荐配置：8M


* 连接信息及返回客户端前结果集暂存使用内存(net_buffer_lenth)：
用来存放客户端连接线程的连接信息和返回客户端的结果集。当MySQL开始产生可以返回的结果集，会在通过网络返回给客户端请求线程之前，会先暂存在通过net_buffer_lenth所设置的缓冲区中，
等满足一定大小的时候才开始向客户端发送，以提高网络传输效率。大不会超过 max_allowed_packet 参数大小。
Global Session,Dynamic,Default 16K
推荐配置：默认 16K


* 批量插入暂存使用内存(bulk_insert_buffer_size)：
当我们使用如 insert … values(…),(…),(…)… 的方式进行批量插入的时候，MySQL会先将提交的数据放如一个缓存空间中，当该缓存空间被写满或者提交完所有数据之后，MySQL才会一次性将该缓存空间中的数据写入数据库并清空缓存。
此外，当我们进行LOAD DATA INFILE操作来将文本文件中的数据Load进数据库的时候，同样会使用到此缓冲区。
Global Session,Dynamic,Default 8M
推荐配置：默认 8M


* 临时表使用内存(tmp_table_size)：
当我们进行一些特殊操作如需要使用临时表才能完成的Order By,Group By 等等，MySQL可能需要使用到临时表。
当另一个系统参数 max_heap_table_size 的大小还小于 tmp_table_size 的时候，MySQL将使用 max_heap_table_size 参数所设置大小作为最大的内存临时表大小，而忽略tmp_table_size 所设置的值。
Global Session,Dynamic,Default 16M
推荐配置：64M
目前没有一个简便的方式来确定内部临时表的总容量。可以通过MySQL状态变量created_tmp_tables和created_tmp_disk_tables来确定创建了临时表和基于磁盘的临时表
mysql> show global status like 'create%tables';




二、全局共享内存
全局共享内则主要是MySQL Instance以及底层存储引擎用来暂存各种全局运算及可共享的暂存信息，如
存储查询缓存的 Query Cache，
缓存连接线程的 Thread Cache，
缓存表文件句柄信息的 Table Cache，
缓存二进制日志的 BinLog Buffer，
缓存MyISAM存储引擎索引键的 Key Buffer
存储InnoDB数据和索引的 InnoDB Buffer Pool等等。


* MyISAM索引缓存 Key Buffer（key_buffer_size）：
MyISAM 索引缓存将MyISAM表的索引信息（.MYI文件）缓存在内存中，以提高其访问性能。
Global ,Dynamic,Default 8M
推荐配置：默认 8M


* 查询缓存 Query Cache （query_cache_size）：
查询缓存是MySQL比较独特的一个缓存区域，用来缓存特定Query的结果集（Result Set）信息，且共享给所有客户端。
通过对Query语句进行特定的Hash计算之后与结果集对应存放在Query Cache中，以提高完全相同的Query语句的相应速度。
Global Dynamic,Default 0
推荐配置：16M


* 连接线程缓存 Thread Cache（thread_cache_size）：
连接线程是MySQL为了提高创建连接线程的效率，将部分空闲的连接线程保持在一个缓存区以备新进连接请求的时候使用，这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。
可以通过(Connections - Threads_created) / Connections * 100% 计算出连接线程缓存的命中率。
注意，这里设置的是可以缓存的连接线程的数目，而不是内存空间的大小。
Global,Dynamic,Default 0
推荐配置：8个
如何确定系统Thread Cache的情况？
mysql> show global status like 'Threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 506 |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> show global status like 'connections';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Connections | 16513711 |
+---------------+----------+
1 row in set (0.00 sec)
16513711-506/16513711 * 100% =99.9938%


* 表缓存 Table Cache（table_open_cache）：
表缓存区主要用来缓存表文件的文件句柄信息。如果没有Table Cache，那么MySQL就不得不频繁的进行打开关闭文件操作。
Global,Dynamic,Default 400
推荐配置：根据内存配置4G 2048 大于最大Opened_tables
如何确定系统table_open_cache的情况？
mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 512 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'open%_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 512 |
| Opened_tables | 6841 |
+---------------+-------+
其中Open_tables是当前正在打开表的数量，Opened_tables是所有已经打开表的数量。
如果Open_tables的值已经接近table_cache的值，且Opened_tables还在不断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache的值。对于大多数情况，比较适合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_cache <= 0.95


* 表定义信息缓存 Table definition Cache （table_definition_cache）：
表定义信息缓存是从 MySQL5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。
MySQL 提供了 table_definition_cache 参数给我们设置可以缓存的表的数量。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
Global, Dynamic, Default 400
推荐配置：根据内存配置4G 2048 和Table Cache一样即可


* 二进制日志缓冲区Binlog Cache( binlog_cache_size）：
二进制日志缓冲区主要用来缓存由于各种数据变更操做所产生的 Binary Log 信息。
Global,Dynamic,Default 32K
推荐配置：2M


* InnoDB 日志缓冲区 InnoDB Log Buffer （innodb_log_buffer_size）：
这是 InnoDB 存储引擎的事务日志所使用的缓冲区。
类似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innofb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
注：innodb_flush_log_trx_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：
0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；
1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；
2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。
此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会完全确保非常准确的每秒就一定会发生同步，还取决于进程调度的问题。实际上，InnoDB 能否真正满足此参数所设置值代表的意义正常 Recovery 还是受到了不同 OS 下文件系统以及磁盘本身的限制，可能有些时候在并没有真正完成磁盘同步的情况下也会告诉 mysqld 已经完成了磁盘同步。
Global,Dynamic,Default 8M
推荐配置：8M 默认


* InnoDB 数据和索引缓存 InnoDB Buffer Pool（innodb_buffer_pool_size）：
InnoDB Buffer Pool 对 InnoDB 存储引擎的作用类似于 Key Buffer Cache 对 MyISAM 存储引擎的影响，主要的不同在于 InnoDB Buffer Pool 不仅仅缓存索引数据，还会缓存表的数据。
可以通过 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算得到 InnoDB Buffer Pool 的命中率。
global级别，不可动态变更 Default 128M
设置InnoDB数据和索引内存缓存空间大小
配置方式：配置文件中配置
选择参数：50 - 80 % RAM


* InnoDB 字典信息缓存 InnoDB Additional Memory Pool（innodb_additional_mem_pool_size）:
InnoDB 字典信息缓存主要用来存放 InnoDB 存储引擎的字典信息以及一些 internal 的共享数据结构信息。
global级别，不可动态变更 Default 8M
设置InnoDB存放数据库字典信息的Buffer大小
推荐配置：50M


三、查看统计
1.查看各参数内存配置方式
#全局共享内存 9个变量
show variables like 'innodb_buffer_pool_size'; /* InnoDB 数据和索引缓存（InnoDB Buffer Pool） */
show variables like 'innodb_additional_mem_pool_size'; /* InnoDB 字典信息缓存（InnoDB Additional Memory Pool）*/
show variables like 'innodb_log_buffer_size'; /* InnoDB 日志缓冲区（InnoDB Log Buffer） */
show variables like 'binlog_cache_size'; /* 二进制日志缓冲区（Binlog Buffer）*/
show variables like 'thread_cache_size'; /* 连接线程缓存（Thread Cache）*/
show variables like 'query_cache_size'; /* 查询缓存（Query Cache）*/
show variables like 'table_open_cache'; /* 表缓存（Table Cache） */
show variables like 'table_definition_cache'; /* 表定义信息缓存（Table definition Cache） */
show variables like 'key_buffer_size'; /* MyISAM索引缓存（Key Buffer） */
#最大线程数
show variables like 'max_connections';
#线程独享内存 6个变量
show variables like 'thread_stack'; /* 线线程栈信息使用内存(thread_stack) */
show variables like 'sort_buffer_size'; /* 排序使用内存(sort_buffer_size) */
show variables like 'join_buffer_size'; /* Join操作使用内存(join_buffer_size) */
show variables like 'read_buffer_size'; /* 顺序读取数据缓冲区使用内存(read_buffer_size) */
show variables like 'read_rnd_buffer_size'; /* 随机读取数据缓冲区使用内存(read_rnd_buffer_size) */
show variables like 'tmp_table_size'; /* 临时表使用内存(tmp_table_size) ，我实际计算把tmp_table_size放入全局共享内*/
也可以通过系统变量的方式直接获取
select @@key_buffer_size;
select @@max_connections


2.mysql内存计算公式
mysql使用的内存 = 全局共享内存+最大线程数×线程独享内存
mysql used mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+key_buffer_size
+max_connections*（
thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+tmp_table_size）


SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size+@@innodb_additional_mem_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@query_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;


这个理论最大的内存使用量，在5.5版本中tmp_table_size默认是16M，按默认最大连接数151计算，光线程独享的临时表占据的空间都是2416M，我实际计算把tmp_table_size放入全局共享内
我的计算公式
mysql使用的内存 = 全局共享内存+最大线程数×线程独享内存
mysql used mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+key_buffer_size+tmp_table_size
+max_connections*（
thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size）