Percona Server 8.0

我们将介绍PerconaServer8.0——PerconaServer5.7之后的新功能,以及它与MySQL8.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