How MySQL Implements SQL Flow Control to Protect CPU Resources

Introduction

This document describes the design and usage of the SQL flow‑control (throttling) feature in MySQL. It targets developers, testers, and DBAs who need to implement or evaluate the feature.

Purpose

The feature limits the impact of non‑core or slow SQL statements on CPU usage, ensuring that critical workloads remain unaffected when the CPU is saturated.

Requirement Overview

In production, rapid business growth or slow queries can drive CPU usage to 100 %, affecting all services that depend on the database instance. A mechanism is needed to restrict such queries and preserve resources for core business.

Software Architecture

Overview Design

The flow‑control logic is placed in the executor stage, where SQL statements are matched against throttling rules.

Detailed Design

External Interfaces

Switch control command ( du_flow_control) – enables or disables flow control.

Case‑sensitivity command ( du_flow_control_case_sensitive) – toggles case‑sensitive rule matching.

Reserve‑user command ( du_flow_control_reserve_user) – specifies users exempt from throttling.

Delimiter command ( du_flow_control_delimiter) – sets the token separator used when parsing rule strings.

Example of updating the delimiter:

static bool update_delimiter(sys_var *self, THD *thd, enum_var_type type) {
  reload_rules(thd);
}

static bool check_delimiter(sys_var *self, THD *thd, set_var *var) {
  judge(var->value);
  judge(str);
  judge(length);
}

Rule Management

Read : Load throttling rules from the system table into memory at server startup, after delimiter changes, or on explicit command.

Remove : Delete rules when the server stops or when an administrator drops them.

Parse : Convert the stored rule string into a linked list of keyword patterns using the configured delimiter.

Parsing steps:

Read the rule string from the physical table into a memory buffer.

Split the buffer by the delimiter to build a pattern chain.

Flow‑Control Process

Startup : Load and parse all rules into memory; also parse the reserve‑user list.

Execution : Before executing a statement, skip throttling for replication threads, stored routines, or reserve users. Otherwise, match the query against the rule list to decide whether to throttle.

Shutdown : Release memory occupied by rule nodes.

int mysqld_main(int argc, char **argv) {
  ...
  load_rules();
  ...
}

During execution the engine maintains atomic counters cur_concur, cur_reject, and total_reject to track current concurrency and reject statistics.

bool check_rule_matched(THD* thd, LIST* list) {
  while (conditions_met) {
    // match based on case‑sensitivity flag
    it = find(query_str, item->key_array[nums]);
    if (!it) continue; // try next rule
    // additional checks …
  }
  // return match result
}

Data Access Layer

class Du_table_access {
public:
  Du_table_access() : m_drop_thd_object(NULL) {}
  virtual ~Du_table_access() {}
  bool init(THD **thd, TABLE **table, bool is_write);
  bool deinit(THD *thd, TABLE *table, bool error, bool need_commit);
  void before_open(THD *thd);
  THD *create_thd();
  void drop_thd(THD *thd);
};

Read data are stored as nodes in a LIST** for management.

Rule Table Design

SET @cmd = "CREATE TABLE IF NOT EXISTS du_flow_control_rules (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Id of the flow control rules.',
    type ENUM('SELECT','UPDATE','INSERT','DELETE') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Type of flow control rules.',
    max_concur INT NOT NULL COMMENT 'Max concurrent of sql.',
    orig_str VARCHAR(1024) CHARSET SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Original string of flow control rules.',
    PRIMARY KEY(id)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'Flow control rules info.'";
SET @str = IF(@have_innodb <> 0,
            CONCAT(@cmd, ' ENGINE=INNODB;'),
            CONCAT(@cmd, ' ENGINE=MYISAM;'));
PREPARE stmt FROM @str;
EXECUTE stmt;
DROP PREPARE stmt;

Stored procedures provide permission and format control for inserting rules:

DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE mysql.add_flow_control(
    IN sql_type INT,
    IN str VARCHAR(1024),
    IN max_num INT)
SQL SECURITY INVOKER
BEGIN
  IF sql_type = 0 THEN
    INSERT INTO mysql.du_flow_control_rules(type, max_concur, orig_str)
    VALUES('SELECT', max_num, str);
  ELSEIF sql_type = 1 THEN
    INSERT INTO mysql.du_flow_control_rules(type, max_concur, orig_str)
    VALUES('UPDATE', max_num, str);
  ELSEIF sql_type = 2 THEN
    INSERT INTO mysql.du_flow_control_rules(type, max_concur, orig_str)
    VALUES('INSERT', max_num, str);
  ELSEIF sql_type = 3 THEN
    INSERT INTO mysql.du_flow_control_rules(type, max_concur, orig_str)
    VALUES('DELETE', max_num, str);
  ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sql type is error, please input correctly.';
  END IF;
END$$
DELIMITER ;

CREATE DEFINER='root'@'localhost' PROCEDURE mysql.add_select_flow_control(
    IN str VARCHAR(1024), IN max_num INT)
SQL SECURITY INVOKER
BEGIN
  CALL add_flow_control(0, str, max_num);
END$$
DELIMITER ;

Performance Considerations

When the switch is off, the extra check adds negligible overhead.

When on, matching is performed entirely in memory; impact depends on the number and complexity of rules.

Changing the delimiter forces a full reload of all rules and should be avoided during peak load.

Usage Guidelines

Avoid modifying the delimiter in high‑traffic periods.

Prefer concise, generic rules to minimise matching cost.

Limitations

If multiple rules match, the most recently added rule takes precedence.

Queries already executing before a rule is added are not counted toward concurrency.

Stored procedures, triggers, functions, and system‑table queries are exempt from throttling.

Excessive numbers of rules can degrade performance.

Conclusion

The feature is built on native MySQL execution principles and string‑matching logic, with minimal intrusion to the core engine. All decision‑making hooks are isolated, making the implementation safe and controllable.

Reference: MySQL official documentation – https://dev.mysql.com/doc/refman/5.7/en/