Bug #59926 | Query With Stored Function Call Takes Longer With Each Run | ||
---|---|---|---|
Submitted: | 3 Feb 2011 20:01 | Modified: | 21 Jul 2011 11:27 |
Reporter: | Anthony Galano | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
Version: | 5.5.8, 5.6.2 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | performance, query |
[3 Feb 2011 20:01]
Anthony Galano
[3 Feb 2011 20:02]
Anthony Galano
Changed title.
[3 Feb 2011 20:50]
Sveta Smirnova
Thank you for the report. Please provide definition of ConvertGMTDateToLocalDate.
[3 Feb 2011 21:52]
Anthony Galano
Here it is below. You can get the full database with sample data table at this post: http://forums.mysql.com/read.php?24,404798,405541#msg-405541 -- -- Dumping routines for database 'test' -- /*!50003 DROP FUNCTION IF EXISTS `ConvertGMTDateToLocalDate` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `ConvertGMTDateToLocalDate`( WorkDate datetime, TimezoneOffset decimal(3,1), DSTEnabled tinyint(3) unsigned, TimezoneDSTOffset decimal(3,1) ) RETURNS datetime DETERMINISTIC BEGIN DECLARE LocalDateTime datetime; DECLARE DSTStart datetime; DECLARE DSTEnd datetime; DECLARE TimezoneOffsetHour int; DECLARE TimezoneOffsetMinute int; DECLARE TimezoneDSTOffsetHour int; DECLARE TimezoneDSTOffsetMinute int; IF ( TimezoneOffset < 0 ) THEN SET TimezoneOffsetHour = CEILING(TimezoneOffset); SET TimezoneOffsetMinute = ABS(60 * (TimezoneOffset - CEILING(TimezoneOffset))); ELSE SET TimezoneOffsetHour = FLOOR(TimezoneOffset); SET TimezoneOffsetMinute = ABS(60 * (TimezoneOffset - FLOOR(TimezoneOffset))); END IF; IF ( TimezoneDSTOffset < 0 ) THEN SET TimezoneDSTOffsetHour = CEILING(TimezoneDSTOffset); SET TimezoneDSTOffsetMinute = ABS(60 * (TimezoneDSTOffset - CEILING(TimezoneDSTOffset))); ELSE SET TimezoneDSTOffsetHour = FLOOR(TimezoneDSTOffset); SET TimezoneDSTOffsetMinute = ABS(60 * (TimezoneDSTOffset - FLOOR(TimezoneDSTOffset))); END IF; IF ( DSTEnabled = 1 ) THEN SET DSTStart = GetDaylightSavingTimeStart(YEAR(WorkDate)); SET DSTEnd = GetDaylightSavingTimeEnd(YEAR(WorkDate)); SET DSTStart = STR_TO_DATE(CONCAT(YEAR(DSTStart),'-',MONTH(DSTStart),'-',DAY(DSTStart),'-',ABS(TimezoneOffsetHour)+2,'-',TimezoneOffsetMinute,'-0'),'%Y-%m-%d-%H-%i-%s'); SET DSTEnd = STR_TO_DATE(CONCAT(YEAR(DSTEnd),'-',MONTH(DSTEnd),'-',DAY(DSTEnd),'-',ABS(TimezoneDSTOffsetHour)+2,'-',TimezoneDSTOffsetMinute,'-0'),'%Y-%m-%d-%H-%i-%s'); IF ( WorkDate >= DSTStart AND WorkDate < DSTEnd ) THEN SET LocalDateTime = DATE_ADD(WorkDate,INTERVAL CONCAT(TimezoneDSTOffsetHour,':',TimezoneDSTOffsetMinute) HOUR_MINUTE); ELSE SET LocalDateTime = DATE_ADD(WorkDate,INTERVAL CONCAT(TimezoneOffsetHour,':',TimezoneOffsetMinute) HOUR_MINUTE); END IF; ELSE SET LocalDateTime = DATE_ADD(WorkDate,INTERVAL CONCAT(TimezoneOffsetHour,':',TimezoneOffsetMinute) HOUR_MINUTE); END IF; RETURN LocalDateTime; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP FUNCTION IF EXISTS `GetDaylightSavingTimeEnd` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `GetDaylightSavingTimeEnd`( TheYear int ) RETURNS date DETERMINISTIC BEGIN DECLARE TheEndDate date; DECLARE LastSunday int; IF ( TheYear < 2007 ) THEN SET LastSunday = GetNthOccOfDayInMonth(5,1,10,TheYear); IF ( LastSunday <= -1 ) THEN SET TheEndDate = STR_TO_DATE(CONCAT(TheYear,'-10-',GetNthOccOfDayInMonth(4,1,10,TheYear)),'%Y-%m-%d'); ELSE SET TheEndDate = STR_TO_DATE(CONCAT(TheYear,'-10-',LastSunday),'%Y-%m-%d'); END IF; ELSE SET TheEndDate = STR_TO_DATE(CONCAT(TheYear,'-11-',GetNthOccOfDayInMonth(1,1,11,TheYear)),'%Y-%m-%d'); END IF; RETURN TheEndDate; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP FUNCTION IF EXISTS `GetDaylightSavingTimeStart` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `GetDaylightSavingTimeStart`( TheYear int ) RETURNS date DETERMINISTIC BEGIN DECLARE TheStartDate date; IF ( TheYear < 2007 ) THEN SET TheStartDate = STR_TO_DATE(CONCAT(TheYear,'-4-',GetNthOccOfDayInMonth(1,1,4,TheYear)),'%Y-%m-%d'); ELSE SET TheStartDate = STR_TO_DATE(CONCAT(TheYear,'-3-',GetNthOccOfDayInMonth(2,1,3,TheYear)),'%Y-%m-%d'); END IF; RETURN TheStartDate; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP FUNCTION IF EXISTS `GetNthOccOfDayInMonth` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `GetNthOccOfDayInMonth`( NthOccurrence int, TheDayOfWeek int, TheMonth tinyint, TheYear int ) RETURNS int(11) DETERMINISTIC BEGIN DECLARE TheDayInMonth int(11); DECLARE DateToTest date; DECLARE DayOfWeekToTest int(11); SET TheDayInMonth = 0; SET DateToTest = STR_TO_DATE(CONCAT(TheYear,'-',TheMonth,'-',1),'%Y-%m-%d'); SET DayOfWeekToTest = DAYOFWEEK(DateToTest); IF ( TheDayOfWeek < DayOfWeekToTest ) THEN SET TheDayInMonth = (1 + (NthOccurrence*7) + ((TheDayOfWeek-DayOfWeekToTest) % 7)); ELSE SET TheDayInMonth = (1 + ((NthOccurrence-1)*7) + ((TheDayOfWeek-DayOfWeekToTest) % 7)); END IF; IF ( TheDayInMonth > DAY(LAST_DAY(DateToTest)) OR TheDayInMonth < 1 ) THEN RETURN -1; ELSE RETURN TheDayInMonth; END IF; END */;; DELIMITER ;
[4 Feb 2011 12:45]
Sveta Smirnova
Thank you for the feedback. Verified as described using function definitions and table data from forum post provided. This is client issue. Workaround: use client from 5.1 distribution.
[8 Feb 2011 2:25]
Omer Barnir
triage: Not clear how it was concluded this is a client issue setting to MRU as issue needs to be understood.
[16 Feb 2011 4:43]
Peter Brawley
> This is client issue. Workaround: use client from 5.1 distribution. WRONG. The same behaviour occurs with the 5.1 client.
[4 Mar 2011 13:07]
Rob Morris
Having a similar problem. A query containing a user defined function runs longer and longer with each successive execution. The server upgraded is a slave and it’s causing replication to fall further and further behind. Eventually, Mysql needs to be restarted for replication to catch up.
[4 Mar 2011 14:47]
Marques Johansson
A simpler test case: I originally tried a function that only did "return a" but that didn't seem to trigger the problem. Adding an IF was enough to get the time growth. After performing this test I created an identical "test_userfunc3" function without "DETERMINISTIC" and got a very similar growth chart - starting at .13s and exceeding 5 seconds by the 8th successive benchmark (of 5000 execution). FUNCTION `test_userfunc2`(a text) RETURNS text CHARSET utf8 DETERMINISTIC BEGIN IF a = 'test' THEN RETURN a; END IF; END mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.13 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.29 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.44 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.61 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.77 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (0.96 sec) mysql> select benchmark(5000, test_userfunc2('test')) \G *************************** 1. row *************************** benchmark(5000, test_userfunc2('test')): 0 1 row in set (1.92 sec)
[27 May 2011 13:27]
David Poor
I have the same problem with a stored procedure that is DETERMINISTIC and NO SQL. It is in the WHERE clause of a complex query so the function is executed on each row of a table. Initially the query runs in under 1 second, but then it runs about 2 seconds longer each time it is run again. I can reset it back to its shortest time by restarting MySql -- or -- by recreating the function (i.e. DROP FUNCTION ... CREATE FUNCTION ... ) I am running version 5.5.10 Community x86 Win32 on Windows-XP.
[27 May 2011 14:24]
David Poor
WORKAROUND FOUND! If the information_schema ROUTINES LAST_ALTERED is changed for the stored function prior to the execution of the query, the performance does not degrade. Therefore, prior to each execution of the query, perform an executeUpdate with "ALTER FUNCTION yourfunctioname COMMENT "Altered comment" .
[21 Jul 2011 11:27]
Davi Arnaut
Closed as a duplicate of Bug#60025.