P.Linux Laboratory

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/database/plug-in-for-performance-and-scalability.html

原文地址：http://blogs.innodb.com/wp/2009/03/plug-in-for-performance-and-scalability/

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:
为什么你应该关注最近的InnoDB Plugin 1.0.3版？一个词：性能！这个版本包括了这些特性

• Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
• 增强的并发可可伸缩性：”Google 多处理机 补丁” 为Mutext锁操作使用原子操作
• More efficient memory allocation: ability to use more scalable platform memory allocator
• 更有效的内存分配：可以使用更多的可扩展内存 分配器（例如tcmalloc）
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• 改进的即装即用扩展性：默认无限制的线程并发
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing
• 动态优化调整：在运行时，打开或者关闭插入缓 存和自适应哈希索引

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements mean for performance and scalability?
这些新的性能特新可以提升多大两倍甚至更多的的吞吐量，这依赖于你的负载，平台和其他调整事项。在另一篇文章中，我们会探 讨这些改变的一些细节，但首先，我们现探讨这些性能和可扩展性的增强是什么意思，包括哪些内容

In brief, we’ve tested three different workloads (joins, DBT2 OLTP and a modified sysbench) using a memory-resident database. In all cases, the InnoDB Plugin scales significantly better than the built-in InnoDB in MySQL 5.1. And in some cases, the absolute level of performance is dramatically higher too! The charts below illustrate the kinds of performance gains we’ve measured with release 1.0.3 of the InnoDB Plugin. Your mileage may vary, of course. See the InnoDB website for all the details on these tests.
总之，我们已经使用内存驻留数据库（所有数据都载入在内存中）测试了三种不同的工作负载（关联，DBT2 OLTP和修改过的sysbench）。在所有的情况下，InnoDB Plugin的伸缩性明显优于MySQL 5.1内置的InnoDB。在一些场景中，性能提升的水平高的惊人。下面的图说明了InnoDB Plugin 1.0.3的性能提升。你的测试结果可能不同，当然可以在InnoDB网站看到所有测试的细节。

This release of the InnoDB Plugin incorporates a patch made by Ben Handy and Mark Callaghan at Google to improve multi-core scalability by using more efficient synchronization methods (mutexing and rw-locks) to reduce cpu utilization and contention. We’re grateful for this contribution, and you will be too!
这个InnoDB Plugin版本包含了Google的Ben Handy和Mark Callaghan的补丁来提升多处理机扩展性，包括使用了更有效的同步机制（Mutexing和RW-Locks）来减少CPU利用和竞争。我们非常感 谢这个补丁的贡献，相信你也是。

Now to our test results …
现在来看我们的测试结果…

Joins: The following chart shows the performance gains in performing joins, comparing the built-in InnoDB in MySQL (in blue) with the InnoDB Plugin 1.0.3 (in red).
关联：下图展示了执行Join操作时的性能提升，内置InnoDB（蓝）和InnoDB Plugin 1.0.3（红）的比较。

As you can see from the blue bars in the above chart, with MySQL 5.1 using the built-in InnoDB, the total number of joins the system can execute declines as the number of concurrent users increases. In contrast, the InnoDB Plugin slightly improves performance even with one user, and maintains performance as the number of users rises. This performance improvement is due in large part to the use of atomics for mutexing in the InnoDB Plugin.
正如你在上面蓝柱上看到的，MySQL 5.1的内置InnoDB，随着并发数的增加系统的执行速度反而下降了。与此相反，InnoDB Plugin随着并发的提升处理速度甚至略有提高，并且随着用户的增长保持着这种性能。这个性能改善很大程度上是因为对Mutexing使用了原子操作。

Transaction Processing (DBT2): The following chart illustrates a scalability improvement using the OLTP read/write DBT2 benchmark, again comparing the performance of the built-in InnoDB in MySQL with the performance of InnoDB Plugin 1.0.3.
事务处理（DBT2）：下入展示了用DBT2测试OLTP读写性能的提升，再次比较了内置InnoDB和InnoDB Plugin 1.0.3的性能。

Here, the InnoDB Plugin scales better than the built-in InnoDB from 16 to 32 users and produces about 12% more throughput with 64 concurrent users, as other bottlenecks are encountered or system capacity is reached. This improvement is likewise due primarily to the changes in mutexing.
这里，InnoDB Plugin伸缩性在16增加到32线程时表现更好，产生比64线程多大约12%的吞吐量。由于其他性能瓶颈或系统容量达到基线。这个提升依然主要依赖于 Mutexing的改变。

Modified Sysbench: This test uses a version of the well-known sysbench workload, modified to include queries based on a secondary index, as suggested by Mark Callaghan of Google.
修改过的sysbench：这个测试使用了著名的sysbench，修改包括基于非主键索引的查询，由Google的 Mark Callaghan建议。

This time, the InnoDB Plugin shows significantly better scalability from 8 to 64 users than the built-in InnoDB in MySQL, yielding as much as 60% more throughput at 64 users. Like the previous examples, this improvement is largely due to the use of atomics for mutexing.
这次，InnoDB Plugin在8~64线程都展示了明显优于内置InnoDB的可伸缩性。在64并发时多大60%的性能提升！像前一个例子，这个提升依然主要靠 Mutexing的原子性。

Modified Sysbench with tcmalloc: This test uses the same modified sysbench workload, but shows the difference between the built-in InnoDB (which uses the internal InnoDB memory allocator) and the InnoDB Plugin when using a more scalable memory allocator, in this case tcmalloc.
使用tcmalloc的修改过的sysbench：这种测试使用相同的sysbench场景，但是不同于内置InnoDB 的是InnoDB Plugin使用了tcmalloc作为内存分配器。

When the new configuration parameter innodb_use_sys_malloc is set to enable use of the memory allocator tcmalloc, the InnoDB Plugin really shines! Transaction throughput continues to scale, and the actual throughput with 64 users has nearly doubled!
当设置innodb_user_sys_malloc变量为tcmalloc作为内存分配器时，InnoDB Plugin依然是亮点！事务吞吐量继续扩展，在64并发时吞吐量提升接近1倍（相对没有tcmalloc的）。