Transcription
Learning MySQL 5.7Jervin RealJuly 20171 / 25
Agenda1. Background2. New Features3. Upgrading to 5.72 / 25
Background and Current State3 / 25
Background and Current StateIn Comparison, MySQL 5.7 is:Percona Server 5.7MariaDB 10.1 (hybrid with 5.6)DatesFirst GA in October 2015, 5.7.9Matured enough to adopt, stream of bug fixes5.5 is now in extended support, 5.6 will be in extended support after Feb2018This means likely 8.0 could go GA early 2018Do not be behind by more than one major version if possible4 / 25
MySQL 5.7 New Features5 / 25
MySQL 5.7 New FeaturesSome of them in 30 minutes .JSONGISReplicationParallel ReplicationMulti Source ReplicationSemisync ImprovementsGroup ReplicationInnoDB EnhancementsOnline EXPLAINPerformance Schema and sys SchemaComplete List of Changes: http://www.thecompletelistoffeatures.com6 / 25
MySQL 5.7 New FeaturesJSONNative JSON Data Typeutf8mb4 character setJSON ComparatorShort-hand JSON EXTRACT operator(field- "json path")Document validation on INSERTIndexes via scalar generated columnsFunctions to CREATE, SEARCH, MODIFY and return JSON valuesCREATE 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
MySQL 5.7 New FeaturesGISInnoDB supports indexing of spatial ew-gis-features-in-mysql-5-7/Consistent naming scheme for GIS functionsGIS has been refactored internally; now based on Boost::GeometryGeohash functionsGeoJSON functionsFunctions: ST Distance Sphere, ST MakeEnvelope, ST IsValid, ST Validate,ST Simplify, ST Buffer and ST IsSimple8 / 25
MySQL 5.7 New FeaturesReplication (1)Online changes to Replication FiltersCHANGE REPLICATION FILTERREPLICATE WILD DO TABLE ('db1.old%');Parallel replication - Group CommitSTOP SLAVE;SET GLOBAL slave parallel workers 32;SET GLOBAL slave parallel type 'LOGICAL CLOCK';START SLAVE;Multi-source replicationFOR CHANNEL 'channel'Replication filters not configurable per channelSupport multi-threaded slaveMonitoring support in Performance Schema9 / 25
MySQL 5.7 New FeaturesReplication (2)Improved Semisync ReplicationSemi-sync can now wait for N slaves acknowledgementrpl semi sync master wait for slave count NOnline GTID Deployment (already available on Percona Server 5.6)Group Replication10 / 25
MySQL 5.7 New FeaturesInnoDB (1)Online Buffer Pool ResizeSET GLOBAL innodb buffer pool size 402653184;SHOW GLOBAL STATUS LIKE 'innodb buffer pool resize%';ALTER TABLE RENAME INDEX; meta-data changeGeneral Tablespace SupportCREATE TABLESPACE testADD DATAFILE '/tmp/tmp general tablespace.ibd';ALTER TABLE test.title TABLESPACE test;CREATE TABLE test.testtblspc (a int) TABLESPACE test;11 / 25
MySQL 5.7 New FeaturesInnoDB (2)Separate tablespace for temporary tables.No redo log, special UNDO log for ROLLBACK TO SAVEPOINT.No fsync()s, reduces IO overheadVirtual ColumnsMetadata only, not stored by defaultCan be indexed to support tricky queries12 / 25
MySQL 5.7 New FeaturesEXPLAIN for CONNECTIONmysql 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
MySQL 5.7 New FeaturesPerformance Schema and sys Schema (1)Overhead has been reduced in client connect/disconnect phasesMemory footprint has been reduced, auto-size/auto-scale - but neverdeallocatedNumber of new instrumentations added, including watching itself i.e.memory usage, lost metricsMDL lockMemory per user, by event, etcEstimation for ALTER statement progressSYS schema is now bundled by default14 / 25
MySQL 5.7 New FeaturesPerformance Schema and sys Schema (2)100 new views, 21 new stored functions and 26 new stored proceduresTable IO statistics are now batched for improved performanceFor workloads that are read heavyBetter replication monitoring specially for multi-source and multithreaded15 / 25
Upgrading to 5.716 / 25
Upgrading to 5.7Installationtest database no longer createdAnonymous users no longer createdRandom password generated during installAuto generation of SSL keys (CA, cert, key) by default17 / 25
Upgrading to 5.7New Configuration Defaults (Replication)binlog format ROW# OLD: STATEMENTbinlog gtid simple recovery 1# OLD: 0binlog error action ABORT SERVER# OLD: IGNORE ERRORslave net timeout 60# OLD: 3600sync binlog 1# OLD: 018 / 25
Upgrading to 5.7New Configuration Defaults (InnoDB)innodb buffer pool dump at shutdown 1innodb buffer pool load at startup 1# OLD: 0# OLD: 0innodb file format Barracudainnodb default row format DYNAMIC# OLD: Antelope# OLD: COMPACTinnodb page cleaners 4innodb purge threads 4# OLD: 1# OLD: 1innodb strict mode 1innodb checksum algorithm crc32# OLD: 0# OLD: innodb19 / 25
Upgrading to 5.7New Configuration Defaults (Optimizer)internal tmp disk storage engine INNODB# OLD: MyISAM (hardcoded)eq range index dive limit 200# OLD: 10sql 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 SUBSTITUTION20 / 25
Upgrading to 5.7Deprecations and ImcompatibilitiesOld pre-4.1 password formats and functions has been removedYEAR(2) to YEAR(4)INSERT DELAYED is no longer supported21 / 25
Upgrading to 5.7Upgrade Procedure (Oracle)5.6 to 5.7 is the only upgrade path officially supportedBackup your dataRead all release notes and assessIn-Place Upgrade:Clean shutdown (innodb fast shutdown 0)Run mysql upgrade (beware of Barracuda)Logical Upgrade:mysqldump dataImport data againRun mysql upgrade to fix mysql schema22 / 25
Upgrading to 5.7Upgrade Procedure - TestsRead consistency checks with pttable-checksumWrite consistency checks with ptupgrade using slow logsPerform real world workload onseparate environmentChained replication for rollbackcontingencyTest mysql upgrade too,parallelization helps!23 / 25
Questions!24 / 25
Percona Live Europe Call for Papers is -call-for-papers25 / 25
Learning MySQL 5.7 Jervin Real July 2017 1 / 25. Agenda 1. Background 2. New Features 3. Upgrading to 5.7 . 3 / 25. 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 .
