Bug #80923 Reuse sp_rcontext when processing many rows with stored function / triggers
Submitted: 31 Mar 2016 12:12
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.6.28, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[31 Mar 2016 12:12] Andrii Nikitin
Description:
sp_head.cc has comment like below:

    TODO: we should create sp_rcontext once per command and reuse
    it on subsequent executions of a function/trigger.

According to CPU profiling, functions may spend up to ~25% of time in creating sp_rcontext in 5.6 and 5.7 :

+   84.88%     2.38%           928  mysqld   mysqld              [.] sp_head::execute_function                                                       +   44.44%     4.08%          1590  mysqld   mysqld              [.] sp_head::execute                                                                +   22.32%     0.30%           118  mysqld   mysqld              [.] sp_rcontext::create    

The same profiling on 5.1 shows only 1,75% spent in sp_rcontext::create :

+    1.79%     1.79%           587  mysqld   mysqld              [.] sp_rcontext::init   

In particular, handling of 200M MyISAM table with two simple functions takes 2h in 5.6 and only 1h in 5.1 . Without these functions execution time is approximately the same. This means sp_rcontext::create is unnecessary called ~400M times in that case, which should be around 160K / sec, which definitely influences execution time.

How to repeat:
See source code or profile following using any CPU profiler in 5.1 vs  5.6 / 5.7 :

CREATE FUNCTION f1(a int) returns bool DETERMINISTIC return true;
do benchmark(50000000, f1(1));

Suggested fix:
Reuse sp_rcontext when processing many rows with stored function / triggers