1 introduction

CloudDBA is an intelligent diagnosis and optimization platform developed by ali Cloud database team, which can help users better use Ali Cloud database. CloudDBA continues to improve algorithms and rules to better match more user scenarios, and has just launched SQL filtering to address the impact of certain TYPES of SQL on the system.

2 Function Description

The system limits the flow of a certain TYPE of SQL(SELECT, UPDATE, or DELETE) based on the number of concurrent applications. If the number of concurrent applications of this type of SQL is smaller than the number of concurrent applications, the system discards the number of concurrent applications. This function can only be used for emergencies, not for long periods of time.

3 Impact on Services

3.1 earnings

After the PROBLEM SQL is restricted, statements that ensure that the DB Server can respond to normal services ensure that most services run normally

3.2 disadvantages

If an SQL firewall is configured, the application receives error 1317 after the SQL is restricted

query execution was interruptedCopy the code

4 Application Scenarios

If the business side can accept to give up small (small business loss) to protect large (most business normal operation), can carry out traffic limiting; If you can’t accept the error, accept the possibility that the whole business will be brought down.

4.1 Resolvable Scenarios

4.1.1 A Certain TYPE of SQL Concurrency Increases Rapidly, Affecting Normal Services

For example, cache penetration, or exception calls, may cause SQL statements with limited concurrency to suddenly increase in concurrency

4.1.2 Data Skews SQL, Affecting Normal Services

For example, when pushing, pull a particularly large data, resulting in the overall system busy

4.1.3 Normal Services Are Affected when SQL Index Is Not Created

For example, the new online SQL call volume is particularly large, and no index is created, resulting in the overall system busy

4.2 Dealing with Misunderstandings

4.2.1 Performing an Active/Standby Switchover

In the past, some O&M personnel tried to solve the problem by switching the primary and secondary databases. The configuration of the primary and secondary databases is the same. If the primary database has a problem, the same request is sent to the secondary database, so switching cannot solve the problem.

4.2.2 Kill Problem statement

The kill statement is a way to reduce system stress. However, requests are constantly coming in.

4.3 Possible solutions

I mentioned that this is just a contingency so how do you solve this problem?

  • Improve cache hit ratio
  • Using read-only instances
  • Quick indexing

5 Usage Mode

5.1 the path

RDS Console -> Find specific instances ->CloudDBA-> Problem Diagnosis ->SQL Filtering

5.2 Finding the SQL Statement to restrict

In the “problem diagnosis” section, it is observed that a certain type of SQL is executing very slowly with a large number of concurrent statements. This statement may be the problem SQL. After double-checking (as a rule of experience, with the business developer, with the boss), you can limit the concurrency.

Such as:



Want to restrict statements such as:

SELECT * FROM sbtest4 WHERE top_cmt_id=99738 AND status in (1,3)  and parent_cmt_id >= 0 order by add_time desc limit0, 20Copy the code

Perform the following steps

5.3 Creating filtering Rules

5.3.1 SQL type

Whether to restrict SELECT statements, UPDATE statements, or DELETE statements, SELECT statements are supported by default for security reasons. It is recommended that you also set SELECT statements first

5.3.2 Maximum concurrency

The maximum number of SQL runs allowed on the Server

5.3.3 Current limiting time

SQL filter maximum execution time When the specified time is exceeded, the limit is automatically terminated

5.3.4 SQL Keywords

SQL filter matching keywords, multiple keywords separated by ~, do not have special characters such as Spaces following the example above,SQL keywords I can spell like this

SELECT~FROM~sbtest4~WHERE~top_cmt_id~status~parent_cmt_id~order~add_time~descCopy the code

5.4 Resetting or Terminating filtering Rules

You can perform this operation when you encounter a rule setting error or want to terminate SQL filtering in advance.

5.5 Viewing Filtering History Records

Records of instances that have performed SQL filtering can be found here for troubleshooting.

6 Precautions

  • This function can only be used for emergencies, not for long periods of time
  • SELECT statements are supported by default for security reasons. It is recommended that you also restrict SELECT statements
  • This feature only works with MySQL 5.6
  • This function is an improvement at the kernel level, and can be used regardless of the use of high security links or common links
  • Multiple SQL filters can be set (depending on the keyword)
  • If the same TYPE of SQL keyword is set more than once, limit the concurrency by the number of concurrent and limit
  • The more strict and precise the MATCHING of SQL statements to be restricted, the less impact on services

If the setting is not precise, for example, the SQL keyword is

SELECT~FROM~sbtest4Copy the code

SQL statements that match the above rules, even normal business ones, may be restricted

  • The restricted SQL appears on the application side
1317 query execution was interruptedCopy the code
  • This restriction applies only to the instance being set and does not affect other instances under the primary account
  • This restriction rule only applies to new links. If you want to use this rule, you need to manually kill old links after setting the rule (this is done in CloudDBA).