mysql:性能调优

最新推荐文章于 2024-08-31 15:52:24 发布
最新推荐文章于 2024-08-31 15:52:24 发布
阅读量506 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangdamingll/article/details/107943977
版权

mysql性能调优

mysql性能调优设计到方方面面，我在这里就抛砖引玉

一、CPU选择

1. 选择标准

OLTP需要IO密集型操作 OLAP是CPU密集型操作。所以为了支持更大内存,最好选择支持64位的多核CPU。

2. 调优方式

可以利用CPU多核特性,增大读写线程的个数.

mysql> show variables like 'innodb_%io_threads' ;
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+

二、内存选择

内存的大小是最能直接反映数据库的性能。通过之前各个章节的介绍，已经了解到InnoDB存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能。

1. 选择标准

应该在开发应用前预估“活跃”数据库的大小是多少，并以此确定数据库服务器内存的大小。当然，要使用更多的内存还必须使用64位的操作系统。

当缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能。

2. 调优方式

(1) 提前预估“活跃”数据库的大小
(2) 对预估值进行压测

这里需要判断当前数据库的内存是否已经达到瓶颈。

可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB存储引擎的缓冲池的命中率不应该小于99%。如果小于99%，考虑调大buffer pool size。

mysql> SHOW GLOBAL STATUS LIKE 'innodb%read%';
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Innodb_buffer_pool_read_ahead_rnd     | 0       |
| Innodb_buffer_pool_read_ahead         | 0       |
| Innodb_buffer_pool_read_ahead_evicted | 0       |
| Innodb_buffer_pool_read_requests      | 2412    |
| Innodb_buffer_pool_reads              | 357     |
| Innodb_data_pending_reads             | 0       |
| Innodb_data_read                      | 6902272 |
| Innodb_data_reads                     | 496     |
| Innodb_pages_read                     | 356     |
| Innodb_rows_read                      | 15      |
+---------------------------------------+---------+

在这里插入图片描述

三、硬盘选择

1. 传统机械硬盘 VS 固态硬盘

(1). 特点
  • 传统机械硬盘:由于磁头需要旋转和定位,因此顺序访问的速度要远高于随机访问。
  • 固态硬盘:读写速度是非对称的。读取速度要远快于写入的速度。

2. 调优方式

(1) 考虑innodb_io_capacity参数

innodb_io_capacity:用来当刷新脏数据时，控制MySQL每秒执行的写IO量。
如果是固态硬盘，考虑适当增大innodb_io_capacity,充分利用固态硬盘带来的高IOPS特性。

(2) 考虑关闭关闭邻接页的刷新
  • 工作原理:当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。
  • 优点:通过AIO可以将多个IO写入操作合并为一个IO操作,故该工作机制在传统机械磁盘下有着显著的优势。

传统机械键盘要开启该特性，固态硬盘可以选择关闭邻接页的刷新，同样可以为数据库的性能带来一定效果的提升。

四、合理地设置RAID

1. RAID定义

RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。

2. RAID的作用

  • 增强数据集成度
  • 增强容错功能
  • 增加处理量或容量

3. RAID的组合方式

(1) 常见的组合方式

常见的RAID组合方式可分为RAID 0、RAID 1、RAID 5、RAID 10和RAID 50等。

(2) 调优方式

对于数据库应用来说，RAID 10是最好的选择，它同时兼顾了RAID 1和RAID 0的特性。
优点:读取与写入速度快,有镜像备份,可靠性高
缺点:当一个磁盘失效时，性能可能会受到很大的影响，因为条带（strip）会成为瓶颈。

在这里插入图片描述

4. RAID Write Back功能

(1) 定义

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中，并把它们安排到后面再执行。

(2) 优点

不用等待物理磁盘实际写入的完成，因此写入变得更快了。对于数据库来说，这显得十分重要。

(3) 调优方式

视情况开启RAID Write Back功能。

当操作系统或数据库关机时，Write Back功能可能会破坏数据库的数据。这是由于已经写入的数据库可能还在RAID卡的缓存中，数据可能并没有完全写入磁盘，而这时故障发生了。为了解决这个问题，目前大部分的硬件RAID卡都提供了电池备份单元（BBU，Battery Backup Unit），因此可以放心地开启Write Back的功能。不过我发现每台服务器的出厂设置都不相同，应该将RAID设置要求告知服务器提供商，开启一些认为需要的参数。

如果没有启用Write Back功能，那么在RAID卡设置中显示的就是Write Through。Write Through没有缓冲写入，因此写入性能可能不是很好，但它却是最安全的写入。

五、操作系统的选择

使用64位的操作系统，并且使用64位mysql。

六、文件系统的选择

每个操作系统都默认支持一种文件系统并推荐用户使用，如Windows默认支持NTFS，Solaris默认支持ZFS。而对于Linux这样的操作系统，不同发行版本默认支持的文件系统各不相同，有的默认支持EXT3，有的是ReiserFS，有的是EXT4，有的是XFS。
虽然不同特性的文件系统有很多，但是在实际使用过程中从未感觉到文件系统的性能差异有多大。
文件系统可提供的功能也许是DBA需要关注的，例如ZFS文件系统本身就可以支持快照，因此就不需要LVM这样的逻辑卷管理工具。此外，可能还需要知道mount的参数，这些参数在每个文件系统中可能有所不同。

七、基准测试工具的选择

1. 基准测试工具作用

基准测试工具可以用来对数据库或操作系统调优后的性能进行对比。

2. sysbench

(1) 介绍

sysbench是一个模块化的、跨平台的多线程基准测试工具，主要用于测试各种不同系统参数下的数据库负载情况。
官网地址

(2) 功能

它主要包括以下几种测试方式：

  • CPU性能
  • 磁盘IO性能
  • 调度程序性能
  • 内存分配及传输速度
  • POSIX线程性能
  • 数据库OLTP基准测试
(3) 安装
//这里以ubuntu 18.04 为例
apt search sysbench
apt install sysbench
sysbench --version
(4) 测试磁盘
//查看帮助
shell > sysbench fileio help
//sysbench的fileio测试需要经过prepare、run和cleanup三个阶段。prepare是准备阶段，生产需要的测试文件，run是实际测试阶段，cleanup是清理测试产生的文件。
//准备:准备16个文件、总大小2GB的fileio测试：
shell > sysbench fileio --file-num=16 --file-total-size=2G prepare
//运行:测试的最大随机读取请求是100000000次，如果在180秒内不能完成，测试即结束。
shell > sysbench fileio --file-total-size=2G --file-test-mode=rndrd --max-time=180 --max-requests=100000000 --num-threads=16 --file-num=16 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384  run
//删除测试文件
shell > sysbench fileio --file-num=16 --file-total-size=2G  cleanup
(4) 测试MySQL数据库的OLTP性能

需要经历prepare、run和cleanup阶段。prepare阶段会根据选项产生一张指定行数的表，默认表在sbtest架构下，表名为sbtest（sysbench默认生成表的存储引擎为InnoDB）.

//查找相关脚本
shell > find / -name "*" |grep sysbench|grep -E *.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/select_random_points.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/bulk_insert.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/select_random_ranges.lua
/usr/share/sysbench/oltp_point_select.lua

//oltp_read_write.lua 脚本使用
//1. 查看help
shell > sysbench --help
shell > sysbench oltp_read_write help

//2. 首先创建sysbench所需数据库sbtest(这是sysbench默认使用的库名，必须创建测试库)
shell> mysql -uroot -p
mysql> create database sbtest;

//3. 准备数据：--tables=10表示创建10个测试表，--table_size=100000表示每个表中插入10W行数据
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000  prepare

//4. 测试 
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000  run

//5. 删除测试数据(没有删除测试数据库)
shell > sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=密码 --db-driver=mysql --tables=10 --table_size=100000 cleanup
mysql> drop database sbtest;

//6. 测试结果
SQL statistics:
    queries performed:
        read:          35098   # 执行的读操作数量
        write:         10028   # 执行的写操作数量
        other:         5014    # 执行的其它操作数量
        total:         50140
    transactions:      2507   (124.29 per sec.)    # 执行事务的平均速率
    queries:           50140  (2485.82 per sec.)   # 平均每秒能执行多少次查询
    ignored errors:    0      (0.00 per sec.)
    reconnects:        0      (0.00 per sec.)

General statistics:
    total time:                  20.1694s     # 总消耗时间
    total number of events:      2507         # 总请求数量(读、写、其它)

Latency (ms):
         min:                            2.32
         avg:                           32.13
         max:                          575.78
         95th percentile:              118.92    # 采样计算的平均延迟
         sum:                        80554.96

Threads fairness:
    events (avg/stddev):           626.7500/2.49
    execution time (avg/stddev):   20.1387/0.04

3. tpcc-mysql

(1) 介绍

官网地址

TPC（Transaction Processing Performance Council，事务处理性能协会）是一个用来评价大型数据库系统软硬件性能的非盈利组织。TPC-C是TPC协会制定的，用来测试典型的复杂OLTP（在线事务处理）系统的性能。目前在学术界和工业界普遍采用TPC-C来评价OLTP应用的性能。

TPC-C的性能度量单位是tpmC，tpm是transaction per minute的缩写，C代表TPC的C基准测试。该值越大，代表事务处理的性能越高。

tpcc-mysql是开源的TPC-C测试工具，该测试工具完全遵守TPC-C的标准。

(2) 安装
//安装mysql-dev 开发环境(ubuntu 18.04)
shell > apt install libmysqlclient-dev
shell > git clone https://github.com/Percona-Lab/tpcc-mysql
shell > cd src
shell > make
(3) 组成

tpcc-mysql由以下两个工具组成。

  • tpcc_load：根据仓库数量，生成9张表中的数据。
  • tpcc_start：根据不同选项进行TPC-C测试。
(4) 使用
//1. 创建库表
shell> mysql -uroot -p
mysql> create database tpcc1000;
mysql> use tpcc1000
mysql> source ./create_table.sql
mysql> source add_fkey_idx.sql

//2. load 数据
shell> ./tpcc_load --help
选项[warehouse]意为指定测试库下的仓库数量
选项[part]为只创建数据到[part]对应的表中
选项[min_wh]、[max_wh]为min_wid max_wid

//-w 10 指建立的仓库数量
//单进程加载(可选)
shell> ./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p密码 -w 10
//并发加载(可选,注意可能要修改脚本中的一些内容)
shell> ./load.sh tpcc1000 10


//3. 压测
shell> ./tpcc_start --help
-w warehouse 仓库；
-c 连接数；
-r warmup_time:指定预热时间，以秒为单位，默认是10秒，主要目的是为了将数据加载到内存；
-l running_time:指定测试执行时间，以秒为单位，默认是20秒；
-i report_interval:指定生产报告的时间间隔，默认是10秒，我这里设置了20秒；
-f report_file:将测试中各项操作的记录输出到指定文件内保存；
-t trx_file:输出更详细的操作信息到指定文件内保存；

shell> ./tpcc_start -h 127.0.0.1 -p 3306 -d tpcc1000 -u root -p 密码  -w 10 -c 10 -r 100 -l 300 -i 20
shell> ./tpcc_start -h 127.0.0.1 -p 3306 -d tpcc1000 -u root -p 密码 -w 10 -c 10 -r 100 -l 300 -i 20 -f ./tpcc_mysql.log -t ./tpcc_mysql.rtx

以下为本次测试结果数据截图(我的云服务器比较垃圾)
在这里插入图片描述

A鱼翔浅底A
关注
专栏目录
MySQL管理之道：性能调优、高可用与监控.docx
09-13
MySQL 管理之道：性能调优、高可用与监控 MySQL 管理之道是 MySQL 数据库管理员的重要指南。本节将对 MySQL 管理之道的性能调优、高可用和监控进行详细的讲解。 性能调优是 MySQL 管理之道的关键环节。性能调优...
MySQL常见的性能优化方法技巧以及示例
计算机程序定制、毕业项目
09-16 587
MySQL常见的性能优化方法技巧以及示例
【MySQL】性能（压力）测试工具sysbench和tpcc的安装和使用
Stephen_Daa的博客
04-03 1497
因为在国内，可能****（下不了），我是用了香港的服务器下，很快。然后直接用Xtfp传输过来。
性能测试之Mysql数据库调优
最新发布
2301_76643199的博客
08-31 895
前提：无监控不调优，对于mysql性能的监控前几天有文章提到过，有兴趣的朋友可以去看一下1、我们在监控图表中关注的大概有这么几个：CPU、内存、连接数、io读写时间、io操作时间、慢查询、系统平均负载以及memoryOver2、介绍下Grafana模板中各性能指标的意思这个是Mysql数据库的连接数这个图标表示了慢查询上图就是Mysql数据库的缓存区，展示了最大缓存以及已使用缓存等数据3、性能分析一般在产生Mysql瓶颈的时候往往伴随着的是。
linux 时钟频率_针对MySQL的Linux性能调优技巧
weixin_39979159的博客
12-04 212
原文来自Percona工程师：Linux performance tuning tips for MySQL为了方面阅读，我没依照原文按行逐句的进行翻译。另外，我自己的扩充了一下基础知识点，很多知识点也是我第一次去学习，翻阅了一些资料。原文中对Linux系统参数的优化主要分为文件系统、内存与swap、CPU三方面。文件系统(Filesystem)•1)使用ext4或者xfs文件系统，mount选项...
解锁MySQL性能瓶颈！超实用的10种优化方法大揭秘
Lion_Long的博客
07-13 1万+
本文涵盖了索引设计、SQL查询优化、缓存配置、并发控制等多个方面。读者可以通过阅读该文章，了解如何有效地解决MySQL性能问题，并提升系统的响应速度和稳定性。 以上仅为MySQL性能监控和调优的一些常用方法，实际应用中还需要结合具体场景和实际需求来选择和优化相应的策略。建议在进行性能优化前，先对数据库系统进行全面的评估和分析，并考虑合适的监控工具和方法。
MySQL调优之服务器参数优化实践
小小默：进无止境
03-01 3069
**关联博文：** [MySQL中常见的优化策略详解](https://blog.csdn.net/J080624/article/details/53199410) 接上文[MySQL中常见的优化策略详解](https://blog.csdn.net/J080624/article/details/53199410)后，我们继续学习MySQL自身配置的优化。本文是基于Innodb引擎。 目前来说，InnoDB是为MySQL处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库
MySQL数据库优化的八种方式(经典必看)
热门推荐
repoman的博客
02-16 3万+
MySQL/Oracle数据库优化总结（非常全面） 置顶2017年08月21日 21:05:30 阅读数：8442 MySQL数据库优化的八种方式(经典必看) 引言： 关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂 偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是.
mysql 性能调优 运维
01-01
MySQL性能调优与运维是DBA日常工作中至关重要的任务，涉及到数据库系统的稳定性和效率。以下是一些关键知识点的详细说明： 1. **热点数据导出与加载的影响**：热点数据导出是为了避免数据库重启后因预热缓存而消耗...
MySQL性能调优与架构设计.pdf
01-19
"MySQL性能调优与架构设计.pdf" 本资源摘要信息是关于 MySQL 数据库软件的性能调优和架构设计的知识点。MySQL 是一个流行的开源数据库管理系统，具有简单高效可靠的特点，广泛应用于各个行业。以下是从给定的文件中...
Mysql性能调优详解
02-19
Mysql性能调优详解 MySQL性能优化是一个复杂的过程，需要考虑多个方面的因素，包括硬件配置、系统设置、数据库设计、查询优化等。在这篇文章中，我们将详细介绍MySQL性能优化的相关知识点，包括my.cnf/my.ini配置...
mysql 5.6优化 性能提升10倍_mysql 5.6优化,性能提升10倍
weixin_35158372的博客
02-07 279
dell R710[51ou.com@mysql tpcc-mysql]# grep 'physical id' /proc/cpuinfo | sort -uphysical id : 1[51ou.com@mysql tpcc-mysql]# grep 'core id' /proc/cpuinfo | sort -u | wc -l4[51ou.com@mysql tpcc-mysq...
用tpcc测试对比 innodb 和 tokudb
weixin_30799995的博客
12-08 154
测试环境 1台IBM Intel(R) Xeon(R) CPU E5606 @ 2.13GHz，内存12G cd tpcc/tpcc-mysql/src # make cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load /usr/bin/ld: cannot find -lpercon...
tpcc-mysql percona_percona server 二进制安装下编译tpcc-mysql的坑
weixin_35387118的博客
01-30 216
出于习惯，percona server的部署都是通过二进制包自动化安装，结果遇到一个硕大无比的坑，编译TPCC-MySQL时出现警告10:49:36 root@DB-Master:~/tpcc-mysql/src$ makecc load.o support.o `mysql_config --libs_r` -lrt -L /usr/local/mysql/lib/ -o ../tpcc_lo...
Mysql的硬件优化和配置优化
QQ1057081261的专栏
07-07 419
如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。③物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了16G。mysql数据库的优化，算是一个老生常谈的问题了，网上也有很多关于各方面性能优化的例子，今天我们要谈的是MySQL硬件优化和系统参数的优化-即优化my.cnf文件。
MYSQL performance schema详解
m0_46690280的博客
03-05 407
MYSQL performance schema详解MYSQL performance schema详解0、performance_schema的介绍1、performance schema入门2、performance_schema表的分类3、performance_schema的简单配置与使用4、常用配置项的参数说明5、重要配置表的相关说明6、performance_schema实践操作 MYSQL performance schema详解 0、performance_schema的介绍 ​ MySQ
tpcc mysql优化_使用tpcc-mysql进行性能测试
weixin_36189159的博客
01-30 396
tpcc-mysql源代码进行编译，服务器上需要安装有mysqlgit clone https://github.com/Percona-Lab/tpcc-mysql.gitcd tpcc-mysql/srcmake可能会遇到如下错误：需要安装以下组件yum install mysql-develyum install gccmake成功后，会在tpcc-mysql下生成两个命令行工具创建库# 本...
linux下tpcc测试mysql_tpcc_mysql性能测试
weixin_42398730的博客
01-27 125
. bzr branch lp:~percona-dev/perconatools/tpcc-mysql 该方法需要下载bzr工具，还要在网站注册并添加SSH KEY，非常麻烦，并且还没有成功(卡在SSH KEY上)。 2. 直接在 http://bazaar.launchpad.net/~percona-dev/perconatools/tpcc-mysql/files 手动下载tpcc-my...
数据库性能压测之TPC-C基准测试
TIME_1981的博客
08-05 1万+
TPC-C是用商品批发业务混合了只读和读写等复杂事务来模拟OLTP场景。本文的目的是梳理TPC-C的特点以及工具使用，希望对同学们做TPC-C基准压测有所帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值