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

1) Thread Pool

1.1) Model

  • Customers are slow (slow SQL queries). In this case, we recommend that you optimize the SQL queries to reduce the execution cost.
  • 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

  • Every transaction is brief (OLTP scenario) and the performance bottleneck is the CPU resources.
  • 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

2) Throttling

2.1) Statement Concurrency Control

# 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

3) Keep the Enemy Outside

3.1) Terminology

3.2) Short-lived Connection Optimization

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com