MySQL 内存相关参数设置

read_buffer_size = 16M
- MyISAM 表顺序扫描的缓存大小，如果要经常顺序扫描 MyISAM 表，可以通过增大 read_buffer_size 的值来改善性能，但是这个变量是每会话独占，如果设置太大，会造成内存浪费，甚至物理内存耗尽。
  - 在以下场景中适用于所有的存储引擎
  - 此选项也在以下场景中用于所有存储引擎：
    * 控制 ORDER BY 行排序时的临时文件（不是临时表，而是排序
    
    的文件）中缓存索引的大小
    
    * 控制批量插入到分区中的数据大小
    
    * 控制缓存嵌套查询的结果集大小
  - memory 存储引擎中该参数的值决定 MEMORY 表的内存块大小，注意是块大小，而不是 memory 表可以使用的内存总大小，memory 引擎表的数据总大小限由max_heap_table_size 参数控制。
  - 全局，会话变量，默认为 128K，最大为 2G，整型值。注意：设置值必须为 4K 的倍数，否则会截取你设定值的最接近 4K 的倍数的值作为该参数的值。取值范围为：8200~2147479552 字节。
  - PS：为啥这个变量官方描述的是用于 MyISAM，而在某些场景也适用于所有存储引擎呢？因为在这些场景下，mysql 内部保存这些临时数据仍然使用的是 MyISAM 表，在 mysql 5.7.x 的版本中，这些内部临时表可以设置使用 InnoDB 存储引擎，如果临时表设置为 InnoDB 存储引擎，则这个参数的大小就无需多关注了（除非你使用 MyISAM 引擎和 memory 引擎）。
read_rnd_buffer_size = 32M
- MyISAM 表排序缓存的缓存大小（注意，是 key-sorting 索引排序），如果需要对 MyISAM 表做排序，可以增大 read_rnd_buffer_size 改善性能，这个变量同样的每会话独占，也不能设置太大。
  - 该变量控制的 buffer 在从任何存储引擎读取数据的时候，都会用于缓存读取的数据，包括 MRR 优化特性也会使用。
  - 全局，会话变量，动态变量，默认值为 256K，最大值为 2G，整型值。取值范围 1~2147483647 字节（2G）。
sort_buffer_size = 32M
- 用于存放排序数据的缓存大小，超过这个大小就会使用文件排序。如果通过 show global status 看到 Sort_merge_passes 的值很大，可以考虑通过适当调整 sort_buffer_size 的值来增大排序缓存区（另外增加max_sort_length 参数的值时可能也需要增加 sort_buffer_size 参数的值），改善带有 order by 和 group by 子句的 sql 性能。sort_buffer_size 是每线程独占分配（每个执行排序操作的会话才会分配），不要设置过大，最好是设置一个较小的全局值，如果碰到较大表做排序，就对这个 session 设置较大的值。
  - 该参数适通用于所有存储引擎，最小值必须要足够大，至少要在排序缓冲中能够存放 15 个排序元组。
  - 5.6.4 之前的版本总是在有排序操作时直接分配整个排序缓冲给会话，从 5.6.4 版本开始优化器尝试计算排序数据的大小来分配排序缓冲的大小给会话。
  - 在 linux 上，大于 256K 或者 2M 的排序缓冲大小可能显著降低内存分配的性能。
  - 全局变量，会话变量，动态变量，默认值在不同版本中频繁变化，大于 5.6.4 版本号的 64 位版本默认值为 256K，整型值。64位平台取值范围为：32768~18446744073709551615 字节。
tmp_table_size = 64M
- 内存临时表超过这个大小后，就会把内存临时表转换为磁盘临时表，线程级别的参数，不要设置过大，一般全局设置不大于 100M 就可以了，否则很容易发生内存溢出，但是，如果说在临时大数据查询的时候，可以打开一个会话临时设置大一点，避免产生临时磁盘表。或者，可以使用索引查询尽量减少返回的数据量。
  - 内部用于控制内存临时表大小使用，是适用于用户创建的内存表和临时表。
  - 实际限制从 tmp_table_size 和 max_heap_table_size 两个变量的的值中取较小值。如果内存中临时表超过这个限制，MySQL会自动将其转换为磁盘上的 MyISAM 表。如果您执行许多高级GROUP BY 查询并且用到大量内存，请增加 tmp_table_size 的值（如果必要，也请增加 max_heap_table_size 的值）。
  - 状态变量 Created_tmp_disk_tables 持续增加时，需要增加tmp_table_size 的值。
  - Created_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100% > 10%的话，就需要注意适当提高tmp_table_size 的大小，但是不能设置太大，因为它是每个session 都会分配的，可能会导致 OOM(out of memory）。
  - 全局变量，会话变量，动态变量，默认值为 16M，整型值。取值范围为：1024~18446744073709551615 字节。
max_heap_table_size=64M
- 默认是 16M，可以根据需要加大，在定义内存表时，可以使用max_rows 子句指定表的最大行数来约定内存表的数据量。该参数是用于控制用户创建的内存表的数据大小。
  - 设置此变量不会影响任何现有的 MEMORY 表，除非使用如CREATE TABLE 或使用 ALTER TABLE 或 TRUNCATE TABLE 语句重新创建表、修改表结构或清空表数据。服务器重新启动也会将现有 MEMORY 表的数据最大大小限制设置为全局max_heap_table_size 值。
  - 此变量也与 tmp_table_size 结合使用以限制内部内存表的大小。如果设置与 tmp_table_size 大小不一样，则控制内部内存临时表以较小的为准。
  - 全局变量，会话变量，动态变量，64 位版本默认值为 16M，整型值。取值范围为：16384~1844674407370954752 字节。
join_buffer_size = 32M
- 用于存放 join 查询中间结果的缓存大小。对于无法通过索引进行联结操作的查询，可以通过适当增大 join_buffer_size 的值来改善联结查询性能（但最好是想办法让 join 使用到索引来提高性能）。join_buffer_size 都是每线程独占分配，不要设置过大（除非能够使用BKA 特性，当使用 BKA 时，join_buffer_size 的值的大小决定了向存储引擎的每个请求中包含的键值对的多少，缓冲区越大，联结操作的右表即被驱动表的顺序 I/O 访问就越多，BKA 的作用就是把随机 I/O 访问变为顺序 I/O 访问，这可以显着提高性能），最好是设置一个较小的全局值，如果碰到较大表做联结查询，或者是比较复杂的联结表查询，就对这个 session 设置较大的值。
  - 对于简单查询的索引扫描、索引范围扫描以及因为不能使用到索引做全表扫描的 join 查询时，无论返回的数据多大，都会分配该参数的最小值 128 字节那么多 join buffer 做查询。
  - 对于简单的两个表之间的查询分配一个 join buffer，但是对于复杂的多表 join 查询且不能使用索引的时候，可能会分配多个 join buffer。
  - 全局变量，会话变量，动态变量，默认值在不同版本中频繁变化，大于 5.6.6 版本号的 64 位版本默认值为 256K，最小值为128 字节，整型值。取值范围为：128~18446744073709547520 字节。
thread_cache_size = 64
- 为加快数据库连接的速度，mysql 会缓存一定数量的客户端服务线程以备重用，通过这个参数可以控制 mysql 缓存客户端服务线程的数量，可以通过计算线程 cache 的失效率：Threads_created/Connections 状态变量比值来衡量 thread_cache_size 参数的设置是否合理，该值越接近 1，说明线程 cache 的命中率越低，就应该考虑增加这个参数的值。
  - 当一个客户端访问完成断开连接时，如果线程缓存中的客户端连接线程没有达到 thread_cache_size 定义的值，则这个客户端线程会被 put 到缓存中，当另外一个客户端新建连接时，如果线程缓存不为空（即有客户端线程缓存在线程缓存中），就会从缓存中取出这个客户端连接进行重用。在大多数情况下，如果你的并发连接不高的时候，这个值对性能的影响可能就看不出来，但是当有大量并发连接时，通过增大这个值可以缓解高并发连接的压力。
  - 默认值为-1，表示自动计算（计算公式：8 + (max_connections/ 100)），全局变量，动态变量，整型值。固定值取值范围为：0~16384
  - PS：这个变量对嵌入式服务器（libmysqld）没有任何影响，而在 MySQL 5.7.2 在嵌入式服务器中移除了这个变量。

参数：

存储引擎/共享
日志缓冲区，缓冲区池

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size

服务器/共享
查询调整缓存
线程高速络缓存

query_cache
table_cahce
table_definition_cache

连接/会话
排序缓冲区，读取缓冲区，临时表

binlog_cache_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
sort_buffer_size
tmp_table_size
thread_cache_size
bulk_insert_buffer_size
net_buffer_length
thread_stack

下面转载自：http://www.bitscn.com/pdb/mysql/201405/227583.html

*.线程独享内存
*.全局共享内存
全局共享内存类似ORACLE的系统全局区SGA，线程独享内存类似ORACLE的进程全局区PGA

一、线程独享内存

在MySQL中，线程独享内存主要用于各客户端连接线程存储各种操作的独享数据，如线程栈信息，分组排序操作，数据读写缓冲，结果集暂存等等，而且大多数可以通过相关参数来控制内存的使用量。

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

* 排序使用内存(sort_buffer_size)：
MySQL 用此内存区域进行排序操作(filesort)，完成客户端的排序请求。当我们设置的排序区缓存大小无法满足排序实际所需内存的时候，MySQL会将数据写入磁盘文件来完成排序。由于磁盘和内存的读写性能完全不在一个数量级，
所以sort_buffer_size参数对排序操作的性能影响绝对不可小视。排序操作的实现原理请参考：MySQL Order By的实现分析。
什么时候会用到？
对结果集排序时
使用确认：
可以通过查询计划中的Extra列的值为Using file-sort来证实使用了和这个缓冲区。
>explain select * from user1;
Global Session,Dynamic,Default 2M(32bit), 2M(32bit),
推荐配置：8M（内存足够的情况下），默认（内存紧张的情况）
优化建议：一种说法是增大可以提高order by,group by性能，防止数据写入磁盘占用IO资源，还有一种说法是不推荐增加这个缓冲区的大小，理由是当值太大时可能会降低查询的执行速度。目前我没有实验证实。

* Join操作使用内存(join_buffer_size)：
应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些Join需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到Join Buffer来协助完成Join操作
（具体Join实现算法请参考：MySQL中的 Join 基本实现原理）。当Join Buffer太小，MySQL 不会将该Buffer存入磁盘文件，而是先将Join Buffer中的结果集与需要Join的表进行Join操作，然后清空Join Buffer中的数据，
继续将剩余的结果集写入此Buffer中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加IO访问，降低效率。
什么时候会用到？
当查询必须连接两个表（或多个）的数据集并且不能使用索引时，这个缓冲区会被用到。这个缓冲区专门为每个线程的无索引链接操作准备的。
使用确认：
可以通过查询计划中的Extra列的值为Using join bufer来证实使用了和这个缓冲区。

>explain select * from user1;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user1 | index | NULL | name | 78 | NULL | 3 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

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所设置的缓冲区中，
等满足一定大小的时候才开始向客户端发送，以提高网络传输效率。不过net_buffer_lenth参数所设置的仅仅只是该缓存区的初始化大小，MySQL会根据实际需要自行申请更多的内存以满足需求，
但最大不会超过 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可能需要使用到临时表。当我们的临时表较小（小于tmp_table_size 参数所设置的大小）的时候，MySQL会将临时表创建成内存临时表，
只有当tmp_table_size所设置的大小无法装下整个临时表的时候，MySQL才会将该表创建成MyISAM存储引擎的表存放在磁盘上。不过，当另一个系统参数 max_heap_table_size 的大小还小于 tmp_table_size 的时候，
MySQL将使用 max_heap_table_size 参数所设置大小作为最大的内存临时表大小，而忽略tmp_table_size 所设置的值。而且 tmp_table_size 参数从 MySQL 5.1.2 才开始有，之前一直使用 max_heap_table_size。
谁小谁生效.另外还有一个参数max_tmp_tables,没有使用
tmp_table_size
Global Session,Dynamic,Default 16M
推荐配置：64M
max_heap_table_size
Global Session,Dynamic,Default 8M
This variable sets the maximum size to which user-created MEMORY tables are permitted to grow
这个变量定义了MEMORY存储引擎表的最大容量。
This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. See
这个变量也与tmp_table_size一起使用限制内部内存表的大小。请见
http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
推荐配置：64M
主要根据业务以及服务器内存来调整，如果有需要到则可以调整到。GB居然使用2G的配置，汗

目前没有一个简便的方式来确定内部临时表的总容量。可以通过MySQL状态变量created_tmp_tables和created_tmp_disk_tables来确定创建了临时表和基于磁盘的临时表

mysql> show global status like 'create%tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
+-------------------------+-------+

5.5中，可以使用PERFORMANCE—SCHEMA来帮助统计基于磁盘的临时表的总大小

补充说明：上面所列举的MySQL线程独享内存仅仅只是所有线程独享内存中的部分，并不是全部，只是这些可能对MySQL的性能产生较大的影响，且可以通过系统参数进行调节。
由于以上内存都是线程独享，极端情况下的内存总体使用量将是所有连接线程的总倍数。所以在设置过程中一定要谨慎，切不可为了提升性能就盲目的增大各参数值，
避免因为内存不够而产生Out Of Memory异常或者是严重的Swap交换反而降低整体性能。

二、全局共享内存

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

* MyISAM索引缓存 Key Buffer（key_buffer_size）：
MyISAM 索引缓存将MyISAM表的索引信息（.MYI文件）缓存在内存中，以提高其访问性能。这个缓存可以说是影响MyISAM存储引擎性能的最重要因素之一了，通过 key_buffere_size 设置可以使用的最大内存空间。
注意：即使运行一个全部采用innodb的模式，仍需要定义一个索引码缓冲区，因为MYSQL元信息与MyISAM定义相同。
Global ,Dynamic,Default 8M
推荐配置：默认 8M
如何确认key_buffer_size不够用？
使用show full proceslist的State列中，值Repairing the keycache是一个明显的指标，它指出当前索引码缓冲区大小不足以执行当前运行的SQL语句。这将导致额外的磁盘I/O开销。

* 查询缓存 Query Cache （query_cache_size）：
http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
http://dev.mysql.com/doc/refman/5.5/en/query-cache-status-and-maintenance.html
查询缓存是MySQL比较独特的一个缓存区域，用来缓存特定Query的结果集（Result Set）信息，且共享给所有客户端。通过对Query语句进行特定的Hash计算之后与结果集对应存放在Query Cache中，以提高完全相同的Query语句的相应速度。
当我们打开MySQL的Query Cache之后，MySQL接收到每一个SELECT类型的Query之后都会首先通过固定的Hash算法得到该Query的Hash值，然后到Query Cache中查找是否有对应的Query Cache。如果有，则直接将Cache的结果集返回给客户端。
如果没有，再进行后续操作，得到对应的结果集之后将该结果集缓存到Query Cache中，再返回给客户端。当任何一个表的数据发生任何变化之后，与该表相关的所有Query Cache全部会失效，所以Query Cache对变更比较频繁的表并不是非常适用，
但对那些变更较少的表是非常合适的，可以极大程度的提高查询效率，如那些静态资源表，配置表等等。为了尽可能高效的利用Query Cache，MySQL针对Query Cache设计了多个query_cache_type值
和两个Query Hint：SQL_CACHE和SQL_NO_CACHE。当query_cache_type设置为0（或者 OFF）的时候不使用Query Cache，当设置为1（或者 ON）的时候，当且仅当Query中使用了SQL_NO_CACHE 的时候MySQL会忽略Query Cache，
当query_cache_type设置为2（或者DEMAND）的时候，当且仅当Query中使用了SQL_CACHE提示之后，MySQL才会针对该Query使用Query Cache。可以通过query_cache_size来设置可以使用的最大内存空间。
Global Dynamic,Default 0
推荐配置：16M
如何确定系统query cache的情况？
show global status like 'Qcache%';或者
select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Qcache%';
公式：
（Qcache_hits/Qcache_hits+Com_select+1)*100来确定查询缓存的有效性

mysql> show variables like 'query_cache_size';
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'Qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 535 |
| Qcache_free_memory | 4885448 |
| Qcache_hits | 1858574835 |
| Qcache_inserts | 1619931831 |
| Qcache_lowmem_prunes | 802889469 |
| Qcache_not_cached | 825000679 |
| Qcache_queries_in_cache | 4411 |
| Qcache_total_blocks | 9554 |
+-------------------------+------------+
8 rows in set (0.00 sec)
mysql> show global status like 'Com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 2445037535 |
+---------------+------------+
1 row in set (0.00 sec)

* 连接线程缓存 Thread Cache（thread_cache_size）：
连接线程是MySQL为了提高创建连接线程的效率，将部分空闲的连接线程保持在一个缓存区以备新进连接请求的时候使用，这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。
当我们通过thread_cache_size设置了连接线程缓存池可以缓存的连接线程的大小之后，可以通过(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% 很高的命中率啊这台之只读的slave

* 表缓存 Table Cache（table_open_cache）：
表缓存区主要用来缓存表文件的文件句柄信息，在 MySQL5.1.3之前的版本通过table_cache参数设置，但从MySQL5.1.3开始改为table_open_cache来设置其大小。当我们的客户端程序提交Query给MySQL的时候，
MySQL需要对Query所涉及到的每一个表都取得一个表文件句柄信息，如果没有Table Cache，那么MySQL就不得不频繁的进行打开关闭文件操作，无疑会对系统性能产生一定的影响，Table Cache 正是为了解决这一问题而产生的。
在有了Table Cache之后，MySQL每次需要获取某个表文件的句柄信息的时候，首先会到Table Cache中查找是否存在空闲状态的表文件句柄。如果有，则取出直接使用，没有的话就只能进行打开文件操作获得文件句柄信息。
在使用完之后，MySQL会将该文件句柄信息再放回Table Cache 池中，以供其他线程使用。注意，这里设置的是可以缓存的表文件句柄信息的数目，而不是内存空间的大小。
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 |
+---------------+-------+
2 rows in set (0.00 sec)

调优参考：

http://blog.zfcms.com/article/282
http://www.kuqin.com/database/20120815/328904.html
两个参数的值。其中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
如果对此参数的把握不是很准，VPS管理百科给出一个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。
在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，

* 表定义信息缓存 Table definition Cache （table_definition_cache）：
表定义信息缓存是从 MySQL5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。当我们的 MySQL 中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。
MySQL 提供了 table_definition_cache 参数给我们设置可以缓存的表的数量。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
Global, Dynamic, Default 400
推荐配置：根据内存配置4G 2048 和Table Cache一样即可

* 二进制日志缓冲区Binlog Cache( binlog_cache_size）：
二进制日志缓冲区主要用来缓存由于各种数据变更操做所产生的 Binary Log 信息。为了提高系统的性能，MySQL 并不是每次都是将二进制日志直接写入 Log File，而是先将信息写入 Binlog Buffer 中，
当满足某些特定的条件（如 sync_binlog参数设置）之后再一次写入 Log File 中。我们可以通过 binlog_cache_size 来设置其可以使用的内存大小，同时通过 max_binlog_cache_size 限制其最大大小
（当单个事务过大的时候 MySQL 会申请更多的内存）。当所需内存大于 max_binlog_cache_size 参数设置的时候，MySQL 会报错：“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”。
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 不仅仅缓存索引数据，还会缓存表的数据，
而且完全按照数据文件中的数据快结构信息来缓存，这一点和 Oracle SGA 中的 database buffer cache 非常类似。所以，InnoDB Buffer Pool 对 InnoDB 存储引擎的性能影响之大就可想而知了。
可以通过 (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

mysql> show variables like '%innodb_buffer%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 268435456 |
+------------------------------+-----------+
2 rows in set (0.00 sec)

通过show global status和show engine innodb status/G的BUFFER POOL AND MEMORY

mysql> show global status like '%innodb_buffer%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_pages_data | 15684 |
| Innodb_buffer_pool_bytes_data | 256966656 |
| Innodb_buffer_pool_pages_dirty | 210 |
| Innodb_buffer_pool_bytes_dirty | 3440640 |
| Innodb_buffer_pool_pages_flushed | 372378403 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_misc | 698 |
| Innodb_buffer_pool_pages_total | 16383 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 691803 |
| Innodb_buffer_pool_read_ahead_evicted | 41350 |
| Innodb_buffer_pool_read_requests | 170965099291 |
| Innodb_buffer_pool_reads | 5392513 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 5825388207 |
+---------------------------------------+--------------+
15 rows in set (0.01 sec)

mysql> show engine innodb status/G
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 4055091
Buffer pool size 16383
Free buffers 1
Database pages 15673
Old database pages 5765
Modified db pages 521
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27497746, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6346456, created 1902566, written 372381712
0.00 reads/s, 0.37 creates/s, 27.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15673, unzip_LRU len: 0
I/O sum[1107]:cur[0], unzip sum[0]:cur[0]

命中率 Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%
170965099291-5392513/170965099291 × 100% = 99.99%

* InnoDB 字典信息缓存 InnoDB Additional Memory Pool（innodb_additional_mem_pool_size）:
InnoDB 字典信息缓存主要用来存放 InnoDB 存储引擎的字典信息以及一些 internal 的共享数据结构信息。所以其大小也与系统中所使用的 InnoDB 存储引擎表的数量有较大关系。不过，如果我们通过 innodb_additional_mem_pool_size 参数所设置的内存大小不够，InnoDB 会自动申请更多的内存，并在 MySQL 的 Error Log 中记录警告信息。
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，按默认u自大连接数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）

posted @ 2021-01-11 17:40 VicLW 阅读(2450) 评论(0) 编辑收藏举报

刷新页面返回顶部

VicLiu

人生近看是悲剧，远看是喜剧。

MySQL 内存相关参数设置

参数：

一、线程独享内存

二、全局共享内存

三、查看统计

公告