[譯]InnoDB官方博客:InnoDB Plugin的性能和可伸縮性
Jun 07, 2016 pm 04:32 PM本文內(nèi)容遵從CC版權(quán)協(xié)議, 可以隨意轉(zhuǎn)載, 但必須以超鏈接形式標明文章原始出處和作者信息及版權(quán)聲明網(wǎng)址: http://www.penglixun.com/tech/database/plug-in-for-performance-and-scalability.html 原文地址:http://blogs.innodb.com/wp/2009/03/plug-in-for-
本文內(nèi)容遵從CC版權(quán)協(xié)議, 可以隨意轉(zhuǎn)載, 但必須以超鏈接形式標明文章原始出處和作者信息及版權(quán)聲明網(wǎng)址: http://www.penglixun.com/tech/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:
為什么你應(yīng)該關(guān)注最近的InnoDB Plugin 1.0.3版?一個詞:性能!這個版本包括了這些特性
- Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
- 增強的并發(fā)可可伸縮性:”Google 多處理機 補丁” 為Mutext鎖操作使用原子操作
- More efficient memory allocation: ability to use more scalable platform memory allocator
- 更有效的內(nèi)存分配:可以使用更多的可擴展內(nèi)存 分配器(例如tcmalloc)
- Improved out-of-the-box scalability: unlimited concurrent thread execution by default
- 改進的即裝即用擴展性:默認無限制的線程并發(fā)
- Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing
- 動態(tài)優(yōu)化調(diào)整:在運行時,打開或者關(guān)閉插入緩 存和自適應(yīng)哈希索引
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?
這些新的性能特新可以提升多大兩倍甚至更多的的吞吐量,這依賴于你的負載,平臺和其他調(diào)整事項。在另一篇文章中,我們會探 討這些改變的一些細節(jié),但首先,我們現(xiàn)探討這些性能和可擴展性的增強是什么意思,包括哪些內(nèi)容
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.
總之,我們已經(jīng)使用內(nèi)存駐留數(shù)據(jù)庫(所有數(shù)據(jù)都載入在內(nèi)存中)測試了三種不同的工作負載(關(guān)聯(lián),DBT2 OLTP和修改過的sysbench)。在所有的情況下,InnoDB Plugin的伸縮性明顯優(yōu)于MySQL 5.1內(nèi)置的InnoDB。在一些場景中,性能提升的水平高的驚人。下面的圖說明了InnoDB Plugin 1.0.3的性能提升。你的測試結(jié)果可能不同,當然可以在InnoDB網(wǎng)站看到所有測試的細節(jié)。
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 …
現(xiàn)在來看我們的測試結(jié)果…
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).
關(guān)聯(lián):下圖展示了執(zhí)行Join操作時的性能提升,內(nèi)置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的內(nèi)置InnoDB,隨著并發(fā)數(shù)的增加系統(tǒng)的執(zhí)行速度反而下降了。與此相反,InnoDB Plugin隨著并發(fā)的提升處理速度甚至略有提高,并且隨著用戶的增長保持著這種性能。這個性能改善很大程度上是因為對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.
事務(wù)處理(DBT2):下入展示了用DBT2測試OLTP讀寫性能的提升,再次比較了內(nèi)置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線程時表現(xiàn)更好,產(chǎn)生比64線程多大約12%的吞吐量。由于其他性能瓶頸或系統(tǒng)容量達到基線。這個提升依然主要依賴于 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線程都展示了明顯優(yōu)于內(nèi)置InnoDB的可伸縮性。在64并發(fā)時多大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場景,但是不同于內(nèi)置InnoDB 的是InnoDB Plugin使用了tcmalloc作為內(nèi)存分配器。
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!
當設(shè)置innodb_user_sys_malloc變量為tcmalloc作為內(nèi)存分配器時,InnoDB Plugin依然是亮點!事務(wù)吞吐量繼續(xù)擴展,在64并發(fā)時吞吐量提升接近1倍(相對沒有tcmalloc的)。

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

The performance comparison of PHP array key value flipping methods shows that the array_flip() function performs better than the for loop in large arrays (more than 1 million elements) and takes less time. The for loop method of manually flipping key values ??takes a relatively long time.

Performance comparison of different Java frameworks: REST API request processing: Vert.x is the best, with a request rate of 2 times SpringBoot and 3 times Dropwizard. Database query: SpringBoot's HibernateORM is better than Vert.x and Dropwizard's ORM. Caching operations: Vert.x's Hazelcast client is superior to SpringBoot and Dropwizard's caching mechanisms. Suitable framework: Choose according to application requirements. Vert.x is suitable for high-performance web services, SpringBoot is suitable for data-intensive applications, and Dropwizard is suitable for microservice architecture.

Effective techniques for optimizing C++ multi-threaded performance include limiting the number of threads to avoid resource contention. Use lightweight mutex locks to reduce contention. Optimize the scope of the lock and minimize the waiting time. Use lock-free data structures to improve concurrency. Avoid busy waiting and notify threads of resource availability through events.

The best way to generate random numbers in Go depends on the level of security required by your application. Low security: Use the math/rand package to generate pseudo-random numbers, suitable for most applications. High security: Use the crypto/rand package to generate cryptographically secure random bytes, suitable for applications that require stronger randomness.

According to benchmarks, for small, high-performance applications, Quarkus (fast startup, low memory) or Micronaut (TechEmpower excellent) are ideal choices. SpringBoot is suitable for large, full-stack applications, but has slightly slower startup times and memory usage.

The performance of different PHP functions is crucial to application efficiency. Functions with better performance include echo and print, while functions such as str_replace, array_merge, and file_get_contents have slower performance. For example, the str_replace function is used to replace strings and has moderate performance, while the sprintf function is used to format strings. Performance analysis shows that it only takes 0.05 milliseconds to execute one example, proving that the function performs well. Therefore, using functions wisely can lead to faster and more efficient applications.

In PHP, the conversion of arrays to objects will have an impact on performance, mainly affected by factors such as array size, complexity, object class, etc. To optimize performance, consider using custom iterators, avoiding unnecessary conversions, batch converting arrays, and other techniques.

InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.
