maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

The server parses the function into a form of bytecode which is then cached
until the routine changes.  In the past any routine change would invalidate
cache for all routines and that sometimes can still happen but it is rare.
The server then interprets the bytecode for every call to the function and
yes, the function is executed every time unless you memoize the function (
if(function_input != @last_input)  then @last_input = function_input;
return @last_result := function(function_input); else return @last_result;

On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <peter_laursen@xxxxxxxxxx>
wrote:

> I really don't know if it should take 2, 5 or 60 seconds.  The numbers
> surprise me (in the bad maner). There is an incredible overhead when
> wrapping this query in a function. I wonder if there is an invocation of
> the function for every iteration.
>
> -- Peter
>
> On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <federico_raz@xxxxxxxx>
> wrote:
>
>> SELECT 1 was .42. What I find amazing is not the absolute numbers (it's
>> an old computer with desktop installed, etc) but the difference between the
>> tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do
>> you agree?
>>
>> Federico
>>
>>
>> --------------------------------------------
>> Mar 3/3/15, Justin Swanhart <greenlion@xxxxxxxxx> ha scritto:
>>
>>  Oggetto: Re: [Maria-discuss] stored programs
>>  A: "Peter Laursen" <peter_laursen@xxxxxxxxxx>
>>  Cc: "Federico Razzoli" <federico_raz@xxxxxxxx>, "Maria Discuss" <
>> maria-discuss@xxxxxxxxxxxxxxxxxxx>
>>  Data: Martedì 3 marzo 2015, 14:28
>>
>>  You
>>  probably have either a faster CPU or bigger cache on your
>>  cpu.  How long does benchmark select 1 take?  You should
>>  find it is faster on your system too, right?
>>  --Justin
>>
>>  Sent from my iPhone
>>  On Mar 3,
>>  2015, at 6:18 AM, Peter Laursen <peter_laursen@xxxxxxxxxx>
>>  wrote:
>>
>>  Your 3rd test case takes
>>  2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system
>>  (when otherwise idle) .
>>  --
>>  Peter
>>  On Tue,
>>  Mar 3, 2015 at 1:58 PM, Federico Razzoli <federico_raz@xxxxxxxx>
>>  wrote:
>>  I made
>>  some quick test to show what I mean by "performance
>>  problem". Note that I'm not saying the the first
>>  and the second test cases should perform equally. But there
>>  is too much difference - see the conclusion.
>>
>>
>>
>>  1)
>>
>>
>>
>>  MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT
>>  1));
>>
>>  +---------------------------------+
>>
>>  | BENCHMARK(50000000, (SELECT 1)) |
>>
>>  +---------------------------------+
>>
>>  |                               0 |
>>
>>  +---------------------------------+
>>
>>  1 row in set (0.42 sec)
>>
>>
>>
>>  2)
>>
>>
>>
>>  DELIMITER ||
>>
>>  CREATE FUNCTION f()
>>
>>          RETURNS TINYINT
>>
>>  BEGIN
>>
>>          RETURN 1;
>>
>>  END ||
>>
>>  DELIMITER ;
>>
>>
>>
>>  MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT
>>  f()));
>>
>>  +-----------------------------------+
>>
>>  | BENCHMARK(50000000, (SELECT f())) |
>>
>>  +-----------------------------------+
>>
>>  |                                 0 |
>>
>>  +-----------------------------------+
>>
>>  1 row in set (2 min 5.70 sec)
>>
>>
>>
>>  3)
>>
>>
>>
>>  DELIMITER ||
>>
>>  CREATE FUNCTION f(x TINYINT)
>>
>>          RETURNS TINYINT
>>
>>  BEGIN
>>
>>          RETURN x;
>>
>>  END ||
>>
>>  DELIMITER ;
>>
>>
>>
>>  MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT
>>  f(1)));
>>
>>  +------------------------------------+
>>
>>  | BENCHMARK(50000000, (SELECT f(1))) |
>>
>>  +------------------------------------+
>>
>>  |                                  0 |
>>
>>  +------------------------------------+
>>
>>  1 row in set (3 min 35.20 sec)
>>
>>
>>
>>  -- Conclusions:
>>
>>
>>
>>  Times in seconds:
>>
>>
>>
>>  0.42
>>
>>  125.70
>>
>>  215.20
>>
>>
>>
>>  I don't know which ratio would be acceptable, but the
>>  difference betweem a trivial query and a trivial function is
>>  too high.
>>
>>  Also, the difference between 2) and 3) is that the function
>>  in 3) accepts and returns a parameter. Again, the
>>  performance difference seems to me too high.
>>
>>
>>
>>  Regards
>>
>>  Federico
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>  _______________________________________________
>>
>>  Mailing list: https://launchpad.net/~maria-discuss
>>
>>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>
>>  Unsubscribe : https://launchpad.net/~maria-discuss
>>
>>  More help   : https://help.launchpad.net/ListHelp
>>
>>
>>
>>  _______________________________________________
>>  Mailing list: https://launchpad.net/~maria-discuss
>>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>  Unsubscribe : https://launchpad.net/~maria-discuss
>>  More help   : https://help.launchpad.net/ListHelp
>>
>>
>