注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

网易杭研后台技术中心的博客

 
 
 
 
 

日志

 
 

MySQL用户资源限制改进  

来自江羽   2013-03-07 11:05:25|  分类: 默认分类 |举报 |字号 订阅

  下载LOFTER 我的照片书  |
在前篇几种常用数据库资源隔离分析中我们介绍了ORALCE,SQL Server和MySQL在资源限制方面实现和大致的测试,发现MySQL在用户隔离方面相对于其他两种数据库显得非常薄弱,本文我们从MySQL的切实出发,增加MySQL用户在CPU,IO资源方面的隔离措施。
MySQL整个架构通过上层服务和下层引擎方式进行组织,下层引擎通过继承上层服务的Handler来实现,所以为了通用性,我们考虑通过改造Handler层的形式,实现更多样化的MySQL用户资源限制措施——MySQL Profile
由于在MySQL上层实现用户的资源限制,脱离了底层相关引擎,所以在用户资源限制上,采用限制用户的查询次数的方式来间接限制用户的CPU使用,通过限制用户查询字节数的方式来间接限制用户的IO
MySQL Profile主要限制的对象:
指定用户单位时间内扫描的行数(RPU:rows per unit)
指定用户单位时间内扫描的字节数(BPU: bytes per unit)
指定用户单位时间内查询的次数(QPU: query per unit)
主要实现方法
在MySQL的上层与引擎层结合部,增加一层Profile用于实现用户在RPU,BPU,QPU,对于MySQL中表数据的访问,都需要通过Profile层进行统计,Profile层内嵌于所有MySQL上层与下层数据交换的接口中,能很方便的获取数据扫描行数,扫描字节数等信息。Profile的内部设计一个HASH表用于保存指定用户的Profile信息,Profile层会以一个指定的时间周期为单位,去检查用户的Profile信息,如果该用户在周期内的RPU,BPU,QPU中的一种或者几种达到了资源限制上面,会自动降低该用户的所有连接访问数据库的速度,从而释放资源给其他用户进行使用。Profile实现用户资源限制的主要步骤如下:
1.       通过新增的数据库配置参数配置单位时间间隔
2.       通过新增的数据库配置表配置需要隔离的用户的资源上限,系统内部自动绑定相关配置项到指定用户上
3.       用户通过相关SQL客户端实现对数据库的访问,在用户开始访问时,记录下用户访问的时间戳,标记为当前时间周期的开始时间。
4.       用户在操作过程中,不断去检查当前时间点与标记的开始时间之间的时间差是否满足一个时间周期,同时系统针对在该用户身上的配置(QPURPUBPU中的一项或多项),统计相关的信息,当用户限制的资源在该时间周期内达到限制条件时,通过相关处理,限制该用户的访问速度直到该时间周期结束,然后清空本次统计的资源项和重新标记开始时间。
相关配置
我们通过在mysql数据库下增加一个系统表user_profile的形式,实现对用户的Profile配置,具体表结构如下

user_profile | CREATE TABLE `user_profile` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Max_rows` int(11) unsigned NOT NULL DEFAULT '0',
`Max_bytes` int(11) unsigned NOT NULL DEFAULT '0',
`Max_qps` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User Profile' |

相应的字段说明如下表:
 字段名  备注
 Host  连接用户的主机
 User  连接用户的用户名
 Max_rows  最大扫描行数
 Max_bytes  最大扫描字节数
 Max_qps  最大查询次

通过对上表中数据的操作来配置哪个用户(user@host)需要进行资源限制
注意:凡是对user_profile表的修改,都需要通过执行flush privileges来使新的修改生效
 
除了提供user_profile表对指定用户进行配置外,还提供了一个变量profile_time_span用于设置时间间隔,profile_time_span的默认值为3秒,表示在3秒内,用户的资源限制为user_profile表中配置的对应项,如果在不足3秒时,该用户已经达到了上限,则接下去剩余的时间,该用户的执行速度会被限制,直到下个时间周期开始。
profile_time_span可以通过修改MySQL系统参数的形式进行修改
set global profile_time_span= value
 
信息查看
在Profile运行过程中,可以通过show方法查看相关的Profile统计信息,语法如下:

show user profiles;

MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
参数说明
 参数  说明
 host  连接用户的主机名
 user  连接用户的用户名
 max_rows  配置的最大扫描行数
 max_bytes  配置的最大扫描字节数
 max_qps  配置的最大查询次数
 count_rows  从启动(或者上次重置)开始,该用户总的行扫描数
 count_bytes  从启动(或者上次重置)开始,该用户总的字节扫描数
 count_qps  从启动(或者上次重置)开始,该用户总的查询次数
 delete_flag  该用户的配置是否被删除(删除1,未删除0)
 connections  该用户当前的连接数
如果需要重置总的统计信息(count_*),通过新增命令进行

reset user profiles;

注意:由于InnoDB内部锁机制的问题,在使用InnoDB引擎时,需要关闭InnoDB的自适应HASH功能才能实现Profile对用户的限制,否者在限制一个用户的同时,其他用户的效率也会受到影响
set global innodb_adaptive_hash_index=off
 
上面主要介绍了MySQL Profile的实现原理和配置,下面来让我们做下具体的测试,测试主要分两步:
1. Profile主键只读测试
2. Profile oltp测试
每个步骤中又可以分为扫描行数测试,扫描字节数测试,QPS测试,主要采用的测试工具为sysbench 0.5
 
Profile主键只读测试
用户数:5      每个用户线程数:20
默认的 profile_time_span = 3
扫描行数限制测试
测试方法:
1.分别创建5个用户和5个数据库,用户和数据库之间一一对应
用户 数据库
admin_0 mydb_0
admin_1 mydb_1
admin_2 mydb_2
admin_3 mydb_3
admin_4 mydb_4
2.不限制用户的扫描行数进行测试一段时间
3.经过一段时间,限制用户admin_0的扫描行数为3000(profile_time_span=3,折算成TPS=1000)   
4.每过一段时间,分别限制admin_1, admin_2, admin_3的扫描行数为3000
5.在限制了admin_3的扫描行数后,过一个时间间隔,取消对admin_0的限制
6.每过一段时间,分别取消对admin_1, admin_2, admin_3的扫描行数限制
根据上面的方法测试结果如下:
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
结果分析:
1.在开始一段时间,没有对用户进行资源限制,所以每个用户的QPS都可以在3700左右,系统整个QPS=3700*5=18500
2.当设置了admin_0的QPS为1000时,admin_0的QPS下降到1000左右,而其他用户此时的QPS都有所上升到了4400
3.下一个时间段,分别设置admin_1=1000,admin_2=1000,admin_3=1000时,其他没有被限制的用户的QPS都有所升高
4.通过上述图上的数据,我们假设每个时间间隔为T1,T2,...,Tn,可得到如下表格:
   T1 T2   T3  T4  T5  T6  T7  T8  T9
 admin_0  3700  1000  1000  1000  1000  8200  5600  4400  3700
 admin_1  3700  4400  1000  1000  1000  1000  5600  4400  3700
 admin_2  3700  4400  5600  1000  1000  1000  1000  4400  3700
 admin_3  3700  4400  5600  8100  1000  1000  1000  1000  3700
 admin_4  3700  4400  5600  8100  17500  8200  5600  4400  3700
 sum  18500  18600  18800  19200  21500  19400  18800  18600  18500
在分别对admin_0, admin_1, admin_2, admin_3进行限制后,其他没被限制的用户的QPS明显得到了上升,在取消限制后,其他没被限制的用户的QPS又被拉了下来
QPS测试
对于主键只读测试来说,QPS测试与扫描行数测试基本上是一致的,结果如下:
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
结果跟扫描行数限制基本一致
扫描字节数限制
扫描字节数是根据指定用户扫描数据量的大小来进行资源限制,测试方法与上面两种类似,设置的对象为max_bytes,开始不限制用户max_bytes大小,然后依次限制admin_0, admin_1, admin_2, admin_3的max_bytes=300K,(在不限制字节数时,观察用户每秒钟的数据量大约在500K,QPS=3600左右,平均每条查询返回的数据大约为140Bytes,所以我们设置max_bytes为300,限制每次查询的数据量为300/profile_time_span = 100Bytes),理论上计算,被限制的用户的QPS为原来的1/5,约为700左右
测试结果如下:
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
结果分析:
扫描字节数限制,整个QPS图的趋势跟前面类似,只是被限制的用户的QPS大约在550左右,小于理论上的700,这个可能跟字节数的计算和每行数据大小不一致有关。
Profile oltp测试
oltp的测试整个的测试步骤与主键测试相同,下面给出各种测试结果图:
扫描行数测试
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
结果分析:
从上图的结果可以看出,oltp扫描行数测试的整个图的趋势与主键测试一致,只是每个用户的TPS相比于主键测试时的QPS小了很多
扫描字节数限制
oltp的扫描字节数限制于主键的相比,字节数明显会高很多,因为在主键测试的时候,受到用户的QPS的限制,其实返回的数据量并不是很大,而在oltp测试时,测试返回的数据量会很大,通过观察在不限制扫描字节数时,每个用户返回的数据量约为42M,TPS=560,所以我们在测试时,限制用户的max_bytes=30M(由于profile_time_span=3, 用户每秒的数据被限制在10M),大约是不限制扫描字节数时的1/4,计算被限制后用户的TPS大约为560/4 = 140
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
实际的测试结果正如我们的预期,被限制后的用户的TPS基本维持在140左右,而在每个用户被限制的同时,其他不进行扫描字节数限制的用户的TPS有所升高,而在用户解除字节限制时,其他不限制的用户的TPS又被降低了。
QPS测试
MySQL用户资源限制改进 - 网易杭研后台技术中心 - 网易杭研后台技术中心的博客
总结
从上面的测试来看,MySQL Profile在Server满负荷运行下,限制一个用户的同时,提升了其他用户的效率,很好的达到了隔离用户的效果
  评论这张
 
阅读(1635)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017