文档

关系型数据库读写分离实现性能提升

更新时间:
一键部署

在业务使用过程中当业务读流量非常大时,可以使用读写分离来将读流量分配到只读实例,依次减轻主实例的压力。

场景描述

读写分离是指通过独享代理地址(读写属性的代理终端)实现读写请求的自动转发。

在对数据库有少量写请求,但有大量读请求的应用场景下,单个实例可能无法承受读取压力,甚至对业务产生影响。为了实现读取能力的弹性扩展,分担数据库压力,您可以创建一个或多个只读实例,利用只读实例满足大量的数据库读取需求,增加应用的吞吐量。1创建只读实例后,您可以开通读写分离,此时独享代理地址就可以作为读写分离地址,在应用程序中配置独享代理地址,就可以使写请求自动转发到主实例,读请求自动转发到各个只读实例。您只需增加只读实例的个数,即可不断扩展系统的处理能力,降低主实例的读取压力,应用程序无需做任何修改。2

方案价值

  • 系统资源灵活扩展:统一读写分离地址,方便维护。 不开通读写分离时,您需要在应用程序中分别配置主实例和每个只读实例的连接地址,才能实现将写请求发往主实例而将读请求发往只读实例。RDS读写分离功能提供一个独享代理地址,您连接该地址后即可对主实例和只读实例进行读写操作,读写请求被自动转发到对应实例,可降低维护成本。同时,您只需添加只读实例的个数,即可不断扩展系统的处理能力,应用程序无需做任何修改。

  • 系统性能提升:原生链路支持,提升性能,减少维护成本。 如果您在云上自行搭建代理层实现读写分离,数据在到达数据库之前需要经历多个组件的语句解析和转发,对响应延迟有较大的影响。而RDS读写分离内置在RDS原生生态里,能够有效降低延迟,提升处理速度,同时减少客户的维护成本。

  • 自定义负载权重配置:可设权重和阈值,符合多场景使用。 您可以设置主实例和只读实例的读请求权重,以及设置只读实例的延迟阈值。

  • 系统具备高可用性:实例健康检查,提升数据库系统的可用性。 读写分离模块将自动对主实例和只读实例进行健康检查,当发现某个实例出现宕机或者延迟超过阈值时,将不再分配读请求给该实例,读写请求在剩余的健康实例间进行分配。以此确保单个只读实例发生故障时,不会影响应用的正常访问。当实例被修复后,RDS会自动将该实例纳回请求分配体系内。

说明

为避免单点故障,建议您为一个主实例创建至少两个只读实例。

组件介绍

云数据库RDS(MySQL):

阿里云关系型数据库RDS(RelationalDatabase Service)是一种稳定可靠、可弹性伸缩的在线数据库服务。RDS支持MySQL、SQL Server、PostgreSQL、引擎,并且提供了容灾、备份、恢复、监控、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。

前提条件

实例版本如下:

  • MySQL 8.0高可用版。

  • MySQL 5.7高可用版或三节点企业版。

  • MySQL 5.6高可用版。

  • 基于企业版V3.14及以上版本。

限制与约束

  • 只能在主实例内创建只读实例,不能将已有实例切换为只读实例。

  • 只读实例的参数不继承主实例上的参数设置,会生成默认的参数值,可以在只读实例的控制台上进行修改。

  • 只读实例的数量:最多创建5个只读实例。

实践步骤

  1. 创建MySQL数据库实例、数据库、账号。

    1. 使用管理员账号登录Apsara Uni-manager运营控制台,在页面顶部的菜单栏中,单击产品>云数据库RDS。进入RDS运营管理页面。单击创建实例进入创建云数据库RDS页面。3

    2. 选择组织、资源集、地域,输入实例名称,数据库类型选择:MySQL,选择数据库版本、实例规格,输入存储空间大小,网络类型选择:专有网络(只读实例的版本、网络类型跟主实例一致),输入IP白名单,选择访问模式为标准访问模式,单击提交456

    3. MySQL数据库实例创建成功后,在实例列表页面,单击实例右侧的管理进入实例基本信息页,选择左侧导航栏的数据库管理,为实例创建一个数据库。7

    4. 单击创建数据库,在弹框中输入数据库名称,选择字符集,单击创建完成创建动作。8

    5. 数据库创建完成后,选择左侧导航栏的账号管理,为实例创建读写账号。9

    6. 单击创建账号,打开账号创建页面,单击普通账号,输入账号名称,授权为数据库的读写权限,输入密码、确认密码,单击创建,完成创建动作。10

  2. 创建MySQL只读实例。

    1. 在实例基本信息页面,在右侧只读实例位置,单击添加只读实例,打开只读实例创建页面,进行创建账号操作。1

    2. 选择只读实例规格,单击创建账号,下发创建只读实例任务,等待只读实例创建完成。213

  3. 开通读写分离(共享代理)。

    1. 操作步骤。

      1. 只读实例创建完成后,回到主实例的基本信息页面,在左侧导航栏中选择数据库代理,开通数据库代理,并等待开通完成(数据库代理开通需要1-2分钟时间,可以查看右上角的任务进度)。14

      2. 实例恢复运行中状态后,在数据库代理页签右侧会出现读写分离页签,单击读写分离页签,进入读写分离页面,单击立即开启,设置读写分离。15

      3. 设置读写分离地址类型可选择内网或外网,读写分离比例可选择系统分配或者自定义,设置完成后,单击确定,开通读写分离功能。16

      4. 读写分离开通成功后,实例状态会由:创建网络连接中恢复为:运行中,此时提供的读写分离地址可用于连接实例。实例后端处理程序会根据设置的读权重分配自行调整只读实例的读请求占比。

        下图表示主库仅负责写入,两个只读实例分担的读流量相同(读权重相同)。17

      5. 可单击设置读写分离,重新调整读权重分配,调整成功后,立即生效。 可单击示意图中的加号图标继续添加只读实例,添加新的只读实例成功后,系统会自动进行读权重比例调整,如100:100、100:300 等,也可以手工配置读写比例权重。

    2. 参数说明:

      参数

      说明

      延迟阈值

      只读实例同步主实例数据时允许的最长延迟时间。若一个只读实例的延迟时间超过该阈值,则不论该只读实例的权重是多少,读请求都不会转发至该只读实例。取值范围为0秒到7200秒。受限于SQL的执行情况,只读实例有一定的几率会出现延迟,建议该值不小于30秒。

      读权重分配

      实例的读权重越高,处理的读请求越多。例如,假设主实例有3个只读实例,读权重分别为0、100、200和200,则表示主实例不处理读请求(写请求仍然自动发往主实例),3个只读实例按照1:2:2的比例处理读请求。 系统分配:系统根据实例规格自动分配各个实例的读权重。后续该主实例下新增的只读实例也会自动按照系统分配的权重加入到读写分离链路中,无需手动设置。

      自定义:手动设置各个实例的读权重,范围为0至10000。后续该主实例下新增只读实例的读权重默认为0,需要您手动修改。

      说明

      不支持为已经设置只读实例延时复制时间的实例设置权重。

  4. 请求转发逻辑。

    • 只发往主实例 。

      • INSERT、UPDATE、DELETE、SELECT FOR UPDATE。

      • 所有DDL操作(建表或库、删表或库、变更表结构、权限等)。

      • 所有事务中的请求。

      • 用户自定义函数。

      • 存储过程。

      • EXECUTE语句。

      • Multi Statements

      • 使用到临时表的请求。

      • SELECT last_insert_id()。

      • 所有对用户变量的查询和更改。

      • KILL(SQL语句中的KILL,非命令KILL)。

    • 发往只读实例或主实例。

      • 非事务中的SELECT。

      • COM_STMT_EXECUTE命令。

    • 总是发往所有实例 。

      • 所有系统变量的更改。

      • USE命令。

      • SHOW PROCESSLIST。

        说明

        执行SHOW PROCESSLIST命令后,代理将返回所有节点的PROCESSLIST汇总信息。

      • COM_STMT_PREPARE命令。

      • COM_CHANGE_USER/COM_QUIT/COM_SET_OPTION等命令。

结果验证

  1. 创建并配置压测机。

    1. 使用管理员账号登录Apsara Uni-manager运营控制台,在页面顶部的菜单栏中,单击产品>云服务器ECS,进入ECS运营管理页面,创建ECS虚拟主机。18

    2. 在页面顶部的菜单栏中,单击产品>负载均衡SLB,进入SLB运营管理页面,创建SLB负载均衡,配置后端服务器为压测机ECS(后续可以从OPS#1节点通过SSH远程访问SLB的IP地址,登录压测机ECS)。19

    3. 登录压测机ECS,通过yum源安装MySQL。

      执行命令:yum -y install mysql20

    4. 将压测工具rwsplit(源文件rwsplit.c)拷贝到ECS压测机,参考如下命令行进行编译,编译成功后,生成可执行文件。 编译命令:g++ -g rwsplit.c -I/u01/mysql_current/include-L/u01/mysql_current/lib -lmysqlclient -o rwsplit -lpthread

      说明

      也可采用sysbench标准工具,使用sysbench标准的读写模型。

  2. 启动压测,确认读写分离配置生效。

    1. 在ECS压测机上运行压测工具rwsplit,模拟对数据库的并发读写操作。

      脚本示例:./rwsplit $ip $port $user $password $dbname $threads $times.

      /rwsplit 172.XX.XX.118(MySQL主实例读写分离地址对应的ip) 3306 acc0801 Admin123 db0801 64 2000000 参数说明:threads:并发压的线程数;times:每个线程跑几条sql。

    2. 压测运行一段时间后(约15 min),登录杜康运维系统(登录Apsara Uni-manager运维控制台,单击左侧产品运维管理,单击右侧数据库服务,选择杜康)查看只读实例压力负责情况(页面顶部导航栏单击实例管理,在左侧实例ID名中输入只读实例的ID,搜索到只读实例名称并单击实例名进入运维监控页面)。

    3. 在左侧导航中单击实例诊断,查看该实例性能数据,XXX平均每秒SQL语句执行次数_平均每秒事务数(单位为int)看QPS值。只读实例QPS值相对比,对比结果与负载均衡配置比例一致(误差小于10%)。2122

以上实验验证:MySQL关系型数据库可以通过设置独享代理地址使读写请求自动转发,实现读写业务分离,降低主实例的读取压力,同时支持只读实例的负载均衡配置。

  • 本页导读
文档反馈