InnoDB 缓存相关优化
innodb_buffer_pool_size
InnoDB存储引擎的缓存机制和MyISAM的最大区别就在于：InnoDB不仅仅缓存索引，同时还会缓存实际的数据。所以InnoDB 存储引擎可以使用更多的内存来缓存数据库的相关信息。
innodb_buffer_pool_size 参数用来设置InnoDB最主要的Buffer（InnoDB Buffer Pool）的大小，缓存用户表及索引数据的最主要缓存空间，对InnoDB 整体性能影响也最大。
InnoDB 的 Buffer Pool 简单设置为整个系统物理内存的 50%~80%之间，具体设置多大要根据实际环境技术。
比如，例如一台单独 MySQL 主机，物理内存 8G，MySQL最大连接数为 500，同时还是用了MyISAM存储引擎。内存整体分析：
系统使用，假设预留800MB
线程独享：最大约为2GB = 500 * （1MB + 1MB + 1MB + 512KB + 512KB），其中sort_buffer_size：1MB；join_buffer_size:1MB；read_buffer_size：1MB；read_rnd_buffer_size：512KB；thread_statck：512KB
MyISAM Key Cache：假设大概 1.5GB
InnoDB Buffer Pool：最大可用是8GB - 800MB - 2GB - 1.5GB = 3.7 GB
查看MySQL实际使用情况
1 | show status like 'innodb_buffer_pool_%'; |
输出结果
1 | Innodb_buffer_pool_dump_status Dumping of buffer pool not started |
pages有1245183，其中free是16383，pages_data是1226754
read_requests是8912846406，物理磁盘请求pool_reads是98002，命中率=(8912846406-98002)/8912846406=0.99998900441054
innodb_log_buffer_size
系统默认 1MB。 Log Buffer 的主要作用就是 缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果系统不是写负载非常高而且以大事务居多的话，8MB 以内大小足够。
查看MySQL实际使用情况：
1 | show status like 'innodb_log_%'; |
输出结果
1 | Innodb_log_waits 0 |
事务与 IO 的关系及优化
InnoDB 修改数据操作，实际上修改的是Buffer Pool中的数据，并不是一个事务提交后就将Buffer Pool中被修改的数据同步到磁盘上，而是通过记录到事务日志中，在连续写入磁盘。
控制 InnoDB 事务日志刷新方式参数：innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit = 0，InnoDB 中的 Log Thread 每隔1秒将log buffer中的数据写入文件，同时还会通知文件系统进行与文件同步的 flush操作，保证数据确实已经写入磁盘。
innodb_flush_log_at_trx_commit = 1，InnoDB 默认设置。每次事务的结束都会出发 Log Thread将Log Buffer中的数据写入文件、并通知文件系统同步文件。这个设置最安全，能够保证不论是 MySQL 崩溃、OS崩溃还是主机断电都不会丢失任何已经提交的数据。
innodb_flush_log_at_trx_commit = 2，每次事务结束的时候将数据写入事务日志，仅仅是调用了文件系统的文件写入操作。而文件系统都是有缓存机制的，所以 Log Thread 的写入并不能保证内容已经写入到物理磁盘完成持久化的动作。文件系统什么时候会将缓存中的数据同步到物理磁盘、文件，Log Thread 就完全不知道，所以，当设置2的时候，MySQL崩溃并不会造成数据的丢失，但是OS崩溃或主机断电后可能丢失的数据量就完全控制在文件上了。