Sharp Increase in the Commit Time of Some SQL Statements

Scenario

The commit time of some SQL statements of an RDS for MySQL instance occasionally surges from several milliseconds to hundreds of milliseconds.

Possible Causes

When the thread pool is enabled, SQL requests are put in the task queue and wait to be processed by the worker thread. There is no performance optimization for low-concurrency persistent connections. As a result, a short delay may occur due to the thread pool scheduling mechanism.

If there are a large number of concurrent requests or short connections, performance may deteriorate due to too much thread creation and destruction and context switching.

Solution

Set the threadpool_enabled parameter to OFF, restart the application or database during off-peak hours, and observe the delay. This setting takes effect immediately only for new connections.