Control slow and sleeping MySQL queries

Owl AutoMySQL selects eligible hosting MySQL users for query control. Long-running or sleeping connections can retain memory, connection slots or open transactions.

Why sleeping connections are included

Sleeping (SLEEP) connections can also be terminated when they exceed Maximum query duration. Read the high-load notification to identify the affected account and check its legitimate work before changing the policy.

Review recorded kills from the dashboard

1. On System Dashboard, choose the server scope and select Slow Queries Killed.
2. Filter the server, MySQL user, mode, time and minimum duration, then Apply filters. Use View for the intended record.

A retained Automatic record, not a currently running connection or a new termination. Identifying values are concealed. Select the image for full size; use browser Back to return.

3. Read the recorded outcome and Recorded SQL when available. Private details require Dashboard and Advanced Settings permissions; SQL may be truncated or unavailable for older records.

The 52-second example is a retained result; it does not verify current process state. Select the image for full size; use browser Back to return.

Recommended query duration

For optimal performance, keep Maximum query duration at the default 30 seconds in both automatic and manual modes. Lowering this value is not recommended.

4. With Owl monitoring enabled, open Settings and check Settings apply to … only. Set Maximum query duration (seconds) to 1–86,400 whole seconds; it applies to automatic mode and the manual blacklist. Owl AutoMySQL, Add exclusion → Add, or Exclude this MySQL user from Details also stage edits; review them before Save changes.

The duration is editable; the saved 30-second value is an observation, not a fresh default or successful activation. No duration, exclusion or switch change was saved. Select the image for full size; use browser Back to return.

Follow the shared query drawer guidance for time ranges, pagination, private-detail availability and unsaved-change recovery. Automatic exclusions span a user’s databases; an account entry covers all its associated MySQL users. They do not affect manual blacklist mode, and an account-level exclusion can remain after an exact-user entry is removed. Use the settings steps below to change the manual blacklist.

Configure automatic selection and exclusions

1. Open System Settings → General Settings → Monitoring on the intended server.
2. Enable Owl monitoring and Owl AutoMySQL.
3. Set Maximum query duration; the packaged value is 30 seconds.
4. Add needed AutoMySQL excluded users, separated with |, then select Save changes and inspect the result.
5. Use Dashboard Advanced Tools → Monitoring cPFence Logs → Monitor AutoMySQL Killed Queries to inspect activity.

Automatic exclusions and the manual user list apply in different modes. Check the intended server and user scope. Select the image to enlarge it; use your browser's Back command to return.

Check the identity and exclusion scope

Use the actual MySQL username to exclude one database user. The current field also accepts a verified hosting account name when you deliberately want to exclude its associated database users. A domain name is not a database username. Automatic exclusions apply only in automatic mode.

If the database username is uncertain, ask the database administrator. From an authorized root MySQL session, they can list usernames with:

SELECT user FROM mysql.user GROUP BY user ORDER BY user;

Set account resource limits separately

Query control alone does not bound all CPU or memory use. Review the identified account and set suitable CPU/RAM limits in Enhance, then adjust from measured behavior. No single allocation fits every server or workload.

Discuss recurring problems with the client so they can improve the affected scripts, plugins or themes.

Select users manually

1. In the same Monitoring group, turn Owl AutoMySQL off while keeping Owl monitoring on.
2. Add the intended MySQL users or verified hosting accounts to Blacklisted database users, separated with |.
3. Set Maximum query duration, select Save changes, and inspect the killed-query log.

Review the duration against legitimate database work before changing query control. Select the image to enlarge it; use your browser's Back command to return.

The manual list applies only while automatic mode is off. To stop both modes, turn automatic mode off and clear the manual list. This policy controls eligible MySQL connections.

CLI alternative

The on/off controls are also available under System Settings → Advanced Settings → Owl AutoMySQL. Check their server targets before confirming.

Check targets above the menu. Disabling automatic mode does not clear the manual user list. Select the image to enlarge it; use your browser's Back command to return.

Use cpfence --owl-automysql-on or cpfence --owl-automysql-off on the intended server. Follow activity with cpfence --monitor-killed-queries.

The killed-query view is available from Dashboard Advanced Tools. Check its server scope and keep account and query details private. Select the image to enlarge it; use your browser's Back command to return.

For manual configuration, root administrators can edit /etc/cpfcli/config.conf: OWL_AUTO_MySQL selects the mode, BLACKLISTED_USERS selects manual users, EXCLUDED_USERS_AUTO_MySQL holds automatic exclusions, and MAX_EXECUTION_DURATION sets seconds. Preserve the previous values and follow the file’s activation instructions when changing the Owl master.