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