February 2016 - users-list

Slow Query
by Luciano Sasso 20 Feb '16

20 Feb '16

Hi, I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, 4 of number type, others string type. A simple "select count(*) from table", run on 90 minutes. Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. While monet run the query, command top, show 42G of virtual memory. Its normal ?, any idea ? thanks. -- 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

3 2

Error in connecting monetdb server to localhost
by Debangana Sarkar 17 Feb '16

17 Feb '16

when I am connecting to MonetDB server it is showing an error : FATAL logger error there is no logger catalog but a log file is found. Are you sure you are using the same combination of database --dbpath and log directory Press any key to continue. Client error is: couldnot connect to localhost:50000 unknow error. Previously I installed the monetDB server and client and it was working.But for some reason it was uninstalled.Now after installing this error is coming. Please help

1 0

is it possible for a user to turn assertions on/off in mserver easily?
by Anthony Damico 16 Feb '16

16 Feb '16

might help with future testing..thanks all

3 3

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Thanks, Lefteris. You might be right and the log did suggest some file (.vaultkey) can't be found before it stopped working. I'll move the dbfarm out of /tmp and give it a try. Regards, HQ

1 0

Re: Re: Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi Brian, Thanks for looking into this issue. Here is some more details in meroveringian.log - The DB was up for about a week. There're a lot of lines of 'proxying client ...', until the line 'client error: cannot start database 'EXM': no .vaultkey found...' 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33861 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33862 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:08:13 MSG merovingian[12262]: proxying client localhost:33863 for database 'EXM' to mapi:monetdb:///tmp/exm/EXM/.mapi.sock?database=EXM 2016-02-07 08:08:13 MSG merovingian[12262]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:20 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-07 08:36:20 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 08:36:21 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) And then, a lot of lines like below: 2016-02-07 14:44:18 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-07 14:44:23 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) Eventually, there're lot of errors like this: 2016-02-08 01:13:58 MSG merovingian[12262]: starting database 'EXM', up min/avg/max: 0s/0s/0s, crash average: 1.00 0.10 0.03 (1-0=1) 2016-02-08 01:14:03 ERR merovingian[12262]: client error: cannot start database 'EXM': no .vaultkey found (did you create the database with `monetdb create EXM`?) 2016-02-08 01:14:11 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:18 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file s 2016-02-08 01:14:26 ERR merovingian[12262]: client error: could not retrieve uplog information: could not open file /tmp/exm/EXM/.uplog: Too many open file S Then neither mclient nor monetdbd stop cmd works any more. This is what I have in /tmp/exm folder after reboot: [centos@ip-10-72-0-98 exm]$ pwd /tmp/exm [centos@ip-10-72-0-98 exm]$ ls -al total 8088 drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 . drwxrwxrwt. 11 root root 4096 Feb 11 13:36 .. drwx------. 4 centos centos 77 Feb 9 17:33 EXM -rw-------. 1 centos centos 0 Feb 9 17:33 .merovingian_lock -rw-------. 1 centos centos 8272151 Feb 9 17:33 merovingian.log [centos@ip-10-72-0-98 exm]$ ls -al EXM total 8 drwx------. 4 centos centos 77 Feb 9 17:33 . drwxr-xr-x. 3 centos centos 62 Feb 9 17:33 .. drwx------. 66 centos centos 4096 Feb 2 19:48 bat -rw-------. 1 centos centos 0 Feb 7 08:36 .gdk_lock srwx------. 1 centos centos 0 Jan 27 14:50 .mapi.sock drwx------. 3 centos centos 16 Jan 27 14:50 sql_logs -rw-------. 1 centos centos 22 Feb 9 17:33 .uplog If needed, I can send you the whole merovingian.log file (which is 8GB in size). Thanks, HQ

1 0

copy into stdout and redirect to a file
by Anton Kravchenko 11 Feb '16

11 Feb '16

Hi there, I wonder if one can copy a query result into stdout and redirect it to a file? I've tried: mclient.bat -u monetdb -d somedb -s "copy select * from sometable into stdout" - > .\somefile.csv But, only the table header is saved in somefile.csv... P.S. 'select * from sometable' shows all data rows as expected. I am using MonetDB v11.21.11 (Jul2015-SP1) on Windows. Thank you, Anton

2 2

Some time monetdbd can't start existing dbfarm
by Haiqing Ying 11 Feb '16

11 Feb '16

Hi, I created a dbfarm (/tmp/exm ), then created database (EXM), load data, do queries, etc, everything works fine for a week. Then I found mclient stopped working (can't connect to monetdb). The log file shows 'too many files opened' issue. I guess it might be some connection leak issue. I tried 'monetdbd stop /tmp/exm', but it's not responding. It looks I can't stop the dbfarm now. I then reboot the system and after reboot tried 'monetdbd start /tmp/exm' but I got an error saying ' cannot find file in /tmp/exm'. I verified the /tmp/exm folder did exist and there're still sub folder ( ./EXM) and log file still under /tmp/exm. The only option left seems to be creating a new dbfarm and recreating the whole database again. We're moving to use MonetDB in production and if this is a real bug then it would be too risky for production. The question is, is this a real issue? Or it's just some user error that I made? How do I make sure we won't run into this situation again in production mode. Many thanks, HQ

3 2

sql query failure question
by Lynn Carol Johnson 09 Feb '16

09 Feb '16

Hi all - I’m going nuts trying to determine why a monetdb SQL query does not work. I am trying to pull data from 2 tables. These tables are in the DB as shown here: sql>\d TABLE maizefullgenome.fullgenomegff TABLE maizefullgenome.fullgenomestable TABLE maizefullgenome.gene_from_gff TABLE maizefullgenome.ranges_from_gff sql> If I join only on the “pos” field, the query executes. Here is that query: > delChrPosGene_fullGenome <-dbGetQuery(conn, "SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") If I add the “chr” field, it fails with the message below. Can anyone tell me what is wrong with this second query? > delChrPosGene_fullGenome <-dbGetQuery(conn, "SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene_from_gff gt on (fg.chr = gt.chr AND fg.pos >= gt.start_pos AND fg.pos <= gt.end_pos) where gerp_score> 2 AND gerp_neutral_tree_length > 2 AND ((sift_score_a < 0.05 AND sift_score_a >= -1) OR (sift_score_c < 0.05 AND sift_score_c >=-1) OR (sift_score_g < 0.05 AND sift_score_g >=-1) OR (sift_score_t < 0.05 AND sift_score_t >=-1))") Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT fg.chr,fg.pos,gt.gene_name FROM maizeFullGenome.fullgenomestable fg join maizeFullGenome.gene...'. Server says '!Object not found'. Here are the fields in the gene_from_gff table: sql>\d maizefullgenome.gene_from_gff CREATE TABLE "maizefullgenome"."gene_from_gff" ( "chr" INTEGER, "start_pos" INTEGER, "end_pos" INTEGER, "gene_name" CHARACTER LARGE OBJECT ); sql> Here are the fields in the fullgenomestable table: sql>\d maizefullgenome.fullgenomestable CREATE TABLE "maizefullgenome"."fullgenomestable" ( "chr" INTEGER, "pos" INTEGER, "ref_allele" TINYINT, "two_bp_upstream" INTEGER, "two_bp_downstream" INTEGER, "three_bp_upstream" INTEGER, "three_bp_center" INTEGER, "three_bp_downstream" INTEGER, "four_bp_upstream" INTEGER, "four_bp_downstream" INTEGER, "five_bp_upstream" INTEGER, "five_bp_center" INTEGER, "five_bp_downstream" INTEGER, "gc_content" TINYINT, "gerp_neutral_tree_length" REAL, "gerp_score" REAL, "gerp_conserved" BOOLEAN, "cmethylation" REAL, "cpg_methylation_1bk_moving_average" REAL, "chg_methylation_1bk_moving_average" REAL, "chh_methylation_1bk_moving_average" REAL, "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, "allele_total_depth" INTEGER, "site_presence" INTEGER, "conservedgene_presence" BOOLEAN, "minor_allele" INTEGER, "maf" REAL, "combined_site_uniqueness_score" REAL, "site_uniqueness_score_sd" REAL, "expected_invariant_site" BOOLEAN, "codon_position" TINYINT, "within_cds" BOOLEAN, "within_gene" BOOLEAN, "within_transcript" BOOLEAN, "within_exon" BOOLEAN, "within_five_prime_utr" BOOLEAN, "within_three_prime_utr" BOOLEAN, "within_mirna" BOOLEAN, "line_family" INTEGER, "sine_family" INTEGER, "ltr_family" INTEGER, "helitron" BOOLEAN, "carot_common_support_interval" TINYINT, "toco_common_support_interval" TINYINT, "orig_amino_acid_a" TINYINT, "new_amino_acid_a" TINYINT, "amino_acid_substitute_pos_a" INTEGER, "sift_score_a" REAL, "sift_median_seq_info_a" REAL, "number_seqs_at_pos_a" INTEGER, "orig_amino_acid_c" TINYINT, "new_amino_acid_c" TINYINT, "amino_acid_substitute_pos_c" INTEGER, "sift_score_c" REAL, "sift_median_seq_info_c" REAL, "number_seqs_at_pos_c" INTEGER, "orig_amino_acid_g" TINYINT, "new_amino_acid_g" TINYINT, "amino_acid_substitute_pos_g" INTEGER, "sift_score_g" REAL, "sift_median_seq_info_g" REAL, "number_seqs_at_pos_g" INTEGER, "orig_amino_acid_t" TINYINT, "new_amino_acid_t" TINYINT, "amino_acid_substitute_pos_t" INTEGER, "sift_score_t" REAL, "sift_median_seq_info_t" REAL, "number_seqs_at_pos_t" INTEGER, "b73_uniqueness_score" REAL, "cml247_uniqueness_score" REAL, "ph207_uniqueness_score" REAL, "w22_uniqueness_score" REAL, "coelorachis_anc" TINYINT, "vossia_anc" TINYINT, "sorghum_anc" TINYINT, "oryza_anc" TINYINT, "setaria_anc" TINYINT, "brachypodium_anc" TINYINT, "hordeum_anc" TINYINT, "musa_anc" TINYINT, "populus_anc" TINYINT, "vitis_anc" TINYINT, "arabidopsis_anc" TINYINT, "panicum_anc" TINYINT ); sql> Any hints are greatly appreciated! Thanks - Lynn

3 6

WAL read files
by Pierre-Adrien Coustillas 08 Feb '16

08 Feb '16

Hi, is it possible to read WAL files ? sql/ log.<transaction id> why ? We still have a problem of duplicate table . All our monetsdb are updated with the latest version of the repository debian8 , and that several server. We have learned to remove duplicate tables and a stable production. But I want to understand this bug Duplication of tables are always created after a restart monetdb After reading this page https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme, and particularly this ""By default, persisted transactions are cleaned up from the WAL. During database startup, all transaction still left in the WAL are read and any that are not yet applied are written into the persistent BAT storage"" . I think that the drop or create commands are not cleaned up from the WAL We used the library php _ map.inci and we run a lot of command drop / create ( ... I know this is not recommended ... ) We used autocommit. If I can read and understand what is in the WAL file, can help me understand this bug Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35 // 09 54 38 70 90

3 6

Multi-core join performance
by Koliousis, Alexandros 01 Feb '16

01 Feb '16

Hi, Thank you for your reply. The system has 16 physical cores (2 sockets, 8 cores/sockets, 20MB L3 cache, 256KB L2) and 64GB of RAM. The MonetDB server uses 16 threads to serve my test database and it finds ~63GB of available memory. I used synthetic data to populate the two tables (with names A and B). The particular query I reported about in my previous e-mail generates a lot of output: approx. 500 million tuples. The join predicate is (A.a1 < B.b1) where columns A.a1 and B.b1 contain all unique integers from 1 to 32768. With a lower selectivity (say, 1%, by controlling the values inserted into A.a1 and B.b1), the query executes faster but its behaviour is the same: only 1 thread is utilised. Attached is a trace of the query. Hope this helps, Alexandros PS: Apologies for the separate thread; somehow I ended up subscribing in digest mode > Hi > > This is insufficient information to shed light on it. > Crucial information is system characteristics. > And also a TRACE of your query can shed light on the issue. > > Furthermore, is it a cold or hot execution of the query? > > regards, Martin > > On 20/01/16 13:24, Alexandros Koliousis wrote: > > Hi, > > > > I am joining two tables, each with 32K tuples. The query runs for > ~23-24 seconds on my (multi-core) machine. > > > > During execution, however, only 1 core is utilised, while the rest > of the CPU cores are idle. The cores are under-utilised although I > have configured the MonetDB server to use all the physical cores of my > machine (either by setting 'nthreads' or, in the > > case of mserver5, 'gdk_nr_threads'). > > > > I am running MonetDB server v11.21.13 "Jul2015-SP2", compiled from > source. > > > > Is this due to some configuration setting I missed? Or, due to the > fact that I am running a single-operator query? > > > > Alternatively, I was thinking of creating merge tables, thus > partitioning my tables manually, hoping that this will allow for > intra-operator parallelism. > > > > Thanks, > > > > Alexandros > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list at monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > >

3 2