Bugs item #2644925, was opened at 2009-02-27 11:49 Message generated for change (Comment added) made by stmane You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2644925&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: SQL CVS Head >Status: Closed >Resolution: Fixed Priority: 5 Private: Yes Submitted By: Jens Oehlschlägel (jens_oehl) Assigned to: Niels Nes (nielsnes) Summary: crash-points with 1.5M Initial Comment: Stefan, attached you find a csv with the forward non-ODBC timings of - 600k version from Feb20 - 600k version from Feb26 - 1.5M version from Feb26 The 1.5M was crashing several times, number of the crashing queries: 875, 983, 987, 999, the four SQLs follow for your convenience. version from Feb26 is MonetDB server v5.10.0 (32-bit), based on kernel v1.28.0 (32-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: C:\Documents and Settings\sjoerd\My Documents\src\stable\vs32\NT32 Libraries: libpcre: 7.7 2008-05-07 libxml2: 2.6.32 Compiled by: sjo...@gans Compilation: cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox Linking : cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox crashing query SQL: SELECT KX, PostingYear, PostingStep, SUM(num1) as sNum1, SUM(den1) as sDen1 FROM ( SELECT MIN(1) as PostingStep, PostingYear, Min('X') as KX, SUM(DT_D02) as num1, MIN(0) as den1 FROM voc.q_SourceKPIReportsTarget WHERE ((PostingYear>=2005) AND (PostingYear <= 2006)) GROUP BY PostingYear) as sSub3 GROUP BY KX, PostingYear, PostingStep; SELECT KX, PostingYear, PostingStep, SUM(num1) as sNum1, SUM(den1) as sDen1 FROM ( SELECT Postingsemi as PostingStep, PostingYear, Min('X') as KX, SUM(DT_D02) as num1, MIN(0) as den1 FROM voc.q_SourceKPIReports WHERE (((PostingYear>2005 AND PostingYear<2006) OR (PostingYear=2005 AND Postingsemi>=1) OR (PostingYear=2006 AND Postingsemi<=2))) GROUP BY PostingYear, Postingsemi) as sSub3 GROUP BY KX, PostingYear, PostingStep; SELECT KX, PostingYear, PostingStep, SUM(num1) as sNum1, SUM(den1) as sDen1 FROM ( SELECT Postingsemi as PostingStep, PostingYear, Min('X') as KX, SUM(DT_D02) as num1, MIN(0) as den1 FROM voc.q_SourceKPIReportsTarget WHERE (((PostingYear>2005 AND PostingYear<2006) OR (PostingYear=2005 AND Postingsemi>=1) OR (PostingYear=2006 AND Postingsemi<=2))) GROUP BY PostingYear, Postingsemi) as sSub3 GROUP BY KX, PostingYear, PostingStep; SELECT KX, PostingYear, PostingStep, SUM(num1) as sNum1, SUM(den1) as sDen1 FROM ( SELECT Postingsemi as PostingStep, PostingYear, Min('X') as KX, SUM(DT_D04) as num1, MIN(0) as den1 FROM voc.q_SourceKPIReportsTarget WHERE (((PostingYear>2005 AND PostingYear<2006) OR (PostingYear=2005 AND Postingsemi>=1) OR (PostingYear=2006 AND Postingsemi<=2))) GROUP BY PostingYear, Postingsemi) as sSub3 GROUP BY KX, PostingYear, PostingStep; ---------------------------------------------------------------------- >Comment By: Stefan Manegold (stmane) Date: 2009-04-02 08:28 Message: Since the Feb2009-SP1 release, all 1207 queries now also work on a 2GB Win32 machine. ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-03-03 23:52 Message: By their "nature", the 4 "problematic" queries (#983, 987, 997, 999) and due the way they are currently translated/handled in MonetDB/SQL, produce the largest intermediate results and (also) on my 8GB 64-bit Linux machine require by a margin the longest execution times of the whole set --- ranked by decreasing execution time, the "top-scorers" are 0983 Timer 78029.632 msec 0997 Timer 77901.019 msec 0999 Timer 75571.707 msec 0987 Timer 73198.842 msec 0014 Timer 29003.736 msec 0019 Timer 23283.525 msec 0016 Timer 14071.814 msec 0017 Timer 13464.103 msec 0012 Timer 13250.273 msec 0009 Timer 13162.571 msec 0018 Timer 12509.712 msec 0875 Timer 9233.637 msec We are working on improving the translation/evaluation of these queries --- but that might take some time ... ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-03-01 18:23 Message: More observations with the 1.5M dataset on our 2GB Win32 machine (still with the release code base as used for the installer at http://monetdb.cwi.nl/downloads/Windows/Feb2009/): After successively removing the first query that crashes the server with !FATAL: GDKmallocmax: failed for ? bytes when running all queries from queries1207.sql in forward order, i.e., queries 983, 987, 997, 999, mserver5 "happily" survives 4 consecutive runs of the remaining 1203 queries (without crash or restart). Likewise, a freshly started mserver5 also "happily" survives 4 consecutive runs of (only) the 4 "problem" queries (983, 987, 997, 999). We can conclude that the 4 queries themselves do not require more memory/address space than available, but rather "only" more than left after running all previous queries --- more leak plugging might be required ... ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-28 00:43 Message: some more experimental results: On a 32-bit (Gentoo) Linux box with 1 GB RAM and 2 GB swap, mserver5 "happily" survives 4 consecutive runs of all 1207 queries (without crash or restart) on both the 150k & the 1.5M data set. On the 2 GB Win32 box, mserver5 "happily" survives 4 consecutive runs of the first 982 out of the total 1207 queries without crash or restart) also on the 1.5M data set. ... more to come tomorrow, once nightly testing will have released the Windows box, again ... ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 21:44 Message: some status info about my tests on Windows: Win64 (Server 2003; v5.2) with 2 GB RAM: mserver5 "Happily" survives 4 consecutive runs of all 1207 queries (without crash or restart) on both the 150k & the 1.5M data set. Win32 (XP; v5.1) with 2 GB RAM: mserver5 "happily" survives 4 consecutive runs of all 1207 queries (without crash or restart) only on the 150k data set. On the 1.5M dataset, running queries1207.sql in "forward" direction, query #983 crashes the server with !FATAL: GDKmallocmax: failed for 247669440 bytes while running queries1207.sql in "backward" direction, query #999 (209th query from the end) crashes the server with !FATAL: GDKmallocmax: failed for 206391200 bytes (did not yet test the queries between #983 & #999) ---------------------------------------------------------------------- Comment By: Stefan Manegold (stmane) Date: 2009-02-27 19:09 Message: Jens, thank you very much for the details. I have not yet been able to have a closer look at the 4 failing queries on a 2 GB Win32 machine, but we plugged some more leaks since yesterday's installer. This afternoon, Sjoerd built new installers which will most probably be the version that we will "officially" release; you find them at http://monetdb.cwi.nl/downloads/Windows/Feb2009/ *Maybe* this already helps with the four failing queries ... Unfortunately we have not yet been able to properly fix the (indeed) "scary" SUM-CASE bug you reported. We'll provide you with an other installer, once also this bug is fixed. Stefan ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2644925&group_id=56967 ------------------------------------------------------------------------------ _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
