- users-list - monetdb.org

MonetDB writing to disk?!
by Martin Schwitalla 19 Nov '15

19 Nov '15

Hi, I´m running some analysis on MonetDB and have quite bad results with MonetDB. At first I thought it would be the fault of the Operator-at-a-Time Modell. I could replicate the behaviour that predicates on more columns resulted in a linear growing in execution times. This is a direct consequence of the Operator-at-a-Time Modell. But know i realized that the behaviour heavily depends on the used disk in the system. If I have the database on my primary disk storage I get quite good results. But because the primary disk storage is quite limited in our experimental PC I have worked on an NAS with 11TB. When I launch the same query on the NAS the query doesn`t take 0,4s, but 25s! After further testing I could see with iotop that the mserver is constantly writing something to the disks. But why is that so? I`m running Ubuntu 14.04 64bit, with 2 Intel Xeon E5-2690 CPUs and 62GB RAM. I have installed the TPC-H benchmark and running select count(*) from lineitem where l_orderkey%2=0; on my primary disk storage took about 400ms. On the external disk storage it took about 23s. The table contains 149996355 tuple and I created the TPC-H database with a scaling factor of 25. While running this query SELECT anon_1.sample_id AS anon_1_sample_id, anon_1.variant_id AS anon_1_variant_id, anon_1.qual AS anon_1_qual, anon_1.is_heterozygous AS anon_1_is_heterozygous, anon_1.read_depth AS anon_1_read_depth, anon_1.ref_depth AS anon_1_ref_depth, anon_1.alt_depth AS anon_1_alt_depth, anon_1.strand_bias AS anon_1_strand_bias, anon_1.qual_by_depth AS anon_1_qual_by_depth, anon_1.mapping_qual AS anon_1_mapping_qual, anon_1.haplotype_score AS anon_1_haplotype_score, anon_1.mapping_qual_bias AS anon_1_mapping_qual_bias, anon_1.read_pos_bias AS anon_1_read_pos_bias, annotations.variant_id AS annotations_variant_id, annotations.feature_id AS annotations_feature_id, annotations.ref_codon AS annotations_ref_codon, annotations.alt_codon AS annotations_alt_codon, annotations.ref_acid AS annotations_ref_acid, annotations.alt_acid AS annotations_alt_acid, annotations.type AS annotations_type, annotations.region AS annotations_region, annotations.splice_dist AS annotations_splice_dist FROM (SELECT calls.sample_id AS sample_id, calls.variant_id AS variant_id, calls.qual AS qual, calls.is_heterozygous AS is_heterozygous, calls.read_depth AS read_depth, calls.ref_depth AS ref_depth, calls.alt_depth AS alt_depth, calls.strand_bias AS strand_bias, calls.qual_by_depth AS qual_by_depth, calls.mapping_qual AS mapping_qual, calls.haplotype_score AS haplotype_score, calls.mapping_qual_bias AS mapping_qual_bias, calls.read_pos_bias AS read_pos_bias FROM calls JOIN samples ON samples.id = calls.sample_id JOIN patients ON patients.id = samples.patient_id JOIN diseases ON diseases.id = samples.disease_id LEFT OUTER JOIN (SELECT calls.sample_id AS sample_id, calls.variant_id AS variant_id, calls.qual AS qual, calls.is_heterozygous AS is_heterozygous, calls.read_depth AS read_depth, calls.ref_depth AS ref_depth, calls.alt_depth AS alt_depth, calls.strand_bias AS strand_bias, calls.qual_by_depth AS qual_by_depth, calls.mapping_qual AS mapping_qual, calls.haplotype_score AS haplotype_score, calls.mapping_qual_bias AS mapping_qual_bias, calls.read_pos_bias AS read_pos_bias FROM calls WHERE calls.qual > 0 AND calls.sample_id IN (227, 230, 233, 234, 237, 190, 195, 198, 199, 203, 270, 273, 276, 189, 343, 366, 367, 368)) AS anon_2 ON anon_2.variant_id = calls.variant_id LEFT OUTER JOIN known ON known.variant_id = calls.variant_id AND known.source_id IN (1, 2, 20, 19, 46) WHERE samples.accession IN ('17041R5', '20195R', '21984R', '23273R', '23390R', '13264R', '18337R', '18533R', '19811R', '20039R', '21776R', '21809R', '22927R', '17294R', '17071R', '21016R', 'Greif1R', '18337T', '18533T', '19811T', '20039T', '21809T', '22927T', '20195T', '21984T', '23273T', '23390T', '17041T', '17294T', '17071T', '21016T', '13264T', '21776T', 'Greif1T1S') AND calls.qual >= 50 AND anon_2.variant_id IS NULL AND (known.variant_id IS NULL OR known.clinical AND (NOT known.clinical_significance = 2) OR known.precious OR known.locus_specific_db)) AS anon_1 JOIN variants ON variants.id = anon_1.variant_id JOIN annotations ON variants.id = annotations.variant_id JOIN features ON features.id = annotations.feature_id JOIN transcripts ON transcripts.id = features.transcript_id JOIN genes ON genes.id = transcripts.gene_id WHERE (variants.is_transition IS NULL OR variants.is_transversion IS NULL OR (variants.is_transition OR variants.is_transversion) AND (anon_1.strand_bias IS NULL OR anon_1.strand_bias <= 60.0) AND (anon_1.qual_by_depth IS NULL OR anon_1.qual_by_depth >= 2.0) AND (anon_1.mapping_qual IS NULL OR anon_1.mapping_qual >= 40.0) AND (anon_1.haplotype_score IS NULL OR anon_1.haplotype_score <= 13.0) AND (anon_1.mapping_qual_bias IS NULL OR anon_1.mapping_qual_bias >= -12.5) AND (anon_1.read_pos_bias IS NULL OR anon_1.read_pos_bias >= -8.0) OR NOT (variants.is_transition OR variants.is_transversion) AND (anon_1.strand_bias IS NULL OR anon_1.strand_bias <= 200.0) AND (anon_1.qual_by_depth IS NULL OR anon_1.qual_by_depth >= 2.0) AND (anon_1.read_pos_bias IS NULL OR anon_1.read_pos_bias >= -20.0)) AND (abs(annotations.splice_dist) <= 10 OR annotations.region = 2 AND (NOT annotations.type = 2) OR annotations.region = 3 OR annotations.region = 1); I could see that the mserver is constantly writing to the disks. So why is MonetDB constantly writing to the disks, even when the query is quite simpel like the one TPC-H query above? The data should fit into the memory without problems. Any ideas or suggestions? Kind Regards, Martin

2 3

Using multiple COPY INTO inside a transaction from Java
by Gerardo Blanco 18 Nov '15

18 Nov '15

Hi, We are already using the COPY INTO bulk insert from Java via BufferedMCLWriter, as you suggest in your recipes book<https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData>, and based in the example<http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java> you provided. I understood that each COPY INTO behaves as a single transaction. My question now is if there is a way to group two or more COPY INTO and make them behave as a single transaction. Thank you Gerardo

3 2

publishing this november 30th
by Anthony Damico 17 Nov '15

17 Nov '15

some good news to go with the bad https://github.com/ajdamico/asdfree/tree/master/IPUMS%20International asdfree.com has had over 1,000 unique visitors per week since august. hopefully this will push even more users your way. thanks

1 0

the majority of my monetdb programs broke between Jul2015 and Jul2015-SP1
by Anthony Damico 17 Nov '15

17 Nov '15

am i alone here? i'm very unhappy about this.. i'm sorry i'm being such a pain in the ass, but this has disrupted all of my work. all of my code worked on both unix and windows on prior versions. both the "american community survey" and "national incident based reporting system" bugs were introduced recently. [1] this bug [2] was also introduced after Jul2015, but luckily fixed before sp1's release. that had affected another two scripts. i am having a hell of a time reproducing a similar bug that's equally difficult to reproduce. it was a bit of a herculean effort for hannes to get 3828 isolated, the bug i am struggling with now is no different. [1] https://www.monetdb.org/bugzilla/buglist.cgi?bug_status=NEW&bug_status=ASSI… [2] https://www.monetdb.org/bugzilla/show_bug.cgi?id=3828

1 0

what could i explore to create a reproducible example for a bug report?
by Anthony Damico 17 Nov '15

17 Nov '15

yellow highlight is the only difference > # this command works fine create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; > # this command crashes mserver create table zp13 as select v.* , arf.fips , arf.county , arf.countyst , arf.msacode , arf.msaname from v13 as v left join arf on v.state_code = arf.state_cd AND v.bene_county_cd = arf.cnty_cd WITH DATA; i've attached the sys.columns for the two tables, not sure if attachments are allowed on the mailing list but i can send separately if needed. thanks!

1 2

MonetDB concurrent commits exception
by Vijay Krishna 17 Nov '15

17 Nov '15

Hi, I am running a setup on MonetDB where in concurrent transactions take place. Everything works very fine but for a few transactions getting aborted due to SQLExceptions. I have set *auto commit to false* for all these transactions. I am getting frequent SQLExceptions with the message as *java.sql.SQLException: COMMIT: failed* But if I retry the same transaction, this works fine. Any idea on what would be the reason for frequent commit fails? When I set *auto commit to true* for the transactions, I get similarly frequent Exceptions but the message becomes *java.sql.SQLException: COMMIT: transaction is aborted because of concurrency conflicts* Is it like both the exception thrown be for the same cause? Any help much appreciated. Thanks in Advance. Vijayakrishna.P. Mobile : (+91) 9500402305.

4 5

Chinese string and varchar size
by Mathieu Raillard 16 Nov '15

16 Nov '15

Hi all, Last week we find a strange case with chinese character and VARCHAR size. We have in our database a field defined with VARCHAR(5) and a chinese string "不要让早把" to be inserted. If we insert the string with SQL insert everything works find, but if we try to insert via BULK monet throw an error. I made several test and i need to extend the varchar from 5 to 10 to get the bulk insert working. It looks like BULK somehow counts bits and not characters ? I give you some infos if you want to reproduce the case: create table test.varcharsize4 ( id int, varchar105 varchar(4), primary key (id) ); create table test.varcharsize5 ( id int, varchar106 varchar(5), primary key (id) ); create table test.varcharsize9 ( id int, varchar200 varchar(8), primary key (id) ); create table test.varcharsize10 ( id int, varchar200 varchar(10), primary key (id) ); *Insert SQL :* insert into test.varcharsize4 values (1,'不要让早把'); insert into test.varcharsize5 values (1,'不要让早把'); insert into test.varcharsize9 values (1,'不要让早把'); insert into test.varcharsize10 values (1,'不要让早把'); *BULK Line to insert :* "1","不要让早把" *BULK executions :* COPY 10 RECORDS INTO test.varcharsize4 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(4)' expected in '不要让早把' ------ COPY 10 RECORDS INTO test.varcharsize5 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(5)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize89 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(9)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize10 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; OK Regards Mathieu

2 1

Merge table; partition pruning?
by Bryan Senseman 13 Nov '15

13 Nov '15

Ok, I was all excited about MERGE TABLEs moving up into the SQL layer, unfortunately my enthusiasm might be misplaced as I don’t see any improvement in query performance. Let me explain: I have a fact table with ~300 M rows with 5 years of data. I created 5 yearly tables, ordering by my date_key (and also created a year column). Checked storage to verify MonetDB knew they were sorted. Created a Merge table over all 5 yearly tables. I then ran test queries with various year subsets (using direct fact column filter and joining from date dimension), from 1 to all 5 and well I didn’t see any improvement in query speed when a subset of the 5 years was queried. In fact I usually saw 10-20% degradation of performance. I am no MAL expert, but looking at the PLAN and the EXPLAIN/TRACE outputs it appears to me tables that shouldn’t have data for the query are still scanned. I ran this both on a 16GB 4 core Windows box and a 32GB 8 core EC2 Linux box, same behavior both places, using both July2015 and July2015SP1. Is this expected behavior? Am I missing some magic to have MonetDB do the Oracle concept of Partition Elimination? Or are Merge tables really just meant for data maintenance? Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

4 3

Error starting a production database
by Juan Carlos Gastélum 13 Nov '15

13 Nov '15

Hi I'm getting the following error when a I try to start our production database: # builtin opt gdk_dbpath = /var/monetdb5/dbfarm/demo # builtin opt gdk_debug = 0 # builtin opt gdk_vmtrim = no # builtin opt monet_prompt = > # builtin opt monet_daemon = no # builtin opt mapi_port = 50000 # builtin opt mapi_open = false # builtin opt mapi_autosense = false # builtin opt sql_optimizer = default_pipe # builtin opt sql_debug = 0 # cmdline opt gdk_dbpath = /usr/local/monetdb/alm_dat_far # cmdline opt merovingian_uri = mapi:monetdb://farser07:50000/alm_dat_far # cmdline opt mapi_open = false # cmdline opt mapi_port = 0 # cmdline opt mapi_usock = /usr/local/monetdb/alm_dat_far/.mapi.sock # cmdline opt monet_vault_key = /usr/local/monetdb/alm_dat_far/.vaultkey # cmdline opt gdk_nr_threads = 6 # cmdline opt max_clients = 64 # cmdline opt sql_optimizer = default_pipe # cmdline opt monet_daemon = yes # cmdline opt gdk_debug = 10 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'alm_dat_far', using 6 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 39.298 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for UNIX domain connection requests on mapi:monetdb:///usr/local/monetdb/alm_dat_far/.mapi.sock # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: cannot create seqs bats What can I do to solve it? I have been trying to recover a backup of the virtual machine where the instance of monetdb is but until now I have not been able to recover this database. Thanks in advance. Juan Carlos Gastelum

3 9

Merge and remote table errors
by Bart Lenoir 12 Nov '15

12 Nov '15

Hi, Just adding another finding: Using 1 MERGE TABLE (on Mac) with 2 REMOTE TABLEs (on Linux [physically remote, in another network]) COUNT, MIN, MAX are fine but AVG, MEDIAN, QUANTILE etc. make the server crash. Using 1 MERGE TABLE (on Linux) with 1 REMOTE TABLE (on Linux [physically together, in the same network]) and 1 local TABLE, all the above functions are fine except for the occasional error. sql>select sys.median(doubleDataType) from xxxxxxx; (mapi:monetdb://monetdb@192.168.1.212/xxxxxxx) 'user.l2' undefined in: (rmt3853_X_62_bat_oid_str:bat[:oid,:str],rmt3854_X_65_bat_oid_str:bat[:oid,:str],rmt3855_X_66_bat_oid_str:bat[:oid,:str ],rmt3856_X_67_bat_oid_str:bat[:oid,:str],rmt3857_X_68_bat_oid_dbl:bat[:oid,:dbl],rmt3858_X_70_bat_oid_dbl:bat[:oid,:d bl],rmt3859_X_71_bat_oid_dbl:bat[:oid,:dbl],rmt3860_X_72_bat_oid_dbl:bat[:oid,:dbl],rmt3861_X_73_bat_oid_dbl:bat[:oid ,:dbl],rmt3862_X_74_bat_oid_dbl:bat[:oid,:dbl],rmt3863_X_75_bat_oid_dbl:bat[:oid,:dbl],rmt3864_X_76_bat_oid_dbl:bat[: oid,:dbl],rmt3865_X_77_bat_oid_dbl:bat[:oid,:dbl],rmt3866_X_78_bat_oid_dbl:bat[:oid,:dbl],rmt3867_X_79_bat_oid_dbl:ba t[:oid,:dbl],rmt3868_X_80_bat_oid_dbl:bat[:oid,:dbl],rmt3869_X_81_bat_oid_dbl:bat[:oid,:dbl],rmt3870_X_82_bat_oid_dbl: bat[:oid,:dbl],rmt3871_X_83_bat_oid_dbl:bat[:oid,:dbl],rmt3872_X_84_bat_oid_dbl:bat[:oid,:dbl],rmt3873_X_85_bat_oid_d bl:bat[:oid,:dbl],rmt3874_X_86_bat_oid_dbl:bat[:oid,:dbl],rmt3875_X_87_bat_oid_dbl:bat[:oid,:dbl],rmt3876_X_88_bat_oid _dbl:bat[:oid,:dbl],rmt3877_X_89_bat_oid_dbl:bat[:oid,:dbl],rmt3878_X_90_bat_oid_dbl:bat[:oid,:dbl],rmt3879_X_91_bat_o id_dbl:bat[:oid,:dbl],rmt3880_X_92_bat_oid_dbl:bat[:oid,:dbl],rmt3881_X_93_bat_oid_dbl:bat[:oid,:dbl],rmt3882_X_94_ba t_oid_dbl:bat[:oid,:dbl],rmt3883_X_95_bat_oid_dbl:bat[:oid,:dbl],rmt3884_X_97_bat_oid_dbl:bat[:oid,:dbl],rmt3885_X_98_ bat_oid_dbl:bat[:oid,:dbl],rmt3886_X_99_bat_oid_dbl:bat[:oid,:dbl],rmt3887_X_100_bat_oid_dbl:bat[:oid,:dbl],rmt3888_X_ 101_bat_oid_dbl:bat[:oid,:dbl],rmt3889_X_103_bat_oid_dbl:bat[:oid,:dbl],rmt3890_X_104_bat_oid_dbl:bat[:oid,:dbl],rmt38 91_X_105_bat_oid_dbl:bat[:oid,:dbl],rmt3892_X_106_bat_oid_dbl:bat[:oid,:dbl],rmt3893_X_107_bat_oid_dbl:bat[:oid,:dbl],r mt3894_X_108_bat_oid_dbl:bat[:oid,:dbl],rmt3895_X_109_bat_oid_dbl:bat[:oid,:dbl],rmt3896_X_110_bat_oid_dbl:bat[:oid,: dbl],rmt3897_X_111_bat_oid_dbl:bat[:oid,:dbl],rmt3898_X_112_bat_oid_dbl:bat[:oid,:dbl],rmt3899_X_113_bat_oid_dbl:bat[: oid,:dbl],rmt3900_X_114_bat_oid_dbl:bat[:oid,:dbl],rmt3901_X_115_bat_oid_dbl:bat[:oid,:dbl],rmt3902_X_116_bat_oid_dbl: bat[:oid,:dbl],rmt3903_X_117_bat_oid_dbl:bat[:oid,:dbl],rmt3904_X_118_bat_oid_dbl:bat[:oid,:dbl],rmt3905_X_119_bat_oid _dbl:bat[:oid,:dbl],rmt3906_X_120_bat_oid_dbl:bat[:oid,:dbl],rmt3907_X_121_bat_oid_dbl:bat[:oid,:dbl],rmt3908_X_122_ba t_oid_dbl:bat[:oid,:dbl],rmt3909_X_123_bat_oid_dbl:bat[:oid,:dbl],rmt3910_X_124_bat_oid_dbl:bat[:oid,:dbl],rmt3911_X_12 5_bat_oid_dbl:bat[:oid,:dbl],rmt3912_X_126_bat_oid_bit:bat[:oid,:bit],rmt3913_X_128_bat_oid_bit:bat[:oid,:bit],rmt3914_X_ 130_bat_oid_oid:bat[:oid,:oid]) := user.l2(); Server version on Mac and Linux machines: MonetDB 5 server v11.21.5 "Jul2015" (64-bit, 64-bit oids, 128-bit integers) Thanks. Kind regards, Bart

1 0