- users-list - monetdb.org

Re: How to use Data Vault with FITS files?
by integrity 05 Aug '13

05 Aug '13

Thanks very much Stefan! Does ' configure MonetDB with "--enable-fits" ' mean I have to re-install MonetDB? If I re-install it, can i still use my current db-farm? I have loaded a large catalog into it for a long time... Regards, Meng ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Mon, Aug 5, 2013 04:13 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: How to use Data Vault with FITS files? Hi Meng, to get the MonetDB FITS support, you need to have the cfitsio-dev[el] package installed and you need to configure MonetDB with "--enable-fits". However, while the respective code is more than 2 years old, I just realize that there is no (user-)documentation for it, and so far, support for fits files is only available in MAL, but not (yet?) in SQL. Since the original author of that code is no longer with out group, I currently cannot estimate, when we will have the resources to make this experimental code user-/production-ready. If you are familiar with MAL, please have a look at $ mclient -lmal Welcome to mclient, the MonetDB interactive terminal (unreleased) Type \q to quit, \? for a list of available commands mal>?fits.*() pattern fits.attach(fname:str):void address FITSattach; comment Open a FITS file and return catalog of the table HDUs pattern fits.export(tablename:str):void address FITSexportTable; comment Export a table to a FITS file command fits.fitstest(filename:str):int address FITStest; comment Returns the type of first extension in the FITS file filename pattern fits.load(tablename:str):void address FITSloadTable; comment Load a FITS table from an attached file pattern fits.listdirpattern(dirname:str,pattern:str):void address FITSdirpat; comment Attach all FITS file in the directory, giving a pattern pattern fits.listdir(dirname:str):void address FITSdir; comment Attach all FITS files in the directory mal> And if you're familiar with the MonetDB code base, you find the respective code in sql/backends/monet5/vaults/*fits* Best, Stefan ----- Original Message ----- > Hi all, > > > Could you give a tutorial of Data vault with FITS files, i searched the > moentdb.org and google, but didn't find the step-by-step tutorials. > > > Thanks! > Meng > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

How to use Data Vault with FITS files?
by integrity 05 Aug '13

05 Aug '13

Hi all, Could you give a tutorial of Data vault with FITS files, i searched the moentdb.org and google, but didn't find the step-by-step tutorials. Thanks! Meng

4 3

How to use Data Vault with FITS files?
by integrity 04 Aug '13

04 Aug '13

Hi all, Could you give a tutorial of Data vault with FITS files, i searched the moentdb.org and google, but didn't find the step-by-step tutorials. Thanks! Meng

1 0

Scale Factor 2000 TPCH Database?
by Stephen P. Morgan 02 Aug '13

02 Aug '13

Has anyone successfully created a scale factor 2000 TPCH database with MonetDB? We're having some problems trying to do so. We don't have enough disk space to hold both the data (created by dbgen) and the MonetDB database, so we have to create the database in two steps. (Create the first half of the data as dbgen table files, load it, delete the dbgen files, create the second half of the data as dbgen table files, load it, then delete those files.) The problem shows up when loading the second half of the data: the mserver crashes and won't restart. This is what the dbfarm/merovingian.log says: 2013-08-02 18:42:19 MSG merovingian[39857]: database 'q1_s2000_n2' (39920) has crashed (dumped core) 2013-08-02 18:42:19 ERR merovingian[39857]: client error: database 'q1_s2000_n2' has crashed after starting, manual intervention needed, check monetdbd's logfile for details 2013-08-02 18:42:19 MSG merovingian[39857]: database 'q1_s2000_n2' has crashed after start on 2013-08-02 18:42:19, attempting restart, up min/avg/max: 0s/0s/0s, crash average: 1.00 0.50 0.17 (5-0=5) Other databases on the same system are still running. We're running Feb13 SP1 with minor local modifications that are not coming into play at this point. We're running on a large Linux server. Is there anything we can look at to help diagnose the problem? Thanks, Steve Morgan

3 3

Copy From SQL server
by soumik ghosh 02 Aug '13

02 Aug '13

Hello All, Could someone point me to an example of copying a table from SQL Server to MonetDB over a JDBC or ODBC bridge? Is this possible at all? Thanks Soumik

5 7

Background processes in MonetDB
by DrRock 01 Aug '13

01 Aug '13

Dear fellows, we installed MonetDB a few weeks ago on a Linux Machine (8 cores, 32GB RAM) and our overall experience is very good. So far, we are testing Monet with different ammounts of data, roughly peaking at 250GB. After loading plenty of data into the database or after interrupting a query, Monet feels somehow slow for a certain period of time. Another observation we made is that diskspace remains occupied after the deletion of all tables in the database for a certain time. Further, indices can be created, although this seems not to be really essential as MonetDB provides some kind of own intelligence, optimizing queries/ tables by themself. All of mentioned observations point at some kind of background processes and optimizing routines. Is there a way to modify the behaviour of MonetDB (Such as providing diskspace instantly after deletion of tables)? Is it possible to switch these processes off? Best regards, DrRock

1 0

user defined directory
by Adnan Agbaria 31 Jul '13

31 Jul '13

I'd like to add a new directory named CAP at MonetDBRoot/sql/backends/monet5 The new directory contains cap.c, cap.h, cap.mal, 80_cap.mal, 80_cap.sql, and makefile.ag I modified MonetDBRoot/sql/backends/monet5/Makefile.ag at: SUBDIRS = NOT_WIN32?vaults UDF CAP LSST ENABLE_DATACELL?datacell However, when I run MonetDBRoot/bootstrap, i got the following error message: ... /home/adnan/MonetDB-11.15.11/sql/backends/monet5 /home/adnan/MonetDB-11.15.11/sql/backends/monet5/vaults /home/adnan/MonetDB-11.15.11/sql/backends/monet5/UDF /home/adnan/MonetDB-11.15.11/sql/backends/monet5/CAP Traceback (most recent call last): File "buildtools/autogen/autogen.py", line 157, in <module> (InstallList, DocList, OutList) = main(topdir, topdir, automake, []) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 132, in main read_makefile(p, cwd) File "buildtools/autogen/autogen.py", line 96, in read_makefile p.parse(token, lineno, line) File "buildtools/autogen/autogen.py", line 71, in parse v = self.stack[last] IndexError: list index out of range Any idea please?

3 2

Re: Monetdb copy binary time varys very much!
by integrity 30 Jul '13

30 Jul '13

Hi Stefan, Just as you said ,we issue the next copy into as soon as the previous ended. What would be different if i mimic the real-world scenario and respect these gaps between each two consecutive copy into's? Thank! Meng ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Mon, Jul 29, 2013 02:45 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi Meng, My analysis was mainly an (educated) guess of what (most probably) happens. To be sure, you need to profile your system in detail, e.g., monitor CPU and I/O activities. Having said that, with less RAM, you might force the system to write the loaded data to disk instantly with each copy into, making each copy into slower than merely loading the data into disk, but the worst case might become better since each time less data has to we written than with more RAM. Again, this is my guess what happening; the behaviour you observe might be caused by something else; only a detailed profiling analysis can tell. Also, if you eventually want to query your 300+ GB (or even more?) efficiently, you might want to have a suitable system, in particular sufficient RAM. (Would you mind sharing the hardware characteristics of your machine?). Moreover, what was the time gap between two consecutive copy into's in your experiment, i.e., did you issue the next copy into as soon as the previous ended? Does this mimic the your "real-world" scenario realistically? Or would there be a time gap between to copy into's in reality? I recall you mentioned some 15 seconds? If so, you should rerun your experiment respecting these gaps between each two consecutive copy into's. Best, Stefan ----- Original Message ----- > Hi Stefan, > > > Due to all the data in RAM having to be written to disk when it's full, if > the RAM grows larger and i don't change hard disk, the data volume become > larger, will it make the COPY BINARY INTO much slower compared to the small > RAM? > > > Contrarily, if the RAM get smaller, will it make the COPY BINARY INTO > quicker? > > > I just want to lower the peak value of COPY, the 10+ seconds every about a > hundred times. > > > For the most time COPY BINARY INTO took only less than 1 second, is it > because they are written to RAM not disk when the RAM is not full? if so, > can i write to disk more frequently before the disk is full so i could lower > down the peak value of COPY. > > > > > Regrads, > Meng > > > ------------------ Original ------------------ > From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; > Date: Fri, Jul 26, 2013 11:10 PM > To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; > > Subject: Re: Monetdb copy binary time varys very much! > > > > Hi, > > I'm not sure whether I understand correct what you are doing. > > If you repeat the test 1000 times, does that mean that (1) 10000 times you > re-create (or empty) the table and thus always copy into an empty table, or > (2) 10000 times you copy into the same (growing) table, i.e., resulting in a > table of 10,000 times 200,000 rows, i.e., 2,000,000,000 rows, i.e., ~16 GB > per column, i.e., ~336 GB in total? > > (Only) in case (1) the binary files to be imported are simply moved at zero > costs. > In case (2), only the first copy into (into the empty table) can simply move > the files at zero costs; all subsequent copy into (into a no longe empty > table) must copy the files (and delete them afterwards to mimic the same > behavior as the initial copy into), which is of cause not "for free". > > Also, as Martin explained, unless your machine has (significantly) more RAM > than the ~336 GB of data you copy, the data needs to be written to disk in > between, making some copy into's "slower" than others. There's not much to > do about that other than (a) getting more RAM, or (b) improving I/O > bandwidth by using either a high performance RAID or SSDs. > > Stefan > > . > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

SQL: AVG function works in SQL but it does not in procedure
by Radovan Bičiště 29 Jul '13

29 Jul '13

Hello, We are unable to solve the following problem. This SQL runs fine as standalone: INSERT INTO crmkartastitek ( cislo_karty, stitek_id, stitek_nazev, stitek_upresneni, stitek_hodnota_decimal ) select cislo_karty,170,'KEO','',round(avg(obrat),2) from (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat from crmkartapohyby ckp join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty where ckp.pohyb in ('VR', 'VV') and ck.aktivita between 1 and 3 -- omezení na LM a L3M group by ckp.cislo_karty, ckp.id_expedice ) as exp_pripady group by cislo_karty; But when wrapped in procedure raises an error as follows: CREATE PROCEDURE crm_segm_keo_rl(p_interval INT, p_datum timestamp) BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crm_segm_kpn_rl'; -- nastav id pravidla DECLARE v_stitek_id int; DECLARE v_stitek_nazev,v_stitek_upresneni VARCHAR(200); -- nastav štítek SET v_stitek_id = 170; SELECT nazev,upresneni INTO v_stitek_nazev,v_stitek_upresneni FROM crmregstitek WHERE stitek_id = v_stitek_id; -- proveď výpočet pouze pokud spouštím měsíční přepočet IF p_interval = crm_segm_vrat_spusteni(v_stitek_id) THEN INSERT INTO crmkartastitek ( cislo_karty, stitek_id, stitek_nazev, stitek_upresneni, stitek_hodnota_decimal ) select cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni,round(avg(obrat),2) from (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat from crmkartapohyby ckp join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty where ckp.pohyb in ('VR', 'VV') and ck.aktivita between 1 and 3 -- omezení na LM a L3M group by ckp.cislo_karty, ckp.id_expedice ) as exp_pripady group by cislo_karty; END IF; END; Procedure is created fine. call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00'); Causes: An error occurred when executing the SQL command: call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00') TypeException:user.crm_segm_keo_rl[362]:'aggr.subavg' undefined in: _546:bat[:any,:dbl] := aggr.subavg(_543:bat[:oid,:dbl], _209:bat[:oid,:oid], r1_209:bat[:oid,:oid], _535:bit) [SQL State=22000] Next: TypeException:user.s4_24[5]:'user.crm_segm_keo_rl' undefined in: _9:void := user.crm_segm_keo_rl(_5:int, _7:timestamp) [SQL State=22000] Next: program contains errors [SQL State=39000] Are we missing something ? Thank you, Radovan -- Radovan Biciste Hradec Kralove Czech Republic

1 0

Re: Monetdb copy binary time varys very much!
by integrity 29 Jul '13

29 Jul '13

Yes, I use the server only myself only to run the MonetDB test and notify other users not to use the server in advance. and I reboot the server before tests . ------------------ Original ------------------ From: "Ying Zhang"<Y.Zhang(a)cwi.nl>; Date: Mon, Jul 29, 2013 04:28 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! On Jul 29, 2013, at 05:57, integrity wrote: > Hi Stefan, > > Due to all the data in RAM having to be written to disk when it's full, if the RAM grows larger and i don't change hard disk, the data volume become larger, will it make the COPY BINARY INTO much slower compared to the small RAM? > > Contrarily, if the RAM get smaller, will it make the COPY BINARY INTO quicker? > > I just want to lower the peak value of COPY, the 10+ seconds every about a hundred times. Hai Meng, Have you made sure that there are no processes running other than the ones strictly necessary? Have you closed all user applications, such as mail client, browser, and chat clients? Regards, Jennie > > For the most time COPY BINARY INTO took only less than 1 second, is it because they are written to RAM not disk when the RAM is not full? if so, can i write to disk more frequently before the disk is full so i could lower down the peak value of COPY. > > > Regrads, > Meng > > ------------------ Original ------------------ > From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; > Date: Fri, Jul 26, 2013 11:10 PM > To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; > Subject: Re: Monetdb copy binary time varys very much! > > Hi, > > I'm not sure whether I understand correct what you are doing. > > If you repeat the test 1000 times, does that mean that (1) 10000 times you > re-create (or empty) the table and thus always copy into an empty table, or > (2) 10000 times you copy into the same (growing) table, i.e., resulting in a > table of 10,000 times 200,000 rows, i.e., 2,000,000,000 rows, i.e., ~16 GB > per column, i.e., ~336 GB in total? > > (Only) in case (1) the binary files to be imported are simply moved at zero > costs. > In case (2), only the first copy into (into the empty table) can simply move > the files at zero costs; all subsequent copy into (into a no longe empty > table) must copy the files (and delete them afterwards to mimic the same > behavior as the initial copy into), which is of cause not "for free". > > Also, as Martin explained, unless your machine has (significantly) more RAM > than the ~336 GB of data you copy, the data needs to be written to disk in > between, making some copy into's "slower" than others. There's not much to > do about that other than (a) getting more RAM, or (b) improving I/O > bandwidth by using either a high performance RAID or SSDs. > > Stefan > > . > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0