MYSQL 内存 正本清源

MYSQL locked_in_memory

探索MYSQL开启大页内存

很多时候MYSQL大佬对系统页表也不太熟悉,懂MYSQL的人也会说MYSQL一般不开大页来弹塞别人.
一个大佬给MYSQL的INNODBUF 设置433GB内存后系统页表大小如图

虽然是144GB ,可系统页表也占用了375MB,433GB内存那差不多近1GB.

关于页表太大会导致什么影响 这里有介绍:

Linux 64 页表，进程内存，大页
Linux_x86_64BIT内存管理与分布
Linux 关闭透明大页

关于MYSQL 一般是不开启大页内存,这说法是过去时! 原因在于MYSQL用的内存小,并且使用线程模型,而不是ORACLE进程模型.相对系统页表消耗很小.

度娘文有一大片的介绍:
设置MySQL使用大内存页面 https://blog.51cto.com/tempcom/101306

性能测试 一个7346MB内存
4K页

2M页

(449.5-374.114)/449.5*100%=16.7%

在设置MYSQL 大页内存的时候 遇到问题,从而被狗屎的运维踩了几脚
什么问题呢?MYSQL 部分使用大页内存,就20个页! 大部分都使用传统页
导致内存非常紧张,达到95%! 主要大页内存开了后,系统不会给别的进程使用,

这里就涉及到了MYSQL内存组成部分

在ORACLE 这里我们很清楚内存 分两块 SGA和PGA .设置大页内存参数主要大于SGA一点点就好.
而MYSQL 哪部分是SGA 哪部分是PGA
http://www.mysqlcalculator.com/

这个计算公式有些错误, 5.7.35 INNODB_BUFFER_POOL_SIZE默认是128MB
TMP_TABLE_SIZE应该算是线程内存

我买了本书,今年出的书,在计算内存的时候把BINLOG_CAHCE_SIZE归算在全局内存里. 还80元一本.

这说明大佬们也很水啊!

官方MYSQL文档也没有说明是全局共享,还是线程会话私有的呢!
不过MYSQL是开源的,应该可以从源代码上分析出来.

1.前言

　　关于Mysql的内存这里要好好地说一下，以便能在后期好好理解Mysql，MySQL中内存分为全局内存和线程内存两大部分（其实并不全部，只是影响比较大的 部分）　

per_thread_buffers=(read_buffer_size + read_rnd_buffer_size+sort_buffer_size+thread_stack + join_buffer_size+binlog_cache_size
+ tmp_table_size)*max_connections

global_buffers=
innodb_buffer_pool_size + innodb_additional_mem_pool_size 
+ innodb_log_buffer_size + key_buffer_size + query_cache_size

total_memory=global_buffers + per_thread_buffers+Performace_schema+Memory

全局内存+线程内存+PS内存+内存引擎占用的
2.Mysql之全局缓存
key_buffer_size：决定索引处理的速度，尤其是索引读的速度。默认值是16M，通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads  key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用'key_read%'获得用来显示状态数据）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值'created_tmp_disk_tables'得知详情。
innodb_buffer_pool_size：InnoDB使用该参数指定大小的内存来缓冲数据和索引，这个是Innodb引擎中影响性能最大的参数。
innodb_additional_mem_pool_size：指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是8M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。
innodb_log_buffer_size：指定InnoDB用来存储日志数据的缓存大小，如果您的表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。
query_cache_size：是MySQL的查询缓冲大小。（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
3.Mysql之线程缓存
　　每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。
read_buffer_size：是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
sort_buffer_size：是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。
read_rnd_buffer_size：是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
tmp_table_size：是MySQL的临时表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果。
thread_stack ：主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。 
join_buffer_size：应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。
binlog_cache_size：在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的错误。

其中需要注意的是：table_cache表示的是所有线程打开的表的数目，和内存无关。

--查看每个线程占用多少内存，然后乘以正在运行的线程（也就是排查sleep的）。
SELECT ( ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@max_allowed_packet
+ @@net_buffer_length )
) / (1024*1024) AS MEMORY_MB;

--查看MySQL全局占用多少内存
select (@@innodb_buffer_pool_size
+@@innodb_log_buffer_size
+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;

--查看performance_schema占用多少内存
SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, sys.format_bytes(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;

--查看 memory 存储引擎占用多少内存
select sum(max_data_length)/1024/1024 as MEMORY_MB from tables where engine='memory';

--查看innodb_buffer_pool情况
select sum(POOL_SIZE),sum(free_buffers),sum(DATABASE_PAGES) from INNODB_BUFFER_POOL_STATS;

--根据thread的个数，计算内存
select count(*) from information_schema.processlist where db="db_name" ;

扩展：如果出现MySQL内存过高，一般需要优化数据库的参数

performance_schema_max_table_instances=150
table_definition_cache=150
table_open_cache=64
performance_schema = off
innodb_buffer_pool_size = 6144M    
#这个参数值生产环境一般是物理内存的 3/4 ，其他环境可以根据情况调整

另外那个常用的PS库使用的内存表,它可要占很大的内存

觉得好 友情支持下点下广告哦!