1 @RonaldBradford Helsinki 20 Aug Stockholm 21 Aug Riga 26 Aug Tallinn 27 Aug Ronald Bradford August 2014 Upcoming MySQL Features for Modern Applications

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications 2 Improved Features New Features Labs New Tools 5.5 GA 5.6 GA 5.7 DMR Architecture ScaleUp Replication Security Plugins

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications overview Improved features New features New products/tools Labs Architectural (re)structure 3

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications components 1. Versions 2. Plugin Architecture 3. Instrumentation 4. Scale Up 5. Replication 6. Authentication/Security 7. Product Roadmap 4

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com MySQL Versions 5 1

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Version 5.5 GA (12/2010) Version 5.6 GA (3/2013) Version 5.7.4 DMR (3/2014) Release cycle around every 2 years 6 Oracle supplied 5.6 5.7 5.5

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Release Cycle Labs - Experimental features DMR - Development Milestone Release Version 5.7.4 GA - General Availability Version 5.6 (3/2013) Version 5.5 (12/2010) EOL - End of Life Version 5.1, 5.0, 4.1, 4.0, 3.23 DOA - Never Released Version 5.4, 6.0 7

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Make it a plugin 8 2

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL plugins 9 Plugin API Storage Engine Full Text (MyISAM) Daemon Plugins I_S Plugins Audit Plugins (Undocumented) 5.1 http://dev.mysql.com/doc/refman/5.1/en/plugin-types.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL plugins 10 Plugin API ... Semi synchronous replication Audit Plugins Authentication Plugins Thread Pool (Enterprise) 5.5 http://dev.mysql.com/doc/refman/5.5/en/plugin-types.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL plugins 11 Plugin API Password Validation Plugins http://dev.mysql.com/doc/refman/5.6/en/plugin-types.html 5.6

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL plugins 12 Plugin API InnoDB Fulltext Plugins Protocol Trace Plugins http://dev.mysql.com/doc/refman/5.7/en/plugin-types.html 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications binary log api Not a plugin, but a great extension Listener of the Binary Log (5.6 Labs) SOLR integration (3rd party FTS) Hadoop Applier (???) 13

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Instrumentation & Measurement 14 3

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications information schema SHOW 41 different options in 5.5 INFORMATION_SCHEMA 5.0 - 17 views 5.1 - 27+ views 5.5 - 39+ views 15 5.6 - 58+ views 5.7 - 59+ views 5.5 5.5 5.6 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications performance schema 5.5 17 tables 5.6 52 tables 5.7.4 75 tables 16 5.5 5.6 5.7 • 5.7.5 (not yet released) The Performance Schema now provides a user_variables_by_thread table that exposes user-defined variables. For more information, see Performance Schema Connection Attribute Tables. 
 In consequence of this change, the server now limits user-defined variable names to a maximum of 64 characters, the length of the VARIABLE_NAME column in the table. Previously, the server did not enforce a limit. The new limit is similar to the limit on the lengths of many other identifiers in MYSQL (see User- Defined Variables). Queries that use very long user-defined variable names must be rewritten to user shorter names.

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications PS HELPER Written by Mark Leith https://github.com/MarkLeith/dbahelper 5.5 - 12 views 5.6 - 29 views 5.7 - 32 views 17

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications PS HELPER Written by Mark Leith https://github.com/MarkLeith/mysql-sys/ 5.6 - 32 views 5.7 - 35 views 18

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Instrumentation What is running in my instance now? Performance Schema User/Application level 19 5.6

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Bigger!!! 20 4

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Scale up More cores More connections Faster hardware SSD Options 21 5.6 5.7 5.5 5.6 5.7 5.5 5.1

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications larger data Partitioning Better Partitioning Export/Import to table Transportable per table tablespaces 22 5.6 5.6 5.6 5.1 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Little big wins Better connection speed (2x) Thread handling Thread Pool (Enterprise) Explain a running thread Optimizer Trace 23 5.7 5.7 5.5 5.6 5.7 5.5 5.6

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com MySQL runs on replication 24 5

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications replication 101 You cannot run a MySQL system without replication Scale out HA / Failover / Failback Backups Rolling Upgrades ... 25

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications replication 101 Replication limitations Delay Drift Not crash safe Management of operations Binary/Relay logs master/slave A slave has only one master 26

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications replication 102 --dump-slave mysqlbinlog --remote... Transaction based replication (GTID) Parallel threads (MTS) Binary Log API Crash Safe Slave Meta Data 27 5.5 5.6 5.6 5.7 5.6 5.7 5.6 5.6 http://geek.rohitkalhans.com/2013/09/enhancedMTS-configuration.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications replication 102 Group Commit --sync_binlog=1 Delayed Replication Optimized row based replication Multi Master Source Semi sync Replication P_S tables 28 5.6 5.6 5.6 5.7 5.6 5.7 5.7

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Security & Authentication 29 6

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications security 101 30 CONS no ‘root’ user password create new users without passwords sql_mode='NO_AUTO_CREATE_USER' PROS Could remove/change 'root' user Could disable networking

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications security 102 31 ‘optional’ root password password expiry Alternative authentication old_passwords no longer valid Enhanced AES Encryption 192/256 bit Password Plugin 5.6 5.6 5.7 5.5 5.7 http://mysqlblog.fivefarmers.com/2012/09/04/new-5-6-password-verification-plugin-and-impacts-to-password-function/ 5.6

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Product Roadmap for the end user 32 7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL Utilities Database Management Database Operations Replication Management Configuration Management 33 1.4 http://dev.mysql.com/downloads/utilities/ http://dev.mysql.com/doc/mysql-utilities/1.4/en/index.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MySQL Fabric Shard Manager HA Manager of Shards Connector/Python Connector/J Replication via GTID 34 http://www.oracle.com/us/corporate/press/2208808 May 2014 1.4 http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications MYSQL product suite All the other products MySQL Cluster MySQL Workbench MySQL Connectors ... 35 6.1 7.3

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Hadoop Applier 36 http://dev.mysql.com/tech-resources/articles/mysql-hadoop-applier.html

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications enterprise features Enterprise Audit Pluggable Authentication Thread Pool Enterprise Monitor Enterprise Backup 37

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications NOSQL access GET/SET Key in InnoDB Memcache protocol 38 5.6

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications LABS Independent features Not a assurance for GA JSON UDFs 39 http://labs.mysql.com/

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications LABS JSON UDFs Multi-threaded slave Hadoop Applier InnoDB Compression InnoDB GIS 40

Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com What matters? 41 7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications scope Where do you start? Where do I start? Release Notes 42

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications release notes New features Change/incompatibility (DMR) Deprecating features (EOL) Crazy bugs Improved internal architecture 43

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications hardware features SSD capacity Vendor specific SSD features 44 • InnoDB: The Fusion-io Non-Volatile Memory (NVM) file system on Linux provides atomic write capability, which makes the InnoDB doublewrite buffer redundant. For system tablespace files (ibdata files) located on Fusion-io devices that support atomic writes, the InnoDB doublewrite buffer is automatically disabled. 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Crazy Bugs innodb_max_dirty_pages_pct=0 5.5+ = 75, 5.1 = 90 45 • InnoDB: With innodb_max_dirty_pages_pct=0 buffer pool flushing would not be initiated until the percentage of dirty pages reached at least 1%, which would leave up to 1% of dirty pages unflushed. (Bug #13029450, Bug #62534) 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Long past EOL How many programs in $MYSQL_HOME/bin? 46 • The deprecated mysqlbug, mysql_waitpid, and mysql_zap utilities have been removed from MySQL distributions. • The deprecated mysqlhotcopy utility has been removed from MySQL distributions. Alternatives include mysqldump and MySQL Enterprise Backup. 5.7 $ ls 5.1.72/bin | wc -l # 44 $ ls 5.5.39/bin | wc -l # 44 $ ls 5.6.20/bin | wc -l # 45 $ ls 5.7.4/bin | wc -l # 37 mysql_config_edit

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications compiling/Internals 47 • The Boost library now is required to build MySQL. Two new CMake options enable control over the library source location, and whether to download it automatically: • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel. If DTrace is present, server builds will detect it with no special CMake options required. For information about using DTrace on MySQL, see Tracing mysqld Using DTrace. • CMake support was updated to handle CMake version 3. (Bug #19001781) • MySQL now compiles using Clang 3.4. (Bug #18047020) 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Queries Improved Query Optimization Optimizer Trace Sub Queries Multi Range Read (MRR) Index Condition Pushdown (ICP) File Sort Optimization 48 5.6

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Queries Improved Query Optimization Temporary Tables DDL Index Condition Pushdown (ICP) with partitions Explain a running thread 49 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Data types Milliseconds Improved Spatial/Geo InnoDB Spatial Index IPV6 ??? 50 5.6 5.7

EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications conclusion 51 New Release of the World’s Most Popular Open Source Database is 2x Faster than MySQL 5.6 and Over 3x Faster than MySQL 5.5 in Benchmark Tests Continuing to lead MySQL innovation, Oracle announced the immediate availability of a new Development Milestone Release for MySQL 5.7. To meet the demands of today’s web, cloud, and embedded applications, the new release extends the performance, scalability and reliability of MySQL, the world’s most popular open source database, while also enhancing management capabilities. In addition, Oracle announced the availability of several other MySQL product releases and early access features. http://www.oracle.com/us/corporate/press/2180737 March 2014