Notes from InnoDB status, architecture and new features

Here is the notes from “InnoDB status, architecture and new features” by Heikki Tuuri and Ken Jocobs.

• Introduction
  • Announced Plugin 1.0 for MySQL 5.1
  • fast, reliable, proven
  • fast
    • row-level locking, MVCC -> high concurrency & throughput
    • high performance CPU, memory and I/O architecture
    • efficient indexing (covering)
  • reliable
    • automatic crash recovery
    • integrated referencial integrity and transactions
    • online backup
    • well written, well tested and large user community
• New in 5.1
  • plugin 1.0 announced
  • can be usable with partitioning, row based replication
  • reduced “next-key” locking with READ COMMITTED
  • optimized create/drop index
  • optimized create/drop of secondary keys
• Auto-increment in 5.1
  • Auto increment usedto lock around the counter till SQL statement exists in the engine
  • new in 5.1 lighter-weight locking controlled by InnoDB_autoinc_lock_mode
    • 0 – traditional
    • 1 – consecutive
    • 2 – interleaved, faster
• Plugin new features
  • fast index creating (add/drop without copying the whole table data)
  • data compression
  • new row format (off-page storage for BLOB, TEXT and VARCHAR)
  • Dynamically installed without re-linking
  • can downgrade to standard edition at any time
  • forums.innodb.com for support and QA
• InnoDB plugin setup
  • edit my.cnf to have skip_innodb, innodb_file_per_table, innodb_file_format=Barracuda
  • start mysql sever
  • login to server and issue the following set of two commands (INSTALL PLUGIN INNODB SONAME ‘ha_innodb.so’ and
    INSTALL PLUGIN INNODB_LOCKS SONAME ‘ha_innodb.so’ )
  • SHOW PLUGINS to make sure InnoDB is listed
• Fast Index Create
  • just builds the new index rather than whole table
  • sorts data on secondary key
  • inserts rows into the index
  • much faster as it does not re-create the table
  • DROP INDEX for secondary is much faster, data dictionary change only
  • Need to recreate the whole table if its primary key
  • use one alter table to recreate several secondary indexes
  • adding/dropping foreign keys still needs re-building of tables
  • benchmark: 3G table add index used to take 88 minutes, but now just takes 8 minutes (5.1)
  • Faster index scans
• Concurrency
  • add/drop index must wait for active updates
  • create of secondary index locks table in shared mode
  • create/drop of primary key index needed exclusive lock
• Compression
  • compressed page size per table KEY_BLOCK_SIZE=8 (8 & 4)
  • Need innodb_file_per_table=1 and innodb_file_format=Barracuda
  • uses zlib
  • patterns in data will define compression ratio (50%)
  • Innodb can predict if 16KB page will compress to 8KB
  • compresses all data in table and indexes
• Compression and buffer pool
  • caches compressed (disk) pages in the buffer pool
  • if page is frequently used, keeps it uncompressed
  • LRU algorithm balances memory use to save CPU
  • with CPU bound, do not use compression
• When to use compression
  • when IO bound
  • compression means more data pages in the buffer pool
  • do not compress small and frequently used tables
• When not to use compression
  • when it is CPU bound
• How to monitor compression
  • using INFORMATION_SCHEMA.Innodb_CMP table
• File format
  • before pre-plugin model, by default it is Antelope
  • now it can be controlled using innodb_file_format
  • Barracuda enables compression, ROW_FORMAT=Dynamic
  • Existing engine can access Antelope but not Burracuda
• Information schema now has a way to query Innodb locks (INSTALL PLUGIN INNODB_LOCKS
• Truncate table reclaims .ibd file space
• Innodb strict mode respects SQL Syntax (innodb_strict_mode)

Try the new plugin today (well I will do it over the weekend anyway)