Bug #105924 SHOW SLAVE STATUS became expensive for Replica->Slave column name conversions
Submitted: 16 Dec 2021 20:40 Modified: 25 Jul 15:16
Reporter: Yoshinori Matsunobu (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Replication Severity:S5 (Performance)
Version:8.0.22, 8.0.27 OS:Any
Assigned to: CPU Architecture:Any

[16 Dec 2021 20:40] Yoshinori Matsunobu
We compared performance between 8.0.20 and 8.0.23, and noticed 8.0.23 was using a lot more CPU time on SHOW SLAVE STATUS.

Most of the extra time spent in 8.0.23 was from rename_fields_use_old_replica_source_terms() -- https://github.com/mysql/mysql-server/blob/8.0/sql/rpl_utility.cc#L1260-L1285

SHOW REPLICA STATUS was introduced in 8.0.22, and SHOW SLAVE STATUS was deprecated since then.
SHOW SLAVE STATUS (and SHOW SLAVE HOSTS) entered rename_fields_use_old_replica_source_terms() for compatibility -- to return legacy column names like "Slave_IO_Running" instead of "Replica_IO_Running". The column name conversion was done by calling std::regex_replace() to replace "Replica" and "Source" with "Slave" and "Master" for each output columns. The extra time spent for the regex_replace was very visible from our profiles.

SHOW SLAVE HOSTS has similar issue.

How to repeat:
Repeat SHOW SLAVE STATUS (e.g. mysqlslap) and compare throughput between 8.0.20 and 8.0.22+.

Suggested fix:
Predefine columns for SHOW SLAVE STATUS and SHOW SLAVE HOSTS, and return them instead of regex_replace.
[17 Dec 2021 12:11] MySQL Verification Team
Hello Yoshinori,

Thank you for the report.
Verified as described with 8.0.27 build.

[17 Dec 2021 12:22] MySQL Verification Team
MySQL 8.0.27/8.0.20 test results

Attachment: 105924_8.0.27_8.0.20.results (application/octet-stream, text), 2.19 MiB.

[17 Dec 2021 12:42] Sven Sandberg
Posted by developer:
Thank you for the bug report.

A workaround is to switch to the new terminology, SHOW REPLICA STATUS.
How does that work for you?
[17 Dec 2021 12:48] Sven Sandberg
Posted by developer:
Also, you mention running an artificial test based on mysqlslap. In a workload that executes transactions and uses the SHOW statement for monitoring, how big is the performance regression from using SHOW SLAVE STATUS instead of SHOW REPLICA STATUS?
[24 Jan 5:23] Yoshinori Matsunobu
I have seen about ~5% CPU regression on our production-simulated workload tests. It certainly depends on how often SHOW SLAVE STATUS is executed.

This feedback is from production operation perspective. SHOW REPLICA STATUS was introduced in 8.0.22, and SHOW SLAVE STATUS was deprecated accordingly. As of now, we have many application codes calling legacy SHOW SLAVE STATUS and using legacy column names. Originally we planned to use SHOW SLAVE STATUS until upgrading all of our production version to 8.0.23+ then switching to SHOW REPLICA STATUS. During the transition period, we expected not just syntax compatibility but also good enough performance. 5% was too big for us to ignore. Without fixing the perf regression, we will need additional logic to check version, and use SHOW REPLICA STATUS on version 8.0.23+ and SHOW SLAVE STATUS for older version (to avoid syntax errors). This is a bit painful.
[25 Jul 15:16] Jon Stephens
Documented fix as follows in the MySQL 8.0.31 changelog:

    SHOW SLAVE STATUS and SHOW SLAVE HOSTS used more CPU following
    the introduction of SHOW REPLICA STATUS and SHOW REPLICA HOSTS
    than previously.