D.S

dev.mysql.com

MySQL :: MySQL 5.7 Reference Manual :: A.15 MySQL 5.7 FAQ: InnoDB Change Buffer

MySQL :: MySQL 5.7 Reference Manual :: A.15 MySQL 5.7 FAQ: InnoDB Change Buffer Contact MySQL  |  Login  |  Register The world's most popular open source database MySQL.com Downloads Documentation Developer Zone Developer Zone Downloads MySQL.com Documentation MySQL Server MySQL Enterprise Workbench Router Utilities/Fabric MySQL NDB Cluster Connectors More MySQL.com Downloads Developer Zone Section Menu:   Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as a Document Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL NDB Cluster 7.5 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL sys Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions MySQL 5.7 FAQ: General MySQL 5.7 FAQ: Storage Engines MySQL 5.7 FAQ: Server SQL Mode MySQL 5.7 FAQ: Stored Procedures and Functions MySQL 5.7 FAQ: Triggers MySQL 5.7 FAQ: Views MySQL 5.7 FAQ: INFORMATION_SCHEMA MySQL 5.7 FAQ: Migration MySQL 5.7 FAQ: Security MySQL 5.7 FAQ: MySQL Cluster MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets MySQL 5.7 FAQ: Connectors & APIs MySQL 5.7 FAQ: Replication MySQL 5.7 FAQ: MySQL Enterprise Thread Pool MySQL 5.7 FAQ: InnoDB Change Buffer MySQL 5.7 FAQ: InnoDB Tablespace Encryption MySQL 5.7 FAQ: Virtualization Support Errors, Error Codes, and Common Problems Restrictions and Limits Indexes MySQL Glossary Related Documentation MySQL 5.7 Release Notes Download this Manual PDF (US Ltr) - 35.7Mb PDF (A4) - 35.7Mb PDF (RPM) - 34.7Mb EPUB - 8.7Mb HTML Download (TGZ) - 8.5Mb HTML Download (Zip) - 8.5Mb HTML Download (RPM) - 7.3Mb Eclipse Doc Plugin (TGZ) - 9.3Mb Eclipse Doc Plugin (Zip) - 11.5Mb Man Pages (TGZ) - 203.5Kb Man Pages (Zip) - 308.9Kb Info (Gzip) - 3.3Mb Info (Zip) - 3.3Mb Excerpts from this Manual MySQL Backup and Recovery MySQL Globalization MySQL Information Schema MySQL Installation Guide MySQL and Linux/Unix MySQL and OS X MySQL Partitioning MySQL Performance Schema MySQL Replication Using the MySQL Yum Repository MySQL Restrictions and Limitations Security in MySQL MySQL and Solaris Building MySQL from Source Starting and Stopping MySQL MySQL Tutorial MySQL and Windows MySQL Cluster NDB 7.5 version 5.7 8.0 5.6 5.5 5.6  Japanese MySQL 5.7 Reference Manual  /  MySQL 5.7 Frequently Asked Questions  /  MySQL 5.7 FAQ: InnoDB Change Buffer A.15 MySQL 5.7 FAQ: InnoDB Change Buffer A.15.1. What types of operations modify secondary indexes and result in change buffering? A.15.2. What is the benefit of the InnoDB change buffer? A.15.3. Does the change buffer support other types of indexes? A.15.4. How much space does InnoDB use for the change buffer? A.15.5. How do I determine the current size of the change buffer? A.15.6. When does change buffer merging occur? A.15.7. When is the change buffer flushed? A.15.8. When should the change buffer be used? A.15.9. When should the change buffer not be used? A.15.10. Where can I find additional information about the change buffer? A.15.1. What types of operations modify secondary indexes and result in change buffering? INSERT, UPDATE, and DELETE operations can modify secondary indexes. If an affected index page is not in the buffer pool, the changes can be buffered in the change buffer. A.15.2. What is the benefit of the InnoDB change buffer? Buffering secondary index changes when secondary index pages are not in the buffer pool avoids expensive random access I/O operations that would be required to immediately read in affected index pages from disk. Buffered changes can be applied later, in batches, as pages are read into the buffer pool by other read operations. A.15.3. Does the change buffer support other types of indexes? No. The change buffer only supports secondary indexes. Clustered indexes, full-text indexes, and spatial indexes are not supported. Full-text indexes have their own caching mechanism. A.15.4. How much space does InnoDB use for the change buffer? Prior to the introduction of the innodb_change_buffer_max_size configuration option in MySQL 5.6, the maximum size of the on-disk change buffer in the system tablespace was 1/3 of the InnoDB buffer pool size. In MySQL 5.6 and later, the innodb_change_buffer_max_size configuration option defines the maximum size of the change buffer as a percentage of the total buffer pool size. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50. InnoDB does not buffer an operation if it would cause the on-disk change buffer to exceed the defined limit. Change buffer pages are not required to persist in the buffer pool and may be evicted by LRU operations. A.15.5. How