Bugs item #2540169, was opened at 2009-01-27 12:42
Message generated for change (Comment added) made by stmane
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2540169&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 "stable"
>Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: Jens Oehlschlägel (jens_oehl)
>Assigned to: Stefan Manegold (stmane)
Summary: Memory leak and crash with UNION ALL
Initial Comment:
Querying two tables combined with UNION ALL in view accumulates memory leak and
crashes the server.
-- 1. fresh installation of MonetDB Win32 Nov 2008 SP2
-- 2. start server with db demo
-- 3. log in as sys
CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";
CREATE SCHEMA "voc" AUTHORIZATION "voc";
ALTER USER "voc" SET SCHEMA "voc";
-- 4. log off
-- 5. log in as voc
CREATE TABLE databasetest1 (
"id" INTEGER
, "ubyte1" SMALLINT
, "ubyte2" SMALLINT
, "ubyte3" SMALLINT
, "ubyte4" SMALLINT
, "ubyte5" SMALLINT
, "ubyte6" SMALLINT
, "ubyte7" SMALLINT
, "ubyte8" SMALLINT
, "ubyte9" SMALLINT
, "ubyte10" SMALLINT
, "ubyte11" SMALLINT
, "ubyte12" SMALLINT
, "ubyte13" SMALLINT
, "ubyte14" SMALLINT
, "ubyte15" SMALLINT
, "smallint1" SMALLINT
, "smallint2" SMALLINT
, "smallint3" SMALLINT
, "smallint4" SMALLINT
, "smallint5" SMALLINT
, "smallint6" SMALLINT
, "smallint7" SMALLINT
, "smallint8" SMALLINT
, "smallint9" SMALLINT
, "smallint10" SMALLINT
, "smallint11" SMALLINT
, "smallint12" SMALLINT
, "smallint13" SMALLINT
, "smallint14" SMALLINT
, "smallint15" SMALLINT
, "float1" DOUBLE
, "float2" DOUBLE
, "float3" DOUBLE
, "float4" DOUBLE
, "float5" DOUBLE
, "float6" DOUBLE
, "float7" DOUBLE
, "float8" DOUBLE
, "float9" DOUBLE
, "float10" DOUBLE
, "float11" DOUBLE
, "float12" DOUBLE
, "float13" DOUBLE
, "float14" DOUBLE
, "float15" DOUBLE
, "float16" DOUBLE
, "float17" DOUBLE
, "float18" DOUBLE
, "float19" DOUBLE
, "float20" DOUBLE
)
;
START TRANSACTION;
COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest1" FROM
'D:/tmp/databasetest.csv' USING DELIMITERS ',';
COMMIT;
CREATE TABLE databasetest2 (
"id" INTEGER
, "ubyte1" SMALLINT
, "ubyte2" SMALLINT
, "ubyte3" SMALLINT
, "ubyte4" SMALLINT
, "ubyte5" SMALLINT
, "ubyte6" SMALLINT
, "ubyte7" SMALLINT
, "ubyte8" SMALLINT
, "ubyte9" SMALLINT
, "ubyte10" SMALLINT
, "ubyte11" SMALLINT
, "ubyte12" SMALLINT
, "ubyte13" SMALLINT
, "ubyte14" SMALLINT
, "ubyte15" SMALLINT
, "smallint1" SMALLINT
, "smallint2" SMALLINT
, "smallint3" SMALLINT
, "smallint4" SMALLINT
, "smallint5" SMALLINT
, "smallint6" SMALLINT
, "smallint7" SMALLINT
, "smallint8" SMALLINT
, "smallint9" SMALLINT
, "smallint10" SMALLINT
, "smallint11" SMALLINT
, "smallint12" SMALLINT
, "smallint13" SMALLINT
, "smallint14" SMALLINT
, "smallint15" SMALLINT
, "float1" DOUBLE
, "float2" DOUBLE
, "float3" DOUBLE
, "float4" DOUBLE
, "float5" DOUBLE
, "float6" DOUBLE
, "float7" DOUBLE
, "float8" DOUBLE
, "float9" DOUBLE
, "float10" DOUBLE
, "float11" DOUBLE
, "float12" DOUBLE
, "float13" DOUBLE
, "float14" DOUBLE
, "float15" DOUBLE
, "float16" DOUBLE
, "float17" DOUBLE
, "float18" DOUBLE
, "float19" DOUBLE
, "float20" DOUBLE
)
;
START TRANSACTION;
COPY 1000000 OFFSET 1000002 RECORDS INTO "voc"."databasetest2" FROM
'D:/tmp/databasetest.csv' USING DELIMITERS ',';
COMMIT;
CREATE VIEW databasetest AS
SELECT * FROM databasetest1
UNION ALL
SELECT * FROM databasetest2
;
-- 6. log off
-- 7. shut down server
-- 8. restart server
-- 9. log in as voc
-- 10. each execution of the following SQL consumes additional memory, repeat
it until server crash
SELECT sum(float1) AS currencyfloat, sum(float2) AS unitfloat FROM databasetest;
-- The csv file can be created with the following R-script
# this is an R comment and you find R at CRAN.r-project.org
filename <- "D:/tmp/databasetest.csv"
# create 2Mio rows in 2000 batches of 1000, total of 347MB csv file, takes ~4
minutes
system.time({
b <- 2000L # number of batches
n <- 1000L # batch size
csvfile <- file(filename, open="w")
for (i in 1:b){
x <- data.frame(
id = ((i-1L)*n+1L):(i*n) # 4-byte integer record identifier
, ubyte1 = sample(0:35, n, TRUE, prob=NULL) # unsigned byte columns (1 byte)
, ubyte2 = sample(0:1, n, TRUE, prob=NULL)
, ubyte3 = sample(0:3, n, TRUE, prob=NULL)
, ubyte4 = sample(0:9, n, TRUE, prob=NULL)
, ubyte5 = sample(0:24, n, TRUE, prob=NULL)
, ubyte6 = sample(0:99, n, TRUE, prob=NULL)
, ubyte7 = sample(-127:127, n, TRUE, prob=NULL)
, ubyte8 = sample(0:1, n, TRUE, prob=c(0.75, 0.25))
, ubyte9 = sample(0:1, n, TRUE, prob=c(0.9,0.1))
, ubyte10 = sample(0:1, n, TRUE, prob=c(0.96,0.04))
, ubyte11 = sample(0:1, n, TRUE, prob=c(0.99,0.01))
, ubyte12 = sample(0:1, n, TRUE, prob=c(0.996,0.004))
, ubyte13 = sample(0:1, n, TRUE, prob=c(0.999,0.001))
, ubyte14 = sample(0:1, n, TRUE, prob=c(0.9996,0.0004))
, ubyte15 = sample(0:1, n, TRUE, prob=c(0.9999,0.0001))
, smallint1 = sample(1:4000, n, TRUE, prob=NULL) # smallint columns (2 bytes)
, smallint2 = sample(1:2, n, TRUE, prob=NULL)
, smallint3 = sample(1:4, n, TRUE, prob=NULL)
, smallint4 = sample(1:10, n, TRUE, prob=NULL)
, smallint5 = sample(1:25, n, TRUE, prob=NULL)
, smallint6 = sample(1:100, n, TRUE, prob=NULL)
, smallint7 = sample(1:256, n, TRUE, prob=NULL)
, smallint8 = sample(1:1000, n, TRUE, prob=NULL)
, smallint9 = sample(1:32000, n, TRUE, prob=NULL)
, smallint10 = sample(0:1, n, TRUE, prob=c(0.75, 0.25))
, smallint11 = sample(0:1, n, TRUE, prob=c(0.9,0.1))
, smallint12 = sample(0:1, n, TRUE, prob=c(0.96,0.04))
, smallint13 = sample(0:1, n, TRUE, prob=c(0.99,0.01))
, smallint14 = sample(0:1, n, TRUE, prob=c(0.996,0.004))
, smallint15 = sample(0:1, n, TRUE, prob=c(0.999,0.001))
, float1 = round(runif(n, 0, 100), 1) # float columns (4 bytes)
, float2 = round(runif(n, 0, 100), 1)
, float3 = round(runif(n, 0, 100), 1)
, float4 = round(runif(n, 0, 100), 1)
, float5 = round(runif(n, 0, 100), 1)
, float6 = round(runif(n, 0, 100), 1)
, float7 = round(runif(n, 0, 100), 1)
, float8 = round(runif(n, 0, 100), 1)
, float9 = round(runif(n, 0, 100), 1)
, float10 = round(runif(n, 0, 100), 1)
, float11 = round(runif(n, 0, 100), 1)
, float12 = round(runif(n, 0, 100), 1)
, float13 = round(runif(n, 0, 100), 1)
, float14 = round(runif(n, 0, 100), 1)
, float15 = round(runif(n, 0, 100), 1)
, float16 = round(runif(n, 0, 100), 1)
, float17 = round(runif(n, 0, 100), 1)
, float18 = round(runif(n, 0, 100), 1)
, float19 = round(runif(n, 0, 100), 1)
, float20 = round(runif(n, 0, 100), 1)
)
write.table(x, file=csvfile, row.names=FALSE, col.names=(i==1L), append=i!=1L,
dec=".", sep=",")
}
close(csvfile)
})
# End R script
-- Machine configuration
Lenovo Thinkcentre with 3 GB RAM under Windows XP Professional SP2
RAM consumption of m5server.exe during load ~300MB according to task manager
Free RAM 2.1 GB
Size of dbfarm finally 1.9 GB
DiskFreeSpace 1.8 TB
[System Summary]
Item Value
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
OS Manufacturer Microsoft Corporation
System Name GEMUN-38396-10-
System Manufacturer LENOVO
System Model 6073AA7
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 11 GenuineIntel ~2992 Mhz
Processor x86 Family 6 Model 15 Stepping 11 GenuineIntel ~2992 Mhz
BIOS Version/Date LENOVO 2RKT41AUS, 3/20/2008
SMBIOS Version 2.5
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale Germany
Hardware Abstraction Layer Version = "5.1.2600.3023 (xpsp_sp2_qfe.061030-0020)"
Time Zone W. Europe Standard Time
Total Physical Memory 3,072.00 MB
Available Physical Memory 1.70 GB
Total Virtual Memory 2.00 GB
Available Virtual Memory 1.96 GB
Page File Space 5.79 GB
Page File D:\pagefile.sys
----------------------------------------------------------------------
>Comment By: Stefan Manegold (stmane)
Date: 2009-02-02 00:38
Message:
added test in sql/src/test/BugTracker-2009/Tests/union_leak.SF-2540169.*
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-01-31 00:56
Message:
It took some time, "forensics" and combined forces (THANKS go to Niels,
Martin & Sjoerd!), but finally, we located and fixed the reported leak in
revision 1.312.2.1 (new Feb2009 release branch) of
sql/src/backends/monet5/sql_gencode.mx by making sure that we use the BAT
returned by bat.append since
~~~~~~~
2008/08/09 - mlkersten: MonetDB5/src/modules/kernel/bat5.mx,1.37
The access modes of the kernel BAT update operations were not aligned.
This caused the SQL front-end to inject setWriteMode calls before each
append, and possibly trimming it again with an optimizer.
The BAT insert, append, delete, and replace functions now always return
the first argument, which my be a copy with the proper access properties.
This elicits the flow. The side effectness can be checked with the
isUpdateFunction.
The majority of files involve the now visible temporary in the output.
~~~~~~~
I'll add the respective test during the weekend ...
----------------------------------------------------------------------
Comment By: Jens Oehlschlägel (jens_oehl)
Date: 2009-01-28 09:27
Message:
If I remember correctly I saw both: silent crashes or frozen server console
with a memory allocation bug shown.
May be it helps to know that this bug was not in the release distributed
as MonetDB5-SQL-Installer-i386-20080624.msi
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-01-27 17:48
Message:
Using the new Algebra back-end of MonetDB/SQL via `mclient -G ...`
respectively `\G` on the mclient console --- the new Algebra back-end will
be default in the next feature release of MonetDB/SQL (most probably called
"Feb2009") --- (only) for the leaking query makes mserver5 grow only to 533
MB (instead of 628 MB) with the first call, but subsequent calls fail with
sql>SELECT sum(float1) AS currencyfloat, sum(float2) AS unitfloat FROM
databasetest;
!relational query without result
0 tuples
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-01-27 16:57
Message:
On a 64-bit Linux system (the only one I can quickly check it on) I can
reproduce the leaking.
After the first query (i.e., once the data is initially loaded) mserver5
is 628 MB, then it grwos by about 38 MB per query.
(with 8 GB RAM, crashes are "out of reach" ...)
While this is obviously not desirable and needs to be analyzed and fixed,
the question is, why this seems to (quickly?) cause a server crash on a 3
GB (Win32) machines?
Jens,
did you see any error messages on the server console windows, or does it
just "disappear"?
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2009-01-27 15:47
Message:
Jens,
no being familiar with R myself, could you please
(a) give a short hint (commandline call) how to use the R script to create
the data file and
(b) attach the R-script to this bug report to avoid unwanted line breaks
introduced by SF formatting/rendering?
Thanks in advance!
Stefan
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2540169&group_id=56967
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
