MySQL 5.5: Performance and Scalability Unleashed!

MySQL 5.5 is set to ship GA soon so I thought I would create a series of blogs that highlight the key new features  I will start with a rundown of where we have made improvements and then expand into details as things progress.  MySQL 5.5 merges the technical architectures of the MySQL database with the InnoDB storage engine so that the two should now be considered one and will move in tandem for the most part from now on (We can do this now that Oracle owns both...)  That said, the key things to know about MySQL 5.5 and the re-architected InnoDB storage engine are:

  • Oracle's InnoDB is the default database storage engine
  • Performance and Scalability on Windows is on par or better than for other platforms
  • MySQL now fully utilizes the computing power of modern multi-core architectures across all platforms
  • Replication is better and easier to monitor, manage and use
  • InnoDB provides improved features around storage, manageability and efficiency
  • Developers will like the new SIGNAL/RESIGNAL syntax and the new partitioning options
  • The new Performance Schema is good and will only get better

So, to get the ball rolling, let's delve into the first couple of bullet items:

Oracle's InnoDB is the mostly widely used MySQL storage engine and for good reason; it provides ACID compliant transactional capabilities and includes unique architectural elements that assure high performance and scalability.  It also is has built-in crash recovery, referential integrity, high levels of user concurrency and SLA exceeding response times.  Given most applications, even if they are read-intensive by nature, need most of these things, it makes complete sense to make InnoDB the default storage engine so developers can get to work building apps without the need to reconfigure things. MyISAM and the other table types are still readily available.

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.  A nice side-effect of this is that the exceedingly high number of allocated Windows events, and InnoDB's high consumption of related event handles. has gone down dramatically.
  • 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.
  • 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 recently 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 can now use multiple rollback segments 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).  This improvement reduces the contention on a single rollback mutex resulting in higher throughput.

 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 - Operations involving the buffer pool and the flush list previously were protected by a single buffer pool mutex, which could cause contention and unnecessary delays. In MySQL 5.5 the flush list has its own mutex, reducing contention with other  buffer pool operations.  This is the new default behavior and requires no configuration setting to enable. With the multiple buffer pool improvement this only gets better because each buffer pool instance has a separate flush list mutex reducing contention even further.

 
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. 

The MySQL 5.5 and InnoDB docs provide a complete rundown of all the new performance and scalability enhancements and how to enable them.

I will cover the MySQL 5.5 Replication and availability enhancements in my next post.  Thanks for reading! 

Comments:

•MySQL on Windows I/O thread handles maximum now equals the maximum on other platforms.

I think "I/O" and "thread" is slightly out-of-place in this sentence:)

I guess what the talk is about is that Innodb used to consume far too many handles (Windows events), creating memory pressure in the pools used by kernel and drivers (i.e non-paged pool). This was fixed, the number of allocated evens went down dramatically. It was a nice side-effect of using native (Vista) synchronization primitives.

Right?

Posted by wlad on November 09, 2010 at 05:06 PM GMT #

Vlad, yes! Thanks for keeping me honest on this. I have updated the post to clarify what was fixed and the end result.

Posted by Rob Young on November 10, 2010 at 08:59 AM GMT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today