Percona Server for MySQL 8.0

1 .Percona Server for MySQL 8.0 Laurynas Biveinis
 Percona 

2 .First of All, What Is Percona Server for MySQL? “…a free, fully compatible, enhanced and open source drop-in replacement for any MySQL database…” !2 

3 .First of All, What Is Percona Server for MySQL? • Based on open-source MySQL community edition • With enhancements • With bugfixes • Care is taken to maintain drop-in quality unless some specific features are enabled !3 

4 .What Is Percona Server for MySQL 8.0? Two Ways to Define It: 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 + Percona 8.0-specific enhancements !4 

5 .Let’s Focus on a “New / MySQL 8.0 User” New / MySQL 8.0 user: Percona Server 8.0 = MySQL 8.0 + enhancements Additionally we will certain upgrade from Percona Server 5.7 points at the end !5 

6 .Percona Server 8.0 Enhancements over MySQL • Write-optimized storage engines • Data encryption • Enterprise features • Flexibility & management !6 

7 .Percona Server 8.0: Write-Optimized Storage Engines • InnoDB is a battle-tested B-tree-based read-optimized storage engine • LSM-tree-based RocksDB key-value store • Efficient writes, efficient compression, reads not that bad • RocksDB-based MyRocks storage engine • New in 8.0: native partitioning • New in 8.0: cross-engine consistent physical backups • TokuDB fully supported but scheduled to EOL at the end of 8.0 cycle !7 

8 .Percona Server 8.0: Data Encryption: Project Goals • Do not write any unencrypted data to disk in an InnoDB-based server • Support several key storage options • Support key rotation • Implemented through a combination of existing MySQL features, MariaDB security feature porting, & own development !8 

9 .Percona Server 8.0: Data Encryption: Current Status MySQL 8.0 MariaDB Percona Server 5.7 Percona Server 8.0 File-per-table tablespace encryption ✅ ✅ ✅ ✅ General tablespace encryption ✅ N/A ✅ ✅ Temporary tablespace encryption pre-GA pre-GA System tablespace encryption pre-GA pre-GA mysql.ibd encryption ✅ N/A N/A ✅ InnoDB redo log encryption ✅ ✅ (?) pre-GA ✅ InnoDB undo log encryption ✅ pre-GA ✅ InnoDB temp file (DDL log) encryption ✅ (?) pre-GA pre-GA Server temp file encryption ✅ (?) pre-GA pre-GA Binary log encryption ✅ ✅ (?) ✅ ✅ Key storage in a local file ✅ ✅ ✅ ✅ Key storage in Hashicorp Keyring Vault ✅ ✅ Key storage in Amazon KMS ✅ (EE) ✅ Key storage in Oracle Vault ✅ (EE) Key versioning and rotation ✅ (?) pre-GA pre-GA !9 

10 .Percona Server 8.0: Encryption in Depth • Robert Golebiowski presenting “In-depth Percona Server/MySQL encryption” today at 2:55PM @ Texas 7 !10 

11 .Percona Server 8.0: Enterprise Features • Threadpool • Audit plugin • PAM authentication plugin !11 

12 . Percona Server 8.0: 
 Flexibility & Management 

13 .Percona Server 8.0: MEMORY Engine VARCHAR/BLOB Percona Server MySQL
 MySQL MEMORY SE MEMORY SE TempTable SE Can store VARCHAR ✅ ✅ Can store BLOB ✅ (JSON, TEXT, …) Users can create ✅ ✅ tables Query optimiser can ✅ use if VARCHAR !13 

14 .Percona Server 8.0: Column Compression with Dictionaries • A problem to solve: store lots of small JSON documents efficiently • InnoDB table compression: limited compression ratio • InnoDB page compression: just don’t • Compression at application level: limited compression ratio, needs application changes • MyRocks: maybe? !14 

15 .Percona Server 8.0: Column Compression with Dictionaries • CREATE TABLE t (foo JSON COLUMN_FORMAT COMPRESSED) ENGINE=InnoDB; • Now “foo” gets compressed and decompressed transparently on each access • Create a dictionary with shared words between different rows for compression ratio improvement: • CREATE COMPRESSION DICTIONARY address_parts (‘country’ ‘state’ ‘city’ … ) • CREATE TABLE … address JSON COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY address_parts … !15 

16 .Percona Server 8.0: Backup Support • MySQL 8.0: LOCK INSTANCE FOR BACKUP • MySQL 8.0: performance_schema.log_status • Percona Server 8.0: LOCK TABLES FOR BACKUP blocks less • Percona Server 8.0: performance_schema.log_status extended for MyRocks • Percona Server 8.0: START TRANSACTION WITH CONSISTENT SNAPSHOT consistent across storage engines & binlog !16 

17 .Upgrading from Percona Server 5.7 

18 .Partitioned TokuDB & MyRocks Tables • MySQL dropped support for legacy partitioning handler, all storage engines must implement partitioning natively • Implemented for TokuDB and MyRocks in 8.0 • That is not enough for upgrades: 8.0 server cannot read 5.7-format partitioned tables • Hence native partitioning implemented in 5.7 too for the sole purpose of upgrade • ALTER TABLE … UPGRADE PARTITIONING !18 

19 .SET STATEMENT FOR → /* SET_VAR */ • Percona Server 5.7 (and MariaDB) per-statement variable assignments: • SET STATEMENT sort_buffer_size = 100000 FOR SELECT name, id … • MySQL 8.0 query optimizer hint for setting variables: • SELECT /*+ SET_VAR(sort_buffer_size=100000) */ name, id … • MySQL 8.0 implementation is restricted compared to Percona Server 5.7 one • Percona Server 8.0 addresses most restrictions !19 

20 .“Userstat” Duration Columns Now Floating-Point • INFORMATION_SCHEMA.CLIENT_STATISTICS, THREAD_STATISTICS, USER_STATISTICS tables column CONNECTED_TIME, BUSY_TIME, CPU_TIME types changed: • Percona Server 5.7: integers, providing 1 second resolution only • Percona Server 8.0: floating-point doubles !20 

21 .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 • Changed to show effective but not assigned grants • Percona Server 8.0 SHOW [EFFECTIVE] GRANTS • The above was also considered a bug • SHOWS GRANTS: shows assigned grants • SHOW EFFECTIVE GRANTS: shows effective grants !21 

22 .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: no need to combine with other variables • Easier to manage, works as expected !22 

23 .[innodb_]kill_idle_transactions • Percona Server 5.7: kill_idle_transactions & innodb_kill_idle_transactions • Identically-behaving aliases • innodb_kill_idle_transactions deprecated • Percona Server 8.0: kill_idle_transactions only !23 

24 .Removed Deprecated Features • Scalability metrics plugin • Subtly broken architecture with no easy fix • No users we are aware of • INFORMATION_SCHEMA.THREAD_STATISTICS.
 CONCURRENT_CONNECTIONS column • All of our query cache patches !24 

25 .Removed Features due to Upstream • Query Response Time plugin: replaced by Performance Schema execution time histograms • innodb_flush_method=ALL_O_DIRECT • Avoided keeping redo logs in the kernel filesystem cache • But MySQL 8.0 redo logging is dependant on logs being cached !25 

26 .Removed Features 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 • Let us know if you use any of those! !26 

27 .Thank You to Our Sponsors 

28 .Rate My Session !28