MySQL 内存优化配置

MySQL 最大使用内存

select (@@key_buffer_size+ \
@@innodb_buffer_pool_size+ \
@@tmp_table_size+ \
@@query_cache_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”;

1、key_buffer_size (MyiSAM)

对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）,可通过以下方法查询是否合理

mysql> show status like '%key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 8000  |
| Key_reads         | 5     |
+-------------------+-------+
2 rows in set (0.01 sec)

一共有 Key_read_requests 个索引请求，一共有发生了 Key_reads 次物理IO，

Key_reads/Key_read_requests = 0.1% 以下比较好 

2、innodb_buffer_pool_size（InnoDB）

主要针对InnoDB表性能影响最大的一个参数，可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。可通过以下方法查询是否合理

mysql> show status like '%Innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 1093  |
| Innodb_buffer_pool_reads              | 232   |
+---------------------------------------+-------+
5 rows in set (0.00 sec)

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

InnoDB缓冲池性能 ＝ innodb_buffer_pool_reads / innodb_buffer_pool_read_requests  值越小越好

3、tmp_table_size

控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定。通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级分组排序操作生成的临时表。注意 max_heap_table_size 比 tmp_table_size 小时，则系统会把 max_heap_table_size 的值作为最大的内存临时表的上限。可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。可通过以下方法查询是否合理

mysql> show global status like '%created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Created_tmp_disk_tables / Created_tmp_tables  值越小越好

4、query_cache_size

当query_cache_type为1时，此设置才有效。MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用。

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Qcache_hits：多少次命中，Qcache_inserts：多少次未命中然后插入。Qcache_hits / (Qcache_hits + Qcache_inserts) 命中率越高越好

5、innodb_log_buffer_size 

这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit（日志刷新的频率）参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。

这个值的大小主要影响到刷磁盘的次数，设置的过小，Buffer容易满，就会增加fsync的次数，设置过大，占用内存。该值默认是8M，个人觉得目前每次提交都会刷buffer，所以除非有大事务的情况，一般buffer不太可能被占满，所以没必要开的很大， 8M应该是满足需求的。

---

Mysql 单个连接最大内存

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”;

1、read_buffer_size

是MySql读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

256K适用于512MB内存，1GB内存则可以设置成1M，依次类推即可。

2、read_rnd_buffer_size

当以任意顺序读取行时，可以分配随机读取缓冲区，通过该缓冲区读取行，以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

512K使用于 512MB内存，1GB内存则可以设置成1MB，依次类推即可。

3、sort_buffer_size

每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的，如果是希望扩大的话，建议在会话层设置，默认值2M。

512K使用于 512MB内存，1GB内存则可以设置成1MB，依次类推即可。

4、join_buffer_size

MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描这些操作所用的缓存大小。通常，获取最快连接的方法是增加索引。当不能增加索引的时候，使全连接变快的方法是增大join_buffer_size参数。对于执行全连接的两张表，每张表都被分配一块连接内存。对于没有使用索引的多表复杂连接，需要多块连接内存。通常来说，可以将此参数在全局上设置一个较小的值，而在需要执行大连接的会话中在会话级别进行设置。默认值是256KB。

5、binlog_cache_size

类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。

如果设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache是不是全局的，是按SESSION为单位独享分配的，也就是说当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache。

设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件。可通过以下方法查询是否合理

show status like '%binlog%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_cache_disk_use      | 0     |
| Binlog_cache_use           | 0     |
| Binlog_stmt_cache_disk_use | 0     |
| Binlog_stmt_cache_use      | 0     |
| Com_binlog                 | 0     |
| Com_show_binlog_events     | 0     |
| Com_show_binlogs           | 0     |
+----------------------------+-------+
7 rows in set (0.05 sec)

Binlog_cache_disk_use  查看调整写入磁盘的次数，写入磁盘为0最好

thread_stack 

默认256K，MySQL为每个线程分配的堆栈大小，当线程堆栈太小时，这限制了服务器可以处理的SQL语句的复杂性。这个值一般认为默认就可以应用于大部分场景了,除非必要非则不要动它。