Bug #69350 performance regression between 5.5 and 5.6 for simple statement in routine loop
Submitted: 30 May 2013 5:44
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[30 May 2013 5:44] Shane Bester
Description:
The testcase shows a sharp performance degradation between 5.5 and 5.6.
5.7 improves a bit, but I feel there is some further investigation needed here.

Version, call1,  call2,  call3,  call4,  call5
----------------------------------------------
5.7.2,   33.20,  33.25,  33.25,  33.29,  33.22
5.6.13,  36.83,  36.46,  36.62,  36.50,  36.42
5.5.33,  25.73,  25.22,  25.32,  25.35,  25.38
5.1.71,  24.37,  24.31,  24.13,  24.29,  24.28
5.0.96,  28.86,  28.87,  28.94,  28.70,  28.88

How to repeat:
set names latin1;
drop procedure if exists `p1`;
delimiter $
create procedure `p1`(`n` bigint unsigned)
begin
  declare `i` bigint unsigned default 0;
  declare `j` bigint unsigned default 0;
  repeat
    select 1 into `j`;
    set `i`:=`i`+1;
  until `i`>=`n` end repeat;
end $
delimiter ;
call `p1`(10000000);
call `p1`(10000000);
call `p1`(10000000);
call `p1`(10000000);
call `p1`(10000000);
select version();

Suggested fix:
Will upload the callgrind files.
[30 May 2013 6:07] Shane Bester
server was run with options:
--no-defaults --skip-grant-tables --skip-name-resolve --console \
--loose-performance-schema=0 --skip-symbolic-links --query-cache-size=0 \
--query-cache-type=0
[30 May 2013 8:41] Shane Bester
hard to compare when code is in different functions in different versions.

Attachment: profile_comparison.csv (application/octet-stream, text), 47.72 KiB.