February 2013 - users-list

Suggested correction for the Java SQLcopyinto example
by Percy Wegmann 09 Feb '13

09 Feb '13

Dear Monet team, I've been getting quite a bit of use out of your example code here: http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java Recently, though, I ran into a problem. If the "COPY INTO" statement that I issue is wrong, for example the referenced table doesn't exist, Monet comes back with an error. The example isn't checking for this error condition and proceeds to send data anyway. With a really large batch of data, this becomes a problem because the SQL parser ends up trying to parse all that data as if it were SQL and basically chokes on it. The solution is to add something like the following after the out.newLine() on line 86: error = in.waitForPrompt(); if (error != null) { throw new Exception(error); } Cheers, -- Percy Wegmann +1 512 637 8500 ext 148

3 5

Requirement gathering and questions about binary bulk loading.
by Brandon Jackson 08 Feb '13

08 Feb '13

Hello fellow MonetDB'ers, The Pentaho MonetDB Bulk Loader transformation step is now working pretty well in our tests. http://ci.pentaho.com/view/Data%20Integration/job/Kettle contains a continuous integration build of the software if anyone wanted to play with it. New Transformation -> Steps -> Bulk Loading (category)-> MonetDB Bulk Loader Thank you for the previous information about enabling log output from the MonetDB JDBC driver. After killing a stalled "monetdb start ~/sg-farm" thread and restarting it, MonetDB decided to work really well. It was suggested early on that I take a look at the binary bulk loading approach to get the fastest results. Now that I have a streaming ascii version of a bulk loader done for Pentaho Data Integration, I wanted to come back to the binary approach and see what that might look like and what to be aware of. I read http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad It looks like the requirements are: 1. Write a physical file per column to the disk the monetdb farm exists on. 2. Tables with DATES, TIMESTAMPS or UTF-8 characters ie: Simplified Chinese are verboten and must be handled with COPY INTO .... USING DELIMITERS Assuming the above is a correct understanding, given the command below: copy into Tmp from ('path_to_file_i', 'path_to_file_f', 'path_to_file_s'); 1. If run from a computer separate from the machine running the monetdb server, would the 'path_to_file_i' be a pathname relative to the monetdb farm root directory on the remote server machine? 2. This process should work for appending new data on to an existing table, right? 3. Am I out of luck if my table has any of the verboten data types mentioned above mixed in with supported binary bulkloadable types; aka C-array style data types? 4. If not out of luck for a mixed type table, what is the procedure to load the table? In practice I imagine I will not need to fret too much about DATES needing a COPY INTO .... USING DELIMITERS ascii streaming approach because it is likely part of a dimension table with integer keys matching integers on a fact table, which is far more likely to have the supported types MonetDB Binary Bulk-loading likes. Is it true that MonetDB does not handle joins well, but instead prefers single, denormalized tables with many columns? >From what you know of the internals of MonetDb, is the effort to make some kind of streaming binary bulk loader worth it in terms of execution time on the database converting to native binary types? The documentation used the words, 'slightly faster', but I do not have a feel for when 'slightly' makes a big difference. Thanks again for considering these questions. Brandon

2 1

High memory usage while loading data with many columns
by Andrew Duffey 08 Feb '13

08 Feb '13

Hi, I'm loading data into a table with >20,000 columns and several hundred thousand rows using "COPY INTO". It works, but I can't seem to make it take less than about 30GB of memory. I've tried specifying the number of records as well. Is there any way to reduce memory usage when loading? Is this much memory usage expected? It's a shame because our queries take very little memory. We have to size our machine simply to handle the load. It's impressive (and very convenient) that I can actually have a table with so many columns, but are there any other limitations I might come across? I've just been trying to keep the column count in the queries low. A "select *" seems to crash MonetDB. Thanks, Andrew

2 8

Data storage capacity in monetdb
by KHALED DEHDOUH 06 Feb '13

06 Feb '13

I installed the version of windows for monetdb http://www.monetdb.org/Downloads. I installed a database column, but when I went over 30 GB of stokage given. the query response time has become very slow. Please, I have two questions: - Are this version is limited or no storage ? - I have noticed that the name of the database is by default "demo". Can i create another one with this version with 1 TB?

4 3

Create stored procedure to add a list of ID's to a table
by Richard Siebeling 05 Feb '13

05 Feb '13

Hello, is it possible to create a stored procedure in MonetDB that will have one parameter containing a string with several thousands of comma-separated ID's and insert these comma-separated ID's as separate rows into a table? I am receiving thousands of ID's from a separate webservice and want to join these ID's to a table in monetDB, aggregate the results and return them back to the client for further processing in the GUI. I tried inserting the ID's into a temporary table using insert into instructions from PHP, but that didn't gave me the performance I wanted (about 6 seconds for 10.000 ID's). So the next approach would be to create a stored procedure receiving all the ID's in one parameter, splitting them and inserting them one-by-one into the temporary table... Or is there a better approach for this problem? thanks in advance, Richard

2 1

Memory allocations in MonetDB
by Hassan, Ahmad 04 Feb '13

04 Feb '13

Hi Team, I am running monetdb with default settings. After profiling the application instructions, I have noticed that all the memory allocation are currently being performed by the following functions: GDKmalloc, GDKmallocmax, GDKzalloc, HEAPalloc So mmap and malloc are being used with in all GDK* and HEAPalloc. I have three questions please: 1) Which memory allocations of monetdb are performed through mmap and which memory allocation are performed through malloc. I know this is very open ended question but little bit info about this would be helpful. Are the BAT structures allocated through mmap or malloc? 2) One thing I noticed that, when the DB is running and even if I am not running any queries, still the DB is constantly issuing load/store instructions to Main memory. I assume that some thread in monetdb is doing bookkeeping or persistence related stuff. Can you please point out which part of the code is responsible for this? I want to exclude that part of the code from profiling 3) When we start monetdb with multiple threads, whether multiple threads use the same common virtual address memory region to allocate memory? Or each thread has their own distinct memory region? Thanks. Kind Regards, Ahmad

2 1