July 2013 - users-list

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

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