Learning MySQL 5.7

1 .Learning MySQL 5.7 Jervin Real July 2017 1 / 25 

2 . Agenda 1. Background 2. New Features 3. Upgrading to 5.7 2 / 25 

3 .Background and Current State 3 / 25 

4 . Background and Current State In Comparison, MySQL 5.7 is: Percona Server 5.7 MariaDB 10.1 (hybrid with 5.6) Dates First GA in October 2015, 5.7.9 Matured enough to adopt, stream of bug fixes 5.5 is now in extended support, 5.6 will be in extended support after Feb 2018 This means likely 8.0 could go GA early 2018 Do not be behind by more than one major version if possible 4 / 25 

5 .MySQL 5.7 New Features 5 / 25 

6 . MySQL 5.7 New Features Some of them in <30 minutes ... JSON GIS Replication Parallel Replication Multi Source Replication Semisync Improvements Group Replication InnoDB Enhancements Online EXPLAIN Performance Schema and sys Schema Complete List of Changes: http://www.thecompletelistoffeatures.com 6 / 25 

7 . MySQL 5.7 New Features JSON Native JSON Data Type utf8mb4 character set JSON Comparator Short-hand JSON_EXTRACT operator (field->"json_path") Document validation on INSERT Indexes via scalar generated columns Functions to CREATE, SEARCH, MODIFY and return JSON values CREATE TABLE t1 (jdoc JSON); INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}'); SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'); +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | JSON_OBJECT('key1', 1, 'key2', 'abc') | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | {"key1": 1, "key2": "abc"} | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ 7 / 25 

8 . MySQL 5.7 New Features GIS InnoDB supports indexing of spatial datatypes https://www.percona.com/blog/2016/02/03/new-gis-features-in-mysql-5-7/ Consistent naming scheme for GIS functions GIS has been refactored internally; now based on Boost::Geometry Geohash functions GeoJSON functions Functions: ST_Distance_Sphere, ST_MakeEnvelope, ST_IsValid, ST_Validate, ST_Simplify, ST_Buffer and ST_IsSimple 8 / 25 

9 . MySQL 5.7 New Features Replication (1) Online changes to Replication Filters CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.old%'); Parallel replication - Group Commit STOP SLAVE; SET GLOBAL slave_parallel_workers=32; SET GLOBAL slave_parallel_type='LOGICAL_CLOCK'; START SLAVE; Multi-source replication FOR CHANNEL 'channel' Replication filters not configurable per channel Support multi-threaded slave Monitoring support in Performance_Schema 9 / 25 

10 . MySQL 5.7 New Features Replication (2) Improved Semisync Replication Semi-sync can now wait for N slaves acknowledgement rpl_semi_sync_master_wait_for_slave_count = N Online GTID Deployment (already available on Percona Server 5.6) Group Replication 10 / 25 

11 . MySQL 5.7 New Features InnoDB (1) Online Buffer Pool Resize SET GLOBAL innodb_buffer_pool_size=402653184; SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_resize%'; ALTER TABLE RENAME INDEX; meta-data change General Tablespace Support CREATE TABLESPACE test ADD DATAFILE '/tmp/tmp_general_tablespace.ibd'; ALTER TABLE test.title TABLESPACE=test; CREATE TABLE test.testtblspc (a int) TABLESPACE=test; 11 / 25 

12 . MySQL 5.7 New Features InnoDB (2) Separate tablespace for temporary tables. No redo log, special UNDO log for ROLLBACK TO SAVEPOINT. No fsync()s, reduces IO overhead Virtual Columns Metadata only, not stored by default Can be indexed to support tricky queries 12 / 25 

13 . MySQL 5.7 New Features EXPLAIN for CONNECTION mysql> show processlist; +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | Id | User | Host | db | Command | Time | State | Info | +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | 18 | root | localhost | NULL | Query | 0 | init | show processlist | | 19 | root | localhost | test | Query | 4 | Sending data | select * from bbb | +‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ 2 rows in set (0.00 sec) mysql> explain for connection 19; +‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+ | id | table | type | rows | Extra | +‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+ | 1 | bbb | ALL | 215913534 | NULL | +‐‐‐‐+‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+ 1 row in set (0.00 sec) 13 / 25 

14 . MySQL 5.7 New Features Performance_Schema and sys Schema (1) Overhead has been reduced in client connect/disconnect phases Memory footprint has been reduced, auto-size/auto-scale - but never deallocated Number of new instrumentations added, including watching itself i.e. memory usage, lost metrics MDL lock Memory per user, by event, etc Estimation for ALTER statement progress SYS schema is now bundled by default 14 / 25 

15 . MySQL 5.7 New Features Performance_Schema and sys Schema (2) 100 new views, 21 new stored functions and 26 new stored procedures Table IO statistics are now batched for improved performance For workloads that are read heavy Better replication monitoring specially for multi-source and multi- threaded 15 / 25 

16 .Upgrading to 5.7 16 / 25 

17 . Upgrading to 5.7 Installation test database no longer created Anonymous users no longer created Random password generated during install Auto generation of SSL keys (CA, cert, key) by default 17 / 25 

18 . Upgrading to 5.7 New Configuration Defaults (Replication) binlog_format=ROW # OLD: STATEMENT binlog_gtid_simple_recovery=1 # OLD: 0 binlog_error_action=ABORT_SERVER # OLD: IGNORE_ERROR slave_net_timeout=60 # OLD: 3600 sync_binlog=1 # OLD: 0 18 / 25 

19 . Upgrading to 5.7 New Configuration Defaults (InnoDB) innodb_buffer_pool_dump_at_shutdown=1 # OLD: 0 innodb_buffer_pool_load_at_startup=1 # OLD: 0 innodb_file_format=Barracuda # OLD: Antelope innodb_default_row_format=DYNAMIC # OLD: COMPACT innodb_page_cleaners=4 # OLD: 1 innodb_purge_threads=4 # OLD: 1 innodb_strict_mode=1 # OLD: 0 innodb_checksum_algorithm=crc32 # OLD: innodb 19 / 25 

20 . Upgrading to 5.7 New Configuration Defaults (Optimizer) internal_tmp_disk_storage_engine=INNODB # OLD: MyISAM (hardcoded) eq_range_index_dive_limit=200 # OLD: 10 sql_mode=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION # OLD: NO_ENGINE_SUBSTITUTION 20 / 25 

21 . Upgrading to 5.7 Deprecations and Imcompatibilities Old pre-4.1 password formats and functions has been removed YEAR(2) to YEAR(4) INSERT DELAYED is no longer supported 21 / 25 

22 . Upgrading to 5.7 Upgrade Procedure (Oracle) 5.6 to 5.7 is the only upgrade path officially supported Backup your data Read all release notes and assess In-Place Upgrade: Clean shutdown (innodb_fast_shutdown=0) Run mysql_upgrade (beware of Barracuda) Logical Upgrade: mysqldump data Import data again Run mysql_upgrade to fix mysql schema 22 / 25 

23 . Upgrading to 5.7 Upgrade Procedure - Tests Read consistency checks with pt- table-checksum Write consistency checks with pt- upgrade using slow logs Perform real world workload on separate environment Chained replication for rollback contingency Test mysql_upgrade too, parallelization helps! 23 / 25 

24 .Questions! 24 / 25 

25 .Percona Live Europe Call for Papers is Open! https://www.percona.com/live/e17/percona-live-call-for-papers 25 / 25