MySQL服务器内存利用

MySQL服务器内存利用

有很多人问我这个问题，我们如何评估一个给定的MySQL server配置所消耗的内存。我们使用什么公式。担心内存使用率的原因是可以理解的。如果你配置了一个MySQL 服务器，它使用很少的内存而它的性能欠优化。而如果你配置的MySQL服务器消耗太多的内存有可能会崩溃，执行查询失败或导致内存和交换分区频繁操作降低速度。运行在32位平台，可能运行时产生地址溢出，因此不得不监视MySQL。正如所说的，我认为寻找一个有可能计算出内存使用公式的方法不是解决问题的正确途径。原因就是----这个公式非常复杂，进而只是提供给我们“原理的可能性”和真实内存消耗根本没有实质性关系。事实上，典型的有8GB内存的服务器运行在通常用理论上100GB或更多的内存。此外，没有很轻易的使用过的“确凿保证的因素”----它依靠的是应用程序和配置。应用程序将消耗理论上10%的内存而其他程序消耗1%的内存。因此你能做些什么？首先，看一下在开始运行时候分配的全局性的缓存配置，如key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size。如果你正使用MySIAM，也可以添加操作系统缓存大小让MySQL用在表操作上。增加操作系统的内存容量，把一部分分配给应用程序，也可以分给MySQL32MB以上内存和各种静态缓存。这些你考虑分配的内存在MySQL开启的时候就分配。余下的内存分配给连接请求。例如，一个有8GB的服务器，可以分6GB给MySQL，而2GB分给余下的进程。每个连接到MySQL服务器的线程都需要自己的缓存。即使该线程空闲，大约256KB也要立刻分给一个连接----这是默认的线程栈，网络缓存等。如果一个事务开启会分配更多的内存空间。正在运行的简单查询可能仅仅增加内存消耗在给定的线程上，而如果表执行复杂造作如全表搜索，排序，否则就需要临时表，尽可能按照read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size这些参数分配内存。但预先分配的内存会比需要大点，并且当一次既定的查询完成就立即释放内存。有些内存被分配作为单独的块，例如tmp_table_size就是MySQL分配给这种操作的最大的内存数量。注意这比想象的要复杂----相同类型的多个缓存可能被分配，例如处理子句查询（subqueries）。对于一些特殊的查询，内存使用甚至更大----如果在MyISAM表操作，大量插入可能分配bulk_insert_buffer_size字节的内存。ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 语句按照myisam_sort_buffer_size分配内存。

对于有简单查询的OLTP应用程序，内存消耗每个线程通常小于1MB，你不必为每个线程增加缓存除非你有复杂的查询语句。做一个有10行的排序用1MB的缓存和16MB的缓存在速度上是没区别的（事实上16MB的速度可能会更慢但这是另一回事）。你可能采取的另一个方法是将碰到你想让MySQL服务器运行在一个顶峰时刻的内存消耗。计算出操作系统，文件缓存，和其他程序需要的内存是简单的。对于32bit的环境，你会有32bit的限制需要考虑到，并且可能”MySQLd”限制在2.5GB大小（精确的数字取决于许多因素）。你可以是用“ps aux”看VSZ----被MySQL进程分配的虚拟内存。你也可以看“常住内存”但我发现这没有什么帮助，尤其是在由于交换分区频繁交换而down机的情况---这不是你想看到的。监视这些值是如何改变的，就会知道当前设置的内存需求，增加/减少适当的值。

有人可能说，嘿我们想要100%的保证我们的服务器从不内存溢出，无论查询或用户都要求保证运行。不幸的是，这几乎是不可能的，是不切合实际的。这有一些原因：

很少考虑的MySQL服务器内存需要的列表

·为每个线程分配的线程缓存可能被分配不止一次。考虑一个子查询（subqueries）的例子----每层可能需要自己的read_buffer,sort_buffer, tmp_table_size等。

·许多变量可以由每个连接所改变。因此如果开发人员使用他们的本地值去运行一些查询，你不能依靠全局变量值。

·有多个主键缓存。多主键缓存可能被创建以适应查询的执行。

·查询分析和优化需要内存。这个可能不太会被忽略但某些查询可能需要很大的内存需求。

·存储的过程。复杂的存储过程可能需要很多内存。

·Prepared语句和焦点。单一的连接可能有许多prepared语句和焦点。它们的数量被最终限制但每个都需要大量的内存消耗。

·Innodb表缓存。Innodb有自己的表缓存，该表缓位于每个表的原数据在开始被访问的地方存储的。这意味这用户有CREATE TABLE权限将能够使MySQL服务器内存溢出。

·MyISAM缓存。MyISAM可以分配足够大的缓存以容纳在给定表的最大记录，该表被存储直到该表被关闭。

·Federated Storage Engine（联合存储引擎）。这可能有不确定的内存需求，这个需求从删除的查询中重新获得查询结果的子集。

·Blob可能需要3x的内存。这点很重要，如果你正处理大的blobs（max_allowed_packet是很大）blobs有256MB可能需要768MB的内存。

·存储引擎。通常来讲，存储引擎为它们自己每个线程或全局分配内存，这些内存不能变成缓存。通过各个方面，监视这些由MySQL发布的特殊的存储引擎

我不想要完成这个列表。相反，我确定我落下了一些东西。而重要点是----有许多内存的消耗进程要找出来并且尝试去找到每一个可能的峰值是不太现实的-----因此我的建议是测量你在实际中所知道的，内存消耗和改变的变量是如何作用的。例如你可能发现了把sort_buffer_size从1MB增加到4MB和1000 max_connections增加的内存峰值30MB而不是3000MB，这可能是你可以计算出来的。

原文：

http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/