- users-list - monetdb.org

[MonetDB-users] sql - division
by dariuszs 13 Feb '09

13 Feb '09

Hello, How do I modify this sql to get correct answer? sql> select 10/100; I got 0 instead of 0.10 Thanks. Dariusz.

2 2

[MonetDB-users] Challenge string is not valid
by David Svenson 12 Feb '09

12 Feb '09

Hi, While working with MonetDB, I suddenly reached some sort of state in which I constantly get: "Challenge string is not valid" Whenever I try to connect to MonetDB. I am using the Mapi libraries, and get the same error if I use mclient.exe. I can't figure out how I reached this state and how I'm supposed to get out of it. Please help. Thanks, David. -- View this message in context: http://www.nabble.com/Challenge-string-is-not-valid-tp21976173p21976173.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] copy/select * from /into - very slow
by dariuszs 09 Feb '09

09 Feb '09

Hi, Could you please help me here. I've got 300 fields, 100 million records, 800 bytes per row in a table. Exporting 60 fields into a table takes about 1 minute and selecting * from table takes very long time, and I don't see lots of cpu usage when exporting all records. Thanks. Dariusz.

2 9

[MonetDB-users] Algebra Mode vs sql debug 16384
by Ross Bates 06 Feb '09

06 Feb '09

Hi All - can someone help me clarify the difference between Algebra "-G" mode and setting SQL debug equal to 16384 in the monetdb config file? With an older version of MonetDb I set the sql_debug=16384 in the config file so I could take advantage of Algebra mode when starting the server via mero. Today I compiled the Nov08-SP2 release and ran some tests using sql_debug=0 vs sql_debug=16384. I assumed they would do the exact same since Algebra mode is now the default. They returned very different results however. For SQL statements with a large number of predicates (100+), performance is about the same. But for anything smaller the query speed is much worse (10x avg) when I don't have sql_debug=16384 turned on. The query trace files are also different when I run the exact same query with and without the 16384 flag. I made sure to restart the server process in between. Also, below is the latest output from mserver --version: Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008- MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Configured for prefix: /home/ross/monetNov08sp2 Libraries: openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) Compiled by: ross@home Compilation: gcc -O2 -std=c99 -fgnu89-inline -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : /usr/bin/ld Any thoughts? Best Regards, Ross

5 4

[MonetDB-users] sql - string functions
by dariuszs 06 Feb '09

06 Feb '09

Hi, Just wondering why - strings functions seem to be very slow eg. 'select substring(field1,1,10) || substring(field2,1,20) as field3 from table1'. Thanks for help. Dariusz.

1 0

[MonetDB-users] Fedora 9 RPMs in Stable DailyBuild directory
by Hans van Rijswijk (DT) 06 Feb '09

06 Feb '09

Dear MonetDB users, In the release directories (http://monetdb.cwi.nl/downloads/Fedora/9/x86_64/) I can find RPMs for Fedora 9. Why can't I find Fedora 9 RPMs in the Stable DailyBuild directory (http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./200 90206/RPMs/) ? I needs to make a distribution including the latest Stable MonetDB and including Fedora 9 RPMs. Regards, Hans van Rijswijk

2 1

[MonetDB-users] Re naming a column via MAL/SQL
by Alfred Nordman 03 Feb '09

03 Feb '09

Hi guys, Is there a way to rename an existing column within a table via SQL? (the RENAME COLUMN syntax is not supported as it seems) and if not, can i do it via MAL? I tried to use MAL as follows, but after i do, the DB seems to get corrupt when i try to access the specific table. include sql; sql.init(); bat1 := sql.bind("sys","t1","Field1",0); bat.setName(bat1,"Field2"); bat.setPersistent(bat1,true); bot.commit; Thanks & Best Regards... -- View this message in context: http://www.nabble.com/Renaming-a-column-via-MAL-SQL-tp21807788p21807788.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Logs & Corrupt databases
by David Svenson 02 Feb '09

02 Feb '09

Hi, Every once in a while I get this message in the server: !ERROR: Please move away <log folder> and its corresponding dbfarm. !mvc_init: unable to create system tables When I delete the logs from the log folder, the server starts normally and the database is ok. Any ideas on what the reason for that might be? Is it possible to disable logging altogether? Thanks, David. -- View this message in context: http://www.nabble.com/Logs---Corrupt-databases-tp21792046p21792046.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Searching for possibilities to reduce the fluctuation in insertion times.
by John van Schie (DT) 02 Feb '09

02 Feb '09

Hi, We use MonetDB v4.26.4 (Nov 2008-sp2) for our application with the XQuery frontend. Often, queries are executed very fast, but sometimes, execution of the same query takes significantly more time. For example: We insert a XML document of 1.4Mb. with a maximum depth of 4. The document is structured as follows: <rootelement> <item> <child1> <sha1>sha1-value</sha1> <md5>md5-value</md5> </child1> </item> ... </rootelement> We have 8k 'item' elements. Often the insertion of this document is performed in ~0.5s, which is fine. But when the system is performing IO (e.g. copying a large file), the insertion can take up to 6 minutes. So I have two questions: 1) What is the cause of this large difference in execution times; 2) Is there a way to configure MonetDB (or the server) in such a way that the difference between the 'fast' and 'slow' is smaller. Regards, John van Schie

3 7

[MonetDB-users] sql performance - in vs between
by dariuszs 01 Feb '09

01 Feb '09

Hi, I know it's probably a trivial question but after looking at the following trace - is there a way to make sql 'in' behave like sql 'between', make *algebra.semijoin behave like **algebra.uselect*? Thanks for you help. Dariusz. :0[0]:W Strace select f25,count(*) from trw100 where f25 in ('F','M') group by f25; :13172[0]:R [ 0 usec mdb.setTimer(true); ] [ 15000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 16000 usec _8 := sql.bind("sys","trw100","f25",1); ] [ 16000 usec _10 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_20266>:bat[:oid,:str][0]); ] [ 0 usec _3 := nil; ] [ 15000 usec _8 := nil; ] [ 16000 usec _11 := sql.bind("sys","trw100","f25",2); ] [ 15000 usec _13 := algebra.kdifference(<tmp_20616>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _10 := nil; ] [ 16000 usec _14 := algebra.kunion(<tmp_20617>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _13 := nil; ] [ 0 usec _11 := nil; ] [ 15000 usec _15 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _16 := bat.reverse(<tmp_20253>:bat[:oid,:oid][0]); ] [ 16000 usec _15 := nil; ] [ 0 usec _17 := algebra.kdifference(<tmp_20616>[96240405],<~tmp_20253>[0]); ] [ 15000 usec _16 := nil; ] [ 16000 usec _18 := bat.reverse(<tmp_20617>[96240405]); ] [ 0 usec _17 := nil; ] [ 15000 usec _19 := bat.new(nil,nil); ] [ 16000 usec bat.append(<tmp_20506>:bat[:oid,:str][1],"F",true); ] [ 0 usec bat.append(<tmp_20506>:bat[:oid,:str][2],"M",true); ] [ 16000 usec _25 := bat.reverse(<tmp_20506>:bat[:oid,:str][2]); ] [ 15000 usec _19 := nil; ] [ 10782000 usec _26 := *algebra.semijoin(<~tmp_20617>[96240405],<~tmp_20506>[2]); *] [ 15000 usec _18 := nil; ] [ 16000 usec _25 := nil; ] [ 15000 usec _27 := bat.reverse(<tmp_20620>[93838869]); ] [ 0 usec _26 := nil; ] [ 16000 usec _29 := algebra.markT(<~tmp_20620>[93838869],0@0); ] [ 16000 usec _27 := nil; ] [ 0 usec _30 := bat.reverse(<tmp_20617>[93838869]); ] [ 15000 usec _29 := nil; ] [ 453000 usec _31 := algebra.join(<~tmp_20617>[93838869],<tmp_20616>[96240405]); ] [ 63000 usec _30 := nil; ] [ 0 usec _14 := nil; ] [ 953000 usec (ext37,grp35) := group.new(<tmp_20621>[93838869]); ] [ 16000 usec _34 := bat.mirror(<tmp_20542>[2]); ] [ 15000 usec ext37 := nil; ] [ 0 usec _35 := algebra.join(<tmp_20620>[2],<tmp_20621>[93838869]); ] [ 47000 usec _31 := nil; ] [ 344000 usec _36 := aggr.count(<tmp_20616>[93838869],<tmp_20616>[93838869],<tmp_20620>[2]); ] [ 31000 usec grp35 := nil; ] [ 16000 usec _34 := nil; ] [ 15000 usec _37 := sql.resultSet(2,1,<tmp_20544>[2]); ] [ 0 usec sql.rsColumn(0,"sys.trw100","f25","varchar",1,0,<tmp_20544>[2]); ] [ 16000 usec _35 := nil; ] [ 16000 usec sql.rsColumn(0,"sys.trw100","count_f25","wrd",64,0,<tmp_20545>:bat[:oid,:wrd][2]); ] [ 15000 usec _36 := nil; ] [ 0 usec _45 := io.stdout(); ] &1 0 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 16000 usec sql.exportResult(137554336,0,""); ] [ 13172000 usec user.s0_2("F","M"); ] :13203[0]:R :39062[0]:W Strace select f25,count(*) from trw100 where f25 between 'F' and 'M' group by f25; :43156[0]:R [ 0 usec mdb.setTimer(true); ] [ 16000 usec _3 := sql.bind("sys","trw100","f25",0); ] [ 1750000 usec _8 := *algebra.uselect(<tmp_5213>:bat[:oid,:str][96240405],"F","M",true,true);* ] [ 15000 usec _10 := sql.bind("sys","trw100","f25",1); ] [ 0 usec _12 := algebra.uselect(<tmp_20266>:bat[:oid,:str][0],"F","M",true,true); ] [ 16000 usec _13 := algebra.kunion(<tmp_20620>[93838869],<tmp_20621>[0]); ] [ 16000 usec _8 := nil; ] [ 15000 usec _12 := nil; ] [ 0 usec _14 := sql.bind("sys","trw100","f25",2); ] [ 16000 usec _16 := algebra.kdifference(<tmp_20616>[93838869],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _13 := nil; ] [ 0 usec _17 := algebra.uselect(<tmp_61>:bat[:oid,:str][0],"F","M",true,true); ] [ 15000 usec _18 := algebra.kunion(<tmp_20621>[93838869],<tmp_20617>[0]); ] [ 16000 usec _16 := nil; ] [ 15000 usec _17 := nil; ] [ 0 usec _19 := sql.bind_dbat("sys","trw100",1); ] [ 16000 usec _20 := bat.reverse(<tmp_20253>:bat[:oid,:oid][0]); ] [ 16000 usec _19 := nil; ] [ 0 usec _21 := algebra.kdifference(<tmp_20616>[93838869],<~tmp_20253>[0]); ] [ 15000 usec _18 := nil; ] [ 16000 usec _20 := nil; ] [ 16000 usec _23 := algebra.markT(<tmp_20617>[93838869],0@0); ] [ 0 usec _21 := nil; ] [ 15000 usec _24 := bat.reverse(<tmp_20616>[93838869]); ] [ 16000 usec _23 := nil; ] [ 0 usec _25 := algebra.kunion(<tmp_5213>:bat[:oid,:str][96240405],<tmp_20266>:bat[:oid,:str][0]); ] [ 15000 usec _3 := nil; ] [ 16000 usec _10 := nil; ] [ 16000 usec _26 := algebra.kdifference(<tmp_20617>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 0 usec _25 := nil; ] [ 15000 usec _27 := algebra.kunion(<tmp_20621>[96240405],<tmp_61>:bat[:oid,:str][0]); ] [ 16000 usec _26 := nil; ] [ 0 usec _14 := nil; ] [ 453000 usec _28 := algebra.join(<~tmp_20616>[93838869],<tmp_20617>[96240405]); ] [ 31000 usec _24 := nil; ] [ 16000 usec _27 := nil; ] [ 922000 usec (ext34,grp32) := group.new(<tmp_20621>[93838869]); ] [ 15000 usec _31 := bat.mirror(<tmp_20542>[2]); ] [ 16000 usec ext34 := nil; ] [ 0 usec _32 := algebra.join(<tmp_20620>[2],<tmp_20621>[93838869]); ] [ 47000 usec _28 := nil; ] [ 344000 usec _33 := aggr.count(<tmp_20617>[93838869],<tmp_20617>[93838869],<tmp_20620>[2]); ] [ 31000 usec grp32 := nil; ] [ 16000 usec _31 := nil; ] [ 15000 usec _34 := sql.resultSet(2,1,<tmp_20544>[2]); ] [ 16000 usec sql.rsColumn(1,"sys.trw100","f25","varchar",1,0,<tmp_20544>[2]); ] [ 0 usec _32 := nil; ] [ 15000 usec sql.rsColumn(1,"sys.trw100","count_f25","wrd",64,0,<tmp_20545>:bat[:oid,:wrd][2]); ] [ 16000 usec _33 := nil; ] [ 16000 usec _42 := io.stdout(); ] &1 1 2 2 2 % sys.trw100, sys.trw100 # table_name % f25, count_f25 # name % varchar, wrd # type % 1, 8 # length [ "F", 29577383 ] [ "M", 64261486 ] [ 15000 usec sql.exportResult(137554336,1,""); ] [ 4094000 usec user.s1_2("F","M"); ] :43187[0]:R :253953[0]:W Connection closed

3 3

[MonetDB-users] Building 64 bits
by David Svenson 01 Feb '09

01 Feb '09

Hi, I followed the instructions for building MonetDB in 64-bit, however, after finishing the build process and trying to run the server, I get this error message: !FATAL: BBPinit: database created with incompatible server: expected pointer size 8, got 4, expected OID size 4, got 4. I added "bits=64" to all my nmakes, but I guess something is still missing. Are there any other flags I need to raise in the build process that I might have missed? Thanks, David. -- View this message in context: http://www.nabble.com/Building-64-bits-tp21772757p21772757.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] mclient - copy into problem
by dariuszs 31 Jan '09

31 Jan '09

Hi, Please verify this - when you output a file from the table with 'copy... into...' and terminate that client - the output file is still being created even though the client was terminated. Thanks. Dariusz.

5 16

[MonetDB-users] Running into swap during large dataset loading
by Ukyo Virgden 30 Jan '09

30 Jan '09

Hi, I'm tring to build a database with around 1B rows. I'm testing on a 64bit AMD Opteron 2200 dual core x 2 CPU system with 16GB mem and 16G swap connected to an external fiber storage. I've got a set of datafiles around 8GB. each with 100M records. I'm running the following commands. copy 300000000 records into test_table from 'datafile-1.csv','datafile-2.csv','datafile-3.csv'; copy 300000000 records into test_table from 'datafile-4.csv','datafile-5.csv','datafile-6.csv'; copy 300000000 records into test_table from 'datafile-7.csv','datafile-8.csv','datafile-9.csv'; The first command completes around 15mins. The second also completes around 15min. However the the third command forces the system to swap and at some point cause the system to crash. I've also tried by NOT giving the record count i.e. copy into test_table from 'datafile-X.csv' which were not only slowe but also resulted in the same swap outage crash. I need to load around 3 billion records. Any thoughts on what might be the problem here? Thanks in advance, Regards, Ukyo

1 0

[MonetDB-users] sql_logs
by dariuszs 29 Jan '09

29 Jan '09

Hi, How do I read what's in the sql_logs directory files? Thanks. Dariusz.

3 5

[MonetDB-users] server crash/sql prompt
by dariuszs 29 Jan '09

29 Jan '09

Hi, Can somebody help me with this one. I was running a very long query, decided to terminate that job. Now when I try to get the client all I see is blinking prompt. How do I get back the sql prompt? Thanks. Dariusz.

5 26

[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