MySQL使用内存的计算方法

因为MySQL为每个连接创建一个线程，所以MySQL所使用的内存可以分为两个部分。一个部分是公共内存，即所有连接公用的内存，比如QueryCache, Key Cache等，这一部分内存在系统初始化的阶段就已经分配。另一个部分是每个连接单独使用的内存，比如Sort Buffer等。

公共内存

公共内存在MySQL启动的时候所分配，如下描述：
1）分配Query Cache和Key Cache所占用的内存
2）分配InnoDB Addition Mem Pool所占内存，由innodb_additional_mem_pool_size指定。
3）分配InnoDB Buffer Pool所占用的内存，由innodb_buffer_pool_size指定。InnoDB把Buffer Pool的内存按UNIV_PAGE_SIZE(16K)分页，所以InnoDB Buffer Pool页的个数blocks = innodb_buffer_pool_size/16K。为管理这些页，InnoDB为每个页分配一个管理单元buf_block_t，每个占用内存800字节。所以管理单元所需内存blocks800。此外InnoDB为每个管理单元还分配一个指针（占8字节），占用内存blocks8。InnoDB还为页分配了Hash数据结构，占用内存为blocks*2。所以InnoDB Buffer所需要的总内存为

innodb_buffer_pool_size + (innodb_buffer_pool_size/16K) *800 + (innodb_buffer_pool_size/16K)*8 + (innodb_buffer_pool_size/16K)*2

示意图如下：

4）创建Adaptive Hash查找单元btr_search_sys，为btr_search_sys->hash_index分配内存
hash_cell的个数计算方法为大于innodb_buffer_pool_size/sizeof(void*)/64的最小素数，每个hash_cell占用内存为8字节，所以总的内存消耗近似为innodb_buffer_pool_size/64
由于InnoDB在初始化的时候已经为内存池分配了内存，后续MySQL在处理每个连接的时候，InnoDB层所使用的内存可以直接在内存池中分配，通过LRU算法替换出旧的内存。所以可以认为InnoDB在初始化的时候已经确定了它所能使用内存的上限，在MySQL处理每个连接的时候不再考虑从InnoDB内存池中占用的内存。

私有内存

这里把MySQL为每个连接所分配的内存称为私有内存。它们不能为每个连接所共享，如果有需要，每个连接都要分配这样的内存，比如Sort Buffer。MySQL是分阶段处理用户的连接请求的，所以可以分阶段来分析MySQL为每个连接所需要分配的内存。在连接处理过程中，MySQL会为用户名，数据库名，以及某些文件的路径等分配内存，由于这些占用的内存较小，在以下分析中被忽略。
1）创建线程，以及初始化THD

- 每个连接独占一个线程，每个线程独占一个堆栈，大小为为thread_stack（x64机器默认为256K）
- thd->net.buf为用户线程接收网络包的缓冲区，大小可动态调整。初始大小为net_buffer_length（默认为16K）

2）登录阶段：

- thd->packet为MySQL发送数据的缓冲区，MySQL在登录阶段为thd->packet分配内存，大小为net_buffer_length（默认为16K）

3）登录完成：

- 为thd→mem_root为每个线程的内存池，登录成功后为其分配内存，初始大小为query_prealloc_size（默认8K）
- 为thd->transaction.mem_root分配内存，初始大小为transaction_prealloc_size（默认4K）

4）接收SQL阶段

- 如果thd->net.buf大小不足以容纳用户发送的SQL，MySQL会重新为thd→net.buf分配内存，大小足以容纳整个SQL，但是最大不超过max_allowed_packet
- 从mem_root中为thd->query分配内存内存，如果mem_root不足以容纳整个Query，则调用系统malloc为thd->query分配内存，大小为整个SQL长度

5）词法语法分析阶段

- 为Lex词法分析器Lex_input_stream分配内存，大小为整个SQL的长度。
- 词法分析器提取查询Query的文本，从mem_root分配内存，如果Query中某个字段长度过长，则调用malloc分配，大小为整个字段长度。比如带有blob或者text字段的insert语句，字段长度可能接近整个SQL的长度。

6）查询阶段

- 打开FRM表时，使用IO_CACHE作为缓存，分配内存大小硬编码为8024，使用完成后立即被删除。
- 如果有中间临时表产生，MySQL会为临时Heap表分配内存，按需分配，但是最大不会超过tmp_table_size，如果中间表数据量非常大，占用内存超过了tmp_table_size，MySQL会将内存里的Heap表转成磁盘的MyISAM表，转换完成后释放Heap临时表占用的空间。由于MyISAM表使用操作系统的缓存作为数据Cache，所以如果临时表非常大的话，容易造成内存飙升。磁盘文件使用IO_CACHE缓冲，缓冲区占用内存为read _buffer_size。
- 对于临时表，由于要把中间数据写入临时表文件，如果有blob或者text的大字段，还需要为他们分配内存。分配的方法和下一条INSERT/UPDATE语句类似。使用完毕后立即释放内存。
- 在使用INSERT/UPDATE语句时候，对于文本字段，实际占用内存size = field_charset->mbmaxlen * length。比如text字段，字符集是utf-8，文本字符数为1000，由于utf-8字符集，对于每个字符最多能使用4个字符，所以实际分配内存为4000。使用完毕后立即释放内存
- 对于JOIN查询，根据情况分配join_buffer_cache大小为join_buffer_size
- 对于order by查询，MySQL为排序关键字分配内存，大小为sort_buffer_size。
- 对于filesort，MySQL先将待排序的表（或者临时表）的排序关键字取出来，放到另一个文件中，这两个文件都需要缓存，大小分别为read_buffer_size和64K

7）返回结果

- MySQL使用thd->packet作为MySQL的发送缓冲区，初始大小为net_buffer_length。如果MySQL表有blob或者text字段，导致发送缓冲区不足以容纳一行数据，MySQL会重新分配内存，但是最大不超过max_allowed_packet。返回结果以后，MySQL会把发送缓冲区恢复为默认

计算公式

3.1 公共内存计算公式
公共内存由MySQL启动时所分配。计算公式为：

query_cache_size +
key_buffer_size +
innodb_additional_mem_pool_size +
innodb_buffer_pool_size + (innodb_buffer_pool_size/16K) *800 + (innodb_buffer_pool_size/16K)*8 + (innodb_buffer_pool_size/16K)*2 +
innodb_log_buffer_size

3.2 连接私有内存计算公式

按第2节所描述，客户端每个连接所消耗的内存需要考虑如下因素
1) Thread_stack：每个线程一个，消耗内存为：
thread_stack
2) 网络缓冲：Net IO缓冲包括thd->net.buff，thd->packet。分别最小占用net_buffer_length，如果用户发送的SQL或者某一行查询结果大于net_buffer_length，MySQL重新分配内存，但是最大不超过max_allowed_packet。而且thd->net.buff申请的内存不会被释放。总的内存消耗近似为：
Max(net_buffer_length, len(SQL)) + Max(net_buffer_length, len(result_row))
3) 每个线程的内存池：thd->mem_root为每个线程的内存池，存储MySQL内存的数据结构（比如某些String），初始大小为query_prealloc_size，如果内存池内存不足以使用，MySQL会额外增加内存的分配，每次最少query_alloc_block_size。在每个Query处理完毕后，内存被统一释放。占用内存近似为：
query_prealloc_size + k * query_alloc_block_size //通常k是一个很小的值，不超过10
4) 查询语句：如果MySQL的查询语句很长，也会比较消耗内存，因为它们会在多处存在。比如：thd->query, thd->lex（可以认为query在thd->lex中有两个副本，一个是输入，另一个是解析之后的结果）。在每个Query处理完毕后，内存被统一释放。消耗内存为：3 * len(SQL)
5) Join buffer：根据Jion表的个数来分配内存，两个表之间Join会使用一个Join Buffer，消耗内存：
k * join_buffer_size
6) read_buffer: MySQL内部每生成一个临时文件，都会使用IO_CACHE作为缓冲管理，IO_CACHE所用内存为read_buffer_size。如果有多个临时表，则会有多个IO_CACHE被生成。使用内存为
k * read_buffer_size
7) 临时表：临时表可能是MySQL每个连接最耗内存的部分，比如两个表之间做join的字段不能使用索引，就会生成较大的临时表。临时表占用内存按需分配，最大不超过tmp_table_size和max_heap_table_size的最小值，所以占用内存为：
Min(临时文件大小，tmp_table_size)

总的来看，每个连接的内存消耗近似为：

thread_stack +
Max(net_buffer_length, len(SQL)) + Max(net_buffer_length, len(result_row)) + query_prealloc_size + k1 * query_alloc_block_size +
3 * len(SQL) +
k2 * join_buffer_size +
k3 * read_buffer_size +
sort_buffer_size +
min(临时文件大小, tmp_table_size, max_heap_table_size);

如果是Insert/Update语句并带有Text类型的字段，还应该在上述的基础上再增加一个Text字段的长度len(Text)

实验验证

4.1 环境配置
机器为Redhat 4 x64操作系统，物理内存48G
MySQL内存相关配置文件如下：

query_cache_size         = 32M
thread_stack             = 192K
key_buffer_size           = 256M
sort_buffer_size           = 2M
read_buffer_size          = 2M
join_buffer_size           = 8M
read_rnd_buffer_size      = 8M
query_prealloc_size       = 8K
net_buffer_length         = 16K
max_allowed_packet       = 64M
binlog_cache_size             = 1M
innodb_additional_mem_pool_size  = 100M
innodb_buffer_pool_size    = 2G
innodb_log_buffer_size      = 8M
tmp_table_size           = 512M
max_heap_table_size      = 512M

通过查看/proc/${pid}/status文件，查看MySQL对内存的使用情况，这里主要关注两个参数，VmData和VmRSS。VmData代表进程虚拟内存中数据段的大小（静态内存以及通过malloc动态分配的内存）大小，VmRSS代表进程实际使用的物理内存大小。

4.2 测试场景
1） MySQL初始化占用内存
初始化完毕后，/proc/${pid}/status的文件内容如下

初始化内存占用公式：

query_cache_size +
key_buffer_size +
innodb_additional_mem_pool_size +
innodb_buffer_pool_size + (innodb_buffer_pool_size/16K) *800 + (innodb_buffer_pool_size/16K)*8 + (innodb_buffer_pool_size/16K)*2 +
innodb_log_buffer_size +
10 * thread_stack
= 2608256 K

略低于实际消耗的内存VMData：2875876K。
计算偏差为2875876K - 2608256 K = 267620K，约261M，应该还有其他未考虑到的Buffer也会在初始化的时候分配。

2） 大的输入SQL占用内存
通过一个客户端连接到MySQL服务器，不断执行带有text字段（utf-8编码，字符个数为1418461）的Insert语句，整个SQL占用内存4252120 Byte，其中Text字段占用内存4252057Byte，而服务器返回的结果集字段很小。如：

    Insert into blog(username, content) values(‘wyl’, ‘xxxxxxxxxxxxxxxx’)
    其中content的数据类型为LongText类型

在Insert语句执行过程中，/proc/${pid}/status的内容如下：

按第3节给出的公式计算内存消耗 ：

thread_stack +
Max(net_buffer_length, len(SQL)) + Max(net_buffer_length, len(result_row))
query_prealloc_size +
3 * len(SQL) +
charset->mbmaxlen*len(Text) //Insert语句在将Text字段写文件之前，还需要为Text分配一次内存。对于utf8而言，charset->mbmaxlen=3，len(Text)长度为4252057Byte，MySQL申请的内存为3*4252057
= 192K + 4252120 + 16K + 8K+ 3*4252120 + 3*4252057
= 29283K

MySQL实际的内存增长为：2905468K - 2875876K = 29592K
按第三节给的公式计算的偏差为29592K-29283K = 309K

3） 结果集带有大的Text字段
通过MySQL客户端发送select语句，select语句返回的结果集中带有Text字段，Text字段长度为4252120 Byte。在执行过程中，/proc/${pid}/status的内容为

按第三节给出的公式计算：

thread_stack +
Max(net_buffer_length, len(SQL)) + Max(net_buffer_length, len(result_row)) +
query_prealloc_size
= 192K + 16K + 4252120 + 8K
= 4368K

实际的内存消耗为：2880328 – 2875876 = 4452K
按第三节给的公式所计算的内存偏差为：4452K - 4368K = 84K
这个由于SQL简单，使用的额外空间比较少，和实际消耗的内存消耗比较接近

4） 无索引JOIN生成大的临时表
执行SQL语句，select * from tb t1 , mir t2 order by t1.val，对两个大的表做JOIN，并排序，返回的结果的行数非常大，但是每行的数据比较小。在执行过程中包含如下几个过程：
第一个阶段在内存中创建临时的Heap表，最多使用的内存为tmp_table_size

第二阶段，由于JOIN生成的结果集非常大，在内存中超过了tmp_tmp_size。MySQL将内存Heap表，转为磁盘的MyISAM表。

第三阶段，在磁盘的MyISAM表创建完成以后，MySQL将Heap表中的数据转存到磁盘的MyISAM表中。在这个阶段，虽然MySQL释放了内存Heap表占的内存，但是由于MyISAM表使用操作系统的缓存作为数据Cache。所以通过iostat观察，仍然可以看到系统空闲的内存在下降

第四阶段，由于查询SQL带有Order By语句，MySQL将JOIN生成的临时表数据进行排序，由于没有索引，这里使用的是filesort。Filesort仍然使用MyISAM作为磁盘的临时文件，和第三阶段类似，在filesort阶段，仍然可以看到系统空闲的内存在下降。

第五阶段，排序完成以后，MySQL将结果发生给客户端。由于MySQL是将结果集一行一行的发送给客户端的，而且这个测试用例的结果集没有Text字段，在这个阶段没有额外的内存分配。

通过第三节给的公式计算内存消耗：

thread_stack +
Max(net_buffer_length, len(SQL)) + Max(net_buffer_length, len(result_row)) + query_prealloc_size +
k2 * join_buffer_size +
k3 * read_buffer_size +
sort_buffer_size +
min(临时文件大小，tmp_table_size);
= 192K + 16K + 16K + 8K + 8M + 2M + 2M + 512M
= 536808K

通过查看/proc/${pid}/status的内容

峰值内存消耗为3412788K - 2875876K = 536912K
计算误差为：536912K - 536808K = 104K