MySQL Bugs: #105924: SHOW SLAVE STATUS became expensive for Replica->Slave column name conversions

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

Description:
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.

Hello Yoshinori,

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

regards,
Umesh

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.

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?

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?

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.

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.

Closed.