高性能MySQL.读书笔记(一)优化服务器设置

MySQL有大量可以修改的参数——但不应该随便去修改。通常只需要把基本的配置项配置正确(大部分情况下只有很少一些参数是真正重要的),应该将更多的时间花在schema的优化、索引,以及查询设计上。
确保基本的配置是正确的,如果碰到了问题,并且问题是由于服务器的某部分导致的,而这恰好可以通过某个配置项解决,那么需要做的就是更改配置。

MySQL基本配置
InnoDB在大多数情况下如果要运行的很好,配置大小合适的缓冲池(Buffer Pool)和日志文件(Log File)是必须的。默认值都太小了。其他所有的InnoDB设置都是可选的。

我们建议,当配置内存缓冲区的时候,宁可谨慎,而不是把它们配置的过大。如果把缓冲池配置得比它可设的值少了20%,很可能只会对性能产生小的影响,也许就只影响几个百分点。如果设置得大了20%,则可能会造成更严重的问题:内存交换、磁盘抖动,甚至内存耗尽和硬件死机。

配置内存使用
可以按下面的步骤来配置内存:
1、确定可以使用的内存上限。
2、确定每个连接MySQL需要使用多少内存,例如排序缓冲和临时表。
3、确定操作系统需要多少内存。包括同一台机器上其他程序使用的内存。
4、把剩下的内存全部给MySQL的缓存,例如InnoDB缓冲池,这样做很有意义。

为缓存分配内存
下面是我们认为大部分情况来说最重要的缓冲:
InnoDB缓冲池(innodb_buffer_pool_size)
InnoDB日志文件(innodb_log_buffer_size)和MyISAM数据的操作系统缓存(key_buffer_size)
MyISAM键缓存(myisam_block_size)
线程缓存(thread_cache_size)
查询缓存(query_cache_size)
无法手工配置的缓存,例如二进制日志和表定义文件的操作系统缓存

InnoDB缓冲池
如果大部分都是InnoDB表,InnobDB缓冲池或许比其他任何东西更需要内存。
如果数据量不大,并且不会快速增长,就没必要为缓冲池分配过多的内存。

很大的缓冲池也会带来一些挑战,例如,预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里,InnoDB关闭时可能会花费较长的时间,因为关闭之前需要把脏页写回数据文件。也可以强制快速关闭,但是重启时就必须多做更多的恢复工作,也就是说无法同时加速关闭和重启两个动作。如果事先知道什么时候需要关闭InnoDB,可以在运行时修改innodb_max_dirty_pages_pct变量,将值改小,等待刷新线程清理缓冲池,然后在脏页数量较少时关闭。可以监控innodb_buffer_pool_pages_dirty状态变量或者SHOW INNODB STATUS来观察脏页的刷新量。

更小的innodb_max_dirty_pages_pct变量值并不保证InnoDB将在缓冲池中保持更少的脏页。他只是控制InnoDB是否可以“偷懒(Lazy)”的阈值。InnoDB默认通过一个后台线程来刷新脏页,并且会合并写入,更高效地顺序写出磁盘。当脏页的百分比超过了这个阈值,InnoDB将快速地刷写脏页,尝试让脏页的数量更低。当事务日志没有足够的空间剩余时,InnoDB也将进入“激烈刷写(Furious Flushing)”模式,这就是大日志可以提升性能的一个原因。

InnoDB事务日志
InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量,只修改每个日志文件的大小即可。
要确定理想的日志文件大小,必须权衡正常数据变更的开销和崩溃需要的时间。如果日志太小,InnoDB将必须做更多的检查点,导致更多的日志写。在极个别情况下,写语句可能被拖累,在日志没有空间继续写入前,必须等待变更应用到数据文件。另一方面,如果日志太大了,在崩溃恢复时InnoDB可能不得不做大量的工作。这可能极大地增加恢复时间。

当InnoDB变更任何数据时,会写一条变更记录到内存日志缓冲区。在缓冲满的时候、事务提交的时候,或者每一秒钟,InnoDB都会刷写缓冲区的内容到磁盘日志文件。如果有大事务,增加日志缓冲区(默认1MB)大小可以帮助减少I/O。变量innodb_log_buffer_size可以控制日志缓冲区的大小。

可以通过检查SHOW INNODB STATUS的输出中LOG部分来监控InnoDB的日志和日志缓冲区的I/O性能,通过观察Innodb_os_log_written状态变量来查看InnoDB对日志文件写出了多少数据。一个好的经验法则是,查看10~100秒间隔的数字,然后记录峰值。

日志缓冲必须被刷新到持久化存储,以确保提交的事务完全被持久化了。如果和持久化相比更在乎性能,可以修改innodb_flush_log_at _trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下:
0
把日志缓冲写到日志文件,并且每秒刷新一次,但是事务提交时不做任何事。
1
将日志缓冲写到日志文件,并且每次事务提交都刷新到持久化存储。
2
每次提交时把日志缓冲写到日志文件,但是并不刷新,InnoDB每秒做一次刷新。
0与2最重重要的不同是,如果MySQL进程“挂了”,2不会丢失任何事务。如果整个服务器“挂了”或者断电了,则还是可能会丢失一些事务。

高性能事务处理需要的最佳配置是把innodb_flush_log_at_trx_commit设置为1且把日志文件放到一个有电池保护的写缓存的RAID券中。这兼顾了安全和速度。事实上,我们敢说任何希望能扛过高负载的产品数据库服务器,都需要这种类型的硬件。
(注:在测试环境可以将innodb_flush_log_at_trx_commit设为0,这个速度同上面=1时的RAID缓存的情况差不多)

InnoDB怎样打开和刷新日志以及数据文件
使用innodb_flush_method选项可以配置InnoDB如何跟文件系统相互作用。它既影响日志文件,也影响数据文件,而且有时候对不同类型的文件的处理也不一样。
下面是一些可能的值:
fdatasync
这是非Windows系统上的默认值:InnoDB用fsync()来刷新数据文件和日志文件。使用fsync()的缺点是操作系统至少会在自己的缓存中缓冲一些数据。理论上,这种双重缓冲是浪费的。因为InnoDB管理自己的缓冲比操作系统能做的更加智能。
O_DIRECT
这个设置并不影响日志文件,它主要是影响数据文件的写操作,不象O_DSYNC标记同时影响读和写。
这个设置依然使用fsync()来刷新文件到磁盘,但是会通知操作系统不要缓存数据,也不要预读。这个选项完全关闭了操作系统缓存,并且使所有的读和写都直接通过存储设备,避免了双重缓冲。
如果使用O_DIRECT选项,通常需要带有写缓存的RAID卡,并且设置为Write-Back策略,因为这是典型的唯一能保持好性能的方法。当InnoDB和实际存储设备之间没有缓冲时使用O_DIRECT,例如当RAID卡没有写缓存时,可能导致严重的性能下降。
O_DSYNC
它使得所有写同步,就是只有数据写到磁盘后写操作才返回,这个选项不影响数据文件。
O_DSYNC和O_DIRECT的不同之处在于O_DSYNC没有禁用操作系统层的缓存。因此,它没有避免双重缓冲,并且它没有使写操作直接操作到磁盘。用了O_DSYNC标记,也还是要在缓冲中写数据,然后发送到磁盘。

InnoDB表空间
Innodb_file_per_table选项让InnoDB为每张表使用一个文件,它在数据字典存储为“表名.idb”的数据。
即使打开Innodb_file_per_table选项,依然需要为回滚日志和其他系统数据创建共享表空间。
我们建议使用Innodb_file_per_table并且为共享表空间设置大小范围,这样可以过得舒服点。
如果有个很大的回滚日志并且表空间因此增长很快,可以强制MySQL减速来使InnoDB的清理线程可以跟得上。可以设置innodb_max_purge_lag变量为一个大于0的值。这个值表示InnoDB开始延迟后面的语句更新数据之前,可以等待被清除的最大的事务数量。例如:事务平均影响1KB的行,并且可以容许表空间里有100M的未清理,那么可以设置这个值为100000。设置innodb_max_purge_lag也会降低性能,但是它的伤害较少。

双写缓冲(Doublewrite Buffer)
双写缓冲是表空间一个特殊的保留区域,在一些连续的块中足够保存100个页。本质上是一个最近写回的页面的备份拷贝。当InnoDB从缓冲池刷新页面到磁盘时,首先把它们写(或者刷新)到双写缓冲,然后再把它们写到其所属的数据区域中。这可以保证每个页面的写入都是原子并且持久的。
有些场景下,双写缓冲确实没必要,例如,你也许想在备库上禁止双写缓冲。此外一些文件系统(例如ZES)做了同样的事,所以没必要再让InnoDB做一遍。可以通过设置innodb_doublewrite为0来关闭双写缓冲。

二进制日志
sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘。默认值是0,意味着MySQL并不刷新,由操作系统自己决定什么时候刷新缓存到持久化设备。如果这个值比0大,它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作(如果autocommit被设置了,每个独立的语句都是一次写,否则就是一个事务一次写)。把它设置为0和1以外的值是很罕见的。
像InnoDB日志文件一样,设置sync_binlog=1并把二进制日志放到一个带有电池保护的写缓存的RAID券,可以极大地提升性能并保证日志的持久化。事实上写和刷新二进制日志缓存其实比InnoDB事务日志要昂贵多了,因为不像InnoDB事务日志(事务日志文件大小是预分配的,而且是group commit),每次写二进制日志都会增加他们的大小,而且在sync_binlog=1的情况下是每个事务都要写一次。这需要每次写入文件系统都更新元信息。所以,设置sync_binlog=1可能比innodb_flush_log_at_trx_commit=1对性能的损害要大的多。

把带有电池保护写缓存的高质量RAID控制器设置为使用写回(Writeback)策略,可以支持每秒数千的写入,并且依然会保证写到持久化存储。数据写到了带有电池的高速缓存,所以即使系统断电它也能存在。但电源恢复时,RAID控制器会在磁盘被设置为可用前,把数据从缓存中写到磁盘。因此,一个带有电池保护写缓存的RAID控制器可以显著地提升性能,这是非常值得投资的。

MyISAM的I/O配置
MyISAM通常每次写操作之后把索引变更刷新到磁盘。如你打算在一张表上做很多修改,那么毫无疑问,批量操作会更块一些。一种办法是用LOCK_TABLES延迟写入,直到解锁这个表。这是个提升性能的很有价值的技巧。

InnoDB并发配置
在高并发场景下,InnoDB的某些方面的性能可能会降低,唯一的办法是限制并发。如果是高版本的MySQL,在大部分场景都不再需要限制并发。
最基本的限制并发的方式是使用innodb_thread_concurrency变量,它会限制一次性可以有多少线程进入内核,0表示不限制,理论上,下面的公式可以给出一个这样的值:并发值 = CPU数量 * 磁盘数量 * 2。


阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页