Insights from Alibaba Cloud Experts: 3 Ways to Deal with High Concurrency

1) Thread Pool

1.1) Model

  • The bank is too small, or the windows are too few (small instance specification). In this case, we recommend that you expand the bank (upgrade the instance specification).
  • Low efficiency at the windows (physical server problems or database bugs; not within the scope of this article).

1.2) Applicable Scenarios

The preceding example can help summarize the applicable scenarios of the thread pool as follows:

  • There are not many SQL queries that require prolonged and non-stop execution in the scenario. When execution is stopped, it indicates that no CPU is used.
  • The transactions can accept a certain extent of loss (errors/overhead). (A certain overhead is required after the thread pool is enabled. The execution time of a simple query may be longer than that when the thread pool is not enabled, for example, the thread group that is assigned to be stalled needs to wait for some time for execution.)

1.3) Summary

The following table summarizes the overall concepts.

2) Throttling

If the “tsunami” is exceptionally violent, and you can identify a number of queries with the same characteristics in the “tsunami”, such as in the case that the Redis cache was broken down and a large number of similar or duplicate queries hit the database layer, or such as the long-running queries in the thread pool as in the preceding example, you can apply the throttling method to these requests — if the transactions support or allow degradation — to maintain the normal operation of databases.

2.1) Statement Concurrency Control

For RDS for MySQL 8.0 and PolarDB for MySQL, use the statement concurrency control feature to implement throttling on specified queries.

# High-cost slow queries
select count(*)
from jacky.mytab
where cid = 90363
or uid = ???
# Add a throttling rule to allow a maximum of one concurrent execution
call dbms_ccl.add_ccl_rule('select','jacky','mytab',1,'cid=;uid=');
# Display the current throttling rule
call dbms_ccl.show_ccl_rule();
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+
| ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+
| 2 | SELECT | jacky | mytab | Y | N | 1 | 116 | 1 | 26 | cid=;uid= |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+-----------+

2.2) DAS Throttling

The CloudDBA in the console of RDS for MySQL 5.6 and RDS for MySQL 5.7 integrates the SQL throttling feature.

3) Keep the Enemy Outside

The preceding part discusses the countermeasures at the database layer. Do we have to passively defend against the problems at the database level? Is there a way to keep the enemy outside?

3.1) Terminology

3.2) Short-lived Connection Optimization

First, let’s take a look at how a common SQL request is sent from an application to a database over the network and then processed.

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store