MySQL 5.1 auto-inc patch in action: InnoDB scalability test

I’ve recently done a quick scalability test with MySQL 5.0 and 5.1 to check the new auto-inc patch with InnoDB and to see how MySQL 5.1 scales with InnoDB:

New in MySQL 5.1: innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
With this lock mode, “simple inserts” (only) use a new locking model where a light-weight mutex is used during the allocation of auto-increment values, and no AUTO-INC table-level lock is used, unless an AUTO-INC lock is held by another transaction. If another transaction does hold an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it too were a “bulk insert.”
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable
This fixes the Auto-Inc bug with InnoDB

I’ve run sysbench in OTLP mode against MySQL 5.0.45 and 5.1.22-rc. Insert queries with auto_inc field were running in multiple threads: 1, 4, 16, 64, 128 and 256 threads on 4 cores box. Benchmark is CPU bound (except for log flushing/fsync).

Results

With 256 threads MySQL 5.0 starts deadlocking and MySQL 5.1 shows no deadlocks.
However, MySQL 5.1 is still affected by broken group commit bug for InnoDB, so enabling bin-log and using innodb_flush_log_at_trx_commit = 1 with no battery backup cache caused significant decrease in performance: tests showed only around 100 tps constantly with MySQL 5.1 and MySQL 5.0. Waiting on InnoDB logs/bin logs serialized transactions; with this situation speed depends upon hardware. After enabling Battery Backup Cache (BBU cache) on RAID we were able to handle around 2000 tps.

PeterZ originally performed InnoDB scalability tests for MySQL 5.0 to show broken group commit bug.