首页 数据库 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脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

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

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

MySQL的角色:Web应用程序中的数据库 MySQL的角色:Web应用程序中的数据库 Apr 17, 2025 am 12:23 AM

MySQL在Web应用中的主要作用是存储和管理数据。1.MySQL高效处理用户信息、产品目录和交易记录等数据。2.通过SQL查询,开发者能从数据库提取信息生成动态内容。3.MySQL基于客户端-服务器模型工作,确保查询速度可接受。

laravel入门实例 laravel入门实例 Apr 18, 2025 pm 12:45 PM

Laravel 是一款 PHP 框架,用于轻松构建 Web 应用程序。它提供一系列强大的功能,包括:安装: 使用 Composer 全局安装 Laravel CLI,并在项目目录中创建应用程序。路由: 在 routes/web.php 中定义 URL 和处理函数之间的关系。视图: 在 resources/views 中创建视图以呈现应用程序的界面。数据库集成: 提供与 MySQL 等数据库的开箱即用集成,并使用迁移来创建和修改表。模型和控制器: 模型表示数据库实体,控制器处理 HTTP 请求。

notepad怎么运行java代码 notepad怎么运行java代码 Apr 16, 2025 pm 07:39 PM

虽然 Notepad 无法直接运行 Java 代码,但可以通过借助其他工具实现:使用命令行编译器 (javac) 编译代码,生成字节码文件 (filename.class)。使用 Java 解释器 (java) 解释字节码,执行代码并输出结果。

解决数据库连接问题:使用minii/db库的实际案例 解决数据库连接问题:使用minii/db库的实际案例 Apr 18, 2025 am 07:09 AM

在开发一个小型应用时,我遇到了一个棘手的问题:需要快速集成一个轻量级的数据库操作库。尝试了多个库后,我发现它们要么功能过多,要么兼容性不佳。最终,我找到了minii/db,这是一个基于Yii2的简化版本,完美地解决了我的问题。

git怎么查看仓库地址 git怎么查看仓库地址 Apr 17, 2025 pm 01:54 PM

要查看 Git 仓库地址,请执行以下步骤:1. 打开命令行并导航到仓库目录;2. 运行 "git remote -v" 命令;3. 查看输出中的仓库名称及其相应的地址。

laravel框架安装方法 laravel框架安装方法 Apr 18, 2025 pm 12:54 PM

文章摘要:本文提供了详细分步说明,指导读者如何轻松安装 Laravel 框架。Laravel 是一个功能强大的 PHP 框架,它 упростил 和加快了 web 应用程序的开发过程。本教程涵盖了从系统要求到配置数据库和设置路由等各个方面的安装过程。通过遵循这些步骤,读者可以快速高效地为他们的 Laravel 项目打下坚实的基础。

laravel安装代码 laravel安装代码 Apr 18, 2025 pm 12:30 PM

要安装 Laravel,需依序进行以下步骤:安装 Composer(适用于 macOS/Linux 和 Windows)安装 Laravel 安装器创建新项目启动服务访问应用程序(网址:http://127.0.0.1:8000)设置数据库连接(如果需要)

MySQL与其他编程语言:一种比较 MySQL与其他编程语言:一种比较 Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

See all articles