OpenWorld 2017: MySQL Server 8.0

Morgan Tocker from Oracle gave a good overview of what is new in MySQL 8.0.  Notes:

  • 5.7 improvements
    • Group Replication
    • Performance Schema
    • SYS Schema
    • JSON Support
    • Performance
    • Partitions
    • Trigger Changes
    • MAIN changes
      • GIS support
      • JSON Types
      • Observational Management
  • Premium placed on Time to Market in modern applications
  • MysQL 8
    • GIS
      • Spatial Reference System (SRS) Support
      • Helper functions to manipulate and convert data
        • st_x
        • st_y
        • st_srid
    • JSON
      • a bunch of new functions
      • example showing how to query JSON data out of a JSON doc stored in a column
    • utf8mb4 now default (UNICODE 9)
      •  a lot already were using as default
      • emojis
      • new collations
        • Japanese and Russian
    • Performance
      • showed sysbench read only performance improvements
      • utfmb3 and utfmb4
        • 4 byte more performant so they are deprecating the 3 byte
    • UUID
      • ability to convert to bin and back
      • binary format reorders pieces of the UUID to allow them to follow insert order to avoid splits
    • Hot Row contention handling
      • NOWAIT – Error immediately
      • SKIP LOCKED – only operate on non locked rows (non deterministic)
    • Common Table Expressions
      • WITH t1 AS (SELECT * FROM tblA WHERE a=’b’) SELECT * FROM t1;
      • optimizer know if it needs to use temp table sor if it just needs to rewrite the query on its end
      • Recursive CTEs also supported
    • Analytics Queries with Windows Functions
      • PARTITION BY
    • Optimizer improvements
      • considers how much of an index is in memory vs. on disk
      • Range Scans will be used if all data is in memory
      • can see in info schema how much is in memory (estimated by InnoDB)
      • Histograms support
        • lower cost alternative to an index
        • created on demand and updated manually
        • no regular overhead until told to re-update
        • 2 types
          • equi-height
          • singleton
      • Descending indexes
      • Invisible Indexes (Hidden indexes)
        • Soft deletes
        • Staged Rollouts
        • Still maintained even if not visible
        • only a metadata change to switch visible vs invisible
      • Performance Schema
        • A lot more instrumentation
        • improved defaults so things are on
          • memory
          • transactions
          • metadata – all three on by default in MySQL 8
        • Better indexing on the performance schema – improved performance of querying this data
        • Now can see errors returned to clients from within performance schema
        • Added histograms in addition to min max average to show distribution better (write CTE to visualize)
          • per statement digest basis
        • Window into InnoDB locks – row locks
        • see where variables are sourced from (configuration variables)
      • Information Schema
        • a lot of tables are migrated to views on top of mysql tables to improve performance
        • 30 times faster
      • Consistent Configuration
        • json file that allows you to persist changes to configuration from commands in the instance
      • Crash safe data dictionary
      • New defaults
        • utf8md4
        • new collation
        • performance schema – memory, transactions, MDL on by default
        • InnoDB –
      • Some replication changes
      • Can “set persist_only innodb_dedicated_server = true” – resets settings as systems are sized up and down

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s