maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

However I wasn't able to produce any bad/good speed change by playing with @@stored_program_cache. Anyone knows the reason? (this is just a curiosity)

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, 15:04
 
 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