8. 优化服务器设置

8.1 MySQL配置的工作原理

8.1.1 语法、作用域和动态性

8.1.2 设置变量的副作用

key_buffer_size

table_cache_size

thread_cache_size

query_cache_size

read_buffer_size

read_rnd_buffer_size

sort_buffer_size

8.1.4 通过基准测试迭代优化

Linux系统  open_files_limit

8.4 配置内存使用

innodb_buffer_pool

8.4.2 每个连接需要的内存

8.4.3 为操作系统保留内存

至少保留 1-2G，建议是 2G 或者总内存的 5%

8.4.4 为缓存分配内存

InnoDB缓存池

InnoDB日志文件和MyISAM数据的操作系统缓存

MyISAM键缓存

查询缓存

linux有一个innotop工具

8.4.5 InnoDB缓冲池 (Buffer Pool)

InnoDB严重依赖缓冲池

8.4.7 线程缓存

thread_cache_size

8.4.8 表缓存

8.4.9 InnoDB数据字典

8.5 配置mySQL的I/O行为

8.5.1 InnoDB I/O配置

InnoDB事务日志

InnoDB日志用来减少提交事务时的开销，无须每个事务提交时都把缓存池的脏快刷新到磁盘中，这些刷新很多随机I/O

InnoDB用日志把随机I/O变成顺序I/O，一旦日志安全写到磁盘，事务就持久化了。一旦断电，InnoDB可以重放日志并恢复已经提交的事务。

InnoDB使用一个后台线程智能地刷新这些变更好数据库文件。这些线程可以批量组合写入，使得数据写入更顺序，提高效率。

整体的日志文件大小受控于：  innodb_log_file_size 和 innodb_log_files_in_group

当InnoDB变更任何数据时，会写一条更变记录到内存日志缓冲区。在缓冲满的时候，事务提交的时候，或者每一秒钟，InnoDB都会刷写缓存区的内存到磁盘日志文件（这个有点难记，我简单总结一下）

当InnoDB变更任何数据时，满足

1.缓冲满

2.事务提交的时候

3.每一秒

InnoDB都会写缓冲区的内容到磁盘日志文件。

如果有大事务，增加日志缓冲区（默认1MB）大小可以帮助减少I/O，变量 innodb_log_buffer_size 可以控制日志缓冲区的大小，推荐的范围是 1MB-8MB

较大的日志缓冲区在某些情况下也是有好处的：可以减少缓冲区中空间分配的争用。有时候分配 32M-128M的日志缓冲区，还可以帮助避免压力瓶颈，如果有问题，瓶颈一般会表现为日志缓冲 Mutex的竞争

修改 innodb_flush_log_at_trx_commit 变量来控制日志缓冲刷新的频繁程度

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

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

2：每次提交时把日志缓冲写到日志文件，但不刷新。InnoDB每秒种做一次刷新。  0与2最重要的不同事，如果mySQL进程挂了，2不会丢失任何事务，如果整个服务器挂了，则还是可能会丢失一些事务。

上面这个背起来有点头疼于是我就概括的简单记为了：每一个参数值 都会 把日志缓存写到日志文件，然后

0：每秒钟把日志缓存写到日志文件中，事务提交啥也不做。   事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成。而master thread中每秒进行一次重做日志文件的fsync操作

1：                                                        每次事务提交都刷新到持久化（默认且安全）  事务提交时，必须调用一次fsync操作

2：                                                        每次提交时把日志缓冲写到日志文件，但不刷新 。 InnoDB每秒刷新一次。 事务提交时将重做日志写入重做日志文件，仅写入文件系统的缓存，不进行fsync

了解清楚“把日志缓冲写到日志文件”和“把日志刷新到持久化存储”之间的不同很重要。

大部分操作系统，把缓存写到日志只是简单的把数据从InnoDB的内存缓冲转移到操作系统的缓存，也是内存，比没有真的把数据写到持久化存储。

如果mySQL崩溃或电源断电了，设置0和2通常会导致最多一秒的数据丢失.

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

来来来，由于MySQL事务是一个特别重要的知识点，这里补充一下内容：

x 事务的实现

原子性、一致性、持久性 通过 redo log 和 undo log来完成。

redo log 称为重做日志，用来保证事务的 原子性和持久性

undo log 用来保证事务的一致性。

undo redo 不是逆过程， redo undo 的作用可是视为一种恢复操作。 redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。

redo通常是物理日志，记录的是页的物理修改操作。

undo是逻辑日志，根据每行记录进行记录。

x.1 redo

重做日志实现事务的持久性，ACID中的D。包含两部分：一是内存中的重做日志缓存(redo log buffer)，容易丢失的；二是重做日志(redo log file)，是持久的。

InnoDB事务，通过Force Log at Commit机制实现事务的持久化，即当事务提交COMMIT时，必须先将该事务的所有日志写入到重做日志文件。

redo log 保证持久性， undo log帮助事务回滚及MVCC。redo log 基本都是顺序写，而undo log 是随机读写的。

为可确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重写日志文件后，InnoDB存储引擎都需要调用一次fsync操作。

因此重做日志缓冲先写入文件系统缓存，为了确保重做日志写入磁盘，必须进行一次fsync操作。所以磁盘性能决定事务提交的性能，也是数据库的性能。

x.2 重做日志与Binlog的区别 ：

重做日志是在InnoDB存储引擎产生。 Binlog日志是在MySQL数据库的上层产省的，并binnlog仅仅针对于InnoDB存储引擎

 

 

 

 

 

InnoDB表空间

表空间不只是存储表和索引，还保存了回滚日志，插入缓冲，双写缓冲，以及其他内部数据结构。

innodb_file_per_table = 1 设置独立表空间， 设置的缺点： 更差的DROP TABLE性能。

双写缓冲(Doublewrite Buffer)

8.6 配置MySQL并发

8.6.1 InnoDB并发配置

innodb_thread_concurrency 限制一次性可以有多少线程进入内核 , 0表示不限制

8.7 基于工作负载的配置

 

总结一下：

如果是使用InnoDB,最最最最最重要的两个参数：

innodb_buffer_pool_size

innodb_log_file_size