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

  • Introduction
    • 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)



Related posts:

  1. Notes on InnoDB Scale on servers with many cores
  2. Notes from Architecture of Maria Storage Engine
  3. Notes from Falcon from the beginning
  4. Notes from MySQL Cluster