InnoDB Plugin for MySQL - Early Adopter Release User's Guide

This is the User's Guide for InnoDB Plugin 1.0 for MySQL 5.1 - Early Adopter Release, generated on Aug 25 2008 (rev 178).

Provided that you reproduce the above copyright notice, this list of conditions and the following disclaimer on all copies of this documentation (the “Documentation”) for the InnoDB Plugin for MySQL (the “Program”): (a) permission is hereby given to make a reasonable number of complete copies of the Documentation in formats of your choice, solely for your use with the Program, and (b) you may copy and distribute unmodified and complete versions of the content of this Documentation in format(s) of your choice, but only in conjunction with your otherwise-authorized distribution of the Program.

THIS DOCUMENTATION IS PROVIDED BY INNOBASE OY AND ITS CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL INNOBASE OY OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS DOCUMENTATION, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Abstract

Starting with version 5.1, MySQL AB has promoted the idea of a pluggable” storage engine architecture , which permits multiple storage engines to be added to MySQL. Beginning with MySQL version 5.1, it is possible for users to swap out one version of InnoDB and use another. The pluggable storage engine architecture also permits Innobase Oy to release new versions of InnoDB containing bug fixes and new features independently of the release cycle for MySQL.

This User's Guide documents the installation and removal procedures and the additional features of the InnoDB Plugin 1.0 for MySQL 5.1.

WARNING: Because the InnoDB Plugin introduces a new file format, restrictions apply to the use of the plugin with earlier versions of InnoDB, when using mysqldump or MySQL replication and if you use the InnoDB Hot Backup utility. See Section 1.4, “Operational Restrictions”.


Table of Contents

1. Introduction to the InnoDB Plugin
1.1. Overview
1.2. Features of the InnoDB Plugin
1.3. Obtaining and Installing the InnoDB Plugin
1.4. Operational Restrictions
2. Fast Index Creation in the InnoDB Storage Engine
2.1. Overview
2.2. Examples
2.3. Implementation
2.4. Concurrency Considerations
2.5. Crash Recovery
2.6. Limitations
3. InnoDB Data Compression
3.1. Background
3.2. Specifying Compression
3.2.1. Configuration Parameters for Compression
3.2.2. SQL Compression Syntax Warnings and Errors
3.3. How Compression Works in InnoDB
3.3.1. Compression Algorithms
3.3.2. InnoDB Data Storage and Compression
3.3.3. Compression and the InnoDB Buffer Pool
3.3.4. Compression and the InnoDB Log Files
3.4. Tuning InnoDB Compression
3.4.1. When to Use Compression
3.4.2. Monitoring Compression at Runtime
4. InnoDB File Format Management
4.1. Overview
4.2. Named File Formats
4.3. Enabling File Formats
4.4. File Format Compatibility
4.4.1. Startup File Format Compatibility Checking
4.4.2. Table-access File Format Compatibility Checking
4.5. Identifying the File Format in Use
4.6. Downgrading the File Format
4.7. Future InnoDB File Formats
5. Storage of Variable-Length Columns
5.1. Overview
5.2. COMPACT and REDUNDANT Row Format
5.3. DYNAMIC Row Format
5.4. Specifying a Table's Row Format
6. InnoDB INFORMATION_SCHEMA tables
6.1. Overview
6.2. Information Schema Tables about Compression
6.2.1. INNODB_CMP and INNODB_CMP_RESET
6.2.2. INNODB_CMPMEM and INNODB_CMPMEM_RESET
6.2.3. Using the Compression Information Schema Tables
6.3. Information Schema Tables about Transactions.
6.3.1. INNODB_TRX
6.3.2. INNODB_LOCKS
6.3.3. INNODB_LOCK_WAITS
6.3.4. Using the Transaction InfoSchema Tables
6.4. Notes on Locking in InnoDB
7. Changes for Flexibility, Ease of Use and Reliability
7.1. Overview
7.2. Viewing the InnoDB Plugin Version Number
7.3. Enabling New File Formats
7.4. Dynamically Changing innodb_file_per_table
7.5. TRUNCATE TABLE Reclaims Space
7.6. InnoDB Strict Mode
8. Installing the InnoDB Plugin
8.1. Installing the Dynamic Library
8.2. Building the Plugin from Source Code
8.2.1. Building the plugin on Unix-like systems
8.2.2. Building the Plugin on Microsoft Windows
8.3. Configuring the Plugin
9. Downgrading from the InnoDB Plugin
9.1. Overview
9.2. The Built-in InnoDB, the Plugin and File Formats
9.3. How to downgrade
9.3.1. Converting Tables
9.3.2. Adjusting the Configuration
9.3.3. Uninstalling a Dynamic Library
9.3.4. Uninstalling a Statically Built InnoDB Plugin
9.4. Possible Problems
9.4.1. Accessing COMPRESSED or DYNAMIC Tables
9.4.2. Issues with UNDO and REDO
9.4.3. Issues with the Doublewrite Buffer
9.4.4. Issues with the Insert Buffer
Index

List of Tables

3.1. Meaning of CREATE and ALTER TABLE options
3.2. Warnings and Errors with CREATE and ALTER TABLE options
6.1. Columns of INNODB_CMP and INNODB_CMP_RESET
6.2. Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET
6.3. The contents of INFORMATION_SCHEMA.INNODB_CMP under light load
6.4. The contents of INFORMATION_SCHEMA.INNODB_CMPMEM under light load
6.5. INNODB_TRX columns
6.6. INNODB_LOCKS columns
6.7. INNODB_LOCK_WAITS columns
6.8. The contents of INFORMATION_SCHEMA.PROCESSLIST in a loaded system
6.9. The contents of INFORMATION_SCHEMA.INNODB_TRX in a loaded system
6.10. The contents of INFORMATION_SCHEMA.INNODB_LOCKS in a loaded system
6.11. The contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS in a loaded system

List of Examples

6.1. Using the Compression Information Schema Tables
6.2. Identifying blocking transactions
6.3. More Complex Example of Transaction Data in Information Schema Tables