5.5 innodb engine 新增功能及性能改进

cenalulu

For 5.5 InnoDB has been re-architected so that it can be optimized to take full advantage of modern hardware and operating system resources and efficiencies.  For brevity, I will pick off the key enhancements and refer to the MySQL docs for a complete rundown of all of the new improvements.  Here goes:

Improved Performance and Scalability on Windows - MySQL has traditionally performed well on UNIX based platforms but not so much on Windows.  With more developers now building and deploying applications on Windows, MySQL's footprint has expanded on Windows from the development desktop to the production datacenter.  In fact, Windows is now the most commonly downloaded MySQL platform.  MySQL 5.5 includes Windows specific improvements that ramp up performance and scalability for systems and applications designed to service high concurrency and user loads.  The key MySQL 5.5. improvements for Windows include:
MySQL now uses native Windows synchronization primitives to implement mutexes and locking algorithms.
MySQL now uses native Windows atomic operations vs POSIX threads to implement and free read/write specific locks.
MySQL now uses native Windows operating system memory allocators by default.
MySQL on Windows I/O thread handles maximum now equals the maximum on other platforms.
Legacy optimizations made on other platforms have now been ported to MySQL on Windows.
Many lingering Windows specific bugs have been cleaned up.
This is a biggie as MySQL on Windows is a viable alternative to the much more costly SQL Server for Windows based applications and web sites..

Improved Default Thread Concurrency - InnoDB now defaults to allow an unlimited number of concurrently executing threads, leveraging the processing power of multi-threaded and multi-core systems.  Users can override this default behavior by setting the value of innodb_thread_concurrency to the limit that best works for specific implementations.  This one actually got applause when presented at MySQL Sunday during Oracle Open World.

Control of Using Operating System Memory Allocators - Users can now control whether InnoDB uses it own memory allocator or leverages the more efficient allocators available in the current versions of the most commonly deployed operating systems.  This is easily controlled by setting the value of the new system configuration parameter innodb_use_sys_malloc in the MySQL 5.5 option file (my.cnf or my.ini).   The default setting is 1, which instructs InnoDB to use the operating system resource.

Improved scalability via Faster Locking algorithm - For most platforms (UNIX, Linux, Windows), InnoDB now uses native atomic operations vs POSIX threads to implement mutexes and read/write locks.   This boosts InnoDB performance and scale, specifically on multi-core systems.

Improved Recovery Performance - InnoDB is known for its ability to reliably recover data after a crash.  In previous versions the recovery time needed to scan and apply the redo log prior to the next startup could be exceedingly long depending on the amount of data and time between server restarts.  MySQL 5.5 includes a number of default optimizations designed to speed up the scanning and applying of redo logs so the next restart is faster.  Users who had previously sized redo logs artificially low because of slow recovery times can now increase the log file size without concern.  We actually had a customer report 10x improvement in recovery time, but mileage may vary.

Multiple Buffer Pool Instances - Today's buffer pools are consistently sized in the multi-gigabyte range, data pages are persisted and are constantly being read and updated by different database threads.  MySQL 5.5 removes the bottleneck of waiting threads when one thread is updating the buffer pool.  All the structures normally associated with the buffer pool can now be multiplied, such as its protecting mutex, the last row used (LRU) information, and the flush list.  Users can now control and tune how many buffer pool instances are used; however, for backward compatibility the default is still 1.  This feature works best with combined buffer pool sizes of several gigabytes, where each buffer pool instance can be a gigabyte or more.

Multiple Rollback Segments - InnoDB's single rollback segment has been divided into multiple parts improving performance and scalability and greatly increasing the number of concurrent transactions that can be serviced.   While previous InnoDB versions had a limit of 1023 concurrent transacations, MySQL 5.5 now allows for up to 128K concurrent transactions that create undo data (from insert, update, and delete operations) without locking/blocking contention.  This feature is backward compatible with previous InnoDB versions and file formats but requires a new system tablespace to be created and that data be imported into it to leverage the new transaction concurrency functionality.

Native Asynchronous I/O for Linux - MySQL 5.5 enables improved concurrency of I/O requests on Linux systems.  Previous versions of InnoDB have provided "simulated asynchronous I/O" for Linux by internally arranging I/O calls as if they were asynchronous, while behind the scenes the query thread would block competing threads until the I/O request was complete. MySQL 5.5 now provides true native asynchronous I/O support for Linux and Windows based systems.  This feature requires the libaio userspace library to be installed on Linux and comes with a configuration option innodb_use_native_aio that can turned off if the new default setting is not compatible with the host I/O subsystem.

Improved Log Sys Mutex and Separate Flush List Mutex - In previous InnoDB versions a single mutex protected memory areas related to the undo log and logging operations.  This mutex blocked access to the InnoDB buffer pool, while DDL changes to the data dictionary were being logged.  MySQL 5.5 removes this limitation by splitting the legacy log_sys mutex to create a separate log_flush_order mutex.  The resulting new mutex supports a new default behavior that allows undo log and logging operations to happen without blocking other operations involving the buffer pool.

Similarly, operations involving the buffer pool and the flush list previously were protected by a single mutex, which could cause contention and unnecessary delays. In MySQL 5.5 the flush list has its own mutex, reducing contention with buffer pool operations.  This is the new default behavior and requires no configuration setting to enable.

Improved Purge Scheduling - The InnoDB purge operation is a type of garbage collection that runs periodically.  In previous versions, the purge was part of the master thread, meaning that it could block other database operations when running.  In MySQL 5.5 this operation can run in its own thread, allowing for more concurrency.  Users can control whether the purge operation is split into its own thread with the innodb_purge_threads configuration option, which can be set to 0 (the default) or 1 (for a single separate purge thread).

Improved Metadata Locking Within Transactions - In previous MySQL versions when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a data definition language ("DDL") statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.  MySQL 5.5 ensures transaction serialization by not permitting one session to perform a DDL statement on a table that is used in an incomplete transaction in another session.  This is achieved by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.  This metadata locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.  For example, if a table t1 is in use by a transaction, another session that attempts to execute DROP TABLE t1 will block until the transaction ends.

These changes, along with optimizations made to how MySQL internally manages table locking (LOCK_open) improve performance for OLTP applications, specifically those that require frequent DDL activity.