InnoDB 1.1 for MySQL 5.5 User’s Guide

This is the User’s Guide for the InnoDB storage engine 1.1 for MySQL 5.5, generated on 2011-07-06 (revision: 26748) .

Copyright © 1997, 2011, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle Corporation and/or its affiliates, and shall not be used without Oracle's express written authorization. Other names may be trademarks of their respective owners.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle or as specifically provided below. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.

For more information on the terms of this license, for details on how the MySQL documentation is built and produced, or if you are interested in doing a translation, please visit MySQL Contact & Questions.

For additional licensing information, including licenses for third-party libraries used by MySQL products, see Preface and Notes.

If you want help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users.

For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.

Abstract

Beginning with MySQL version 5.1, it is possible to swap out one version of the InnoDB storage engine and use another (the “plugin”). This manual documents the latest InnoDB plugin, version 1.1, which works with MySQL 5.5 and features cutting-edge improvements in performance and scalability.

This User's Guide documents the procedures and features that are specific to the InnoDB storage engine 1.1 for MySQL 5.5. It supplements the general InnoDB information in the MySQL Reference Manual.

Because InnoDB 1.1 is integrated with MySQL 5.5, it is generally available (GA) and production-ready.

WARNING: Because the InnoDB storage engine 1.0 and above introduces a new file format, restrictions apply to the use of a database created with the InnoDB storage engine 1.0 and above, with earlier versions of InnoDB, when using mysqldump or MySQL replication and if you use the older InnoDB Hot Backup product rather than the newer MySQL Enterprise Backup product. See Section 1.4, “Compatibility Considerations for Downgrade and Backup”.


Table of Contents

1. Introduction to InnoDB 1.1
1.1. Features of the InnoDB Storage Engine
1.2. Obtaining and Installing the InnoDB Storage Engine
1.3. Viewing the InnoDB Storage Engine Version Number
1.4. Compatibility Considerations for Downgrade and Backup
2. Fast Index Creation in the InnoDB Storage Engine
2.1. Overview of Fast Index Creation
2.2. Examples of Fast Index Creation
2.3. Implementation Details of Fast Index Creation
2.4. Concurrency Considerations for Fast Index Creation
2.5. How Crash Recovery Works with Fast Index Creation
2.6. Limitations of Fast Index Creation
3. InnoDB Data Compression
3.1. Overview of Table Compression
3.2. Enabling Compression for a Table
3.2.1. Configuration Parameters for Compression
3.2.2. SQL Compression Syntax Warnings and Errors
3.3. Tuning InnoDB Compression
3.4. How Compression Works in InnoDB
4. InnoDB File Format Management
4.1. Enabling File Formats
4.2. Verifying File Format Compatibility
4.2.1. Compatibility Check When InnoDB Is Started
4.2.2. Compatibility Check When a Table Is Opened
4.3. Identifying the File Format in Use
4.4. Downgrading the File Format
4.5. Future InnoDB File Formats
5. How InnoDB Stores Variable-Length Columns
5.1. Overview of InnoDB Row Storage
5.2. Specifying the Row Format for a Table
5.3. Barracuda File Format: DYNAMIC and COMPRESSED Row Formats
5.4. Antelope File Format: COMPACT and REDUNDANT Row Formats
6. InnoDB INFORMATION_SCHEMA tables
6.1. Information Schema Tables about Compression
6.1.1. INNODB_CMP and INNODB_CMP_RESET
6.1.2. INNODB_CMPMEM and INNODB_CMPMEM_RESET
6.1.3. Using the Compression Information Schema Tables
6.2. Information Schema Tables about Transactions
6.2.1. INNODB_TRX
6.2.2. INNODB_LOCKS
6.2.3. INNODB_LOCK_WAITS
6.2.4. Using the Transaction Information Schema Tables
6.3. Special Locking Considerations for InnoDB INFORMATION_SCHEMA Tables
6.3.1. Understanding InnoDB Locking
6.3.2. Granularity of INFORMATION_SCHEMA Data
6.3.3. Possible Inconsistency with PROCESSLIST
7. InnoDB Performance and Scalability Enhancements
7.1. Overview of InnoDB Performance
7.2. Faster Locking for Improved Scalability
7.3. Using Operating System Memory Allocators
7.4. Controlling InnoDB Change Buffering
7.5. Controlling Adaptive Hash Indexing
7.6. Changes Regarding Thread Concurrency
7.7. Changes in the Read-Ahead Algorithm
7.8. Multiple Background I/O Threads
7.9. Asynchronous I/O on Linux
7.10. Group Commit
7.11. Controlling the Master Thread I/O Rate
7.12. Controlling the Flushing Rate of Dirty Pages
7.13. Using the PAUSE Instruction in InnoDB Spin Loops
7.14. Control of Spin Lock Polling
7.15. Making Buffer Pool Scan Resistant
7.16. Improvements to Crash Recovery Performance
7.17. Integration with MySQL PERFORMANCE_SCHEMA
7.18. Improvements to Performance from Multiple Buffer Pools
7.19. Better Scalability with Multiple Rollback Segments
7.20. Better Scalability with Improved Purge Scheduling
7.21. Improved Log Sys Mutex
7.22. Separate Flush List Mutex
8. Changes for Flexibility, Ease of Use and Reliability
8.1. The Barracuda File Format
8.2. Dynamic Control of System Configuration Parameters
8.2.1. Dynamically Changing innodb_file_per_table
8.2.2. Dynamically Changing innodb_stats_on_metadata
8.2.3. Dynamically Changing innodb_lock_wait_timeout
8.2.4. Dynamically Changing innodb_adaptive_hash_index
8.3. TRUNCATE TABLE Reclaims Space
8.4. InnoDB Strict Mode
8.5. Controlling Optimizer Statistics Estimation
8.6. Better Error Handling when Dropping Indexes
8.7. More Compact Output of SHOW ENGINE INNODB MUTEX
8.8. More Read-Ahead Statistics
9. Installing the InnoDB Storage Engine
10. Upgrading the InnoDB Storage Engine
11. Downgrading the InnoDB Storage Engine
11.1. Overview
12. InnoDB Storage Engine Change History
12.1. Changes in InnoDB Storage Engine 1.x
12.2. Changes in InnoDB Storage Engine 1.1 (April 13, 2010)
12.3. Changes in InnoDB Plugin 1.0.x
A. Third-Party Software
A.1. Performance Patches from Google
A.2. Multiple Background I/O Threads Patch from Percona
A.3. Performance Patches from Sun Microsystems
B. List of Parameters Changed in InnoDB 1.1 and InnoDB Plugin 1.0
B.1. New Parameters
B.2. Deprecated Parameters
B.3. Parameters with New Defaults
C. InnoDB Glossary
Index

List of Tables

3.1. Meaning of CREATE TABLE and ALTER TABLE options
3.2. CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF
4.1. InnoDB Data File Compatibility and Related InnoDB Parameters
7.1. Changes to innodb_thread_concurrency
B.1. InnoDB 1.1 New Parameter Summary
B.2. InnoDB Parameters with New Defaults

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

This is the User’s Guide for the InnoDB storage engine 1.1 for MySQL 5.5, generated on 2011-07-06 (revision: 26748) .