mysql 配置内存

配置mysql 正确使用的内存量对于高性能是至关重要的，需要进行定制需求来定制内存的使用。

mysql内存消耗可以分为两类：可控制内存和不可控制内存。

无法控制

mysql 服务器运行、查询解析、以及内部管理所消耗的内存，但是为特定目的而使用的内存有很多参数可以进行控制，比如关联使用join buffer、排序使用sort buffer等。

可控制

用好mysql可控制的内存则需要对配置的含义非常清楚

 

下面的步骤是来配置内存的

1、确定可以使用的内存上限

2、确定每个连接mysql 需要多少内存，例如排序缓冲和临时表

3、确认操作系统需要多少内存才够用。包括同一台机器上其他程序所使用的内存，比如定时任务等……

4、把剩下的内存交给mysql 缓存，例如innodb的缓冲池，这样做很有意义

 

每个mysql 连接需要的内存

mysql保持一个连接（线程）只需要少量的内存，它还要求一个基本的内存来执行任何给定的查询。需要在高峰期大量的查询时预留好足够的内存，否则查询的执行可能会因为缺乏内存而执行效率不佳

或者执行失败。

知道mysql 在高峰期消耗多少内存是非常有用的，但是一些习惯的用法可能会导致意外的消耗了大量的内存，这对内存的使用量的预测变得比较困难，例如绑定变量和innodb数据字典。

当预测内存峰值消耗时，没必要假设一个最坏的情况，例如配置mysql 允许最多100个连接，在理论上可能出现100个连接同时在运行很大的查询，在现实情况下可能不会发生，例如：

设置myisam_sort_buffer_size为256MB，最差的情况下至少需要使用25GB内存，但是这种最差的情况实际几乎是不可能发生的，使用了许多大的临时表或者复杂存储过程的查询，通常是

导致内存高消耗的最可能的原因。

相对于最坏情况的开销，更好的方法是观察服务器在真实工作压力下使用了多少内存，可以在进程的虚拟内存大小那里看到，许多类unix系统里，可以观察top命令中virt列，或者ps命令中VSZ

的值。

 

为操作系统保留内存

和查询一样，操作系统也需要足够的内存给它工作。如果没有虚拟内存正在交换（paging）到硬盘，就是表明了系统内存足够用的最佳迹象。至少应该为操作系统保留1~2G的内存，如果机器内存多

就多预留一些，我们建议2GB获取总内存的5%作为基准，以较大者为准，为了安全再额外增加一些预留，并且如果机器还在运行内存密集任务（如备份），则可以再多增加一些预留，不要为操作系统缓存

增加任何内存，因为他们可能变得非常大，操作系统通常会利用所有剩下的内存来做文件系统缓存，我们认为，这应该从操作系统自身的需求分离出来。

 

 

为缓存分配内存

如果服务器只运行mysql，所有不需要为操作系统以及查询处理保留的内存都可以用来做mysql缓存。

相比其他，mysql需要为缓存分配更多的内存，它使用缓存来避免磁盘访问，磁盘访问比内存访问要慢得多，操作系统可能会缓存一些数据，这对mysql有些好处，但是mysql自身也需要大量的内存。

下面是我们认为大部分情况最重要的缓存

　　innodb 缓存池

　　innodb 日志文件和myisam 数据的操作系统缓存

　　myisam 键缓存

　　查询缓存

　　无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

还有其他缓存通常不会使用太多内存

 

如果使用单一的存储引擎，配置服务器就简单许多了。如果只使用myisam 就可以完全关闭innodb，如果只使用innoDb，就只需要分配最少的资源给myisam（mysql 内部系统表用myisam），但是

如果正混用各种存储引擎，就很难在他们之间找到恰当的平衡，我们发现最好的办法是做一个有根据的猜测，然后在运行中观察服务器（再进行调整）

 

 

innoDB缓冲池

如果大部分都是innodb表，那么innodb缓存池或许比任何东西都需要内存，innodb缓存池并不仅仅是缓存索引，它还会缓存行的数据，自适应hash索引，插入缓冲（insert buffer）、锁以及其他内部的

数据结构。innodb还是用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后顺序一起写回。总之innodb严重依赖缓冲池，你必须确认为他分配了足够的内存，可以使用show 命令得到变量，或

使用例如innotop的工具监控innoDB的缓冲池内存利用的情况。

如果数据量不大，并且不会快速的增长，就没必要为缓冲池分配过多的内存，把缓冲池配置的比需要缓存的表和索引还要大很多实际没有什么意义，当然，对于一个快速增长的数据库做超前的规划没有

错，但有时候我们也会看到一个巨大的缓冲池之缓存了一点数据，就没必要了。

很大的缓冲池也会带来一些挑战，例如，预热和关闭都会花费很长的时间，如果有很多脏数据在缓冲池里，innodb关闭时可能会花费比较长的时间因为关闭之前需要把脏页写回数据文件，也可以强制关闭

，但是重启的时候需要做很多恢复工作，也就是说无法加快关闭和重启，如果事先知道需要关闭innodb，可以在运行的时候修改innodb_max_dirty_pages_pct 变量，将值进行改小，等待数据刷新线程进行

清理缓冲池，然后在脏页数量较小的时候关闭，可以监控the innodb_buffer_pool_pages_dirty状态的变量或者使用innotop来监控show innodb status来观察脏页的刷新量。

 

转载于:https://www.cnblogs.com/gosimple/p/8040157.html