MySQL stored procedures, functions and triggers, are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.
Why stored routines are not optimal performance-wise
Recently, I worked with a customer to profile the performance of triggers and stored routines. What I’ve learned about stored routines: “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger. We will need to be careful to clean up what we do not need.
Profiling MySQL stored functions
Let’s compare these four simple stored functions (in MySQL 5.7):
Function 1:
1 2 3 4 5 |
CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11) BEGIN declare r int default 0; RETURN r; END |
This function simply declares a variable and returns it. It is a dummy function
Function 2:
1 2 3 4 5 6 7 8 9 10 |
CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select levenshtein_limit_n('test finc', 'test func', 1000) into r; END IF; RETURN r; END |
This function calls another function, levenshtein_limit_n (calculates levenshtein distance). But wait: this code will never run – the condition IF 1=2 will never be true. So that is the same as function 1.
Function 3:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE DEFINER=`root`@`localhost` FUNCTION `func3`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select levenshtein_limit_n('test finc', 'test func', 1) into r; END IF; IF 2=3 THEN select levenshtein_limit_n('test finc', 'test func', 10) into r; END IF; IF 3=4 THEN select levenshtein_limit_n('test finc', 'test func', 100) into r; END IF; IF 4=5 THEN select levenshtein_limit_n('test finc', 'test func', 1000) into r; END IF; RETURN r; END |
Here there are four conditions and none of these conditions will be true: there are 4 calls of “dead” code. The result of the function call for function 3 will be the same as function 2 and function 1.
Function 4:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE DEFINER=`root`@`localhost` FUNCTION `func3_nope`() RETURNS int(11) BEGIN declare r int default 0; IF 1=2 THEN select does_not_exit('test finc', 'test func', 1) into r; END IF; IF 2=3 THEN select does_not_exit('test finc', 'test func', 10) into r; END IF; IF 3=4 THEN select does_not_exit('test finc', 'test func', 100) into r; END IF; IF 4=5 THEN select does_not_exit('test finc', 'test func', 1000) into r; END IF; RETURN r; END |
This is the same as function 3 but the function we are running does not exist. Well, it does not matter as the select does_not_exit will never run.
So all the functions will always return 0. We expect that the performance of these functions will be the same or very similar. Surprisingly it is not the case! To measure the performance I used the “benchmark” function to run the same function 1M times. Here are the results:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
+-----------------------------+ | benchmark(1000000, func1()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (1.75 sec) +-----------------------------+ | benchmark(1000000, func2()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (2.45 sec) +-----------------------------+ | benchmark(1000000, func3()) | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (3.85 sec) +----------------------------------+ | benchmark(1000000, func3_nope()) | +----------------------------------+ | 0 | +----------------------------------+ 1 row in set (3.85 sec) |
As we can see func3 (with four dead code calls which will never be executed, otherwise identical to func1) runs almost 3x slower compared to func1(); func3_nope() is identical in terms of response time to func3().
Visualizing all system calls from functions
To figure out what is happening inside the function calls I used performance_schema / sys schema to create a trace with ps_trace_thread() procedure
- Get the thread_id for the MySQL connection:
1234567mysql> select THREAD_ID from performance_schema.threads where processlist_id = connection_id();+-----------+| THREAD_ID |+-----------+| 49 |+-----------+1 row in set (0.00 sec) - Run ps_trace_thread in another connection passing the thread_id=49:
1234567891011121314mysql> CALL sys.ps_trace_thread(49, concat('/var/lib/mysql-files/stack-func1-run1.dot'), 10, 0, TRUE, TRUE, TRUE);+--------------------+| summary |+--------------------+| Disabled 0 threads |+--------------------+1 row in set (0.00 sec)+---------------------------------------------+| Info |+---------------------------------------------+| Data collection starting for THREAD_ID = 49 |+---------------------------------------------+1 row in set (0.00 sec) - At that point I switched to the original connection (thread_id=49) and run:
1234567mysql> select func1();+---------+| func1() |+---------+| 0 |+---------+1 row in set (0.00 sec) - The sys.ps_trace_thread collected the data (for 10 seconds, during which I ran the
select func1() ), then it finished its collection and created the dot file:
12345678910111213141516171819202122+--------------------------------