MySQL资源组,介绍在MySQL 8中,提供的能力操作运行的线程分配给特定的资源,从而允许DBA管理应用程序的优先级。从本质上讲,你可以分配一个线程一个特定的虚拟CPU。在这篇文章中,我要看看这些在实践中可能会奏效。
让我们开始吧免责声明。
我要在这里讨论的是不常见的做法。这是高级负载优化,您应该方法/实现只如果你100%确定你在做什么,而且,更重要的是,如果你知道你在做什么,为什么你要这样做。
概述
MySQL 8介绍功能,解释只在单个文档页面。这个特性可以帮助很多如果正确使用,但愿他们不会轻视或删除它五分钟后。它是隐藏的优化:优化MySQL服务器一章。
我说的是资源组。资源组允许分配线程内运行MySQL特定人群,这样线程执行根据这组的可用资源。组属性启用资源控制或限制使资源消耗的线程组。dba可以修改这些属性,适合不同的工作负载。
目前,CPU关联(即。,assigning to a specific CPU) is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. MySQL determines, at startup, how many virtual CPUs are available. Database administrators with appropriate privileges can associate virtual CPUs with resource groups and assign threads to these groups.
简而言之,您可以定义,这个特定的线程(因此连接除非连接池或ProxySQL与多路复用),将使用,使用CPU和为主。
设置这个线程可以:
- 危险的
- 不是有用的
危险的,因为如果你设置一个线程使用连接池或ProxySQL和多路复用时,你可能会分配一个限制查询,相反,你想高效运行。
不是有用的因为除非你花时间看processlist(完整的),和/或运行一个脚本所有捕获所需要的时间,99%的时间你将无法有效地分配组。
所以呢?另外一个很酷的无用的功能? ? ?
不…
资源组可以引用在一个声明中,这意味着我只能查询利用资源组。这样会魔法:
|
1
|
选择
/ * + RESOURCE_GROUP (RG)的名称* /
id,millid,
日期活跃,kwatts_s
从
sbtest29
在哪里
id=44
|
但如果我运行:
|
1
|
选择
id,millid,
日期活跃,kwatts_s
从
sbtest29
在哪里
id=44
|
即使我没有资源组利用使用相同的连接。
这是很酷,不是吗?
可能的用法是什么?
一般来说,你可以认为这是一种限制查询的负面影响,你知道会有问题。
很好的例子是:
- ETL过程数据存档、报告、数据整合等
- 不是关键业务的应用程序,可以等待,而你的收入来源的应用程序不能
- GUI客户机应用程序,使用你的公司的一些员工,主要是给你带来麻烦,他们声称他们正在。
“马可,可能意义…但我应该做些什么工作吗?重写我的整个应用程序添加这个特性?”
好问题!谢谢!
我们可以分裂的任务有一个好的资源组实现分为三个步骤:
- 你必须执行你想控制的分析。您需要确定源(如TCP / IP如果它是固定的,用户名)和设计为您的资源组,设置你想要的。确定如果您只希望减少CPU优先,或如果你想隔离查询在一个特定的CPU,或两者的结合。
- 在MySQL中实现资源组。
- 实现一个SQL注入字符串评论的方法。
最后一步,我将向您展示如何做到这一点与ProxySQL直截了当的方式,但嘿…这是真的你。我会告诉你最简单的方法,但是如果你喜欢一个更困难的路线,对我也很好。
设置
在我的情况下,我有一个非常嘈杂的辅助应用程序写的一个非常,非常糟糕的开发者访问我的服务器,主要是阅读查询,和偶尔带写更新。读和写是强迫性的和创建一个对主应用程序的影响。我的任务是限制这个次要的应用程序的影响没有主要的一个影响。
我将创建两个资源组,写,另一个用于读。
第一组,Write_app2,将没有cpu联系,但会有最低(19)优先级:
|
1
|
创建
资源
集团
Write_app2
类型
=
用户
THREAD_PRIORITY=19;
|
第二组,Select_app2,将CPU归属和优先级最低;
|
1
|
创建
资源
集团
Select_app2
类型
=
用户
个VCPU=5THREAD_PRIORITY=19;
|
最后,我已经确定了,应用程序连接来自多个来源,但使用一个公共用户名APP2。有鉴于此,我将使用用户名使用ProxySQL SQL注入的指令(我也可以使用IP或模式名,或目的港,或者在提交的SQL。简而言之,任何可能的查询中过滤规则)。
这样做我将需要四个查询规则:
|
1
2
3
4
|
插入
成
mysql_query_rules(rule_id proxy_port、用户名、destination_hostgroup活跃,重试,match_digest,申请,活动)
值(80年,6033年,“app1”80年,1,3,“^选择。*更新',1,1);
插入
成
mysql_query_rules(rule_id proxy_port、用户名、destination_hostgroup活跃,重试,match_digest,申请,活动)
值(81年,6033年,“app1”81年,1,3,“^选择*’。,1,1);
插入
成
mysql_query_rules(rule_id proxy_port、用户名、destination_hostgroup活跃,重试,match_digest,申请,活动)
值(82年,6033年,app2的80年,1,3,“^选择。*更新',1,1);
插入
成
mysql_query_rules(rule_id proxy_port、用户名、destination_hostgroup活跃,重试,match_digest,申请,活动)
值(83年,6033年,app2的81年,1,3,“^选择*’。,1,1);
|
识别和重定向查询R / W分裂。
|
1
2
|
插入
成
mysql_query_rules(rule_id、活跃、用户名、match_pattern replace_pattern,运用,评论)
值
(32 1app2的,”(^选择)\ s *(. *)美元”,“\ 1 / * + RESOURCE_GROUP (Select_app2) * / \ 2”0,“降低读者一家和CPU绑定”);
插入
成
mysql_query_rules(rule_id、活跃、用户名、match_pattern replace_pattern,运用,评论)
值
(33岁,1,app2的,“^(插入|更新|删除)\ s *(. *)美元”,“\ 1 / * + RESOURCE_GROUP (Write_app2) * / \ 2”0,“作家”低的一家);
|
和一个用户定义:
|
1
2
|
插入
成
mysql_users(用户名、密码、积极、default_hostgroup default_schema transaction_persistent)
值
(app2的,“测试”1、80年,“mysql”1);
插入
成
mysql_users(用户名、密码、积极、default_hostgroup default_schema transaction_persistent)
值
(“app1”,“测试”1、80年,“mysql”1);
|
一个重要的步骤你需要做所有你想要包含在资源组的服务器利用率,是可以肯定的CAP_SYS_NICE功能集。
在Linux上,资源组线程优先级被忽略,除非CAP_SYS_NICE能力。MySQL包安装Linux系统应该设置这个功能。安装使用压缩的tar文件二进制发行版或从源代码,CAP_SYS_NICE功能可以设置手动使用setcap命令,指定的路径名mysqld可执行(这需要sudo访问)。你可以检查功能使用getcap。例如:
|
1
2
3
|
壳牌
>
sudo
setcap
cap_sys_nice
+
ep
<
路径
来
你
mysqld
可执行的
>
壳牌
>
getcap
。
/
本
/
mysqld
。
/
本
/
mysqld
=
cap_sys_nice
+
ep
|
如果需要手动设置CAP_SYS_NICE,那么你需要做它每次执行一个新的安装。
作为参考是一个表关于CPU优先级:
| 优先级范围的窗户 | 优先级 |
| -20年到-10年 | THREAD_PRIORITY_HIGHEST |
| 9比1 | THREAD_PRIORITY_ABOVE_NORMAL |
| 0 | THREAD_PRIORITY_NORMAL |
| 1到10 | THREAD_PRIORITY_BELOW_NORMAL |
| 11到19 | THREAD_PRIORITY_LOWEST |
总结这整个组步骤我的环境:
1)检查CAP_SYS_NICE
|
1
2
|
getcap
/
选择
/
mysql_templates
/
mysql
- - - - - -
8页
/
本
/
mysqld
setcap
cap_sys_nice
+
ep
/
选择
/
mysql_templates
/
mysql
- - - - - -
8页
/
本
/
mysqld
|
2)在MySQL中创建用户和资源组
|
1
2
3
4
|
格兰特
所有
特权
在
“windmills2”。*
来
“app2”@“%”;
创建
资源
集团
Select_app2
类型
=
用户
个VCPU=5THREAD_PRIORITY=19;
创建
资源
集团
Write_app2
类型
=
用户
THREAD_PRIORITY=19;
|
检查:
|
1
|
选择
*
从
INFORMATION_SCHEMA.RESOURCE_GROUPS;
|
3)创建ProxySQL用户和规则
|
1
2
3
4
5
6
7
8
9
10
11
|
插入
成
mysql_users(用户名、密码、积极、default_hostgroup default_schema transaction_persistent)
值
(app2的,“测试”1、80年,“mysql”1);
插入
成
mysql_users(用户名、密码、积极、default_hostgroup default_schema transaction_persistent)
值
(app1,测试”,80,”mysql',1);
MYSQL用户负载运行时,MYSQL用户保存到磁盘;
插入mysql_query_rules (rule_id proxy_port、用户名destination_hostgroup,活跃,重试,match_digest,申请,活动)值(83、6033、app2”,80年,1,3,^选择。*更新
',1);
插入mysql_query_rules (rule_id proxy_port、用户名destination_hostgroup,活跃,重试,match_digest,申请,活动)值(84、6033、app2”,81年,1,3,^选择。*',1);
插入mysql_query_rules (rule_id proxy_port、用户名destination_hostgroup,活跃,重试,match_digest,申请,活动)值(85、6033、app2”,80 0 3。”。”,1,0);
插入mysql_query_rules (rule_id、活跃用户名、match_pattern replace_pattern,适用,评论)值(32 0 'app2’”(^选择)\ s * (. * $)”、“\ 1 / * + RESOURCE_GROUP (Select_app2) * / \ 2”, 0,“低一家对读者和CPU绑定”);
插入mysql_query_rules (rule_id、活跃用户名、match_pattern replace_pattern,适用,评论)值(33岁,0,'app2’,“^(插入|更新|删除)\ s *(. *)美元”,“\ 1 / * + RESOURCE_GROUP (Write_app2) * / \ 2”0,“作家”低的一家);
负载
MYSQL查询规则
来
运行时,保存MYSQL查询规则
来
磁盘;
|
由于一些原因我将添加资源组查询规则不活跃。
做…
测试
这工作吗?
我们需要看到错误的应用程序的影响我的生产应用程序。
然后我们需要看到如果实现调优工作。
做一个基本的检查我运行四个测试:
- test1运行两个应用程序禁用RG读/写和规则
- test2一次运行一个应用程序没有RG
- test3只运行App2 RG看到执行成本
- test4运行都与RG看看发生什么
测试1
主
奴隶
这个测试的目的是有一个想法,马上,两个应用程序在运行时会发生什么,没有限制。
在测试中我们可以看到,所有的核心是利用,一些更加一致和少一点,但没有巨大的。
有趣的是看到影响响应时间和事件的数量每个应用程序可以执行:
执行图显示一个非常高的时间插入、App1和删除,结果显示非常糟糕的表现只有9插入、删除,1333和165的选择。
但实际上是应用程序应该做什么呢?测试2会告诉我们,创造实际我们的基线。
测试2
在这个测试我有单独运行每个应用程序,所以没有干扰。
主App1
App2大师
奴隶App1
奴隶App2
没有明显不同的CPU利用率App1运行时,当我们可以看到少一点App2的利用率。
然而,对性能的影响是更重要的:
执行时间的插入、删除App1下降显著,我们可以看到,应用程序应该能够插入~ 1320事件和执行更高数量的操作。App2相同,但我们更关心比ETL OLTP应用程序。
会发生什么如果我们激活资源组旗帜App2 (ETL)性能?让我们看看与测试3。
测试3
运行资源组只有App2活跃
App2大师
奴隶App2
主,RG设置会做的就是减少优先级,鉴于没有运行其他进程和其他应用程序连接,不高的影响。
另一方面,奴隶我们可以清楚地看到,现在App2只能使用核心5显示在我们的配置。
到目前为止一切顺利,性能损失将是什么?让我们来看看:
比较两个测试2和3,我们可以看到,在我们应用资源组ETL程序但现有影响很小。预计,需要和必须注意的。不高的影响在该测试中,但它可以扩大在现实世界中运行时间。
是时候把所有,看看是怎么回事。
测试4
运行OLTP应用程序在ETL运行时资源组。
主
奴隶
看这些图CPU利用率非常类似于test1的,但结果是完全不同的:
App1执行时间(OLTP)性能显著下降而增加了好像没有其他正在运行。同时App2已经失去了性能,必须考虑,但它不会停止/防止ETL过程运行。
可以做更多的调整中,ETL太妥协。或者修改服务器布局如添加一个奴隶和奉献ETL读取。组合和可能性很多。
结论
只是想最后的图表将帮助我们达到我们的结论:
比较两个测试1和4,我们可以看到如何使用资源组将有助于我们正确地平衡工作负载和优化性能的情况下不可避免的不同的应用程序之间的争用。
同时,使用资源组单独作为一个毯子设置它的目的不是最佳的,因为它可以失败。而不是提供一些改善,不可预知的影响交通。这也是不可取的修改代码以实现它在查询级别,考虑到这样做可能造成的影响在成本和时间。
ProxySQL和查询重写的引入,使我们能够利用每个查询选项,而不需要任何代码修改,并允许我们来指定想要的,非常高的粒度级别的。
再一次自己不这样做,除非你是主管以上和100%知道你在做什么。在任何情况下,记住一个ETL过程可能需要更长的时间,你需要计划你的工作和相应的计划。
大家MySQL好。
引用
你可能也喜欢
如果你曾经想知道多少存储性能可以改善通过更快的云卷,你并不孤独。雷竞技下载官网Percona联合创始人兼首席技术官瓦迪姆Tkachenko还考虑这个问题,公布他的发现MySQL扩展IO-Bound工作负载的云。
雷竞技下载官网Percona的主要顾问亚历山大·鲁宾研究使用MySQL存储程序对数据库性能的影响。结果可能会使一些读者感到惊奇。亚历山大的文章阅读,为什么MySQL存储过程、函数和触发器对性能不利吗了解更多。
嘿,马克,谢谢你这个很好的解释和使用资源组的例子在MySQL中!
详细阅读上面的文章真的解释了一切,这篇文章是非常有趣的和有效的。谢谢你,祝你好运即将到来的文章