欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2022-04-29 17:07:00
...

以下的环境具备一定的代表性，可以说是中小企业一般配置和工作环境。希望通过本文能让大家理解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内存，因此参数设置如下

    

  1. innodb_buffer_pool_size=8G  
    2. 

  2. innodb_additional_mem_pool_size = 800M,  
    3. 

  3. innodb_log_buffer_size = 4M  
    4. 

  4. innodb_log_file_size = 500M  
    5. 

  5. innodb_log_files_in_group = 4  
    6. 

  6. log-bin 
    7.

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

  1. /proc/sys/vm/dirty_ratio

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

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

  1. /proc/sys/vm/dirty_background_ratio

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

  1. /proc/sys/vm/dirty_writeback_centisecs

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

    

  1. echo 200 >/proc/sys/vm/dirty_writeback_centisecs  
    2. 

  2. /proc/sys/vm/dirty_expire_centisecs 
    3.

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

  1. 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里还有一个参数可以调整，提高数据库的写能力，那就是

  1. innodb_flush_log_at_trx_commit

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

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

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

  1. ALTER TABLE tbl_name ENGINE=INNODB

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

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

相关标签： 实例 详解 Linux MySQL 数据库 性能 调优 方法

上一篇： MySQL删除记录Delete 与Truncate 语句介绍

下一篇： CentOS修改MySql数据库目录datadir_MySQL

推荐阅读