July 2013 - users-list

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

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

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

29 Jul '13

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 .

3 2

Deleting millions of rows every night
by Dennis Pallett 29 Jul '13

29 Jul '13

Hello all, We are going to be using MonetDB as a data mart between our data warehouse and our front-end applications and are planning to insert and delete several millions of rows from MonetDB every night as part of our data update cycle. We've noticed that MonetDB doesn't support the TRUNCATE statement so we're deleting all the data with DELETE FROM table. But is this the best way to delete millions of rows every night? Won't performance or free disk space degrade over time? Best regards, Dennis Pallett

2 1

MonetDB and SQLWorkbench
by Satoshi Nagayasu 29 Jul '13

29 Jul '13

Hi, I have hacked SQLWorkbench to work with MonetDB. SQLWorkbench is a detabase-independent GUI query tool, written in Java. http://www.sql-workbench.net/ To support MonetDB in SQLWorkbench, a new entry needs to be added in DatabaseTemplates.xml, which is contained in sqlworkbench.jar. This modifed files are available: http://www.uptime.jp/downloads/monetdb/SQLWorkbench/ It seems working well so far, and this patch is supposed to be contributed to the SQLWorkbench upstream. If you are looking for a nice GUI tool to issue queries to MonetDB, take a look. Regards, -- Satoshi Nagayasu <snaga(a)uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp

3 3

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

28 Jul '13

Hi Ying, As you mentioned the 2nd case, I just interchanged create 21 files and excute COPY INTO. The plot times include only the execution time of COPY INTO. In 15 seconds, the data volumn i have to process is just similar to storing 200000 rows,21 columns in this simulation program. Thanks! Meng ------------------ Original ------------------ From: "Ying Zhang"<Y.Zhang(a)cwi.nl>; Date: Sun, Jul 28, 2013 00:47 AM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hai Meng, Since you repeat the COPY INTO statement 10000 times, I wonder how exactly did you it and measure the time. For instance, did you first create _all_ necessary files (i.e., 10000 x 21 files) and execute 10000 COPY INTO consecutively? Or did you interchange create 21 files and execute COPY INTO? In the second case, are the times in your plots only the execution time of COPY INTO, or do they also include the time to create the files? Again, in the second case, have you also measured the time of creating the files? You have ever mentioned that the total available execution time in your application is 15 seconds. How many data do you need to process within this time? Regards, Jennie On Jul 26, 2013, at 10:40, integrity wrote: > Hi dear all, > > I did a test of insert 2,000,000,000 rows into MonetDB with "COPY BINARY INTO FROM binary_file", > in this test > 1. i generate 21 files, each file represents a table column and has 200000 rows. > I created them with rand() number and use fwrite() binary write method. > > the table creation sql command is : > create table tmatch(id bigint,a float,b float,c float, d float, e float, > f float,g float,h float, i float,j float, > k float,l float,m float, n float,o float, > p float,q float,r float, s float,t float); > the table has 21 columns,each column has 8 bytes, so each column file is c1=200000*21*8 Byte= 268800000 Byte=3.2MB > 2. I use "COPY BINARY INTO FROM above_binary" to load each binary file into tmatch. > The test was run 10000 times repeatedly. > > the average time of 10000 times is only 1.0635589558955727, > but when at 9043th time, it cost 227m36.136 ,some times later the time value increase to a large number, is it because of flush data from cache into database after the cache is full? > The problem is since we have to control the total process time within 15 seconds , I am wondering if you can help me reduce the maximum time to a lower point? > > > Thanks very much! > Meng > > _______________________________________________ > 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 .

2 1

Monetdb copy binary time varys very much!
by integrity 27 Jul '13

27 Jul '13

Hi dear all, I did a test of insert 2,000,000,000 rows into MonetDB with "COPY BINARY INTO FROM binary_file", in this test 1. i generate 21 files, each file represents a table column and has 200000 rows. I created them with rand() number and use fwrite() binary write method. the table creation sql command is : create table tmatch(id bigint,a float,b float,c float, d float, e float, f float,g float,h float, i float,j float, k float,l float,m float, n float,o float, p float,q float,r float, s float,t float); the table has 21 columns,each column has 8 bytes, so each column file is c1=200000*21*8 Byte= 268800000 Byte=3.2MB 2. I use "COPY BINARY INTO FROM above_binary" to load each binary file into tmatch. The test was run 10000 times repeatedly. the average time of 10000 times is only 1.0635589558955727, but when at 9043th time, it cost 227m36.136 ,some times later the time value increase to a large number, is it because of flush data from cache into database after the cache is full? The problem is since we have to control the total process time within 15 seconds , I am wondering if you can help me reduce the maximum time to a lower point? Thanks very much! Meng

4 3