免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 3054 | 回复: 0

5.5 innodb engine 新增功能及性能改进 [复制链接]

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2010-11-10 10:07 |显示全部楼层
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.
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP