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: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Jens Oehlschlägel (jens_oehl)
Assigned to: Niels Nes (nielsnes)
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-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
