MySQL的内存计算

本文将讨论MySQL内存相关的一些选项,包括：

单位都是b，不是kb，即1B=1/(1024*1024*1024)G

• 1）全局的buffer，如innodb_buffer_pool_size；
• 2）线程级的选项，如binlog_cache_size；
• 3）为特定操作分配的缓冲区，如sort_buffer_size。

我们只讨论内存相关的选项，其他一些如innodb_open_files、thread_cache_size、table_definition_cache、table_open_cache这类限制文件描述符合线程数量的选项不在讨论之列

1. MySQL的理论内存上限

最近我们在做的一个项目，需要检查MySQL的理论内存上限，同事在http://www.mysqlcalculator.com/找到一个内存计算公式，如下所示：

key_buffer_size+ query_cache_size+ tmp_table_size+ innodb_buffer_pool_size+ innodb_additional_mem_pool_size+ innodb_log_buffer_size+ max_connections* (sort_buffer_size+ read_buffer_size+ read_rnd_buffer_size+ join_buffer_size+ thread_stack+ binlog_cache_size)

show variables where variable_name in (
'key_buffer_size',
'query_cache_size',
'tmp_table_size',
'innodb_buffer_pool_size',
'innodb_additional_mem_pool_size',
'innodb_log_buffer_size'
);

show variables like 'max_connections';

show variables where variable_name in (
'sort_buffer_size',
'read_buffer_size',
'read_rnd_buffer_size',
'join_buffer_size',
'thread_stack',
'binlog_cache_size'
);

这个公式可以说是错的，因为，这个公式并不能非常准确的描述MySQL的实际内存使用情况，实际内存使用情况远比这个公式要复杂，随着具体的应用场景不同而存在差异。这个公式也可以说是对的，因为，该公式大致估算了一个MySQL实例可能需要的内存，可以作为一个参考值。

那么，在实际应用中，我们怎么判断具体的内存使用情况？根据什么条件来调整这些参数？调整为多少比较合适呢？要回答这些问题，还得从原理讲起。

2. MySQL内存参数的含义

在这一节，我们首先对内存参数进行了一个分类，然后对各个参数的含义进行了详细的讨论。

2.1 内存参数分类

2.1.1 服务器级别

服务器级别的buffer是全局且唯一的，影响所有的连接和查询，需要注意的是，服务器级别的buffer中，大部分是服务器启动的时候分配的，小部分是后来分配的。如query_cache，初始值是0，后续不断增长，直至最大。

• innodb_additional_mem_pool_size # 该参数查不到
• innodb_buffer_pool_size
• innodb_log_buffer_size
• key_buffer_size
• query_cache_size

sql查询语句

show variables where variable_name in (
'innodb_buffer_pool_size',
'innodb_log_buffer_size',
'innodb_additional_mem_pool_size',
'key_buffer_size',
'query_cache_size'
);

2.1.2 线程级别

这些buffer是线程级别的，对于每个线程都会分配，因此，占用的内存情况为max_connection * (thread_options)，线程级别的选项有：

• binlog_cache_size
• binlog_stmt_cache_size
• net_buffer_length
• query_prealloc_size
• thread_stack

sql查询语句

show variables where variable_name in (
'net_buffer_length',
'thread_stack',
'query_prealloc_size',
'binlog_cache_size',
'binlog_stmt_cache_size'
);

2.1.3 为特定操作分配的缓冲区

当服务器执行特殊操作时，根据需要分配缓冲区。因此，很难计算缓冲区的具体大小。好在这些缓冲区都是session级别的，我们可以保持全局较小的取值，如果需要，再修改session级别的缓冲区大小。

a.对每个线程分配一次

• read_rnd_buffer_size
• sort_buffer_size
• myisam_mmap_size
• myisam_sort_buffer_size
• bulk_insert_buffer_size
• preload_buffer_size

sql查询语句

show variables where variable_name in (
'read_rnd_buffer_size',
'myisam_mmap_size',
'myisam_sort_buffer_size',
'bulk_insert_buffer_size',
'preload_buffer_size'
);

b.对每个线程分配多次（可能）

• join_buffer_size
• read_buffer_size
• tmp_table_size

show variables where variable_name in (
'join_buffer_size',
'read_buffer_size',
'tmp_table_size'
);

2.2 内存参数的含义

各个变量的含义比较好掌握，官方参考手册上都有，但是，什么时候应该修改，修改为多少才是合适的值？手册上并没有给出，本文将回答这个问题。

1. query_cache_size 这是MySQL的查询缓存，用以缓冲SQL语句的结果，如果下次有相同的SQL语句，并且，结果还没有invalid掉，则直接返回查询缓存中的结果即可。这是理想情况，实际情况query_cache可能导致激烈的锁竞争，使得性能反而下降，MySQL 5.7已经可以关闭query_cache了。

2. innodb_additional_mem_pool_size # (没有查到mysql5.7_aliyun_RDS) 该缓存用以存放数据字典和内部数据结构的信息，一般情况下，表越多，该选项也应该越大，该选项过小时，Innodb会在错误日志中打印错误信息，可以等到有错误日志以后再调整。

3. innodb_buffer_pool_size Innodb为存储数据、索引、undo、自适应索引等分配的内存大小，影响innodb性能最重要的选项，一般设置为物理内存的80%。

4. innodb_log_buffer_size Innodb重做日志（redo）的大小，一般取默认值即可。

5. key_buffer_size MyISAM表缓存索引的缓存，建议不用MyISAM表。

6. net_buffer_length 服务器在客户端连接建立以后创建的缓存大小，用来保持请求和结果。根据需要，这个大小可以增长至max_allowed_packet。

7. thread_stack 每个线程的栈大小，如果该变量设置过小，将会限制SQL语句的复杂性、存储过程的递归深度，以及服务器上其他内存消耗型的操作。对于大部分安装来说，默认取值即可。如果有类似”Thread stack overrun”，则需要增大该值。

8. query_prealloc_size 此缓存为语句解析和执行而分配，如果运行复杂查询，增加缓存是合理的，这样mysqld不会在执行查询的时候在分配内存上耗时。

9. binlog_cache_size 缓存binlog的缓冲区，如果大于该值，缓存中的binlog将写到磁盘的临时文件中。

10. binlog_stmt_cache_size 缓存非事务表的binlog。

11. read_rnd_buffer_size 存放排序和发送结果至客户端之间，读取结果的大小，大的值能提高order by的性能

12. sort_buffer_size 每个线程需要排序的时候会分配此缓存，通过检查sort_merge_passes状态变量来判断是否需要增加该缓存的大小。sort_buffer_size缓存经常会分配，所以，大的GLOBAL值会降低性能而不是增加性能。因此，最好不要设置得太大，在需要的时候通过set session增加即可。

13. join_buffer_size 连接操作分配的缓冲区，为了检查是否需要增加join_buffer_size的取值，可以检查Select_sacn状态变量，它包括第一张表执行完整扫描的连接次数，同样，select_full_range_join，它包含使用范围搜索的连接次数。

14. read_buffer_size 为表顺序扫描分配的缓存

15. tmp_table_size 临时表的最大值，服务器默认设置为max_heap_table_size和tmp_table_size两者中较小的一个，如果有足够的内存，并且created_tmp_disk_tables状态变量再增大，则可以适当调大，把需要临时表的所有结果保持在内存中，以提高性能。

原文摘自 http://mingxinglai.com/cn/2016/04/mysql-memory-usage-formula/

http://blog.51cto.com/11819159/2129769

https://help.aliyun.com/document_detail/26316.html

本文将讨论MySQL内存相关的一些选项,包括：

单位都是b，不是kb，即1B=1/(102410241024)G

• 1）全局的buffer，如innodb_buffer_pool_size；
• 2）线程级的选项，如binlog_cache_size；
• 3）为特定操作分配的缓冲区，如sort_buffer_size。

我们只讨论内存相关的选项，其他一些如innodb_open_files、thread_cache_size、table_definition_cache、table_open_cache这类限制文件描述符合线程数量的选项不在讨论之列

1. MySQL的理论内存上限

最近我们在做的一个项目，需要检查MySQL的理论内存上限，同事在http://www.mysqlcalculator.com/找到一个内存计算公式，如下所示：

key_buffer_size+ query_cache_size+ tmp_table_size+ innodb_buffer_pool_size+ innodb_additional_mem_pool_size+ innodb_log_buffer_size+ max_connections* (sort_buffer_size+ read_buffer_size+ read_rnd_buffer_size+ join_buffer_size+ thread_stack+ binlog_cache_size)

show variables where variable_name in (
'key_buffer_size',
'query_cache_size',
'tmp_table_size',
'innodb_buffer_pool_size',
'innodb_additional_mem_pool_size',
'innodb_log_buffer_size'
);

show variables like 'max_connections';

show variables where variable_name in (
'sort_buffer_size',
'read_buffer_size',
'read_rnd_buffer_size',
'join_buffer_size',
'thread_stack',
'binlog_cache_size'
);

这个公式可以说是错的，因为，这个公式并不能非常准确的描述MySQL的实际内存使用情况，实际内存使用情况远比这个公式要复杂，随着具体的应用场景不同而存在差异。这个公式也可以说是对的，因为，该公式大致估算了一个MySQL实例可能需要的内存，可以作为一个参考值。

那么，在实际应用中，我们怎么判断具体的内存使用情况？根据什么条件来调整这些参数？调整为多少比较合适呢？要回答这些问题，还得从原理讲起。

2. MySQL内存参数的含义

在这一节，我们首先对内存参数进行了一个分类，然后对各个参数的含义进行了详细的讨论。

2.1 内存参数分类

2.1.1 服务器级别

服务器级别的buffer是全局且唯一的，影响所有的连接和查询，需要注意的是，服务器级别的buffer中，大部分是服务器启动的时候分配的，小部分是后来分配的。如query_cache，初始值是0，后续不断增长，直至最大。

• innodb_additional_mem_pool_size # 该参数查不到
• innodb_buffer_pool_size
• innodb_log_buffer_size
• key_buffer_size
• query_cache_size

sql查询语句

show variables where variable_name in (
'innodb_buffer_pool_size',
'innodb_log_buffer_size',
'innodb_additional_mem_pool_size',
'key_buffer_size',
'query_cache_size'
);

2.1.2 线程级别

这些buffer是线程级别的，对于每个线程都会分配，因此，占用的内存情况为max_connection * (thread_options)，线程级别的选项有：

• binlog_cache_size
• binlog_stmt_cache_size
• net_buffer_length
• query_prealloc_size
• thread_stack

sql查询语句

show variables where variable_name in (
'net_buffer_length',
'thread_stack',
'query_prealloc_size',
'binlog_cache_size',
'binlog_stmt_cache_size'
);

2.1.3 为特定操作分配的缓冲区

当服务器执行特殊操作时，根据需要分配缓冲区。因此，很难计算缓冲区的具体大小。好在这些缓冲区都是session级别的，我们可以保持全局较小的取值，如果需要，再修改session级别的缓冲区大小。

a.对每个线程分配一次

• read_rnd_buffer_size
• sort_buffer_size
• myisam_mmap_size
• myisam_sort_buffer_size
• bulk_insert_buffer_size
• preload_buffer_size

sql查询语句

show variables where variable_name in (
'read_rnd_buffer_size',
'myisam_mmap_size',
'myisam_sort_buffer_size',
'bulk_insert_buffer_size',
'preload_buffer_size'
);

b.对每个线程分配多次（可能）

• join_buffer_size
• read_buffer_size
• tmp_table_size

show variables where variable_name in (
'join_buffer_size',
'read_buffer_size',
'tmp_table_size'
);

2.2 内存参数的含义

各个变量的含义比较好掌握，官方参考手册上都有，但是，什么时候应该修改，修改为多少才是合适的值？手册上并没有给出，本文将回答这个问题。

1. query_cache_size 这是MySQL的查询缓存，用以缓冲SQL语句的结果，如果下次有相同的SQL语句，并且，结果还没有invalid掉，则直接返回查询缓存中的结果即可。这是理想情况，实际情况query_cache可能导致激烈的锁竞争，使得性能反而下降，MySQL 5.7已经可以关闭query_cache了。

2. innodb_additional_mem_pool_size # (没有查到mysql5.7_aliyun_RDS) 该缓存用以存放数据字典和内部数据结构的信息，一般情况下，表越多，该选项也应该越大，该选项过小时，Innodb会在错误日志中打印错误信息，可以等到有错误日志以后再调整。

3. innodb_buffer_pool_size Innodb为存储数据、索引、undo、自适应索引等分配的内存大小，影响innodb性能最重要的选项，一般设置为物理内存的80%。

4. innodb_log_buffer_size Innodb重做日志（redo）的大小，一般取默认值即可。

5. key_buffer_size MyISAM表缓存索引的缓存，建议不用MyISAM表。

6. net_buffer_length 服务器在客户端连接建立以后创建的缓存大小，用来保持请求和结果。根据需要，这个大小可以增长至max_allowed_packet。

7. thread_stack 每个线程的栈大小，如果该变量设置过小，将会限制SQL语句的复杂性、存储过程的递归深度，以及服务器上其他内存消耗型的操作。对于大部分安装来说，默认取值即可。如果有类似”Thread stack overrun”，则需要增大该值。

8. query_prealloc_size 此缓存为语句解析和执行而分配，如果运行复杂查询，增加缓存是合理的，这样mysqld不会在执行查询的时候在分配内存上耗时。

9. binlog_cache_size 缓存binlog的缓冲区，如果大于该值，缓存中的binlog将写到磁盘的临时文件中。

10. binlog_stmt_cache_size 缓存非事务表的binlog。

11. read_rnd_buffer_size 存放排序和发送结果至客户端之间，读取结果的大小，大的值能提高order by的性能

12. sort_buffer_size 每个线程需要排序的时候会分配此缓存，通过检查sort_merge_passes状态变量来判断是否需要增加该缓存的大小。sort_buffer_size缓存经常会分配，所以，大的GLOBAL值会降低性能而不是增加性能。因此，最好不要设置得太大，在需要的时候通过set session增加即可。

13. join_buffer_size 连接操作分配的缓冲区，为了检查是否需要增加join_buffer_size的取值，可以检查Select_sacn状态变量，它包括第一张表执行完整扫描的连接次数，同样，select_full_range_join，它包含使用范围搜索的连接次数。

14. read_buffer_size 为表顺序扫描分配的缓存

15. tmp_table_size 临时表的最大值，服务器默认设置为max_heap_table_size和tmp_table_size两者中较小的一个，如果有足够的内存，并且created_tmp_disk_tables状态变量再增大，则可以适当调大，把需要临时表的所有结果保持在内存中，以提高性能。

原文摘自 http://mingxinglai.com/cn/2016/04/mysql-memory-usage-formula/

http://blog.51cto.com/11819159/2129769

https://help.aliyun.com/document_detail/26316.html