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
- versioning of tablespaces
- 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
- Cost Based Optimizer statistics
- Movement aware of static variables – everything should be dynamic
You must be logged in to post a comment.