November 2015 - users-list

Range query in MonetDB
by chenkaijiang＠bigknow.com.cn 25 Nov '15

25 Nov '15

Since MonetDB actually ignore SQL statement CREATE INDEX, the performance sometimes is quite poor comparing to traditional DB. Such as: https://www.monetdb.org/pipermail/users-list/2008-April/001839.htm… In reality, queries with ranges (such as date ranges) are very common and thus index is critical. Sometimes, it gives 100 times performance boost, especially when the queries are relatively simple. We debug into MonetDB source codes and found that CREATE INDEX only creates an entry in the system catalog.  Is MonetDB going to support index in the coming future, or in some roadmap? 

1 0

Serious error in data recording of type numeric
by Luciano Sasso 25 Nov '15

25 Nov '15

Serious error in data recording of type numeric. Using the jdbc driver 2.8, recording both base OUT2014SP4 when JUL2015SP1, records normally. But when using any jdbc driver above 2.8, the recording is wrong, for example, the value 845 is recorded as 850. Is it a bug? Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

MonetDB with dynamic columns
by Niklas Bivald 25 Nov '15

25 Nov '15

Hi, I've had my eyes on MonetDB for quite some time and the Numpy implementation really pushed me over the edge (as in this is the right solution for us). A big shout out to Mark Raasveldt for both the function and support and bug-fixes along the way. We have a fairly small dataset which probably won't exceed 10 million rows, 500 columns. We do flight data analysis so each row can be though of as a flight, and all the columns are data-points connected to it. We then use Numpy to prepare the data for graphs and limit our selection using standard SQL. We generally SELECT between 1-5 columns and use the WHERE statement on *another* 1-5 columns to select our rows. However, as we develop more analyses we connect more and more data-points (columns) to each row. Now there is a few ways we could deal with this: 1. Store the data in a column-less format (such as JSON) and re-create the MonetDB database when we need to add or remove columns (since MonetDB is really fast to read data into it, and we have no problem automating this process) - This is what we've done so far in our proof of concept 2. Store everything in MonetDB and use Schema alterations as new columns are added (requires altering in a live database) 3. Split the data into multiple tables (one for each analysis) and use JOIN to select them. This makes it not so much one huge table, but several small. Still need schema alterations when doing anything besides adding analysis. (4. Use a lookup table syntax, where we store three columns: ID, NAME, VALUE and instead of having 10 million rows and 500 columns use 10*500 rows - one row for each data point) - Since MonetDB excels at being a columnar storage putting everything into one table with only three columns seems like a very bad fit. Nevertheless it's a though. Before I spend more time doing proof-of-concepts on scenario 2 and 3 I just wanted to ask: Am I missing something? Is there any obvious solution that I am not covering? Is there any way to have "open" columns (i.e dynamic columns instead of fixed schema where the columns are created upon insert) Again, thank you so much for the work everyone of you is doing on MonetDB. I hope to start contributing as I learn more (probably just adding documentation short-term, possibly committing fixes in the long-term) Regards, Niklas

2 1

about replica table
by chenkaijiang＠bigknow.com.cn 25 Nov '15

25 Nov '15

Hi, from https://www.monetdb.org/Documentation/Manuals/SQLreference/Tables, there 4 types of tables: normal, merge, remote, replica. For merge and remote table, I found explaining on https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Distribu…. But what is replica table? Any documents? Could somebody help?

2 1

Driver JDBC, error rounding numeric value
by Luciano Sasso 25 Nov '15

25 Nov '15

Any idea, about error driver JDBC rounding error. Driver 2.18 run error, driver 2.1 run work ok. -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Error driver JDBC
by Luciano Sasso 24 Nov '15

24 Nov '15

Hi, Iam use MonetDB JUL 2015-SPI, and driver JDBC 2.18, but error on load data, in rooud value, exampla, value 854 in source, load 850 in target. I am test with driver JDBC 2.1 and run word, value 845, load in target with 845. any idea !!! -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Vacuum limitations
by Sébastien RAILLARD (PASSMAN) 24 Nov '15

24 Nov '15

Dear all, I have tried to call the vacuum function using this command: call sys.vacuum('my_schema','my_table'); I have a message telling me "vacuum not allowed on tables with indices". So, if I understand correctly, the table on which vacuum can be done must not have any index, even a primary key? Best regards, Sebastien -- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com <https://www.facebook.com/PASSMAN-187787814053/>

2 2

err incorrect checksum for freed object
by Lynn Carol Johnson 24 Nov '15

24 Nov '15

Hello - I had a working installation of monetdb on my MAC (OS x 10.9.5). Initially it was installed with the July 2015 release, I then updated to the july2015SP1. With the original version I was able to load a large (10G ) file without problems. For reasons relating to debugging a monetdb install on a red hat machine, I deleted the entries in the DB on my MAC and tried to re-add them. My commands were: > delete from annosites; > select count(*) from annosites; The db showed 0 items as expected. sql>\d annosites CREATE TABLE "testjeff"."annosites" ( "chr" INTEGER, "pos" INTEGER, "hapmap31_total_depth" INTEGER, "hapmap31_num_taxa" SMALLINT, "hapmap31_num_alleles" SMALLINT, "hapmap31_minor_allele_avg_depth" REAL, "hapmap31_minor_allele_avg_phred" REAL, "hapmap31_num_hets" SMALLINT, "hapmap31_ed_factor" REAL, "hapmap31_seg_test_p_value" REAL, "hapmap31_ibd_one_allele" BOOLEAN, "hapmap31_in_local_ld" BOOLEAN, "hapmap31_maf" REAL, "hapmap31_near_indel" BOOLEAN, "hapmap31_first_alt_allele_is_ins_or_del" BOOLEAN, "snpeff40e_effect_hapmap31" CHARACTER LARGE OBJECT, "snpeff40e_effectimpact_hapmap31" CHARACTER LARGE OBJECT, "snpeff40e_functionalclass_hapmap31" CHARACTER LARGE OBJECT, "gerp_neutral_tree_length" REAL, "gerp_score" REAL, "gerp_conserved" BOOLEAN, "mnase_low_minus_high_rpm_shoots" REAL, "mnase_bayes_factor_shoots" REAL, "mnase_hotspot_shoots" BOOLEAN, "mnase_low_minus_high_rpm_roots" REAL, "mnase_bayes_factor_roots" REAL, "mnase_hotspot_roots" BOOLEAN, "within_gene" BOOLEAN, "within_transcript" BOOLEAN, "within_exon" BOOLEAN, "within_cds" BOOLEAN, "within_cds_from_gff3" BOOLEAN, "within_five_prime_utr" BOOLEAN, "within_three_prime_utr" BOOLEAN, "codon_position" SMALLINT, "go_term_accession" CHARACTER LARGE OBJECT, "go_term_name" CHARACTER LARGE OBJECT ); But now, doing a "COPY INTO" continues to either hang with no error in the merovingian.log file , or to fail with the following error in the merovingian.log file. Most often it hangs. I have tried COPY into with and without the "records" option: sql> COPY INTO annosites from '/Users/lcj34/notes_files/machineLearningDB/annoDB_related/siteAnnoNoHdrsCol35Fixed_20151011.txt' USING DELIMITERS '\t','\n'; sql> sql>COPY 61000000 records INTO annosites from '/Users/lcj34/notes_files/machineLearningDB/annoDB_related/siteAnnoNoHdrsCol35Fixed_20151011.txt' USING DELIMITERS '\t','\n'; The merovingian.log file the one time it had an error: 015-11-23 15:02:55 MSG testJeff[752]: # loading sql script: 99_system.sql 2015-11-23 15:15:49 ERR testJeff[752]: mserver5(752,0x112908000) malloc: *** error for object 0x7fbe21a08208: incorrect checksum for freed object - object was probably modified after being freed. 2015-11-23 15:15:49 ERR testJeff[752]: *** set a breakpoint in malloc_error_break to debug 2015-11-23 15:15:50 MSG merovingian[747]: database 'testJeff' (752) was killed by signal SIGABRT Any idea what could be wrong? I tried stopping, then destroying the database and starting it over. This didn't help. I then created a new dbfarm, and new database, connect monetdbd to it, and tried again. While I can create this database of 37 columns, I am unable to load the file that previously was successfully loaded. Is this my a MAC issue? What else can I try on monetdb side? Is there any reason to re-install everything ? I'm still in the testing stage so would not be losing data. Thanks - Lynn

1 2

commit failures with dbfarm on iSCSI LUN
by Roberto Cornacchia 24 Nov '15

24 Nov '15

Hi there, Do you have any experience with running a dbfarm over iSCSI? We have tried to use the NAS in our 1Gbit LAN for our largish daily experiments with MonetDB. It's a very handy setup and seems more suited than NFS. It seems to achieve reasonable performance, but we get quite regularly (though not predictably for now) commit failures during a rather long ETL. We do not get such commit failures when the same db and ETL are run on a local disk. Excerpt from merovingian.log (Jul2015-SP1): 2015-11-24 11:52:37 ERR trec01[19110]: !ERROR: bm_subcommit: commit failed 2015-11-24 11:52:37 ERR trec01[19110]: !ERROR: log_tend: write failed 2015-11-24 11:52:37 ERR trec01[19110]: !FATAL: 40000!COMMIT: transation commit failed (perhaps your disk is full?) exiting (kernel error: !ERROR: GDKsave: error on: name=07/717, ext=theap, mode=1 2015-11-24 11:52:37 ERR trec01[19110]: !OS: Input/output error 2015-11-24 11:52:37 ERR trec01[19110]: ) The disk is most definitely not full, 1.5 TB available (the same works on a local disk with less space available). It looks like iSCSI is the problem (which works perfectly except these random failures). Can you think of any reason why iSCSI could could fail where a real local block device would not? iscsi client (where MonetDB runs): libiscsi 1.11.0 iscsi storage (where the dbfarm is stored): iscsid 2.0-871 The iSCSI LUN is created as regular file with thin provisioning (a file that dynamically grows on the NAS). We haven't tried yet with a fixed-size block-level LUN (trying this today anyway) Hoping someone can have an idea already. Roberto

2 2

String functions support in MonetDB
by Sébastien RAILLARD (PASSMAN) 24 Nov '15

24 Nov '15

Dear all, Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings. Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere? ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper Best regards, Sebastien -- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com <https://www.facebook.com/PASSMAN-187787814053/>

5 7