MySQL5.1 InnoDB引擎自增列的重大更新特性 New InnoDB Auto-Increment Functionality (MySQL 5.1.22)

| No Comments | No TrackBacks





New InnoDB Auto-Increment Functionality (MySQL 5.1.22)

Traditionally, that is to say, up until MySQL 5.1.22, InnoDB handled newly inserted records into an InnoDB table with an AUTO_INCREMENT column by using a global counter which held the last value for the auto-incrementing column. A lock would be placed on this counter for the duration of the SQL statement which did the inserting. After the statement completed, the lock would be released and other statements which inserted new rows into the table could acquire a lock on the counter.

This "traditional mode" worked well for many scenarios, particularly situations involving short, single-row inserts, as the time the lock was held for the statement was minimized. However, some performance issues would come into play when statements such as INSERT INTO my_innodb_table SELECT * FROM some_other_table were executed, as the lock on the auto-increment global counter would be held for an extended period of time, causing other activity to wait. To be sure, there were reasons for this strategy having to do with the way MySQL replication works -- InnoDB needed to ensure that the replaying of statements on a slave server would be identical to the master server.

Heikki Tuuri and the Innobase/Oracle team spent considerable resources to develop a solution to the above scalability problem. The new solution involves a configurable setting which can be changed without a server restart in MySQL 5.1.22. The new server variable, innodb_autoinc_lock_mode controls how InnoDB treats statements which insert rows into an InnoDB table with an AUTO_INCREMENT column. Depending on your environment -- specifically, whether you are using the binlog for replication or recovery purposes and whether you are executing "batched insert" statements-- you can set this variable to 0, 1, or 2. 0 corresponds to the traditional mode, and is not recommended except for very specific scenarios (see the doc link above). 1 represents "consecutive mode" and is the default. In this mode, only statements where InnoDB cannot determine the number of rows to be inserted will use the global auto-increment lock. All other "simple insert" statements (even those inserting multiple records in batch mode) will use a faster, lighter locking mechanism, which results in significant scalability increases. The final setting, 2, represents an "interleaved" mode and has even greater scalability improvements, but cannot be used in scenarios where the binary log is being used for recovery or statement-based replication.

Again, this is a large and important feature chance in MySQL 5.1.22, and I encourage anyone using InnoDB tables in a production environment to read up on the new documentation.




No TrackBacks

TrackBack URL:

Leave a comment


May 2016

Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

About this Entry

This page contains a single entry by 谷多 published on April 19, 2008 12:42 PM.

The Main Features of MySQL was the previous entry in this blog.

INFORMATION_SCHEMA信息数据库 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.