Re: Big Slowdown (15-20X ) on import functions in Monetdb October 2014 Release

26 Nov 2014

      Hi Dimitar,

Sure I will have it tested in this manner and share results with you 
tomorrow.

Best Regards,
Ashish

-----Original Message-----
From: Dimitar Nedev 
Date: Wednesday, 26 November 2014 8:49 pm
To: Ashish Singh 
Cc: "users-list@monetdb.org" 
Subject: Re: Big Slowdown (15-20X ) on import functions in Monetdb October 
2014 Release
...
Hi Ashish,
Thank you for the feedback, we certainly appreciate it.
I can indeed confirm that using MonetDB Feb2014-SP3 loading the 
individual files also took about 4 minutes. We also re-did the tests on  
SSD storage*. The new result for loading multiple files with Oct2014 are 
not much slower than with Feb2014-SP3. This time I measured only a few 
seconds difference. In other words, it does not explain the significant 
slowdown you observed after upgrading to Oct2014.
In any case, if you have a spare system, try MonetDB Oct2014 with the 
manual transaction management, as suggested on the Bulk Data Loading 
page. This should significantly mitigate the issues you are experiencing.
Best regards,
Dimitar
* The fist test was done on network mounter HDD storage.
...
On 2014-Nov-26, at 14:53 , Ashish Kumar Singh 
 wrote:
Thanks For Looking in to this!
As of now we are using MonetDB 5 server v11.17.17 "Jan2014-SP2” build,  
( 
after rolling back from October 2014 feature build).
With same client code from our side both versions have huge difference 
in 
loading performance, we saw that from 30 minutes it was taking 12 hours 
to 
upload same files to newer monetdb.
We were using local storage in these tests with SSD drives so I will 
not 
expect it to take 12 hours in uploading the files.
We also used some of these test results for combining files and then 
loading them in to database to keep memory footprint under tab :) with 
older version of monetdb(Jan2014-SP2) as it used to crash when memory 
spikes, on crash/segfaults experience was good with October release.
Hopefully some patch on October build will resolve this problem and we 
will be able to take benefit of it stability!
Thanks again for looking in to this and I will be happy to provide any 
additional help / information which can enable your team to make 
product 
better!!
Regards,
Ashish
-----Original Message-----
From: Dimitar Nedev 
Date: Wednesday, 26 November 2014 7:08 pm
To: Ashish Singh 
Cc: "users-list@monetdb.org" 
Subject: Re: Big Slowdown (15-20X ) on import functions in Monetdb 
October 
2014 Release
...
Hi Ashish,
We tested your data on a virtualized instance with comparable specs to 
the one you mentioned. I can confirm on the latest MonetDB Oct2014-SP1 
release, loading the individual files takes longer than the single 
large 
one. This is only natural, since mclient runs in auto-commit modeby 
default, meaning at each SQL statement is a separate transaction. As a 
result, for each COPY INTO, and thus for each file loaded, a new 
transaction is created. Transactions are written to disk by the 
transaction logger, before being persisted in primary storage. This is 
of 
course a lot of extra I/O operations, compared to loading the data in 
a 
single SQL statement.
There is a solution. You can turn the auto-commit off, marking the 
beginning of the transaction with "START TRANSACTION". After all files 
are loaded, terminate it with "COMMIT". With bundling the data loading 
in 
a single transaction, I can confirm that the loading time for the set 
of 
individual files was about 4 minutes.
You can fine more info useful info on the Loading Bulk Data page:
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkDa
ta
We have not tested yet if there's significant difference when loading 
multiple small files with older versions of MonetDB. Which version of 
the 
database did you use before? Also, is the storage for your VM mounted 
locally or over the network?
I hope this helps.
Best regards,
Dimitar
...
On 2014-Nov-21, at 18:54 , Ashish Kumar Singh 
 wrote:
Hey guys,
Just double checking with you guys if there are any plans to  see if 
reported behaviour is an issue, and how can i help?
Regards,
Ashish
Sent from my iPad
...
On 18-Nov-2014, at 10:42 pm, Ashish Kumar Singh 
 wrote:
Sure, take your time! We have older release of monetdb.
Whenever you guys plan to have a look do let me know i can help in 
terms providing a test environment, machine etc.. For couple days if 
that helps.
Sent from my iPad
...
On 18-Nov-2014, at 10:27 pm, Stefan Manegold 

wrote:
Hi Ashish,
please bear with us!
We highly appreciate your feedback and will have a closer look at 
the 
reported problem as soon as our time and other obligations allow so.
Please be aware that the members of this mailing-list provide 
free-of-charge voluntary spare-time best-effort services.
For commercial support, please also consider
https://www.monetdbsolutions.com/
Thanks!
Stefan
----- Original Message -----
> Hi all,
> 
> Are you guys able to reproduce this issue? Any help required from 
>my 
> side do
> let me know.
> 
> 
> Regards,
> Ashish
> 
> Sent from my iPad
> 
> On 17-Nov-2014, at 5:24 pm, Ashish Kumar Singh < 
> ashishkumar.singh@altair.com
>> wrote:
> 
> 
> 
> 
> 
> 
> Hi,
> 
> 
> 
> 
> Below are the steps.
> 
> 
> 
> 
> Unzip archive in to /opt/monetdb_test/ directroy to minimise 
>changes 
> in the
> script datbase can live anywhere in the file system.
> 
> Single Big File Import:
> 
> 1. First create a database using Monetdb October2014 build called 
> pbsworks
> 2. Create data base schema using schema provided in file 
>schema.sql.
> 
> Cat all files present inside testdata directory in to on single 
>file 
> by using
> following command:
> 
> cat * > ../merged_data
> 
> Load this merged file in to database using script 
>single_import.sql 
> from
> mclient
> 
> Note down the timing, usually 3-5 minutes on 4 core 17 GB machine 
>on 
> amazon.
> 
> Multiple small File Import:
> 1. First create a database using Monetdb October2014 build called 
> pbsworks
> 2. Create data base schema using schema provided in file 
>schema.sql.
> 3. Load files in to database using multiple_import.sql using 
>mclient
> 
> 
> 
> 
> 
> 
> 
> Regards,
> 
> Ashish
> 
> From: Anthony Damico < ajdamico@gmail.com >
> Reply-To: " users-list@monetdb.org " < users-list@monetdb.org >
> Date: Monday, 17 November 2014 3:51 pm
> To: " users-list@monetdb.org " < users-list@monetdb.org >
> Cc: " developers-list@monetdb.org " < developers-list@monetdb.org >
> Subject: Re: Big Slowdown (15-20X ) on import functions in Monetdb 
> October
> 2014 Release
> 
> 
> 
> 
> please provide a list of every command that you executed to 
> reproduce the
> problem. you need to make the list of commands as short as 
>possible, 
> so
> people trying to help you are not wasting time on irrelevant 
>code.. 
> thanks!!
> 
> On Mon, Nov 17, 2014 at 4:22 AM, Ashish Kumar Singh <
> ashishkumar.singh@altair.com > wrote:
> 
> 
> 
> Thanks for looking in to it,
> 
> I have uploaded required test files to you at below link:
> 
> https://ftin.india.altair.com:8443/message/eJCZlisrascBO00ffEVsQu
> 
> 
> Regards,
> Ashish
> 
> From: Anthony Damico < ajdamico@gmail.com >
> Reply-To: " users-list@monetdb.org " < users-list@monetdb.org >
> Date: Saturday, 15 November 2014 2:40 pm
> To: " users-list@monetdb.org " < users-list@monetdb.org >
> Cc: " developers-list@monetdb.org " < developers-list@monetdb.org >
> 
> Subject: Re: Big Slowdown (15-20X ) on import functions in Monetdb 
> October
> 2014 Release
> 
> 
> 
> 
> could you provide a minimal reproducible example? the less code 
>that
> re-creates the problem, the better. thanks
> 
> 
> 
>http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-rep
>ro
> ducible-example
> 
> On Sat, Nov 15, 2014 at 1:45 AM, Ashish Kumar Singh <
> ashishkumar.singh@altair.com > wrote:
> 
> 
> 
> Additional information here,
> 
> 
> If I merge all these files in to one file and then import to 
>monetdb 
> then
> time taken is 3-5 minutes as compared to 10-15 hours with October 
> 2014
> branch.
> 
> Hopefully some one from moentdb team will look in to this. I am 
>glad 
> to see
> that inspite of this slowness ( due to which this build is 
>unusable) 
> the
> segfaults are gone!
> 
> 
> Regards,
> Ashish
> 
> 
> From: Ashish Singh < ashishkumar.singh@altair.com >
> Date: Friday, 14 November 2014 7:41 pm
> To: " developers-list@monetdb.org " < developers-list@monetdb.org >
> Cc: " users-list@monetdb.org " < users-list@monetdb.org >
> Subject: Re: Big Slowdown (15-20X ) on import functions in Monetdb 
> October
> 2014 Release
> 
> 
> 
> 
> Is any body else seeing this problem with October,2014 release?
> 
> I wish developer team can provide some insights in this area.
> 
> Regards,
> Ashish
> 
> 
> From: Ashish Singh < ashishkumar.singh@altair.com >
> Date: Thursday, 13 November 2014 11:22 pm
> To: " developers-list@monetdb.org " < developers-list@monetdb.org >
> Cc: " users-list@monetdb.org " < users-list@monetdb.org >
> Subject: Big Slowdown (15-20X ) on import functions in Monetdb 
> October 2014
> Release
> 
> 
> 
> 
> All,
> 
> With October,2014 release we are stucked in to very strange 
> performance issue
> while importing data on virtualised environments.
> 
> On Virtual machines in our local VMware servers and amazon machine 
> we are
> seeing import performance has downgraded to close to 15 to 20 while
> importing ~ 2000 files as compared to 1 hour time being taken with 
> old
> release. This we are seeing consistently with CentOS, SLES vm and
> configuration is 4 Core 33 GB machine. We tried on quite a few 
> amazon vm
> just to make sure its not an issue with a particular instance 
>setup.
> 
> On a physical machine of 4 core 24 GB we are able to match speed 
>of 
> import
> with old monetdb. We are issuing copy into command through JDBC 
> connection.
> 
> Can you guys help me with pointers on this issue, I will be happy 
>to 
> share
> any debug info.
> 
> Regards,
> Ashish Kumar Singh
> Mobile: +91-9972045095
> Skype: toashishk@gmail.com
> 
> 
> 
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
> 
> 
> 
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
> 
> 
> 
> _______________________________________________
> developers-list mailing list
> developers-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list
-- 
| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/developers-list

users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list