Percona Server 8.0

1 . Percona Server 8.0 Laurynas Biveinis Technical Director @ Percona Percona Live Europe 2018 2018-11-06 1 © 2016 Percona 

2 .First of all, what is Percona Server? “…a free, fully compatible, enhanced and open source drop-in replacement for any MySQL database…” 2 © 2016 Percona 

3 .Two Ways to Define Percona Server 8.0 New / MySQL 8.0 user: Percona Server 5.7 user: ▪ Percona Server 8.0 
 ▪ Percona Server 8.0
 = MySQL 8.0
 = Percona Server 5.7
 + enhancements + MySQL 8.0 3 © 2018 Percona 

4 .We will focus on the latter definition For a Percona Server 5.7 user: ▪ Percona Server 8.0
 = Percona Server 5.7
 + MySQL 8.0 ▪ In other words, “what has changed in Percona additions since Percona Server 5.7?” 4 © 2018 Percona 

5 .What has changed since Percona Server 5.7? ▪ Features dropped ▪ Features adjusted to work with MySQL 8.0 features Does the above list seem to miss anything? ▪ “Features added?” 5 © 2018 Percona 

6 .MySQL GA features over time Generally available features MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 6 © 2018 Percona 

7 . Percona Server GA additions over time Generally available Percona features PS 5.5 PS 5.6 PS 5.7 PS 8.0 7 © 2018 Percona 

8 .Deprecated feature removal They had it coming 8 © 2016 Percona 

9 .Scalability metrics plugin Subtly broken architecture that is not easy to fix No users we are aware of 9 © 2018 Percona 

10 .INFORMATION_SCHEMA.THREAD_STATISTICS: CONCURRENT_CONNECTIONS column How to define it for thread_statistics in the first place? The value was zero for years and nobody noticed (or everybody did and were OK with it) 10 © 2018 Percona 

11 .innodb_kill_idle_transactions variable ▪ Only the variable, the feature is not deprecated ▪ Accessible through kill_idle_transactions variable ▪ Drop the innodb_ prefix to upgrade (on 5.7 already!) ▪ A remnant of generalisation of the feature from InnoDB to all SEs 11 © 2018 Percona 

12 .Query Cache enhancements ▪ log_slow_filter=qc_miss ▪ QC_hit in the slow query log extensions ▪ query_cache_strip_comments system variable ▪ “Waiting for query cache mutex” thread state in the processlist 12 © 2018 Percona 

13 .Features removed due to upstream What is the upgrade path? 13 © 2016 Percona 

14 .Query Response Time plugin Use Performance Schema execution time histograms ▪ Before: • SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME ▪ After: • SELECT COUNT(*) FROM performance_schema.events_statements_histogram_global GROUP BY COUNT_BUCKET • … many other possibilities 14 © 2018 Percona 

15 .LOCK BINLOG FOR BACKUP Query performance_schema.log_status instead ▪ Before: • Use Percona XtraBackup ▪ After: • Use Percona XtraBackup, an 8.0-compatible version (8.0-3-rc1 as of today) 15 © 2018 Percona 

16 .innodb_flush_method = ALL_O_DIRECT Set it to something else, O_DIRECT most likely ▪ Before: • Use innodb_flush_method=ALL_O_DIRECT to avoid having redo logs in the kernel FS cache ▪ After: • Oracle MySQL 8.0 redo logging is dependent on having logs in the FS cache 16 © 2018 Percona 

17 .Memory summary in SHOW ENGINE INNODB STATUS Query performance_schema.memory_summary_global_by_event_name instead ▪ Before: Internal hash tables (constant factor + variable factor) Adaptive hash index 2266736 (2213368 + 53368) Page hash 139112 (buffer pool 0 only) Dictionary cache 729463 (554768 + 174695) File system 824800 (812272 + 12528) Lock system 333248 (332872 + 376) Recovery system 0 (0 + 0) Dictionary memory allocated 174695 ▪ After: • Query PFS memory summary tables • Beware of MySQL bug 90561, fixed in 8.0.13 17 © 2018 Percona 

18 .Features removed due to lack of uptake Let us know if you are a user! 18 © 2016 Percona 

19 .Features removed due to lack of uptake ▪ INFORMATION_SCHEMA.XTRADB_RSEG ▪ Expanded program option modifiers ▪ Utility user ▪ pseudo_server_id ▪ CSV_MODE ▪ max_slowlog_files and max_slowlog_size ▪ innodb_show_verbose_locks 19 © 2018 Percona 

20 .Changed features What is the upgrade path? 20 © 2016 Percona 

21 .No more legacy partitioning in 8.0 Percona Server 8.0: native partitioning for MyRocks and TokuDB Partitioned tables must be upgraded before the upgrade to 8.0 Hence, Percona Server 5.7 also features native partitioning 21 © 2018 Percona 

22 .The biggest upstream game-changer: new DD Atomic DDL, no more FRM files, 1 billion tables… TokuDB ROW_FORMAT clause removed, use tokudb_row_format system variable CREATE COMPRESSION DICTIONARY json_keys (‘name’, ‘city’, … ) reimplemented in the new DD Expanded fast index creation will be soon 22 © 2018 Percona 

23 .SET STATEMENT FOR →/* SET_VAR */ Percona Server 5.7 (and MariaDB) per-statement variable assignments: ▪ SET STATEMENT sort_buffer_size=100000 FOR SELECT name … MySQL 8.0 variable-setting optimizer hint ▪ SELECT /*+ SET_VAR(sort_buffer_size=100000)*/ name … Percona Server has extended SET_VAR to cover the majority of SET STATEMENT use cases 23 © 2018 Percona 

24 .“userstat” duration columns now floating-point INFORMATION_SCHEMA.CLIENT_STATISTICS, THREAD_STATISTICS, USER_STATISTICS: CONNECTED_TIME, BUSY_TIME, CPU_TIME ▪ Percona Server 5.7: integers, 1s resolution, ▪ Percona Server 8.0: double precision 24 © 2018 Percona 

25 .SHOW [EFFECTIVE] GRANTS MySQL 5.7 SHOW GRANTS ▪ Shows assigned but not effective grants Percona Server 5.7 SHOW GRANTS ▪ The above was considered a bug ▪ Shows effective but not assigned grants Percona Server 8.0 SHOW [EFFECTIVE] GRANTS ▪ The above was considered a bug ▪ SHOW GRANTS: shows assigned grants ▪ SHOW EFFECTIVE GRANTS: shows effective grants 25 © 2018 Percona 

26 .Binlog space management Percona Server 5.7: max_binlog_files ▪ Manage max space in combination with max_binlog_size ▪ Was not reliable due to extra rotations Percona Server 8.0: binlog_space_limit ▪ binlog_space_limit=10G - works alone, easier to manage 26 © 2018 Percona 

27 .Percona Server 8.0: RC1 as of today RC1 released last week We are still exploring 8.0 performance Questions? 27 © 2018 Percona 

28 .Thank You Sponsors!! !28