展开查看详情
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
29 .Thank You Sponsors!!
29
热门城市
