Percona Server for MySQL 8.0

我们将讨论Percona Server for MySQL8.0的现状,现在是GA,以及它的当前发展。
关于作者:
Laurynas Biveinis
Laurynas is a senior software engineer and Percona Server lead whose primary interest is InnoDB performance. He joined Percona in March 2011 as a member of the Percona Server and Percona XtraBackup development teams. He’s been programming using C and C++ for almost two decades. Laurynas’ past industrial experience includes interning in Google as a compiler software engineer, and in academia he has contributed research of physical database indexes, including large-scale spatial models of the brain. Laurynas lives in Vilnius, Lithuania, with his wife.

展开查看详情

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