maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

Just to avoid ambiguity: the stored function is executed as byte code in
each call, and the native function is called by the byte code for each call.

--Justin

On Tue, Mar 3, 2015 at 7:04 AM, Justin Swanhart <greenlion@xxxxxxxxx> wrote:

> 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
>>>
>>>
>>
>