首页 数据库 mysql教程 实例详解Linux下MySQL数据库性能调优方法

实例详解Linux下MySQL数据库性能调优方法

Jun 07, 2016 pm 04:09 PM
linux mysql 实例 性能 数据库 方法 详解 调优

以下的环境具备一定的代表性,可以说是中小企业一般配置和工作环境。希望通过本文能让大家理解Linux下MySQL数据库性能调优方法。51CTO向您推荐《 MySQL数据库入门与精通教程 》。 硬件准备环境: 硬盘: 16块 SAS 15K RAID5 带512MCache CPU: AMD 4核两颗

以下的环境具备一定的代表性,可以说是中小企业一般配置和工作环境。希望通过本文能让大家理解Linux下MySQL数据库性能调优方法。51CTO向您推荐《MySQL数据库入门与精通教程》。

硬件准备环境:

硬盘: 16块 SAS 15K RAID5 带512MCache

CPU: AMD 4核两颗

内存: 16G

软件环境:

操作系统: RedHat AS4.6 2.6.9-67.ELsmp MySQL: 5.0

下面分别根据不同场景进行性能调优:

使用场景A: 写操作(插入/查询/删除)密集,而且数据容量远远超硬件准备环境:

硬盘: 16块 SAS 15K RAID5 带512MCache

CPU:    AMD 4核两颗

内存: 16G

软件环境

操作系统: RedHat AS4.6 2.6.9-67.ELsmp

MySQL: 5.0

下面分别根据不同场景进行性能调优:

使用场景A:

写操作(插入/查询/删除)密集,而且数据容量远远超过内存的大小(最小是200G以上,甚至更大到1.5T以上), 每秒需要以千为单位的事务操作

数据可靠性要求不高,允许丢几条记录(硬件出故障时)

优化方式:

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

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

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

<ol class="dp-sql">
<li class="alt"><span><span>innodb_buffer_pool_size=8G  </span></span></li>
<li><span>innodb_additional_mem_pool_size = 800M,  </span></li>
<li class="alt"><span>innodb_log_buffer_size = 4M  </span></li>
<li><span>innodb_log_file_size = 500M  </span></li>
<li class="alt"><span>innodb_log_files_in_group = 4  </span></li>
<li><span>log-bin </span></li>
</ol>
登录后复制

因为使用主备复制,因此需要打开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,这时数据刷新到硬盘完全取决于操作系统的配置,相关的配置参数有如下:

<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_ratio </span></span></li></ol>
登录后复制

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

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

<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_background_ratio </span></span></li></ol>
登录后复制

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

<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_writeback_centisecs </span></span></li></ol>
登录后复制

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

<ol class="dp-xml">
<li class="alt"><span><span>echo 200 </span><span class="tag">></span><span>/proc/sys/vm/dirty_writeback_centisecs  </span></span></li>
<li><span>/proc/sys/vm/dirty_expire_centisecs </span></li>
</ol>
登录后复制

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

<ol class="dp-xml"><li class="alt"><span><span>echo 1500 </span><span class="tag">></span><span>/proc/sys/vm/ dirty_expire_centisecs </span></span></li></ol>
登录后复制

因此若没有调整这些参数,全部以缺省值,而且关闭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里还有一个参数可以调整,提高数据库的写能力,那就是

<ol class="dp-xml"><li class="alt"><span><span>innodb_flush_log_at_trx_commit </span></span></li></ol>
登录后复制

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

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

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

<ol class="dp-sql"><li class="alt"><span><span class="keyword">ALTER</span><span> </span><span class="keyword">TABLE</span><span> tbl_name ENGINE=INNODB </span></span></li></ol>
登录后复制

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

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


本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

deepseek网页版入口 deepseek官网入口 deepseek网页版入口 deepseek官网入口 Feb 19, 2025 pm 04:54 PM

DeepSeek 是一款强大的智能搜索与分析工具,提供网页版和官网两种访问方式。网页版便捷高效,免安装即可使用;官网则提供全面产品信息、下载资源和支持服务。无论个人还是企业用户,都可以通过 DeepSeek 轻松获取和分析海量数据,提升工作效率、辅助决策和促进创新。

如何修复 MySQL 8.4 上的 mysql_native_password 未加载错误 如何修复 MySQL 8.4 上的 mysql_native_password 未加载错误 Dec 09, 2024 am 11:42 AM

MySQL 8.4(截至 2024 年的最新 LTS 版本)中引入的主要变化之一是默认情况下不再启用“MySQL 本机密码”插件。此外,MySQL 9.0完全删除了这个插件。 此更改会影响 PHP 和其他应用程序

deepseek怎么安装 deepseek怎么安装 Feb 19, 2025 pm 05:48 PM

DeepSeek的安装方法有多种,包括:从源码编译(适用于经验丰富的开发者)使用预编译包(适用于Windows用户)使用Docker容器(最便捷,无需担心兼容性)无论选择哪种方法,请仔细阅读官方文档并充分准备,避免不必要的麻烦。

BITGet官方网站安装(2025新手指南) BITGet官方网站安装(2025新手指南) Feb 21, 2025 pm 08:42 PM

BITGet 是一款加密货币交易所,提供各种交易服务,包括现货交易、合约交易和衍生品。该交易所成立于 2018 年,总部位于新加坡,致力于为用户提供安全可靠的交易平台。BITGet 提供多种交易对,包括 BTC/USDT、ETH/USDT 和 XRP/USDT。此外,该交易所还在安全性和流动性方面享有盛誉,并提供多种功能,如高级订单类型、杠杆交易和 24/7 全天候客户支持。

欧易okx安装包直接进 欧易okx安装包直接进 Feb 21, 2025 pm 08:00 PM

欧易 OKX,全球领先的数字资产交易所,现推出官方安装包,提供安全便捷的交易体验。欧易 OKX 安装包无需通过浏览器访问,可直接在设备上安装独立应用程序,为用户打造稳定高效的交易平台。安装过程简便易懂,用户只需下载最新版本安装包,按照提示一步步操作即可完成安装。

gate.io安装包免费拿 gate.io安装包免费拿 Feb 21, 2025 pm 08:21 PM

Gate.io是一款受欢迎的加密货币交易所,用户可通过下载其安装包并安装在设备上使用。获取安装包步骤如下:访问Gate.io官方网站,点击“下载”,选择对应操作系统(Windows、Mac或Linux),将安装包下载至计算机。安装过程中建议暂时禁用杀毒软件或防火墙,确保安装顺利。完成后,用户需创建Gate.io账户以开始使用。

欧易交易所下载官方入口 欧易交易所下载官方入口 Feb 21, 2025 pm 07:51 PM

欧易,又称OKX,是一个全球领先的加密货币交易平台。文章提供了欧易官方安装包的下载入口,方便用户在不同设备上安装欧易客户端。该安装包支持 Windows、Mac、Android 和 iOS 系统,用户可根据自己的设备类型选择相应版本下载。安装完成后,用户即可注册或登录欧易账户,开始交易加密货币和享受平台提供的其他服务。

gate.io官网注册安装包链接 gate.io官网注册安装包链接 Feb 21, 2025 pm 08:15 PM

Gate.io 是一款备受赞誉的加密货币交易平台,以其广泛的代币选择、低廉的交易费用和用户友好的界面而闻名。凭借其先进的安全功能和优秀的客户服务,Gate.io 为交易者提供一个可靠且便利的加密货币交易环境。想要加入 Gate.io 的行列,欢迎点击提供的链接下载官方注册安装包,开启您的加密货币交易之旅。

See all articles