mysql 配置文件_mysql配置文件优化

1、innodb_buffer_pool_size

（1）介绍

InnoDB使用一个缓冲池来保存索引和原始数据，如下图所示：

（2）优缺点

缓冲池的作用可以减少磁盘访问，我们知道内存读写速度比磁盘的读写速度快很多，所以这个参数对mysql性能有很大提升。当然，这里不是越大越好，也要考虑实际的服务器情况。总之，InnoDB严重依赖缓冲池，我们必须为它分配了足够的内存。

更大的缓冲池会使得mysql服务在重启和关闭的时候花费很长时间。

（3）如何配置

如果在一个独立使用的mysql服务器上，这个变量按照流行的经验法则，可以把缓冲池大小设置为服务器内存的约75%~80%。

但是，如果服务器上除了跑mysql服务，还有其他服务也在运行，那么在分配缓冲池空间时，需要减去这部分程序占用的内存、mysql自身需要的内存以及减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分。

2、innodb_log_file_size和innodb_log_files_in_group

（1）介绍

InnoDB使用日志来减少提交事务时的开销。

InnoDB用日志把随机I/O变成顺序I/O。

innodb_log_files_in_group参数控制日志文件数，一般默认为2。mysql事务日志文件是循环覆写的，如下图：

（2）优缺点

当一个日志文件写满后，innodb会自动切换到另一个日志文件，而且会触发数据库的checkpoint，这回导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果innodb_log_file_size设置太小，就会导致innodb频繁地checkpoint，导致性能降低。而如果设置太大，由于事务日志是顺序I/O，大大提高了I/O性能，但是在崩溃恢复InnoDB时，会导致恢复时间变长。

如果InnoDB数据表有频繁的写操作，那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。

（3）如何配置

作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。方法如下：

首先，在业务高峰期，计算出1分钟写入事务日志（redo log）的量，然后评估出一个小时的redo log量：

# 

Log sequence number是写入事务日志的总字节数，通过1分钟内两个值的差值，我们可以看到每分钟有多少KB日志写入到MySQL中

mysql> nopager
PAGER set to stdout
mysql> select (3257550399-3257464291)/1024 as KB;
+---------+
| KB      |
+---------+
| 84.0898 |
+---------+

那么，1小时的事务日志写入量为：84KB * 60 = 5040KB，约为5MB。

由于默认有两个日志文件，在日志组中，两个日志文件的大小是一致的。所以我们可以大约设置innodb_log_file_size=3M。

3、innodb_log_buffer_size

innodb_log_buffer_size可以控制日志缓冲区的大小。

通常不需要把日志缓冲区设置得非常大。推荐的范围是1MB~8MB，一般来说是足够了，MySQL默认是8MB。

4、innodb_flush_log_at_trx_commit

（1）介绍

MySQL支持用户自定义在commit时如何将log buffer中的日志刷到log file中。这种控制通过变量：innodb_flush_log_at_trx_commit 来决定，该变量有：0、1、2三种值，默认为1。注意，这个变量只是控制commit动作是否刷新log buffer到磁盘中。

• 设置为0。把日志缓冲写到日志文件中，并且每秒钟刷新一次，但是事务提交时不做任何事，该设置是3者中性能最好的。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 设置为1。将日志缓冲写入到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的设置（并且是最安全的），该设置能保证不会丢失任何已经提交的事务。
• 设置为2。每次提交时把日志缓冲写到日志文件，但并不刷新。InnoDB每秒钟做一次刷新。

（2）如何配置

日志缓冲必须被刷新到持久化存储（磁盘），以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能，则可以修改该参数来控制日志缓冲刷新的频繁程度。

5、thread_cache_size

（1）介绍

线程缓存保存哪些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的链接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL就会销毁这个线程。

只要MySQL在缓存中还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。

（2）如何配置

thread_cache_size指定了MySQL可以保存在缓存中的线程数。一般不需要配置这个值，除非服务器会有很多连接请求。

# 1G  —> 8  
# 2G  —> 16  
# 3G  —> 32  
# 大于3G  —> 64或更大

6、tmp_table_size和max_heap_table_size

（1）介绍

tmp_table_size：临时表的内存缓存大小，临时表是指sql执行时生成的临时数据表。在优化sql时，应该尽量避免临时表。
max_heap_table_size：该参数也会影响到临时表的内存缓存大小。在增加tmp_table_size的同时，也需要增加max_heap_table_size的大小。

（2）如何配置

可以通过Created_tmp_disk_tables和Created_tmp_tables状态来分析是否需要增加tmp_table_size和max_heap_table_size。

#Created_tmp_disk_tables : 磁盘临时表的数量
#Created_tmp_tables      : 内存临时表的数量
mysql> show global status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 15668 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Created_tmp_tables';
+--------------------+--------+
| Variable_name      | Value  |
+--------------------+--------+
| Created_tmp_tables | 737670 |

7、max_connections

（1）介绍

MySQL的max_connections参数用来设置最大连接数。如果该参数设置太小，会导致出现“Too many connections”的错误。

如果服务器的并发连接请求量比较大，建议提高此值，以增加并行连接数量。但是这个是要建立在机器的性能能支撑的情况下，因为MySQL会为每一个连接提供连接缓冲区，如果并发连接数量太高，会导致消耗内存过多。

（2）如何配置

如何判断max_connections设置的是否合理？

首先，查看最大连接上限：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |

然后，可以查看服务器响应的最大连接数：

mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 3     |

可见，服务器历史最大连接数远远低于mysql服务器允许的最大连接上限。

对于mysql服务器最大连接上限的设置范围，最理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

8、总结

如果使用的是InnoDB，在参数优化的选项里面，最重要的就是如下两个：

• innodb_buffer_pool_size
• innodb_log_file_size