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.

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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.

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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/

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide