Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Upcoming MySQL Features for Modern Applications

Upcoming MySQL Features for Modern Applications

Are you running a legacy version of MySQL that is no longer supported? Oracle has provided three versions since purchasing Sun Microsystems in 2010.

- We will discuss the details of each Oracle version and identify the right version for your needs and current technology stack.
- How to integrate with your existing stack including legacy MySQL.
- What is the impact of NoSQL with MySQL.
- How the MySQL product development cycle works. How to try out beta software.

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

Ronald Bradford

August 09, 2014
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

  1. 1 @RonaldBradford Helsinki 20 Aug Stockholm 21 Aug Riga 26

    Aug Tallinn 27 Aug Ronald Bradford August 2014 Upcoming MySQL Features for Modern Applications
  2. 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
  3. EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications overview

    Improved features New features New products/tools Labs Architectural (re)structure 3
  4. 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
  5. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com MySQL

    Versions 5 1
  6. 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
  7. 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
  8. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Make

    it a plugin 8 2
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Instrumentation

    & Measurement 14 3
  15. 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
  16. 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.
  17. 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
  18. 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
  19. 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
  20. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Bigger!!!

    20 4
  21. 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
  22. 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
  23. 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
  24. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com MySQL

    runs on replication 24 5
  25. 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
  26. 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
  27. 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
  28. 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
  29. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Security

    & Authentication 29 6
  30. 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
  31. 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
  32. Upcoming MySQL Features for Modern Applications #mysql #emea2014 EffectiveMySQL.com Product

    Roadmap for the end user 32 7
  33. 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
  34. 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
  35. 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
  36. EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications Hadoop

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

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

    access GET/SET Key in InnoDB Memcache protocol 38 5.6
  39. 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/
  40. EffectiveMySQL.com #mysql #emea2014 Upcoming MySQL Features for Modern Applications LABS

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

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

    Where do you start? Where do I start? Release Notes 42
  43. 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
  44. 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
  45. 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
  46. 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
  47. 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
  48. 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
  49. 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
  50. 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
  51. 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