[Translation] InnoDB official blog: InnoDBPlugin performance and scalability

The content of this article complies with the CC copyright agreement, can be reproduced at will, but must be indicated in the form of Hyperlink original source and author information and copyright statement URL: www.penglixun.comtechdatabaseplug-in-for-performance-and-scalability.html original address: blogs. innodb. comwp200903plug-in-for-

The content of this article complies with the CC copyright agreement, can be reproduced at will, but must be in the form of hyperlink to indicate the original source of the article and author information and copyright statement URL: http://www.penglixun.com/tech/database/plug-in-for-performance-and-scalability.html original address: http://blogs.innodb.com/wp/2009/03/plug-in-for-

The content of this article complies with the CC copyright agreement, can be reproduced at will, but must be in the form of hyperlink to indicate the original source of the article and author information and copyright statement URL: http://www.penglixun.com/tech/database/plug-in-for-performance-and-scalability.html

Address: http://blogs.innodb.com/wp/2009/03/plug-in-for-performance-and-scalability/

Why shoshould you care about the latest "early adopter" release of the InnoDB Plugin, version 1.0.3? ? One word :?Performance!The release introduces these features:
Why should you pay attention to the latest InnoDB Plugin 1.0.3? One word: performance! This version includes these features

• Enhanced concurrency & scalability: the "Google SMP patch" using atomic instructions for mutexing
• Enhanced concurrency Scalability: "Google multi-processor patch" uses atomic operations for Mutext lock operations
• More efficient memory allocation: ability to use more scalable platform memory allocator
• More Effective Memory Allocation: More Scalable Memory splitters (such as tcmalloc) can be used)
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• Improved out-of-the-box Scalability: unlimited thread concurrency by default
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing
• Dynamic Optimization and Adjustment: enable or disable the insertion of slow memory and adaptive hash indexes at runtime.

These new performance features can yield?Up to twice the throughputOr more, depending on your workload, platform and other tuning considerations. In another post, we recommend e some details about these changes, but first, what do these enhancements mean for performance and scalability?
How many times or more throughput can these new performance features be increased, depending on your load, platform, and other adjustments. In another article, we will discuss some details about these changes, but first, we will discuss what these performance and scalability enhancements mean, including

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 has strate the kinds of performance gains we 've measured with release 1.0.3 of the InnoDB Plugin. Your mileage may vary, of course. See? InnoDB website for all the details on these tests.
In short, we have tested three different workloads (associations, DBT2 OLTP and modified sysbench) using a memory resident database (all data is loaded in the memory ). In all cases, InnoDB Plugin is significantly more scalable than MySQL 5.1's built-in InnoDB. In some scenarios, the performance improvement level is astonishing. The following figure shows the performance improvement of InnoDB Plugin 1.0.3. Your test results may be different. Of course, you can view all the test details on the InnoDB website.

This release of the InnoDB Plugin incorporates? 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 re grateful for this contribution, and you will be too!
This InnoDB Plugin version includes Google's Ben Handy and Mark Callaghan patches to improve the multi-processor scalability, including the use of more effective synchronization mechanisms (Mutexing and RW-Locks) to reduce CPU utilization and competition. We are very grateful for the contribution of this patch. I believe you are also.

Now to our test results...
Now let's take a look at our test results....

Joins:The following chart shows the performance gains in memory Ming joins, comparing the built-in InnoDB in MySQL (in? Blue) with the InnoDB Plugin 1.0.3 (in? Red ).
Association: shows the performance improvement when performing the Join operation. The built-in comparison between InnoDB (blue) and InnoDB Plugin 1.0.3 (red) is shown.

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.
As you can see on the Blue column above, the built-in InnoDB of MySQL 5.1 reduces the execution speed of the system as the number of concurrent jobs increases. In contrast, InnoDB Plugin improves the processing speed or even slightly with the increase of concurrency, and maintains this performance with the increase of users. This performance improvement is largely due to the use of atomic operations on Mutexing.

Transaction Processing (DBT2 ):The following chart has strates a scalability improvement using the OLTP read/write DBT2 benchmark, again comparing the performance? The built-in InnoDB in MySQL? With the performance? InnoDB Plugin 1.0.3.
Transaction Processing (DBT2): the next entry demonstrates the improvement in OLTP read/write performance test with DBT2, and the performance of built-in InnoDB and InnoDB Plugin 1.0.3 is compared again.

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.
Here, InnoDB Plugin provides better scalability when 16 is increased to 32 threads, resulting in a throughput of about 12% more than 64 threads. Other performance bottlenecks or system capacity have reached the baseline. This improvement is still mainly dependent on Mutexing changes.

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.
Modified sysbench: This test uses the famous sysbench. modifications include queries based on non-primary key indexes, which are recommended by Google's 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.
This time, InnoDB Plugin is in 8 ~ 64 threads demonstrate higher scalability than built-in InnoDB. How much is the performance improved by 60% when the concurrency is 64! As in the previous example, this improvement still relies mainly on the atomicity of 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.
Modified sysbench using tcmalloc: This test uses the same sysbench scenario. However, unlike the built-in InnoDB, InnoDB Plugin uses tcmalloc as the memory distributor.

When en the new configuration parameter?innodb_use_sys_mallocIs 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!
When the innodb_user_sys_malloc variable is set to tcmalloc as the memory distributor, InnoDB Plugin is still a bright spot! Transaction throughput continues to expand. The throughput increases by nearly doubled when the concurrency is 64 (compared with that without tcmalloc ).