OpenWorld 2017: InnoDB: What’s new in 8.0

Sunny Bains from Oracle

  • Legacy – Multiple Data Dictionaries (.frm & InnoDB DD)
    • changes not atomic
    • mismatch possible
    • concurrent access had to be managed
    • not crash proof
  • 8.0 is transitional so InnoDB will have mutex for this reason but will go away in future releases
  • New Data Dictionary
    • Required for transactional DDL (future)
    • stated in InnoDB
    • Control meta-data access using single locking mechanism
    • no .frm files for temporary tables – meta-data in memory only
    • Improves table spaces by removing .frm files
    • Stored in new InnoDB system tables
    • single set of persisted metadata for all storage engines
    • makes atomic DDL possible
  • InnoDB serves 2 roles
    • Data Dictionary store for all storage engines
    • implements atomic DDL (DDL_Log)
  • InnoDB gets metadata from the server not from system tables
  • DDL_Log
    • post commit is when physical deletes happen
    • during process the intent to delete is recorded in the DDL_Log
    • stored inside InnoDB
    • no direct user access
  • Performance
    • Cost Based Optimizer statistics
      • Number of pages in RAM per index
    • Remove the buffer pool mutex (Percona contribution)
      • Took a long time to fix problems in the contributed patch
      • QA team found lots of problems in edge cases
      • Foundation for more improvements in the future
    • CATS (Contention Aware Transaction Scheduling)
      • contributed by University of Michigan DB researchers
      • No configuration required
      • Switches between FIFO and CATS automatically
        • threshold is >-= 32 waiting threads
    • group records by table id when purging
      • reduces contention of dict_index_t::lock when multiple purge threads enabled
      • –innodb_deadlock_detect := bool (dynamic)
        • on high concurrent workloads deadlock detector becomes expensive so this turns it off and rely on rollback
      • –innodb_stats_include_delte_marked : bool
        • include/exclude rows marked as deleted
    • memcache improvments
      • support multiple get and range search
    • persistent auto increment
      • doesn’t rest to SELECT MAX(AUTOINC_COL) FROM T; on restart
      • probably the most requested feature since v3.x
      • Bug 199 – created 27 March 2003
    • new INFORMATION SCHEMA tabel INNODB_CACHED_INDEXES
      • pages cached in the InnoDB buffer pool cache
    • Ability to manage Undo table space
      • Add Drop undo table space
      • default of 2 undo table space required
      • undo truncate on by default
    • Avoid intermediate commits that would occur every 10000 rows
    • removed .sl files(InnoDB Symbolic Link files)
    • –innodb-read-only semantics change
      • if ON then affects entire MySQL instance
      • Because DD tables are stored in InnoDB
    • Better tablespace management
      • versioning of tablespaces
        • improves upgrade process
        • support multiple tablespace/page/row foramts
        • helps with maintaining backward compatibility
        • easier to introduce new features/capabilities
          • ie page/row formats that give better compression
    • Serialized Dictionary Information (SDI)
      • Tablespaces self describing
      • json file
      • improved import/export
      • tools for extracting
      • online offline
      • useful during disaster recovery
    • New In-Memory storage engine (temptable)
      • for internal use only
      • not shared across connections
      • lifetime limited to query life time
      • limited size, bound by ram used
        • –temptable-max-ram
    • More flexible BLOB handling
      • allow partial fetch and update
      • plan to make streaming easier
    • encrypt redo and undo logs
    • descending indexes
    • NOWAIT & SKIP_LOCK
    • Dedicated server
      • –innodb-dedicated-server (default OFF)
      • sets default value basedon physical memory available
      • dynamically sets the following variables (UNIX only)
        • –innodb-log-file-size
        • –innodb-buffer-pool-size
        • –innodb-flush-method
    • Scalable redo log
      • Dedicated redo log threads
    • Log buffer can be resized online
    • Scalable IO layer
  • Movement aware of static variables – everything should be dynamic

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