[mysql] Bound how long SHOW BINARY LOGS can block commits - #25991

What does this PR do?

Runs the SHOW BINARY LOGS query used for mysql.binlog.disk_use with a session net_write_timeout of 2s, as the last statement of the check run.

If the server hits the timeout, it drops the connection. The check logs a warning and skips the binary log metric for that run. Each run opens a new connection, and nothing else runs on it after this query, so the rest of the run is not affected. If the query ran earlier, the dropped connection would fail every later query in the run, lose the run's status metrics, and report mysql.can_connect as CRITICAL.

A dedicated connection would also avoid that, but it needs an extra slot beyond the documented MAX_USER_CONNECTIONS for Database Monitoring.

I checked the timeout behavior against the MySQL 8.0.36 test environment. With net_write_timeout = 1 and a client that stops reading a large result, the server dropped the connection while the client was not reading, and the client got error 2013 (CR_SERVER_LOST), which is the case the warning handles.

Motivation

SHOW BINARY LOGS holds the binary log index lock (LOCK_index) while it sends one row per binary log file (show_binlogs() in sql/rpl_source.cc). A second SHOW BINARY LOGS, or a binary log rotation, takes the binary log lock (LOCK_log) and then waits for LOCK_index. Every commit needs LOCK_log, so all commits on the server stall until the first statement finishes sending.

The send only finishes as fast as the agent reads. We saw this on an Aurora MySQL 3 writer with long binary log retention and tens of thousands of binary log files. The cluster check runner was CPU-starved, so it read the result slowly. The writer had repeated server-wide commit freezes of up to about 20 seconds. Performance Insights showed one SHOW BINARY LOGS from the datadog user holding LOCK_index while a second one held LOCK_log. We worked around it by setting binlog_size_metrics: false.

The server default net_write_timeout is 60s, so it did not bound these stalls. A low per-query value caps how long a stalled reader can block one write, and hitting it costs one missed binary log metric point. The limit applies to each blocked write, not to the whole statement, so a reader that is slow but still making progress can hold the lock for longer in total.

The value is a fixed constant rather than a config option, to keep the change small. I am happy to make it configurable if you prefer.

Checklist before requesting review

• I self-reviewed the full diff, and this description matches the final change
• Tests cover the change, or this description explains why none are needed
• Automated review comments, including Codex review, were addressed or answered
• I, the human author, understand this change, can explain it to reviewers, and believe it meets the repository standards