SQL Zone is brought to you in partnership with:

Mitch Pronschinske is the Lead Research Analyst at DZone. Researching and compiling content for DZone's research guides is his primary job. He likes to make his own ringtones, watches cartoons/anime, enjoys card and board games, and plays the accordion. Mitch is a DZone Zone Leader and has posted 2576 posts at DZone. You can read more from them at their website. View Full User Profile

MySQL 5.5.4 Breaks Concurrency Barriers

04.16.2010
| 7724 views |
  • submit to reddit
Although some developers are worried about the long term future of MySQL under Oracle, some bloggers are voicing their excitement about the next release - MySQL 5.5.4.  This upcoming release will addresses many of the scalability bottlenecks that begin to occur in hardware with more than four cores.  Other changes involve the InnoDB, which is what Oracle decided to use as the default storage and transaction engine for MySQL.

Multiple Rollback Segments
By adding the ability to have multiple rollback segments, MySQL can break its previous limit of 1,024 concurrent transactions.  This new feature also means that concurrent transactions will experience less mutex (lock) contention:

"To keep backward compatibility the additional rollback segments are created after the double write buffer when creating the rollback segments in a new instance. We keep the total number of rollback segments set to 128 rather than 256 and set the remaining slots to NULL because the older versions will try and scan up to 256. This means that older versions of InnoDB could also benefit from these extra rollback segments.  If you decide to create the extra rollback segments with a newer version but decide to revert back to an older version the older versions should be able to use the additional rollback segments. Newer versions of InnoDB that contain this fix, will create the additional segments in existing instances only if the innodb_force_recovery flag is not set and the database instance was shutdown cleanly. The additional segments will be created by default when creating new instances."  -- InnoDB blog

Removing MySQL’s LOCK_open mutex
In version 5.5.4, LOCK_open mutex has been replaced with a Metadata Locking (MDL) Framework.  This eliminates a pesky bottleneck that occurred when going beyond 4 CPU cores in even read-only benchmarks.

Improved InnoDB Recovery Speed
The InnoDB recovery has become much faster due to some changes in the algorithms used in the redo log and replaying transactions.  A few other optimization have contributed to this performance boost as well.

InnoDB Performance Schema Support
The addition of performance schema provides drastically improved visibility into InnoDB.  Users can now look at critical server synchronization events along with their usage statistics.  Modules for Mutex, RWLOCKs, File I/O, and Threads have been instrumented.

Split Buffer Pools
This feature will mitigate InnoDB's bottleneck on the kernel mutex (lock).  About 30% more performance will be achieved in these workloads.  It works by creating a custom number of buffer pools and using a hash function to spread pages across those pools.  

Other features
New replication features in MySQL 5.5.4 improve replication durability and decrease the likelihood of problems when slaves come back online after crashing.  Another feature in InnoDB is the DELETE buffer that should make workloads with large DELETE runs more manageable.  

For more info on the latest MySQL and InnoDB improvements, check out the MySQL conference roundup.