MySQL 8 advance tuning with Resource Group

MySQL8引入了一个在单个文档页面中解释的特性,但如果正确使用,这会有很大帮助。
在优化:mysql服务器优化一章中,它隐藏得很好。
我说的是资源组。
资源组允许将服务器内运行的线程分配给特定组,以便线程根据组可用的资源执行。
组属性启用对其资源的控制,以启用或限制组中线程的资源消耗。DBA可以根据不同的工作负载修改这些属性。
如果数据库上不同类型的负载之间存在争用,那么这个简单的小特性可能是一个爆炸。
最简单的例子是OLTP/OLAP或OLTP/ETL的组合。
在本演示中,我将演示该特性以及如何轻松和成功地实现它。
还包括那些上下文,比如大型企业,其中代码动态并不像我们希望的那样灵活。

展开查看详情

1.Mysql8 advance tuning with Resource Group Marco Tusa Percona

2.About me Marco “The Grinch” • Former UN, MySQL AB, Pythian, Percona • 2 kids, 1 wife • History of Religions; Ski; Snowboard; Scuba Diving; 2

3.My Motto Use the Right Tool for the Job

4.Summary 1. Resource Group overview 1. RG Attributes 2. RG Management 3. RG limitations/warning 2. Real case to solve 3. The recipe 1. Implement it 4. Tests … and more tests 5. Conclusions 4

5. Distribute load • Multicore CPUs • OS supporting multicore • Application written for parallelism (multi threading) • MySQL Thread-Connection • ProxyMySQL (multiplexing) OR connection pooling 5

6. Resource Group Attributes (root@localhost) [information_schema]>describe resource_groups; +------------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------+------+-----+---------+-------+ | RESOURCE_GROUP_NAME | varchar(64) | NO | | NULL | | | RESOURCE_GROUP_TYPE | enum('SYSTEM','USER') | NO | | NULL | | | RESOURCE_GROUP_ENABLED | tinyint(1) | NO | | NULL | | | VCPU_IDS | blob | YES | | NULL | | | THREAD_PRIORITY | int(11) | NO | | NULL | | +------------------------+-----------------------+------+-----+---------+-------+ 6

7. Resource Group Management • GRANT RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER • STATUS • Com_alter_resource_group • Com_create_resource_group • Com_drop_resource_group drop RESOURCE GROUP Select_app2; CREATE RESOURCE GROUP Select_app2 TYPE=USER VCPU=5 THREAD_PRIORITY=19; ALTER RESOURCE GROUP Select_app2 VCPU = 1 THREAD_PRIORITY = 19 7

8.Resource Group Management For system resource groups, the permitted priority range is -20 to 0. For user resource groups, the permitted priority range is 0 to 19. Priority Range Windows Priority Level -20 to -10 THREAD_PRIORITY_HIGHEST -9 to -1 THREAD_PRIORITY_ABOVE_NORMAL 0 THREAD_PRIORITY_NORMAL 1 to 10 THREAD_PRIORITY_BELOW_NORMAL 11 to 19 THREAD_PRIORITY_LOWEST 8

9. Resource Group Management To assign a thread to the Batch group: SET RESOURCE GROUP Batch FOR thread_id; If a session's own current thread should be in the Batch group, execute this statement within the session: SET RESOURCE GROUP Batch; To execute a single statement using the Batch group, use the RESOURCE_GROUP optimizer hint: INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2); 9

10.Resource Group Warning • Resource group management is local to the server on which it occurs. No replication • Resource groups are unavailable if the thread pool plugin is installed • On FreeBSD and Solaris, resource group thread priorities are ignored. • Linux CAP_SYS_NICE capability must be set. • sudo setcap cap_sys_nice+ep <Path to you mysqld executable> getcap ./bin/mysqld ./bin/mysqld = cap_sys_nice+ep 10

11.The case I have a very noisy secondary application written by a very, very bad developer that accesses my servers, mostly with read queries, and occasionally with write updates. Reads and writes are obsessive and create an impact on the MAIN application. My task is to limit the impact of this secondary application without having the main one affected. To do that I will create two resource groups, one for WRITE and another for READ. The first group, Write_app2, will have no cpu affiliation, but will have lowest priority. 11

12.The recipe • Two users • App1 (good guy) • App2 (nasty bad developer) • Master – Slave pair • 2 Set of Resource group settings (Master/Slave) • ProxySQL • 2 Host groups • Query rules fro read/write split • Query rules fro Query rewrite 12

13. How to implement it • MySQL • CREATE RESOURCE GROUP Write_app2 TYPE=USER THREAD_PRIORITY=19; • CREATE RESOURCE GROUP Select_app2 TYPE=USER VCPU=5 THREAD_PRIORITY=19; • ProxySQL • insert into mysql_users … values ('app2','test',1,80,'mysql',1); insert into mysql_users … values ('app1','test',1,80,'mysql',1); • insert into mysql_query_rules …values(80,6033,'app1',80,1,3,'^SELECT.*FOR UPDATE',1,1); insert into mysql_query_rules… values(81,6033,'app1',81,1,3,'^SELECT.*',1,1); insert into mysql_query_rules ... values(82,6033,'app2',80,1,3,'^SELECT.*FOR UPDATE',1,1); insert into mysql_query_rules ... values(83,6033,'app2',81,1,3,'^SELECT.*',1,1); • INSERT INTO mysql_query_rules … VALUES (32,1,'app2',"(^SELECT)\s*(.*$)","\1 /*+ RESOURCE_GROUP(Select_app2) */ \2 ",0,"Lower prio and CPU bound on Reader"); • INSERT INTO mysql_query_rules … VALUES (33,1,'app2',"^(INSERT|UPDATE|DELETE)\s*(.*$)","\1 /*+ RESOURCE_GROUP(Write_app2) */ \2 ",0,"Lower prio on Writer"); 13

14.Testing the whole thing • test1 run both apps with read/write and rule disabled for RG • test2 run only App2 with RG to see the cost on the execution • test3 run both to see what happen with RG 14

15. Results Test1 - CPU Master Slave 15

16.Results Test1 - CRUD 16

17.Results Test1 - time 17

18. Results Test2 - CPU Master only nasty APP no LIMIT Master only nasty APP WITH LIMIT 18

19. Results Test2 - CPU Slave only nasty APP no LIMIT Slave only nasty APP WITH LIMIT 19

20.Results Test2 - CRUD 20

21.Results Test2 - time 21

22. Results Test3 - CPU Master Slave 22

23.Results Test3 - CRUD 23

24.Results Test3 - time 24

25.Compare Before VS After - CRUD 25

26.Compare Before VS After - time 26

27.Conclusions • Mission accomplished • Resource group is a simple but not easy to use feature • Must be used in conjunction with something like ProxySQL or if you want to use on the thread a smart scripting • Doesn’t work well with Connection Pooling • You will not get it right at the first attempt so: • Work on test environment first • Test Test Test • Keep production under observation for a long while to be sure you are not creating a mess • Use it as Last Man Standing option 27

28.Q&A 28

29.Thank You Sponsors!! 29