MySQL服务器参数优化

转载 2016年05月30日 16:37:20
配置mysql内存步骤
1 确定可以使用的内存上限
2 确定每个连接mysql需要多少内存,例如排序缓冲和临时表
相对于最坏情况考虑,更好的是观察服务器在真实压力的情况下使用了多少内存,可以观察top命令的virit列
3 为操作系统保留内存,至少保留1-2G,建议2G和5%中的较大者
4 为缓存分配内存
4.1 innodn缓冲池
4.2 innodb日志文件盒myisam的操作系统缓存
4.3 myisam的键缓存   key_buffer_size   myisam只缓存索引  不缓存数据
4.4 查询缓存
4.5 其他
如果只使用innodb引擎,则只需分配少量的myisam(内部表使用)


缓冲池太大引发的问题:

预热和关闭花费时间长。如果脏页多,则关闭慢,因为要把脏页写到数据文件,强行关闭会导致重启做更多的恢复操作,可以通过减少innodb_max_dirty_pages_pct的值,并用innotop观察在脏页数量少的情况下关闭服务器


预热问题解决:
1 可以使用percona server的功能来重载缓冲池的页,从而节省时间,5.6已经有类似功能,这对复制有好处,因为单线程复制导致备库需要额外的预热时间。
2 或者比较暴力的方法,重启后立刻进行全表扫描或者索引扫描,把索引载入缓冲池。可以通过设置init_file来实现该功能:把sql放到一个文件中,然后当启动时来执行,文件名必须在init_file选项中指定




myisam配置: 
key_buffer_size   myisam只缓存索引  不缓存数据   可以设置多个
key_block_size  键缓存块大小   设置为和操作系统的页大小一样
thread_cache_size 线程缓存,指定了可以保持在缓存中的线程数,除非服务器有很多请求才需要设置,一般根据threads_connected 的值设置,前者为100  后者设为20;前者为700  后者设为200
table_cache  表缓存  innodb忽略之


innodb事务日志原理:
innodb使用日志来减少提交事务时的开销,因为在日志中已经记录了事务,就无需在每个事务提交时把缓冲池的脏块刷新到磁盘中。事务修改的数据和索引会随机映射到表空间的随机位置,所以刷新这些变更到磁盘需要很多的随机IO。而innodb用日志把这些随机IO变成了顺序IO,一旦日志安全写到磁盘,事务就持久化了,即使变更还没写到数据文件。如果中途断电,也可以通过重放日志并且恢复已经提交的事务。当然innodb最后必须把变更写到数据文件,因为日志有固定的大小,它的日志是环形方式写入的,但不会覆盖还没写到数据文件的记录。innodb通过一个后台线程智能刷新这些变更到数据文件,这个线程可以批量组合写入,所以使得数据写入更顺序,也就是事务日志把数据文件的随机Io转换成几乎顺序的日志文件盒数据文件IO。

事务日志的主要配置:
主要受控于innodb_log_file_size和innodb_log_files_in_group两个参数,这对写性能非常重要!
默认情况有两个事务日志文件,通常不需要修改日志数量,只修改每个日志文件大小即可!

innodb_log_file_size大小设置需要权衡正常数据变更开销和崩溃恢复时间!
关于崩溃恢复时间:假设1T数据,16G缓冲池,128M日志文件,缓冲池有很多脏页并且均匀分布在1T数据中,则恢复时间需要很长!因为innodb必须从头到尾扫描日志,仔细检查数据文件,还需要应用变更到磁盘;但如果几百M数据被频繁更改则恢复时间会很快。

当innodb变更任何数据时,会写入一条变更记录到内存日志缓冲区,当缓冲区满的时候,事务被提交的时候,过了1秒钟的时候,innodb都会刷写缓冲区内容到磁盘日志文件。

innodb_log_buffer_size:innodb日志缓冲区大小,通常1-8M足够,如果有长事务,blob写入可以适当增大。也可以简单设置成32M-128M。
日志文件大小设置:几十几百M到几个G不等。通过show innodb status的写入峰值设定,比如峰值100kb/s,则设置为256M足以,因为它大概能存放2560秒的日志记录,通常应该设置为能够容纳1个小时的活动内容。

innodb_flush_log_at_trx_commit:

0时日志缓冲写到日志文件,每秒刷写一次,提交时不做任何事;

1 日志缓冲写到日志文件,并每次事务提交都刷新到持久化设备,这是默认并且最安全的设置;

2 每次提交把日志缓冲写到日志文件,但不刷新,每秒钟一次刷新。

一般设置为1并且将日志文件放到一个有电池保护的raid卷中!


innodb_flush_method:innodb怎么打开和刷新日志以及数据文件,如何跟文件系统相互作用
fdatasync:刷新文件的数据,但不包括元数据,有时会导致数据损坏
fsync:刷新文件的数据和元数据,但会导致双重缓冲,操作系统层的缓冲没有必要
o_direct:使用fsync刷新,但会通知操作系统不需要缓冲数据,避免双重缓冲,建议使用这个值

表空间文件存放在不同的磁盘没用,因为一次轮流填充满了才下一个,raid控制器才是王道!

innodb_doublewrite:innodb使用双写缓冲避免页没写完整所导致的数据损坏,当一个磁盘写操作不能完整时,比如16k的页只有一部分写入磁盘(系统崩溃等原因),而双写缓冲是表空间的一个特殊区域,在一个连续块中保存了100页,本质上是一个最近写回的页面的备份拷贝!当innodb从缓冲池刷新到磁盘时,首先把他们写入到双写缓冲,然后再写到所属的数据区域中。

一般在备库上没比要开双写缓冲,此外一些ZFS文件系统做了同样的事,也没必要开!



其他相对重要的配置:

expire_logs_days:若干天后清除旧日志


innodb高级配置:在5.6中已经基本不用限制并发,限制方式为innodb_thread_concurrency代表一次性可以有多少线程进入内核,0代表不限制。理论上并发值=cpu数量*磁盘数量*2,还可以使用缓冲池来限制并发数

innodb_autoinc_lock_mode:高并发下自增主键的设置
innodb_buffer_pool_instances:高负载下可将缓冲池切分成多段,可分散工作压力
innodb_read_io_threads和innodb_write_io_threads:多少个后台线程可以被io使用,默认为4,可设置成可提供io能力的磁盘数量
innodb_old_blocks_time:控制内部缓冲池LRU链表从年轻到年老的必须的毫秒数,默认0毫秒太小
innodb_read_io_threads和innodb_write_io_threads:默认为4,如果cpu是2颗8核的,则均设置为8;如果读比写多,read为10,write为6.这两个参数可充分利用cpu多核的处理能力
innodb_io_capacity:脏页刷新速度,解决更新量大的情景,设置大小取决于硬盘的读写次数;简单硬盘200,raid10设2000,ssd设5000


高并发下哈希索引会引起re_latch,show engine innodb status 如果看到很多wait或哈希使用率不高,可关闭自适应哈希索引


innodb_stats_on_metadata:关闭元数据统计
innodb_old_blocks_pct:控制缓冲区非热点区的百分比
innodb_lock_wait_timeout:innodb锁等待时间


通用复制配置
skip_name_resolve:禁用dns
read_only:只读
skip_slave_start:阻止mysql试图自动启动复制
slave_net_timeout:控制备库发现跟主库的连接失败并且需要重连之前的等待时间,最好设置为1分钟或更短
sync_master_info,sync_relay_log,sync_relay_log_info:解决备库中不把它们的状态文件同步到磁盘的问题,所以服务器崩溃后不知道复制的位置实际上在主库是哪个位置。通过设置这些选项解决上述问题,但会有额外代价,如果出现备库延迟,则关闭他们。可用percpna server中打开innodb_ovwewrite_relay_log_info来存储复制的位置。
relay_log_recovery:当从库宕机后假如relay-log损坏,导致一部分中继日志没有处理,则自动放弃所有未执行的中继日志,并且重新从master上获取日志,这样保证relay-log的完整性,默认关闭,建议开启。
innodb_support_xa:支持xa事务,保证redo log于binlog一致,保障主从数据一致性

sync_binlog:控制刷新二进制日志到磁盘,0代表不刷新,由操作系统决定什么时候刷新缓存到持久化设备,这时崩溃后可能导致二进制日志没有同步事务数据,还可以轻易导致复制中断!


和innodb事务日志一样,把二进制日志放在一个带电池保护的写缓存的raid卷,可极大提高性能!

相关文章推荐

MYSQL性能优化之Mysql服务器参数

不同操作系统,mysql读取配置文件的位置顺序可能是不同的MySQL配置参数根据作用域:分为全局参数和会话参数(动态调整)会话参数在没有单独指定的时候,会使用相同名称的全局参数做为默认值; 有的全局...

snmpget请求获取服务器参数(snmpv3) 并连接MySQL数据库

完整的项目代码: 1、连接MySQL数据库 package mysnmp; import java.sql.Connection; import java.sql.DriverManager;...

MySQL数据库服务器端核心参数详解和推荐

http://www.mysqlops.com/2011/10/26/mysql-variables-one.html http://www.mysqlops.com/2011/10/26/mysq...

MySQL服务器my.cnf参数说明

#cat my.cnf # MySQL client library initialization. [client] port            = 3306 socket       ...
  • celte
  • celte
  • 2014年07月31日 20:48
  • 594

优化 WebLogic 服务器性能参数

  • 2008年12月29日 15:11
  • 87KB
  • 下载

MySQL Innodb 参数详解与优化实践

  • 2016年12月12日 14:40
  • 692KB
  • 下载

MySQL帮助-help功能:登陆mysql服务器所需的相关参数选项(5.6.27)

help功能0.2 [root@rhel6 ~]# /usr/local/mysql/bin/mysql --help /usr/local/mysql/bin/mysql  Ver 14.14 D...

mysql参数及其优化

  • 2017年10月11日 16:50
  • 17KB
  • 下载

MySQL之——MySQL5.6 my.cnf 参数说明(附上自己服务器上真实配置文件)

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始化的时...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL服务器参数优化
举报原因:
原因补充:

(最多只允许输入30个字)