无忧技术网 - RSS订阅 
无忧技术网

Linux下Mysql 数据库性能调优


作者:[佚名] - 发布:2010-9-4 0:21:15 - 来源:莫建祥的专栏

 硬件准备环境:

  • 硬盘: 16块 SAS 15K RAID5 带512MCache
  • CPU: AMD 4核两颗
  • 内存: 16G

软件环境:

  • 操作系统: RedHat AS4.6 2.6.9-67.ELsmp
  • 数据库:Mysql: 5.0

使用场景:

写操作(插入/查询/删除)密集,而且数据容量远远超过内存的大小(最小是200G以上,甚至更大到1.5T以上), 每秒需要以千为单位的事务操作数据可靠性要求不高,允许丢几条记录(硬件出故障时)

优化方式:

在这种情况下,数据库的Cache基本上命中率是非常低的,对数据库的操作基本上就是对磁盘进行操作, 因此侧重点应该是对磁盘相关优化。

首先是考虑数据库存储引擎,由于涉及插入/删除/查询等操作,而且要支持事务,因此考虑使用InnoDB存储引擎,不考虑MyIsam原因是该存储引擎是表级锁,有数据删除时会非常慢。

设置合适的Mysql 参数,由于有16G内存,因此参数设置如下:

  • innodb_buffer_pool_size=8G
  • innodb_additional_mem_pool_size = 800M
  • innodb_log_buffer_size = 4M
  • innodb_log_file_size = 500M
  • innodb_log_files_in_group = 4

log-bin

因为使用主备复制,因此需要打开Log-Bin,这里就涉及一个sync-bin的概念,缺省情况下,sync-bin 在mysql中是关闭的,但存在一个风险,因为写日志没有刷新到硬盘中去的话,日志是写在操作系统的文件系统里的Cache中,这样若机器断电,那么日志信息将部分丢失。为了减少数据丢失,我们测试了sync-bin=1,sync-bin=5和sync-bin=20,三种情况,sync-bin=1的情况下,丢失数据最坏概率是丢一个事务的数据,但性能非常差;设置sync-bin=5的情况下,性能可以提高1倍左右;设置sync-bin=20时,性能比sync-bin=5又可以提高1倍,设置大于20的值,基本上相差不大,性能提高不了多少,最坏20个事务数据丢失是在我们的允许范围之内,因此设置20是个合理的值,这个值具体取决于你的系统能够最坏允许丢少的事务数据。

若你的系统对数据丢失不于考虑,可以关闭sync-bin,这时数据刷新到硬盘完全取决于操作系统的配置,相关的配置参数有如下:

/proc/sys/vm/dirty_ratio

这个参数控制一个进程在文件系统中的文件系统写缓冲区的大小,单位是百分比,表示系统内存的百分比,表示当一个进程中写缓冲使用到系统内存多少的时候,再有磁盘写操作时开始向磁盘写出数据。增大之会使用更多系统内存用于磁盘写缓冲,也可以极大提高系统的写性能。但是,当你需要持续、恒定的写入场合时,应该降低其数值,一般缺省是 40。更新方法:

echo 30 >/proc/sys/vm/dirty_ratio (或则修改/etc/sysctl.conf文件,增加sys.vm.dirty_ratio=30 重起机器)

/proc/sys/vm/dirty_background_ratio

这个参数控制文件系统的pdflush进程,在何时刷新磁盘。单位是百分比,表示系统总内存的百分比,意思是当磁盘的脏数据缓冲到系统内存多少的时候,pdflush开始把脏数据刷新到磁盘。增大会使用更多系统内存用于磁盘写缓冲,也可以极大提高系统的写性能。但是,当你需要持续、恒定的写入场合时,应该降低其数值,一般缺省是10。

/proc/sys/vm/dirty_writeback_centisecs

Pdflush写后台进程每隔多久被唤醒并执行把脏数据写出到硬盘。单位是 1/100 秒。缺省数值是500,也就是 5 秒。如果你的系统是持续地写入动作,那么实际上还是降低这个数值比较好,这样可以把尖峰的写操作削平成多次写操作。设置方法如下:

echo 200 >/proc/sys/vm/dirty_writeback_centisecs

/proc/sys/vm/dirty_expire_centisecs

这个参数声明Linux内核写缓冲区里面的脏数据多“旧”了之后,pdflush进程就开始考虑写到磁盘中去。单位是 1/100秒。缺省是 30000,也就是 30 秒的数据就算旧了,将会刷新磁盘。对于特别重载的写操作来说,这个值适当缩小也是好的,但也不能缩小太多,因为缩小太多也会导致IO提高太快。建议设置为 1500,也就是15秒算旧。

echo 1500 >/proc/sys/vm/ dirty_expire_centisecs

因此若没有调整这些参数,全部以缺省值,而且关闭sync-bin的话,那么最多丢失的数据是:

5秒种(dirty_writeback_centisecs)之内的,小于1.6G的数据(dirty_background_ratio,16G/10=1.6G)

当然,实际上5秒之内不太可能写1.6G的数据,因此最坏就是5秒钟之内的数据丢失。因此若要关闭sync-bin,又不想丢失太多数据的话,可以通过调整dirty_writeback_centisecs这个参数,如调整到200(2秒),这样最多就丢2秒钟的数据。又可以提高数据的写能力。

Mysql里还有一个参数可以调整,提高数据库的写能力,那就是

innodb_flush_log_at_trx_commit

这个参数默认是1,即每次事务Commit时,都刷新日志,以免数据丢失。因为我们的系统允许丢失少量数据,因此可以把innodb_flush_log_at_trx_commit设置为2,允许丢失一个事务的数据,经测试,发现2可以提高25%左右的性能。

对于文件系统的mount方式,noatime方式也可以提高部分性能(数据库专用的服务器,一般是noatime)

当数据有删除更新操作后,时间长后一般有碎片,导致索引空间不紧凑,占用更多的硬盘空间,因此会导致查询编码,解决办法是定期执行下面的语句:

ALTER TABLE tbl_name ENGINE=INNODB

sort_buffer_size

另外若sql语句中有sort 和group by之类,需要增大sort_buffer_size

这个参数是每客户端连接的,当有sort/group查询时,会分配sort_buffer_size大小的内存,因此若连接很多,则要小心;合适的值可以查看SHOW GLOBAL STATUS里面Sort_merge_passes的信息以及Created_tmp_tables之类信息

责任编辑:liqwei
打印本页】【关闭本页】【返回列表
·上一篇:mysql 编码问题 latin1(iso8859)
·下一篇:mysql sort 性能优化
 文章评分
  • current rating
-5 -4 -3 -2 -1 0 +1 +2 +3 +4 +5
 相关文章
·[Linux]Linux文件系统结构 (2015-01-27)
·[数据库综合]数据库索引浅谈 (2015-01-27)
·[会展培训]在线研讨会:云时代的列式数据库—SybaseIQ15.3新特性 (2011-08-18)
·[NoSQL]Mongo数据库简介 (2010-11-18)
·[MySQL]加速 MySQL 导入导出的方法 (2010-09-24)
 相关评论
 站点最新文章 更多>> 
·[经典影音]萨利机长
·[经典影音]天空之眼
·[管理知识]康奈尔笔记法,提高100%学习效率
·[管理知识]刘强东:我管75000人靠这4张表格
·[管理知识]跟壳牌学HSE管理
·[运营策划]编辑工作内容整理
·[至理名言]奋斗与决定
·[瀚海拾遗]盲人打灯笼之各家论道
·[搞笑段子]中国男足
·[搞笑段子]大爷被采访
 站点浏览最多 更多>> 
·[协议规范]http断点续传原理:http头 Range、…
·[JS/CSS/HTML]HTML 空格的表示符号 nbsp / en…
·[NoSQL]Mongo数据库简介
·[协议规范]什么是SPF记录?如何设置、检测SP…
·[协议规范]图解 HTTPS 通信过程
·[PHP]精选国外免费PHP空间推荐
·[程序综合]常用IP地址查询接口
·[程序综合]什么是 DNS Prefetch ?
·[程序综合]获取客户端IP地址的三个HTTP请求…
·[Linux]/usr 目录的由来