- users-list - monetdb.org

[MonetDB-users] distinct values limit
by dariuszs 26 Jan '09

26 Jan '09

Hi, Is there a limit on how many distinct values you can have in a column. I've created a table with 100,000,000 records and one column (varchar (16)) has 2,600,000 distinct values so when I run the count on that column - it runs forever. When I watch memory usage it goes to few gigabytes and then it drops to almost nothing, then it goes to few gigs and then it drops. Thanks. Dariusz.

1 0

Re: [MonetDB-users] predictable crashes of MonetDB5 SQL server under Win32bit
by Jens Oehlschlägel 26 Jan '09

26 Jan '09

Thanks Stefan, > with loading ~1.5 GB on a 32 bit machine, there is a reasonable chance that > the loading process (that needs to be able to address all data concurrently) > comes close to or even hits the 32-bit (i.e., max. 4 GB, possibly only 2 GB) > address space limit. Does this limitation refers to the size of the data that I am adding per COPY INTO (was 0.2 GB) or the final size of the table (was at 1.9). Assuming the latter this would give a hard limit for table size under Win32. If this is the case, a workaround could be partioning the data into several tables. What is more efficient in MonetDB: chunks of rows at full columns width or chunks of columns at full row length? > Would it be possible that you could provide us with your data? > That would help us a lot to analyze, locate and hopefully fix the crash... I give you access by seperate mail, you can also create the test data file using the R script snippet in my previous mail. Have a nice weekend Jens

5 5

[MonetDB-users] memory release
by dariuszs 26 Jan '09

26 Jan '09

Hi, MonetDB-SQL server does not release the memory after loading data into table (loaded 40GB) terminating client session (closed the window waiting for count to complete) Also after restarting the server it takes very long time for the client to get the prompt Why is that? Thanks. Dariusz.

1 0

[MonetDB-users] Announcement: New bug fix release of MonetDB suite
by Sjoerd Mullender 23 Jan '09

23 Jan '09

The MonetDB team at CWI is pleased to announce the Nov2008-SP2 bug fix release of the MonetDB suite of programs. This is a bug fix release of the Nov2008 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Nov2008/#Nov200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

[MonetDB-users] predictable crashes of MonetDB5 SQL server under Win32bit
by Jens Oehlschlägel 23 Jan '09

23 Jan '09

Hi, I experience predictable crashes of MonetDB5 SQLserver under Win32bit when COPY INTO in batches of 1 Mio more than ~9Mio rows, which is ~1.5 GB DB size. Any settings to avoid that? Is this a bug? Is MonetDB size limited to available RAM? Or is this a known limit just under Win32? Logs of sql client error messages and details for replication below. Regards Jens Oehlschlägel 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 sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 67515.000 msec 0 rows sql>COMMIT; 0 tuples Timer 1255.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 79141.000 msec 0 rows sql>COMMIT; 0 tuples Timer 6041.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 74227.000 msec 0 rows sql>COMMIT; 0 tuples Timer 6943.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 73084.000 msec 0 rows sql>COMMIT; 0 tuples Timer 6599.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 67158.000 msec 0 rows sql>COMMIT; 0 tuples Timer 6520.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 18529.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 65106.000 msec 0 rows sql>COMMIT; 0 tuples Timer 5184.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 71136.000 msec 0 rows sql>COMMIT; 0 tuples Timer 9104.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 21021.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 69761.000 msec 0 rows sql>COMMIT; 0 tuples !MALException: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: GDKload: cannot mmap(): name=15\1511, ext=tail !OS: Not enough storage is available to process this command. !ERROR: GDKload: failed name=15\1511, ext=tail !ERROR: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: GDKload: cannot mmap(): name=15\1511, ext=tail !OS: Not enough storage is available to process this command. !ERROR: GDKload: failed name=15\1511, ext=tail !ERROR: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 14680064, 0) failed !OS: Not enough storage is available to process this command. !ERROR: GDKload: cannot mmap(): name=15\1511, ext=tail !OS: Not enough storage is available to process this command. !ERROR: GDKload: failed name=15\1511, ext=tail !ERROR: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 34603008, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 34603008, 0) failed !OS: Not enough storage is available to process this command. !ERROR: GDKload: cannot mmap(): name=11\1117, ext=tail !OS: Not enough storage is available to process this command. !ERROR: GDKload: failed name=11\1117, ext=tail !ERROR: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 69206016, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 70254592, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1558, 2, 0, 0, 70254592, 0) failed !OS: Not enough storage is available to process this command. !ERROR: MT_mmap: MapViewOfFileEx(1554, 2, 0, 0, 70254592, 0) failed !OS: Not enough storage is available to process this command. !ERROR: GDKload: cannot mmap(): name=15\1534, ext=tail !OS: Not enough storage is available to process this command. !ERROR: GDKload: failed name=15\1534, ext=tail 0 tuples Timer 9276.000 msec 0 rows sql>START TRANSACTION; 0 tuples Timer 0.000 msec 0 rows sql>COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/d atabasetest1mio.csv' USING DELIMITERS ','; Rows affected 1000000 Timer 96373.000 msec 0 rows sql>COMMIT; MAPI = voc@localhost:50000 ACTION= read_line QUERY = COMMIT; ERROR = Connection terminated Timer 44089.000 msec 0 rows -- restarting the crashed server looks good, but # MonetDB server v5.8.2, based on kernel v1.26.2 # Serving database 'demo', using 1 thread # Compiled for i686-pc-win32/32bit with 32bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008- MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see D:\MonetDB\MonetDB5\etc\monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.26.2 loaded -- when starting mclient the server says >!ERROR: MT_mmap: MapViewOfFileEx(1528, 2, 0, 0, 69206016, 0) failed !OS: Not enough storage is available to process this command. #GDKmmap(69206016) fail => BBPtrim(enter) usage[mem=165285248,vm=1495793664] #GDKmmap(69206016) fail => BBPtrim(ready) usage[mem=165284840,vm=574619648] #GDKmmap: recovery ok. Continuing.. -- replication instructions -- log in to freshly installed MonetDB and create user CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys"; CREATE SCHEMA "voc" AUTHORIZATION "voc"; ALTER USER "voc" SET SCHEMA "voc"; -- log in as voc and execute the following statements CREATE TABLE databasetest ( "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"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; START TRANSACTION; COPY 1000000 OFFSET 2 RECORDS INTO "voc"."databasetest" FROM 'F:/Periscope/databasetest1mio.csv' USING DELIMITERS ','; COMMIT; -- the file databasetest1mio.csv can be created with the following R script # this is an R comment and you find R at CRAN.r-project.org filename <- "F:/Periscope/databasetest1mio.csv" # create 1Mio rows in 100 batches of 10,000, total of 174MB csv file, takes ~2 minutes system.time({ b <- 1000L # 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) })

3 2

Re: [MonetDB-users] copy into table
by Jens Oehlschlägel 22 Jan '09

22 Jan '09

A related feature for bulk import/export that would indeed make sense AND should be easy to implement is: exchange with flat binary files. This requires - the file to be set up with the correct byte-order - the file to contain only data types that have a fixed-width binary representation (like FLOAT, SINGLE, INTEGER, SMALLINT etc) but not variable widths (like VARCHAR) Such file would neither require record_seperator nor tuple_seperator. The only challenge is how to represent NULL, and for this you could turn to the definitions of NA (Not Available atomic value) used in the popular statistical interpreter R (www.r-project.org) for its native data types (double, integer) and for other data types you could use the NA conventions implemented in R's flat file package 'ff' (single, signed and unsigned integers, smallints, bytes, etc, see documentation for 'vmode'). Even dates could be represented unambiguously using the double representation of the POSIXct class in R. For the SQL interface you could simply define binary files by setting tuple_seperator and record_seperator to '' the empty string. If you use a trick and allow COPY INTO not only to accept a table_name but also a view_name - where the view represents a subset of the columns of one table - such an interface could even be used to populate a table that has columns which are not in the binary file, say the table has a varchar column or, exchange data column by column with systems that cannot work with mixed-type binary files (such as ff which currently can only handle homogeneous atomic files). Beside the obvious benefits - for example exporting large data to R for analysis purposes - such a file interface could also be used to create artifical data in R with certain statistical features for datawarehouse test loads (say exploring MonetDBs 'indexing' performance for distributions with certain entropy etc). Greetings Jens Oehlschlägel (one of the ff authors)

1 0

[MonetDB-users] copy into table
by dariuszs 21 Jan '09

21 Jan '09

Hello, Is there any way to copy flat file into the table not using delimiters? Thanks. Dariusz.

4 11

[MonetDB-users] SQL server crash
by dariuszs 20 Jan '09

20 Jan '09

Hi, The SQL server crashes when I try to join 3 tables using 27 bytes key? Can you help me? Thanks. Dariusz.

6 16

[MonetDB-users] Changing locale to support various Datetime formats
by David Svenson 20 Jan '09

20 Jan '09

Is there any recommended way to format dates according to different locales in Windows? I try to do COPY INTOs with dates, but get parsing errors, and I can't find any solution for it because I can't use mtime.str_to_date and such in Windows. Thanks, David. -- View this message in context: http://www.nabble.com/Changing-locale-to-support-various-Datetime-formats-t… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Python3.0
by Kees Varekamp 20 Jan '09

20 Jan '09

Hello, Does anyone know if the Python bindings are working with Python 3.0? Thanks

1 0