Why we're excited about MySQL 8

1 . Why we’re excited about MySQL 8 Practical Look for Devs and Ops Peter Zaitsev, CEO, Percona February 2nd, 2018 Pre FOSDEM MySQL Day © 2018 Percona. 1 

2 .In the Presentation Practical view Exciting things Exciting things on MySQL 8 for Devs for Ops © 2018 Percona. 2 

3 .Warning This assessment is done for Pre-GA MySQL 8, based on documentation and limited testing. We’re yet to see how they behave in production © 2018 Percona. 3 

4 .Source Notes Examples liberally borrowed from Oracle team presentations and Blog Posts © 2018 Percona. 4 

5 .MySQL 8 for Ops © 2018 Percona. 5 

6 .Ops care about Stability High Availability Performance Security Observability Manageability © 2018 Percona. 6 

7 .Native Data Dictionary About 10 years overdue Atomic, Crash Save, DDLs Much Faster Information Schema No more MyISAM System Tables! © 2018 Percona. 7 

8 .Fast Information Schema Scaling from 5.000 to 1.000.000 tables © 2018 Percona. 8 

9 .Much Better and Faster UTF8 • utf8mb4 as Default Charset © 2018 Percona. 9 

10 .Security ROLEs Breakdown of SUPER Privileges Password History Faster cached-SHA2 Authentication (Default) Community Builds use OpenSSL --skip-grants blocks remote connections Redo and Undo Logs are now encrypted if Table Encryption is enabled © 2018 Percona. 10 

11 .Persistent Auto Increment Another feature 10 years overdue Do not reset AUTO INCREMENT to the max value in the table on restart © 2018 Percona. 11 

12 .Auto-Managed Undo Tablespace Do not use system table space for undo space any more Automatically reclaim space on disk from large transactions © 2018 Percona. 12 

13 .Self Tuning (limited to Innodb) •innodb_buffer_pool_size Set innodb_dedicated_server to auto-tune •innodb_log_file_size •innodb_flush_method © 2018 Percona. 13 

14 .Partial In-Place Update for JSON Can update field in JSON object without full re-write Great for counters, statuses, timestamps etc Only update and removal of element is supported Innodb Support Added as of MySQL 8.0.4 © 2018 Percona. 14 

15 .Invisible Indexes • Test impact of dropping indexes before actually dropping them • Can use use_invisible_indexes to use invisible indexes in a session © 2018 Percona. 15 

16 .TmpTable Storage Engine More efficient storage engine for Internal Temporary tables Efficient storage for VARCHAR and VARBINARY columns BLOB/TEXT Columns are not supported (yet?) © 2018 Percona. 16 

17 .Backup Locks Prevent operation which may result in inconsistent backups LOCK INSTANCE FOR BACKUP © 2018 Percona. 17 

18 .Optimizer Histograms • Detailed Statistics on Columns, not just Indexes © 2018 Percona. 18 

19 .Improved Optimizer Cost Model Keep in account how much of data is cached vs on disk http://bit.ly/2CZu6z6 © 2018 Percona. 19 

20 .More on MySQL 8 Optimizer http://www.unofficialmysqlguide.com/ © 2018 Percona. 20 

21 .Performance Schema (Fake) Indexes for Faster Access Error Instrumentation Response Time Histograms (Global and Per Query Digest) Query Examples for Summary by Digest © 2018 Percona. 21 

22 .Performance Schema Performance • Now is Interactively Usable at Scale © 2018 Percona. 22 

23 .Remote Management Features RESTART (Command) SET PERSIST innodb_buffer_pool_size = 1024 * 1024 * 1024; © 2018 Percona. 23 

24 .Assumes storage is SSD by Default Start of the long journey © 2018 Percona. 24 

25 .Binary Log On by Default bin_log is enabled by default log_slave_updates is enabled by default Expire logs after 30 days by default © 2018 Percona. 25 

26 .Query Cache Removed It’s design caused more problems than it fixed Use ProxySQL (or other) external query cache instead © 2018 Percona. 26 

27 .Native Partitioning Only Only “Native” Partitioning supported, not Generic One Remove partitions from MyISAM partitioned tables or convert them ALTER TABLE ... REMOVE PARTITIONING ALTER TABLE ... ENGINE=INNODB © 2018 Percona. 27 

28 .Resource Groups • Isolation and Better Performance © 2018 Percona. 28 

29 .Plain Better Performance at Scale © 2018 Percona. 29