mysql 8 for dbas real use case scenarios

1 .MySQL 8 for DBA's - real use case scenarios of the exciting new features Alex Poritskiy
 Percona 

2 .Is that going to be long and boring ? Definitely NOT ! At least I hope :-) !2 

3 .What features can actually help DBA’s in a day to day tasks ? ● New Database Roles ● Index Hiding a.k.a “Invisible” indexes ● Configuration Persistence ● Resource Groups ● And much more but it’s not the part that will be covered here ….. !3 

4 .“New” Database Roles ● New ? Really ? Are you kidding ? To MySQL definitely - yes !!! ● What is the Role ? A role is a named collection of privileges that define what a user can and cannot do within a database. Roles play a vital part of database security by limiting who can connect to the server, access the database, or even access individual database objects and data. !4 

5 .“New” Database Roles Although prior to version 8, MySQL did provide a set of Privileges and Administrative Roles, MySQL 8 also supports a set of flexible and properly architected roles, thus allowing DBAs to: ● Create and Drop Roles, Grant to Roles ● Grant Roles to Roles, Grant Roles to Users ● Limit Hosts that can use roles, Define Default Roles ● Decide what roles are applicable during a session ● And even visualise Roles with SQL function ROLES_GRAPHML() !5 

6 .“New” Database Roles ● What the benefit of using the roles? Since each role packs multiple privileges, DBAs don’t have to remember exactly which permissions a user requires. Roles are also very easy to set up: ● Creating a new role: 
 
 CREATE ROLE ‘app_developer’, ‘app_read’, ‘app_write’; ● Assigning privileges to roles: 
 
 GRANT SELECT ON my_db.* TO ‘app_read’; ● Assigning the role to a user: 
 
 GRANT ‘app_read’ TO ‘readonly_user1’@’localhost’, ‘readonly_user2’@’localhost’; !6 

7 .Are you fall asleep already ? ? Hopefully not :-) So let’s continue !7 

8 . Index hiding a.k.a. “Invisible” index ● What is that ? Hidden indexes are similar to disabled indexes, except that, in the case of the former, index information remain fully up to date and maintained by Data Manipulation Language (DML); it’s just invisible to the MySQL Optimizer. !8 

9 . Index hiding a.k.a. “Invisible” index ● How we can use it ? This feature is useful in hiding an index you suspect you don’t need, without actually dropping it. By marking an index as invisible, the MySQL optimizer will no longer use it. You can then monitor your server and query performance to decide whether to delete it or re-activate it, if it turns out that the index does provide improved performance. !9 

10 . Index hiding a.k.a. “Invisible” index ● “Soft” Delete If for example you don’t think that an index is utilised any more. In this case rendering the index invisible is akin to throwing it in the recycle bin. In that state it’s still possible to restore it. First you would render the index invisible: ALTER TABLE Transactions ALTER INDEX a INVISIBLE; You can revert it – i.e. make it visible again – if need be: ALTER TABLE Transactions ALTER INDEX a VISIBLE; If it is safe to drop the index: ALTER TABLE Transactions DROP INDEX a; !10 

11 . Index hiding a.k.a. “Invisible” index ● Staged Rollout Adding new index can not only change existing execution plans, like all changes, it also introduces the risk of regression. That’s where your database becomes unstable due to multiple changes and additions that may not have been fully tested as a whole. Invisible indexes allows you to stage all changes by putting the database in a “prepared” state. You can add an index invisibly at an opportune time. !11 

12 .Are you guys still awake ?! ? Attention magnifier , just in case :-) !12 

13 . Persisting configuration variables ● Some Background Changing configuration settings in MySQL wasn’t always easy. Of course it’s possible to change things, but keeping track of everything is not always obvious. This is where configuration management systems like puppet, chef, ansible, … excels in making our life easier. We can modify configuration variable, but after a restart of MySQL, this change is lost, this behaviour, of course is something known by all MySQL DBAs. The usual solution is then to also modify the configuration file . !13 

14 . Persisting configuration variables ● SET PERSIST is on rescue This new feature allowing to modify a configuration variable and make the change persistent. • To illustrate the problem and solution: # The value of the InnoDB Buffer Pool is changed to 512M mysql> SET GLOBAL innodb_buffer_pool_size = 512 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) We can modify this configuration variable, but after a restart of MySQL, this change is lost, this behaviour, of course is something known by all MySQL DBAs. The usual solution is then to also modify the configuration file (/etc/my.cnf in most cases). New feature allowing to modify a configuration variable and make the change persistent: mysql> SET PERSIST innodb_buffer_pool_size = 512 * 1024 * 1024; Query OK, 0 rows affected (0.01 sec) !14 

15 . Persisting configuration variables The change persists even after a restart. We have added SET PERSIST that will change the variable and store it somewhere so value will survive a crash or a restart. Also another good new feature: RESTART via MySQL client command line(since MySQL 8.0.4). Yes, before when you have to restart MySQL, you had to do it via an access to the operating system or using a GUI made available for you. This is the case when you use the “cloud” for example. The RESTART command is way more convenient from the my point of view. !15 

16 . Persisting configuration variables Now it’s possible to change a READ_ONLY variable that will be modified after a restart within PERSIST _ONLY + RESTART and perform all this without quitting the MySQL Client(by using PERSIT_ONLY ). To change such variable extra privileges are needed: SYSTEM_VARIABLES_ADMIN PERSIST_RO_VARIABLES_ADMIN Changes are stored, in MySQL datadir, there is new file mysql-auto.cnf that store all the changes in JSON format !16 

17 . Persisting configuration variables In the future versions planned to keep track of who, where and when the change was made, so it’s already some-kind of configuration and versioning management system in MySQL out of the box. !17 

18 . Persisting configuration variables And as for now, it’s possible to use performance_schema in order to obtain such information from the MySQL Client: mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G *************************** 1. row *************************** VARIABLE_NAME: innodb_buffer_pool_size VARIABLE_SOURCE: PERSISTED VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf MIN_VALUE: 5242880 MAX_VALUE: 9223372036854775807 SET_TIME: 2018-08-25 11:59:25 SET_USER: root SET_HOST: localhost *************************** 2. row *************************** VARIABLE_NAME: innodb_log_file_size VARIABLE_SOURCE: PERSISTED VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf/mysqld-auto.cnf MIN_VALUE: 4194304 MAX_VALUE: 18446744073709551615 SET_TIME: 2018-08-25 11:58:47 SET_USER: root SET_HOST: localhost !18 

19 . Resource Groups ● Some Background As we all know, MySQL has system (background) threads, and user (foreground) threads. Until now, nothing could be done to change their priority. All we could do was to tune InnoDB concurrency tickets to make sure that long running queries don’t prevent other queries from using CPU time. Basically, what we do is splitting the execution of a query to improve concurrency. !19 

20 . Resource Groups Now we can also affect threads priority. By default, all threads have the same priority. We can increase the priority of system threads, or decrease the priority of user threads. In any case, no user thread can have more priority than any system thread. Furthermore, we can limit the execution of some threads to one or more virtual CPUs of our choice. !20 

21 . Resource Groups ● How can we do that ? By creating a resource group and setting THREAD_PRIORITY and VCPU attributes. THREAD_PRIORITY is a number (-20..0 for system threads, 0..19 for user threads; lower numbers have higher priority). VCPU is a virtual CPU number (see which ones are available with lscpu), or a range, or a list of numbers/ranges. Then, we can assign running threads to one of the groups we created. !21 

22 . Resource Groups For example, to create a group for system threads: CREATE RESOURCE GROUP sql_thread TYPE = SYSTEM VCPU = 0,1 THREAD_PRIORITY = -10; To assign an existing thread to that group, check its id within SHOW PROCESSLIST, and then run something like: SET RESOURCE GROUP sql_thread FOR 10; !22 

23 . Resource Groups ● What use cases for that ? Some of them are : ● Increase priority of replication threads to reduce slave lag; ● Increase priority of the event scheduler; ● Decrease priority of long-running analytical queries. ● Increase priority for the some specific query which is priority at the moment !23 

24 . Resource Groups Unfortunately, resource groups can only be assigned to the running threads. There is no way to instruct MySQL that connections from a certain user should use a certain resource group so far. However, as workaround you can do that from the application(or Proxy) side . After connecting, the application can run: SET RESOURCE GROUP some_group; For the more information on how to use resource groups you can check my blogpost: https://www.percona.com/blog/2018/01/25/mysql-8-0-resource_group-overview/ !24 

25 . Thank you ! Be calm and cool as you are now … !25 

26 .Questions ? !26 

27 .Thank you Sponsors ! !27