By Hu Wei, Senior Database Technical Expert at Alibaba Cloud
Hot spots are a common scenario in e-commerce database applications. Hot spot data includes major sellers, best-selling products, and frequently accessed inventories. Processing hot spot data is a tough task even for a mature e-commerce system such as Alibaba's because existing databases are not specially designed to store and access hot spot data with a fine granularity.
To ensure the atomicity, consistency, isolation, and durability (ACID) of transactions, standard relational databases require that different transactions concurrently update the same row of data records in a chronological manner. In optimistic concurrency control, only one of the multiple concurrent transactions to update data is successful, whereas other transactions are rolled back. In pessimistic concurrency control, each update is implemented only after the preceding transaction that occupies the row is committed or rolled back to release the row-lock.
Open-source MySQL implements pessimistic concurrency control through locking. The following figure shows a typical hot spot transaction scenario, which contains five transactions. Q3 updates a hot spot row.
Figure 1: A Typical Hot Spot Transaction Scenario
In pessimistic concurrency control through locking, the database can reach its optimal throughput when only one transaction is updating a row at a given moment. The throughput is as follows:
However, as concurrent requests increase, threads experience a significant overhead increase in context switching, lock waits, and lock wakeup, which greatly reduces the overall throughput.
The following figure shows the performance test results in a simple hot spot update scenario, where all transactions update the same row.
Figure 2: Performance in Hot Spot Update Scenarios
A simple solution is to remove lock waits. AliSQL and MySQL 8.0 provide the no_wait function, which implements optimistic concurrency control in hot spot scenarios. Any transaction in the lock-wait state is directly rolled back.
Another simple solution is to control the concurrent requests of transactions. The number of concurrent threads in the database can be limited by using a thread pool. If the thread pool contains 128 worker threads, the throughput is stabilized at the value when there are 128 concurrent threads, as shown in Figure 2. A third solution is to queue up the transactions that update the same row to reduce the overhead of InnoDB lock wakeup. In this case, the throughput approaches 1s / trxrt.
ApsaraDB for RDS Enterprise Edition increases the throughput in hot spot scenarios through two approaches:
a) Reduce the lock holding time of transactions
b) Commit transactions in group mode
The duration of a transaction includes the time of running each SQL statement in the database kernel and the time of network interaction between the client and the database. In the typical hot spot transaction scenario shown in Figure 1, a hot spot transaction includes five SQL statements and nine network sessions between the client and the database. The transaction ends before the results of the commit operation are returned to the client.
Oracle provides the returning syntax to modify data and return the modified data by using a single data manipulation language (DML) statement. ApsaraDB RDS for MySQL Enterprise Edition supports the "select from update" syntax to update a record and simultaneously return the query results. The following figure shows how to use this syntax:
With this syntax, it's easy to merge the Q3 and Q4 of the typical hot spot model in Figure 1. This saves the time of executing a query in the database and the overhead of two network sessions between the client and the database.
To commit a transaction through a non-auto-commit connection, the client must send an additional commit request to the database. To save the overhead of this network session between the client and database, ApsaraDB for RDS Enterprise Edition provides the Commit On Success, Rollback On Fail, and Target Affect Row hint syntaxes.
The Commit On Success syntax is used to automatically commit a transaction after the transaction successfully updates a hot spot row. The Rollback on Fail syntax is used to roll back the transaction if the update fails.
The Target Affect Row syntax is used to determine whether the update succeeded or failed based on how many data records are actually updated by the update statement. If Target Affect Row is set to 1, the update is successful after one data record is updated. The update fails if no data records meet the WHERE condition. The three syntaxes facilitate the implementation of business logic. Note that these hints must be used along with the hot spot update statement.
It's possible to shorten the runtime of each transaction by reducing the lock holding time of the transaction so that the throughput approaches 1s/trxrt. Use the same approach to bring the single-thread throughput to nearly 10,000 TPS/s. Beyond this point, it is difficult to further increase throughput. ApsaraDB for RDS Enterprise Edition provides the group commit method to group different transactions that concurrently update the same row. This group merges the updates made to the hot spot records in this row, increasing the system throughput several times over.
Assume that 10 transactions want to update records in the row with an ID of 3 in the t1 table by using the following update statement:
The group commit method merges the internal logic of the update statements of the 10 transactions so that the b value of the row with an ID of 3 is changed to b-10. The database's storage engine is updated only once to achieve the effect of sequentially executing the 10 transactions. This process is transparent to the client and significantly reduces the lock holding time. The resulting binlog is equivalent to the binlogs that are generated when the 10 transactions are serially executed. To enable the group update function, set the following system variable:
To verify the group update result, view the global status data:
In the above snapshot, Group_update_leader_count indicates the number of hot spot update groups, and Group_update_follower_count indicates the number of transactions in all groups. Divide Group_update_follower_count by Group_update_leader_count to calculate the average number of transactions per group. The larger the value, the better the result of group updating and merging of the updated records.
ApsaraDB RDS for MySQL Enterprise Edition provides the hot spot update function to significantly increase throughput when updating hot spot records in relational databases. This function is widely used within Alibaba. This function reduces the lock holding time of each transaction and group transactions to merge the updates made by these transactions.
To enable group update, add the Commit On Success hint to the update statement and specify the equivalent condition with the primary key in the WHERE statement. The database determines that this update statement is used to update hot spot records by committing transactions in group mode. The logic is that all transactions in a group are either successful or rolled back. That is, if any transaction in a group is rolled back by the user through active disconnection, then all the other transactions in this group are also rolled back.
In short, if you want a database to ensure high success rate throughput in hot spot scenarios, you can use the hot spot update function of ApsaraDB RDS for MySQL Enterprise Edition to modify the update statement in SQL.
Knowbox is on the Cloud: Lower Migration Costs with Dedicated Instance Clusters
Why AnalyticDB for MySQL is the Best Bet for Building a Real-time Data Warehouse
Alibaba Cloud Native Community - April 26, 2022
ApsaraDB - August 12, 2020
digoal - December 14, 2018
Apache Flink Community China - September 27, 2020
Alibaba Clouder - December 4, 2020
Alibaba Cloud Community - December 6, 2021
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for MariaDB
ApsaraDB RDS for MariaDB supports multiple storage engines, including MySQL InnoDB to meet different user requirements.
Learn More
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by ApsaraDB
