Bug #121752 The rewriter plugin has a 10%-15% performance impact when there are no rules. Should we consider optimizing it.

Submitted: 12 Nov 8:40
Reporter: boom Charon Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:ALL OS:Any
Assigned to: CPU Architecture:Any

[12 Nov 8:40] boom Charon
Description:
Rewriter plugin adds full parse overhead per statement even with zero rewrite rules.

When the rewriter plugin (component_rewriter / rewriter plugin) is installed and enabled (rewriter_enabled=ON) while mysql.rewrite_rules contains zero rows, every SQL statement still passes through the mysql_rewrite_query() service hook. Rewriter::rewrite_query() calls compute_digest() BEFORE looking up the rules hash, so each statement is parsed an additional time even though no rule can possibly match.
On OLTP workloads this results in a 10-15% throughput regression compared with rewriter_enabled=OFF.

How to repeat:
How to repeat:
1. Deploy MySQL 8.0.x.
2. Load data with sysbench: sysbench /usr/share/sysbench/oltp_read_write.lua .......
3. Runtime A (rewriter off):
   SET GLOBAL rewriter_enabled = OFF;
   sysbench ... --threads=32 --time=300 run 
4. Runtime B (rewriter on, no rules):
   SET GLOBAL rewriter_enabled = ON;
   SELECT COUNT(*) FROM mysql.rewrite_rules;   -- expect 0
   sysbench ... --threads=32 --time=300 run 
5. Compare QPS/P95 latency across 3 runs each, with identical warmup.

RESULT:
1. Installed but not enabled: no performance impact (TPS ratio 96%-100%)					
2. Enabled with rules: rule count (10 vs 50) barely matters; the loss comes mainly from enabling the plugin.					
3. Higher concurrency, larger impact: ~3% at 32 threads, 6%-10% at 64-128, 10%-15% at higher concurrency

Suggested fix:
With the rewriter plugin installed and rewriter_enabled=ON, every statement first goes through plugin/rewriter/rewriter_plugin.cc, where the following check is executed before any rule lookup or digest computation:

    if (global_grants_check->has_global_grant(
            reinterpret_cast<Security_context_handle>(sec_ctx),
            STRING_WITH_LEN("SKIP_QUERY_REWRITE")))
      return false;

For accounts that have NOT been granted the SKIP_QUERY_REWRITE dynamic privilege, this check runs for every statement, in every session, even when mysql.rewrite_rules contains zero rows. Profiling shows that for ordinary (non-privileged) users the dominant cost of the rewriter hook is this privilege lookup rather than digest computation or hash lookup.

diff --git a/plugin/rewriter/rewriter_plugin.cc b/plugin/rewriter/rewriter_plugin.cc
index a00422280dc..f4e8987fb11 100644
--- a/plugin/rewriter/rewriter_plugin.cc
+++ b/plugin/rewriter/rewriter_plugin.cc
@@ -467,9 +467,9 @@ static int rewrite_query_notify(MYSQL_THD thd,
 
   const struct mysql_event_parse *event_parse =
       static_cast<const struct mysql_event_parse *>(event);
-
+  // If there are no rules, it is best to exit early to avoid performance impact.
   if (event_parse->event_subclass != MYSQL_AUDIT_PARSE_POSTPARSE ||
-      !sys_var_enabled)
+      !sys_var_enabled || rewriter->get_number_loaded_rules() == 0)
     return 0;
 
   if (!allow_rewrite()) return 0;

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.