Bind Slow MySQL Queries to Resource Groups with a Simple PHP Tool

Overview

This PHP script automates MySQL 8.0 resource‑group management to isolate slow SQL statements. Queries whose execution time exceeds a configurable threshold are bound to the last CPU core, preventing them from monopolizing CPU resources and protecting overall system performance.

Download

GitHub repository: https://github.com/hcymysql/imprison_rg

Prerequisites

Install PHP and the MySQL extension on the host where the script will run:

yum install -y php php-mysql

Configuration

Edit imprison_rg.php and set the MySQL connection parameters and the execution‑time threshold (default 10 seconds). Example:

$hostip='127.0.0.1';
$username='admin';
$password='hechunyang';
$dbname='test';
$dbport=3306;
$long_time=10; // seconds

Running the script

Execute the script on the primary MySQL instance. It creates a resource group (named slowsql_rg by default) and binds any matching slow queries to that group:

php imprison_rg.php

Stopping and removing the resource group

php imprison_rg.php --stop

Enable resource‑group support

MySQL 8.0 resource groups require the CAP_SYS_NICE capability on the mysqld binary. Run the following commands and restart MySQL:

setcap cap_sys_nice+ep /usr/local/mysql/bin/mysqld
getcap /usr/local/mysql/bin/mysqld
systemctl restart mysqld.service

Verification

Use top (or similar) to confirm that the slow query runs on the last CPU core. The script also generates a slowlog.txt file with details of each captured query, for example:

2021-07-21 18:22:30
用户名：root
来源IP：localhost
数据库名：test
SQL语句：select sleep(3600)
资源组：slowsql_rg
执行时间：13 秒

Notes

Resource groups provide CPU isolation by assigning matching threads to a specific core (default is the last core).

The default execution‑time threshold is 10 seconds; adjust $long_time as needed.

Supported statement types are SELECT, INSERT, UPDATE, DELETE, and ALTER.