解决问题:(主要是 InnoDB 引擎)
理解 MySQL 部分运行原理?
结合 MySQL 运行原理修改配置文件, 优化 MySQL 服务器?
优化原则:
MySQL 有大量可以修改的参数 -- 但不应该随便去修改. 保证基本的配置正确, 例如 InnoDB 的 Buffer Pool 和日志文件缓存代销, 如果防止出问题, 就设置一下比较安全和稳健的值, 剩下的配置就不用管了.(来自 《高性能 MySQL》第八章)
一次只改变一个设置! 这是测试改变是否有益的唯一方法.
正确的优化方式是通过基准测试迭代优化.
优化思路:
1. 优化 MySQL 服务器的最大连接数.
具体操作: 参考博客 MySQL 查看状态, 连接数, 线程数以及 MySQL 性能监控工具 doDBA 的使用以及优化 https://www.cnblogs.com/mungerz/p/10413457.html
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 左右.
MySQL 优化服务器设置(MySQL 优化配置文件)
来源: http://www.bubuko.com/infodetail-2963781.html