Present State of MySQL 5.7
MySQL 5.7 will not be ultimate when it comes to scalability. The next determine illustrates the connection between TPC-C throughput and concurrency in MySQL 5.7.39 underneath a particular configuration. This contains setting the transaction isolation degree to Learn Dedicated and adjusting the innodb_spin_wait_delay parameter to mitigate throughput degradation.
Determine 1: Scalability issues in MySQL 5.7.39 throughout BenchmarkSQL testing
From the determine, it’s evident that scalability issues considerably restrict the rise in MySQL throughput. For instance, after 100 concurrency, the throughput begins to say no.
To deal with the aforementioned efficiency collapse difficulty, Percona’s thread pool was employed. The next determine illustrates the connection between TPC-C throughput and concurrency after configuring the Percona thread pool.
Determine 2: Percona thread pool mitigates scalability issues in MySQL 5.7.39
Though the thread pool introduces some overhead and peak efficiency has decreased, it has mitigated the difficulty of efficiency collapse underneath excessive concurrency.
Present State of MySQL 8.0
Let’s check out the efforts MySQL 8.0 has made concerning scalability.
Redo Log Optimization
The primary main enchancment is redo log optimization [3].
commit 6be2fa0bdbbadc52cc8478b52b69db02b0eaff40
Creator: Paweł Olchawa
Date: Wed Feb 14 09:33:42 2018 +0100
WL#10310 Redo log optimization: devoted threads and concurrent log buffer.
0. Log buffer grew to become a hoop buffer, information inside is now not shifted.
1. Consumer threads are capable of write concurrently to log buffer.
2. Relaxed order of soiled pages in flush lists - no have to synchronize
the order wherein soiled pages are added to flush lists.
3. Concurrent MTR commits can interleave on totally different levels of commits.
4. Launched devoted log threads which preserve writing log buffer:
* log_writer: writes log buffer to system buffers,
* log_flusher: flushes system buffers to disk.
As quickly as they completed writing (flushing) and there may be new information to
write (flush), they begin subsequent write (flush).
5. Consumer threads now not write / flush log buffer to disk, they solely
wait by spinning or on occasion for notification. They don't have to
compete for the accountability of writing / flushing.
6. Launched a hoop buffer of occasions (one per log-block) that are used
by consumer threads to attend for written/flushed redo log to keep away from:
* rivalry on single occasion
* false wake-ups of all ready threads at any time when some write/flush
has completed (we are able to wake-up solely these ready in associated blocks)
7. Launched devoted notifier threads to not delay subsequent writes/fsyncs:
* log_write_notifier: notifies consumer threads about written redo,
* log_flush_notifier: notifies consumer threads about flushed redo.
8. Grasp thread now not has to flush log buffer.
...
30. Mysql take a look at runner acquired a brand new function (because of Marcin):
--exec_in_background.
Overview: RB#15134
Reviewers:
- Marcin Babij ,
- Debarun Banerjee .
Efficiency assessments:
- Dimitri Kravtchuk ,
- Daniel Blanchard ,
- Amrendra Kumar .
QA and MTR assessments:
- Vinay Fisrekar .A take a look at evaluating TPC-C throughput with totally different ranges of concurrency earlier than and after optimization was performed. Particular particulars are proven within the following determine:
Determine 3: Impression of redo log optimization underneath totally different concurrency ranges
The ends in the determine present a big enchancment in throughput at low concurrency ranges.
Optimizing Lock-Sys By Latch Sharding
The second main enchancment is lock-sys optimization [5].
commit 1d259b87a63defa814e19a7534380cb43ee23c48
Creator: Jakub Łopuszański
Date: Wed Feb 5 14:12:22 2020 +0100
WL#10314 - InnoDB: Lock-sys optimization: sharded lock_sys mutex
The Lock-sys orchestrates entry to tables and rows. Every desk, and every row,
may be regarded as a useful resource, and a transaction might request entry proper for
a useful resource. As two transactions working on a single useful resource can result in
issues if the 2 operations battle with one another, Lock-sys remembers
lists of already GRANTED lock requests and checks new requests for conflicts in
which case they've to begin WAITING for his or her flip.
Lock-sys shops each GRANTED and WAITING lock requests in lists often called queues.
To permit concurrent operations on these queues, we'd like a mechanism to latch
these queues in protected and fast vogue.
Up to now a single latch protected entry to all of those queues.
This scaled poorly, and the managment of queues turn out to be a bottleneck.
On this WL, we introduce a extra granular strategy to latching.
Reviewed-by: Pawel Olchawa
Reviewed-by: Debarun Banerjee
RB:23836Primarily based on this system earlier than and after optimizing with lock-sys, utilizing BenchmarkSQL to match TPC-C throughput with concurrency, the particular outcomes are as proven within the following determine:
Determine 4: Impression of lock-sys optimization underneath totally different concurrency ranges
From the determine, it may be seen that optimizing lock-sys considerably improves throughput underneath excessive concurrency circumstances, whereas the impact is much less pronounced underneath low concurrency because of fewer conflicts.
Latch Sharding for trx-sys
The third main enchancment is latch sharding for trx-sys.
commit bc95476c0156070fd5cedcfd354fa68ce3c95bdb
Creator: Paweł Olchawa
Date: Tue Might 25 18:12:20 2021 +0200
BUG#32832196 SINGLE RW_TRX_SET LEADS TO CONTENTION ON TRX_SYS MUTEX
1. Launched shards, every with rw_trx_set and devoted mutex.
2. Extracted modifications to rw_trx_set exterior its authentic vital sections
(elimination needed to be extracted exterior trx_erase_lists).
3. Eradicated allocation on heap inside TrxUndoRsegs.
4. [BUG-FIX] The trx->state and trx->start_time grew to become transformed to std::atomic
fields to keep away from threat of torn reads on egzotic platforms.
5. Added assertions which make sure that thread working on transaction has rights
to take action (to indicate there isn't a attainable race situation).
RB: 26314
Reviewed-by: Jakub Łopuszański jakub.lopuszanski@oracle.comPrimarily based on these optimizations earlier than and after, utilizing BenchmarkSQL to match TPC-C throughput with concurrency, the particular outcomes are proven within the following determine:
Determine 5: Impression of latch sharding in trx-sys underneath totally different concurrency ranges
From the determine, it may be seen that this enchancment considerably enhances TPC-C throughput, reaching its peak at 200 concurrency. It’s price noting that the impression diminishes at 300 concurrency, primarily because of ongoing scalability issues within the trx-sys subsystem associated to MVCC ReadView.
Refining MySQL 8.0
The remaining enhancements are our impartial enhancements.
Enhancements to MVCC ReadView
The primary main enchancment is the enhancement of the MVCC ReadView information construction [1].
Efficiency comparability assessments have been performed to judge the effectiveness of the MVCC ReadView optimization. The determine under reveals a comparability of TPC-C throughput with various concurrency ranges, earlier than and after modifying the MVCC ReadView information construction.
Determine 6: Efficiency comparability earlier than and after adopting the brand new hybrid information construction in NUMA
From the determine, it’s evident that this transformation primarily optimized scalability and improved MySQL’s peak throughput in NUMA environments.
Avoiding Double Latch Issues
The second main enchancment we made is addressing the double latch downside, the place “double latch” refers back to the requirement for the worldwide trx-sys latch by each view_open and view_close [1].
Utilizing the MVCC ReadView optimized model, evaluate TPC-C throughput earlier than and after the modifications. Particulars are proven within the following determine:
Determine 7: Efficiency enchancment after eliminating the double latch bottleneck
From the determine, it’s evident that the modifications considerably improved scalability underneath high-concurrency circumstances.
Transaction Throttling Mechanism
The ultimate enchancment is the implementation of a transaction throttling mechanism to protect in opposition to efficiency collapse underneath excessive concurrency [1] [2] [4].
The next determine depicts the TPC-C scalability stress take a look at performed after implementing transaction throttling. The take a look at was carried out in a situation with NUMA BIOS disabled, limiting the entry of as much as 512 consumer threads into the transaction system.
Determine 8: Most TPC-C throughput in BenchmarkSQL with transaction throttling mechanisms
From the determine, it’s evident that implementing transaction throttling mechanisms considerably improves MySQL’s scalability.
Abstract
General, it’s totally possible for MySQL to take care of efficiency with out collapse at tens of 1000’s of concurrent connections in low-conflict situations of BenchmarkSQL TPC-C testing.
References
- Bin Wang (2024). The Artwork of Drawback-Fixing in Software program Engineering: Tips on how to Make MySQL Higher.
- The New MySQL Thread Pool
- Paweł Olchawa. 2018. MySQL 8.0: New Lock free, scalable WAL design. MySQL Weblog Archive.
- Xiangyao Yu. An analysis of concurrency management with one thousand cores. PhD thesis, Massachusetts Institute of Expertise, 2015.
- MySQL 8.0 Reference Handbook