July 2013 - users-list

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

26 Jul '13

you mean change hardware?Is there any software resolution? ------------------ Original ------------------ From: "Martin Kersten"<martin(a)monetdb.org>; Date: Fri, Jul 26, 2013 10:52 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! see http://en.wikipedia.org/wiki/Solid-state_drive On 7/26/13 4:48 PM, Angelasweet wrote: Yes, time is the biggest issue to our project, I cannot find SSD, could you please explain the SSD concept? Thanks! _______________________________________________ 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 26 Jul '13

26 Jul '13

Is it possible to set the cache to low down the maximum copy time? ------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 10:13 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Oops, correct, for binary copies it does not recognize it On 7/26/13 4:11 PM, Angelasweet wrote: > Hi Martin, > I follow you advise, and there is error as follows: > > ERROR = !COPY INTO: cannot pass number of records when using binary COPY > INTO in: "copy 200000 records binary into tmatch > from('/data/writeBinaryWM/id','/data/writeBinaryWM/0','/data/writeBinaryWM/1','/data/writeBinaryWM/2','/data/writeBinaryWM/3','/data/writeBinaryWM/4', > '/data/writeBinaryWM/5','/data/writeBinaryWM/6','/data/writeBinaryWM/7','/data/writeBinaryWM/8','/data/writeBinaryWM/9','/data/writeBinaryWM/10', '/data/writeBinaryWM/11', '/data/writeBinaryWM/12', '/data/writeBinaryWM/13', '/data/writeBinaryWM/14', '/data/writeBinaryWM/15', '/data/writeBinaryWM/16', '/data/writeBinaryWM/17', '/data/writeBinaryWM/18', '/data/writeBinaryWM/19' )" > !current transaction is aborted (please ROLLBACK) > > > 2013/7/26 Martin Kersten <Martin.Kersten(a)cwi.nl > <mailto:Martin.Kersten@cwi.nl>> > > This runs shows expected flushing. > > You might get somewhat better performance when you follow > the advice in > http://www.monetdb.org/__Documentation/Cookbooks/__SQLrecipes/LoadingBulkDa… > <http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData> > e.g. use COPY 44000000 RECORDS .... > > The COPY INTO forms mentioned so far share one important drawback. > When a lot of data is to be inserted, the server doesn't know how > much memory to allocate for the tables, and so will likely allocate > too little. This means that during the insertion process, the server > has to grow the allocated memory area. This can be an expensive > operation. Therefore, it is better to give the server a count of > how many records are to be inserted: > > COPY n RECORDS INTO table FROM 'file'; > > Here n should be a number that is at least as large as the actual > number of records to be inserted. If the file contains more than n > records, only n will be inserted, if the file contains fewer, all > values will be inserted. Giving a higher number is especially > useful if multiple COPY INTO queries are to be done on the same > table. The first COPY INTO, when the table is still empty, should > be the total count of to-be-inserted values so that the server will > allocate enough memory when the tables are first created (they are > only really created once data is inserted). > On 7/26/13 10:58 AM, integrity wrote: > > > > ------------------ Original ------------------ > *From: * "Martin Kersten"<Martin.Kersten(a)cwi.nl > <mailto:Martin.Kersten@cwi.nl>__>; > *Date: * Fri, Jul 26, 2013 04:46 PM > *To: * "users-list"<users-list(a)__monetdb.org > <mailto:users-list@monetdb.org>>; > *Subject: * Re: Monetdb copy binary time varys very much! > > > Hi, > You should plot the times of each of your runs to see the trend. > This single run may be an outlier, which could come from anything > in your system environment. Even a seemingly harmless concurrent > program using significant memory could compete with MonetDB. > > And indeed, at some point you will see disk IO. > > > regards, Martin > > > > > _________________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > http://mail.monetdb.org/__mailman/listinfo/users-list > <http://mail.monetdb.org/mailman/listinfo/users-list> > > _________________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > http://mail.monetdb.org/__mailman/listinfo/users-list > <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 > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

3 3

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

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin

3 3

remote.exec()
by Stephen P. Morgan 26 Jul '13

26 Jul '13

I have a question about remote.exec(). Suppose multiple (say N) remote machines call remote.exec() to start a function on a given local machine. What happens if N is greater than the number of threads supported by the local machine? Are the remote.execs queued until there is a thread available to execute them, or are they simply not started? If the letter, how can one tell whether a remote.exec was run? I have another question abut remote.get() transferring a BAT from one machine to another. Is there a limit on the size of a BAT that can be transferred by a single call to remote.get()? If the BAT is very large, is it paged through disk, or is it alsways transferred directly from memory to memory? Thanks, Steve Morgan

4 5

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

26 Jul '13

1 0

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

26 Jul '13

1 0

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

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin

1 0

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

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin On 7/26/13 10:40 AM, 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 .

1 0

ALTER in procedure
by Radovan Bičiště 23 Jul '13

23 Jul '13

Hello, This is my first post here. I tried to search the archive but did not find anything related. We build application and would like disable foreign keys in a procedure. Something like: CREATE PROCEDURE crmkartaaktivita_drop_cons() BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crmkartaaktivita_drop_cons'; -- drop ALTER TABLE crmkartastitek DROP CONSTRAINT crmkartastitek_crmkartaaktivita_FK; ALTER TABLE crmkartastitekm DROP CONSTRAINT crmkartastitekm_crmkartaakitvita_FK; END; / I'm getting "Statement 'ALTER_TABLE' is not a valid flow control statement". Is there any way around please? Our algorithm should like this: 1. disable/drop foreign keys. 2. load data. 3. enable/create constraints. Thanks, Radovan -- Radovan Biciste Czech Republic

2 7

If MonetDB could use table partition ?
by integrity 22 Jul '13

22 Jul '13

I am using "COPY BINARY INTO ...FROM ..." to insert 1000 FITS files into a table and the table has 21 columns, every FITS file has 200000 records. After some FITS files, the loading becoming very slowly, so If MonetDB could use table partition to accelerating the copy? or if Data Vaults could be loading more quickly? Thanks! Meng

2 2