锐客网

  1. 首页 > 睿知 > it技术 > >

MYSQL小内存， 大问题

内存mysqlIT知识

古人学问无遗力，少壮工夫老始成。这篇文章主要讲述MYSQL小内存， 大问题相关的知识，希望能为你提供帮助。

每种数据库都有自己的管理内存的方法，mysql 管理内存（仅仅讨论 INNODB 数据库引擎）的方法大部分都关注在 innodb_buffer_pool_size   这个设置。MYSQL 本身内存管理有这么简单吗？


首先一个MYSQL系统中的内存大致分为，这里仅仅讨论仅仅提供MYSQL服务的服务器。


1  系统使用内存包含内核运行，系统的缓存等
2  MYSQL 本身系统固定使用的内存，innodb_buffer_pool    query cache 等等
3    MYSQL workload ，例如连接，每个查询的 buffers  join  buffer sort buffer等等
4    MYSQL replication  and log 使用的内存 例如 binary log cache ,replicatiton connection  等等




问题 1  内存泄漏或因为内存不足造成的 OOM 


一般来来说，判断内存是不是泄漏不是DB 应该做的事情，但有的时候知道的多一点没有坏处


内存泄漏的公式 :   centos 6.x    used - buffers - cached 的值 跟 used 的值作比较 
                            centos 7.x    available - free 的值 跟 buff/cache 的值作比较 





可以看到，根据上面的额公式 3019 - 819 = 2200  对比 buff/cache   2684比较 并没有特别大的差异，说明内存并没有泄漏，一般来说不超过10% 与  buffer/cache 相比的来说都不算存在内存泄漏的迹象。


查看SWAP ，这里面就有争论了，有的企业是直接将SWAP 禁用了，这样的企业一般都会给系统分配比较大的内存，如果当内存耗尽，系统OOM的时候也不大会怕 KILL 消耗资源最大的进程。另一部分企业还是使用了SWAP 怕的就是OOM ，但不好的地方就是如果用到了 SWAP模拟内存，则MYSQL的性能会急转直下，所以要不要用SWAP 那就看你怎么选择了。


内存不足的主要原因刨除因为本身系统并发或者本身资源不足的情况，大部分情况还是要看看语句的方面，是不是已经优化了，或者存在的问题较少。


并且由于很多系统不是自研，所以一般遇到这样的问题，除了本单位有能优化的系统的人以外，大概率的可能都是添加内存。


问题2 到底我的innodb_buffer_pool_size 该怎么设置


大部分DB们可能认为这都不是一个问题，你给我多大的机器，我就按照60-80% 来设置innodb_buffer_pool_size 就可以了。


实际上这已经上了一个套，首先我们需要知道给我们的机器大致能承载多大的工作量，如果超过机器能承受的工作量，则就需要和相关的人员谈谈了。
而不是到了后面在去谈，虽然可以亡羊补牢，但在领导的心里，你属于后知后觉，而不是未卜先知。


另外如果系统已经运行了一段时间，则我们怎么知道innodb_buffer_pool_size 是合理的
SELECT engine,
    ->     count(*) as TABLES,
    ->     concat(round(sum(table_rows)/1000000,2),M) rows,
    ->     concat(round(sum(data_length)/(1024*1024*1024),2),G) DATA,
    ->     concat(round(sum(index_length)/(1024*1024*1024),2),G) idx,
    ->     concat(round(sum(data_length+index_length)/(1024*1024*1024),2),G) total_size,
    ->     round(sum(index_length)/sum(data_length),2) idxfrac
    -> FROM information_schema.TABLES
    -> WHERE table_schema not in (mysql, performance_schema, information_schema)
    -> GROUP BY engine
    -> ORDER BY sum(data_length+index_length) DESC LIMIT 10;


通过上面的查询，我们可以看到当前系统里面到大致的表的数量，有的建议里面说要根据总体的数据量来得出一个恰当的 innodb_buffer_pool_size 的量。

下面有的建议里面就给出了一个公式，通过下面的图中的公式算出当前你的innodb_buffer_pool_size   应该设置的一个量级。


SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS_GB FROM      (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes      FROM information_schema.tables WHERE engine=InnoDB) A;





同时也可以关注一段系统状态里面的 innodb_buffer_pool_reads 看看系统从磁盘中读取数据的量在一个规定（业务繁忙）时间的情况，如果经常大量的去读，并且你的I/O系统也不怎么的情况下，建议还是加大innodb_buffer_pool_size ，尽量满足系统的需求。


问题 3 ,我的innodb_buffer_pool_size 设置的较高，但查询还是很慢


在排除innodb_buffer_pool_size 设置不当造成的性能问题后，就需要关注以下几个buffer


read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size


以上几个BUFFER 可以解决如下问题


1   查询的表中无合适的索引，或无法使用索引的情况下，会进行全表扫描，全索引扫描，这种情况会将数据顺序的读入到 read_buffer_size 中，当读取的数据足以在 read_buffer_size 中保存，则读取结束后，会将buffer的数据返回上层，加速这一类的查询。一般例如主键（有序） 或者和日期相关的有序数据的提取，都会用到。


2  查询中如果没有顺序查询而是大量的随机查询，并且也没有索引或有效的索引的情况下，则会直接进行随机数据的存储


3   当多个表进行JOIN 的情况下，在没有有效索引的情况下，为了减少与被驱动表读取的次数，将需要读取的数据放入到 join_buffer 提高JOIN 的效率，而如果JOIN_buffer_size 不足的情况，则会在需要新的数据写入后，清理掉之前写入的数据，而这些数据如果也正在使用，则会在清理掉现在正在用的数据，造成查询缓慢，多次访问I/O。 


4   Sort_buffer_size 因为MYSQL 5.X都不支持倒序，另外如果没有索引的情况下，进行排序也是要进行filesort，而足够大的sort 可以降低查询在排序时和磁盘之间的交互，而在内存中解决，所以对于排序操作多的系统，并且也么有什么优化的情况下，大的sort_buffer_size 是很有用的。


所以如果你正在被垃圾SQL 摧残还不能进行改变的时候，可以提高这几个位置的内存设置，可能会给你带来片刻的喘息。


最后，MYSQL的内存除了上的一些东西，其实可以通过SYS 库 或者 performance_schema 中的一些表来查看当前的内存情况，方便对当前的系统进行调节。


如大部分的MYSQL系统都打开了performance_schema 进行系统的性能信息的收集，而通过相关的信息收集时可以看到相关的内存的一些详细的分配的情况。
下面就展示了一些当前的内存分配的情况



当然查看每个buffer pool 中的内存分配的情况，还是查看 show engine innodb status 会更快查看相关的明细。 





总之MYSQL 的内存其实并不是innodb_buffer_pool_size 那么简单，随着版本的更新，更多的内存的信息的分析和查看将移交到 sys 库和 preformance_schema 库的相关表中。



【MYSQL小内存， 大问题】


    推荐阅读


    上一篇：Zookeeper - 理论基础-补充

    下一篇：投稿|新东方带货能火多久？