MySQL优化服务器设置（MySQL优化配置文件）

解决问题：（主要是InnoDB引擎）

• 理解MySQL部分运行原理？
• 结合MySQL运行原理修改配置文件，优化MySQL服务器？

 

优化原则：

• MySQL有大量可以修改的参数——但不应该随便去修改。保证基本的配置正确，例如InnoDB的Buffer Pool和日志文件缓存代销，如果防止出问题，就设置一下比较安全和稳健的值，剩下的配置就不用管了。（来自 《高性能MySQL》第八章）

• 一次只改变一个设置！这是测试改变是否有益的唯一方法。
• 正确的优化方式是通过基准测试迭代优化。

 

优化思路：

1. 优化MySQL服务器的最大连接数。

具体操作：参考博客 Mysql查看状态，连接数，线程数以及Mysql性能监控工具doDBA的使用以及优化

 

2. 优化须知（主要是InnoDB）

2.1 优化原理

2.1.1 InnoDB缓冲池（Buffer Pool）

如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓冲索引：它还会缓存行数据、自适应hash、插入缓存、锁，以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之，InnoDB严重依赖缓冲区，你必须确认它分配了足够的内存，竟可能的大。

2.1.2 优化MySQL的I/O行为（以下内容来自《高性能MySQL》8.5小节）（如果不关心原理，可跳过2.1）

对于常见的应用，最重要的一部分内容是InnoDB日志文件大小、InnoDB怎样刷新它的日志缓冲，以及InnoDB怎样执行I/O。

InnoDB事务日志。

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机I/O。InnoDB假设使用的是常规磁盘（机械磁盘），随机I/O比顺序I/O要昂贵得多，因为一个I/O请求需要时间把磁头移动正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB用日志把随机I/O变成顺序I/O。一旦日志安全写到磁盘，事务就持久化了，即使变更还没有写到数据文件。如果一些糟糕的事情发生了（断电），InnoDB可以重放日志并恢复已经提交的事务。

当然，InnoDB最后还是必须把变更写到数据文件，因为日志有固定的大小。InnoDB日志是环形方式写的：当写到日志的尾部，会重新跳转到开头继续写，但不会覆盖还没应用到数据文件的日志记录，因为这样做会清掉已提交的唯一持久化记录。

InnoDB使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。实际上，事务日志把数据文件的随机I/O转换为几乎顺序的日志文件和数据文件I/O。把刷新操作转移到后台使查询可以更快完成，并且缓和查询高峰时I/O系统的压力。

整体的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group两个参数，这对写性能非常重要。日志文件的总大小是每个文件的大小之和。默认情况下，只有两个5MB的文件，总共10MB。对高性能工作来说这太小了。至少需要几百MB或者上GB的日志文件。

InnoDB使用多个文件作为一组循环日志。通常不需要修改默认日志数量，只修改每个日志文件的大小即可。要修改日志文件，需要完全关闭MySQL，将旧的日志文件移到其他地方保存，重新配置参数，然后重启。一定要确保MySQL干净地关闭了，或者还有日志文件可以保证需要应用到数据文件的事务记录，否者数据库就无法恢复了！

通常不需要把日志缓冲区设置得非常大，推荐范围是1MB-8MB，一般来说就足够了，除非要写很多相当大的BLOB记录。较大的日志缓冲区在某些情况下也是有好处的：可以减少缓冲区中空间分配的争用。当配置一台有大点内存的服务器时，有时简单地分配32MB-128MB的日志缓冲。

日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲区刷新的频繁程度。可能的设置如下：

0 ：把日志缓冲写到日志文件，并且每秒钟刷新一次，但是事务提交时不做任何事。

1 ：将日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的（并且是最安全的）设置，改设置能保证不会丢失任何已经提交的事务，除非磁盘或者操作系统是“伪”刷新的。

2 ：每次提交时把日志缓冲写到日志文件，但是并不刷新。InnoDB每秒做一次刷新。0与2最要的不同是（也是为什么2更合适），如果MySQL挂了，2不会丢失任何事务。如果整个服务器“挂了”或者断电了，则还是可能会丢失一些事务。

了解清楚“把日志缓冲写到日志文件”和“把日志刷新到持久化存储”之间的不同是很重要的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到操作系统的内存，并没有真的把数据写到持久化存储。

高性能事务处理需要的最佳配置是把innodb_flush_log_trx_commit设置为1且把日志文件放到一个有电池保护的写缓存的RAID卷中。

 

2.2 优化结果

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 以下配置是优化后的结果
# MySQL服务器最大连接数
max_connections=1000
# MySQL服务器线程缓存大小
thread_cache_size=500

# 日志缓冲刷新的频繁程度
innodb_flush_log_at_trx_commit=2
# InnoDB缓冲池
innodb_buffer_pool_size=10000M
# 每个日志文件的大小
innodb_log_file_size=512M
# 日志缓冲的大小
innodb_log_buffer_size=32M

（如果按照以上配置，Jmeter1000并发，数据连接池1000活跃连接，MySQL的写数据操作能到5000-7000TPS）

（测试结果）如果同样条件把innodb_flush_log_at_trx_commit改成1，TPS会下降3000左右。

 

转载于:https://www.cnblogs.com/mungerz/p/10416742.html