原文地址：http://blog.daobidao.com/lnmpa-mysql-5-6-mem-configure-optimize.html
背景
介绍Lnmpa、Lamp、Lnmp等环境的教程很多，但是在默认安装后，很容易出现内存占用光，出现OOM等问题，为了更好的让服务器稳定运行，需要对每个应用服务进行内存配置方面的优化。
MySQL介绍
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL内存计算方式
全局内存消耗（共享内存）[单纯MYSQL服务（不计算连接占用内存）单位 M]：
MYSQL_Service_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size
也可以用SQL命令：select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size )/1024/1024 as “MYSQL_Service_Mem”;
直接在MysQL命令提示符里输入就可以输出结果。
MYSQL服务最大使用内存（包含连接访问数据库）单位 M：
MYSQL_Service_Max_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + max_connections * ( read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack )
也可以用SQL命令：select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size + @@max_connections*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack) )/1024/1024 as “MYSQL_Service_Max_Mem”;
直接在MysQL命令提示符里输入就可以输出结果。
会话级别的内存消耗（连接私有内存）[Mysql 单个连接最大占内存，单位 M]：
MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack
也可以用SQL命令：select (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)/1024/1024 as “MYSQL_Service_Connect_Mem”;
直接在MysQL命令提示符里输入就可以输出结果。
MYSQL优化举例
例如一台2G内存的服务器，安装有Nginx、Apache、Php、Mysql等的环境，准备划分128MB~512MB的内存区间给MySQL使用。
首先就可以配置 MYSQL_Service_Mem（ key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size ）这三个参数，保持在128MB左右。
MYSQL_Service_Mem = key_buffer_size （32MB）+ innodb_buffer_pool_size（32MB）+ tmp_table_size（32MB）+ query_cache_size（8MB） + innodb_additional_mem_pool_size（0MB） + innodb_log_buffer_size（8MB）= 112MB
key_buffer_size 参数：（MyISAM）
对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）
怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads ，比例key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下命令查得：show status like ‘key_read%’;
比如查看系统当前key_read和key_read_request值为：
+——————-+———————–+
| Variable_name | Value |
+——————-+———————–+
| Key_read_requests | 28535 |
| Key_reads | 269 |
+——————-+———————–+
可知道有28535个请求，有269个请求在内存中没有找到直接从硬盘读取索引。未命中缓存的概率为：0.94%=269/28535*100%。一般未命中概率在0.1之下比较好，目前已远远大于0.1，证明效果不好。若命中率在0.01以下，则建议适当的修改key_buffer_size值。
innodb_buffer_pool_size 参数：（InnoDB）
主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。假设：12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占用到14.5G的内存。若系统只有16G，而且只运行MySQL，且MySQL只用InnoDB，那么为MySQL开12G，是最大限度地利用内存了。
另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。
怎么才能知道innodb_buffer_pool_size的设置是否合理呢，可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得：show status like ‘Innodb_buffer_pool_read%’;
比如查看当前系统中系统中
+—————————————+———+
| Innodb_buffer_pool_read_requests | 1283826 |
| Innodb_buffer_pool_reads | 519 |
+—————————————+———+
其命中率99.959% =（1283826-519）/1283826*100% 命中率越高越好。
tmp_table_size 参数：
控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定。
通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级分组排序操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
怎么才能知道tmp_table_size的设置是否合理呢，可以通过 Created_tmp_disk_tables / Created_tmp_tables * 100% 计算缓存命中率，并根据命中率来调整 tmp_table_size 参数大小进行优化。值可以用以下命令查得：show global status like ‘created_tmp%’;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
未命中率为：Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，数字越低越好。
query_cache_size 参数：
MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用，对于绝大多数更新较多的库可能不是很适用，比较受限于应用场景，所以AWS也把这个缓存给关了。我觉得这个值默认应该关闭，根据需求调整。
Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size和query_cache_type，前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用Query Cache。query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，只有显示要求才使用query cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲. 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；
怎么才能知道query_cache_size的设置是否合理呢，命中率:(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，通过命令：show status like ‘Qcache_%’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| Qcache_hits | 1892463 |
| Qcache_inserts | 35627 |
+————————-+———–+
命中率 98.17% = 1892463/(1892463 +35627 )*100，命中率越高越好。
innodb_additional_mem_pool_size 参数：
这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多，你需要在这里分配越多的内存。对于一个相对稳定的应用，这个参数的大小也是相对 稳定的，也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存， InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。默认值是 1MB ，当发现错误日志中已经有相关的警告信息时，就应该适当的增加该参数的大小。
自从Msql 5.6.3 开始，就不需要这个参数。
innodb_log_buffer_size 参数：
这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
这个值的大小主要影响到刷磁盘的次数，设置的过小，Buffer容易满，就会增加fsync的次数，设置过大，占用内存。该值默认是8M，个人觉得目前每次提交都会刷buffer，所以除非有大事务的情况，一般buffer不太可能被占满，所以没必要开的很大， 8M应该是满足需求的。
然后再配置每个连接占用内存大小：MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack，计划512MB分配给Mysql使用，刚刚已经分配了112MB的内存，剩下384MB内存，这个时候就需要考虑最大的连接数，因为这个参数对连接内存影响很大，计划最大连接数为128个，那么每个连接的内存就占用3MB左右。
MYSQL_Service_Connect_Mem = read_buffer_size (0.25MB) + read_rnd_buffer_size (0.5MB) + sort_buffer_size (0.5MB) + join_buffer_size (0.25MB) + binlog_cache_size (1MB) + thread_stack (0.25MB) = 2.75MB
那么连接数max_connections可以设置为：384/2.75=139个
read_buffer_size 参数：
是MySql读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
256K适用于512MB内存，1GB内存则可以设置成1M，依次类推即可。
read_rnd_buffer_size 参数：
当以任意顺序读取行时，可以分配随机读取缓冲区，通过该缓冲区读取行，以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
512K使用于 512MB内存，1GB内存则可以设置成1MB，依次类推即可。
sort_buffer_size 参数：
每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的，如果是希望扩大的话，建议在会话层设置，默认值2M。
512K使用于 512MB内存，1GB内存则可以设置成1MB，依次类推即可。
join_buffer_size 参数：
MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描这些操作所用的缓存大小。通常，获取最快连接的方法是增加索引。当不能增加索引的时候，使全连接变快的方法是增大join_buffer_size参数。对于执行全连接的两张表，每张表都被分配一块连接内存。对于没有使用索引的多表复杂连接，需要多块连接内存。通常来说，可以将此参数在全局上设置一个较小的值，而在需要执行大连接的会话中在会话级别进行设置。默认值是256KB。
binlog_cache_size 参数：
类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。默认32K。
如果设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache是不是全局的，是按SESSION为单位独享分配的，也就是说当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache。
设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件。
如何查看设置的binlog_cache_size是否够用，可以使用命令：show status like ‘binlog_%’;
+———————–+———–+
| Variable_name | Value |
+———————–+———–+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 120402264 |
+———————–+———–+
运行情况Binlog_cache_use 表示binlog_cache内存方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache临时文件方式被用上了多少次。Binlog_cache_disk_use现在等于0，表示内存cache是够用的，从来不需要使用到临时文件。
thread_stack 参数：
默认256K，MySQL为每个线程分配的堆栈大小，当线程堆栈太小时，这限制了服务器可以处理的SQL语句的复杂性。这个值一般认为默认就可以应用于大部分场景了,除非必要非则不要动它。
互联网也有类似计算MysqL占用内存的网站：http://www.mysqlcalculator.com/
其他参数优化建议：http://blog.daobidao.com/mysql-optimize-advise.html
