HI Stephen -

Copying the machine administrators on this email.

The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. When I run “top” I see nothing little else running on it. No other IO going on. The access to this machine is limited and I generally know who other than myself is on it.

Before loading new columns I ALWAYs do a “drop table”. Then I run “create table …” with the new columns added to the list. Follow this with a “COPY BINARY INTO …” . So I monetdb is always loading into an empty table.

On disk, the binary files take up 726G of space.

Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives in /local/monetdb. Are these the same file system?

Stephen: I the last load command around 3:30 yesterday afternoon and it was still not complete this morning. We’ve been very happy with monetdb, but this is quite troubling. Is it a problem to downgrade the monetdb load from dec2016-SP2 back to jun2016-sp2?

Thanks - Lynn

On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn Carol,

COPY BINARY INTO now indeed copies the data, and that can add some cost to it, in particular in case your data is large and your dbfarm is on the same I/O system, or even the same (single) disk.

To better understand your situation, could you possibly share how big your (binary) data is that you load (i.e., how much diskspace do the 135 files with 2 billions values each occupy), and whether you I/O system is a single hard disk, or a RAID system, or an SSD? Also, how much RAM does you machine have?

Given that you used to exploit the old COPY BINARY INTO's "move feature", I assume your data and your dbfarm are on the same filesystem.

(NB. in the old version, we could do the "move trick" only when bulk-loading into an empty table; when loading more data into a non-empty table, we also had to copy the data ...)

In case your machine has more than one filesystem, each on a different hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one and your dbfarm on the other, spreading the I/O load over both (one mostly reading, the other mostly writing).

You can also inspect you systems I/O activity during the load, e.g., using iostat.

Best, Stefan

----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> BTW, my 135 binary files together are 726G. I note the dec2016 release says: > > > > BATattach now copies the input file instead of "stealing" it. > > > > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > files and monetdb are on the same machine – no network access. And “top” shows > nothing of significance running on the machine except mserver5. > > > > I loved the speed with which I could add new columns to my table by dropping it, > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > back, or an idea on what could be wrong. > > > > Thanks - Lynn > > > > > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > Lynn Carol Johnson lcj34@cornell.edu > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > Date: Wednesday, March 8, 2017 at 3:19 PM > To: Communication channel for MonetDB users users-list@monetdb.org > Subject: DEC2016-SP2 and BINARY bulk load > > > > > > Hi all – > > > > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > genetic data into a monetdb table. With 135 columns, it has been blindingly > fast. > > > > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > it could clean up properly). I then started the load again, thinking perhaps > the problem was related to the new columns I was adding. > > I have since dropped the table and remade it using the same data and scripts > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > really chugging along – I’m up to 30 minutes and counting. I don’t have access > to the sql log files, but the Merovingian.log shows nothing. > > > > I do notice that previously the binary files, once loaded, were removed from the > loading directly. This does not happen now. Were these files previously “moved” > and now they are copied? > > > > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > Commands? > > > > Thanks - Lynn > > _______________________________________________ > users-list mailing list > users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-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) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Hi Stephen –

Here is a snapshot from “top” while the COPY INTO command is running this morning. A little difficult to read with the copy/paste, but you can see only 1 running process, and monetdb is takin 252.5 %cpy and 62.0 %mem.

Tasks: 500 total, 1 running, 499 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 10.7 sy, 0.0 ni, 89.3 id, 0.1 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 26386425+total, 669848 free, 21478892 used, 24171552+buff/cache KiB Swap: 4194300 total, 4119876 free, 74424 used. 23973100+avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 20389 root 20 0 0.225t 0.152t 0.139t S 252.5 62.0 31:49.40 mserver5 189 root 20 0 0 0 0 S 1.7 0.0 8:11.46 kswapd1 188 root 20 0 0 0 0 S 0.7 0.0 11:44.18 kswapd0 870 root 20 0 0 0 0 S 0.3 0.0 1:42.00 xfsaild/sdb1 952 root 20 0 560084 17356 5732 S 0.3 0.0 0:32.46 tuned 2825 root 20 0 191852 11388 2156 S 0.3 0.0 57:12.90 lsi_mrdsnmpagen 3686 root 20 0 15.960g 3.981g 13748 S 0.3 1.6 18:29.34 java 14506 root 20 0 0 0 0 S 0.3 0.0 0:09.91 kworker/u49:1 29094 lcj34 20 0 150720 2556 1492 R 0.3 0.0 0:00.20 top 1 root 20 0 190308 5384 2376 S 0.0 0.0 0:49.53 systemd

What other data can I provide?

Thanks - Lynn

On 3/9/17, 7:41 AM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn,

I understood and understand that your machine exclusively used by MonetDB.

My question was more to investigate (using iostat & top) what CPU and/or IO load MonetDB creates while loading the data, in other words trying to understand whether the experienced performace degradiation can be realted to either increased I/O or CPU activity. Afterall, copying 726 GB does cost some time, expecially since this does not fit into RAM (and thus into file system caches) ...

I also understood and understand that you're loading into empty tables. The resaons for us to discontinue the "move feature" include that (a) it only worked if both to be loaded data and dbfarm are on the same filesystem, (b) and it only worked when loading into empty tables. While both seem yo (must) hold for you to get the fast performance, they do not hold in many other cases. Moreover, people ded not like the fact the (some of) the files they loaded from "were gone" after the load, even if the load did not succeed ...

You could consider downgrading (well, most probabaly you'd then first have to dump your entire DB(s) and then re-load them after the downgrade), but it's very unlikely, that we revive the move feature. hence, you'd never be able to upgrade again.

I'd prefer that we could understand where the significant degrading comes from, and whether it's something we could fix ...

Best, Stefan

ps: you dbfarm is not necesarily where your monetdb installation (program) is, but rather where you find the merovingian.log

----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> HI Stephen - > > Copying the machine administrators on this email. > > The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 > storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. > When I run “top” I see nothing little else running on it. No other IO going > on. The access to this machine is limited and I generally know who other than > myself is on it. > > Before loading new columns I ALWAYs do a “drop table”. Then I run “create table > …” with the new columns added to the list. Follow this with a “COPY BINARY > INTO …” . So I monetdb is always loading into an empty table. > > On disk, the binary files take up 726G of space. > > Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives > in /local/monetdb. Are these the same file system? > > Stephen: I the last load command around 3:30 yesterday afternoon and it was > still not complete this morning. We’ve been very happy with monetdb, but this > is quite troubling. Is it a problem to downgrade the monetdb load from > dec2016-SP2 back to jun2016-sp2? > > Thanks - Lynn > > On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > Stefan.Manegold@cwi.nl> wrote: > > Hi Lynn Carol, > > COPY BINARY INTO now indeed copies the data, > and that can add some cost to it, > in particular in case your data is large > and your dbfarm is on the same I/O system, > or even the same (single) disk. > > To better understand your situation, > could you possibly share how big your (binary) data > is that you load (i.e., how much diskspace do the 135 files > with 2 billions values each occupy), > and whether you I/O system is a single hard disk, or a RAID system, > or an SSD? > Also, how much RAM does you machine have? > > Given that you used to exploit the old COPY BINARY INTO's "move feature", > I assume your data and your dbfarm are on the same filesystem. > > (NB. in the old version, we could do the "move trick" only when bulk-loading > into an empty table; when loading more data into a non-empty table, we also > had to copy the data ...) > > In case your machine has more than one filesystem, each on a different > hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one > and your dbfarm on the other, spreading the I/O load over both > (one mostly reading, the other mostly writing). > > You can also inspect you systems I/O activity during the load, e.g., > using iostat. > > Best, > Stefan > > ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > BTW, my 135 binary files together are 726G. I note the dec2016 release says: > > > > > > > > BATattach now copies the input file instead of "stealing" it. > > > > > > > > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > > files and monetdb are on the same machine – no network access. And “top” shows > > nothing of significance running on the machine except mserver5. > > > > > > > > I loved the speed with which I could add new columns to my table by dropping it, > > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > > back, or an idea on what could be wrong. > > > > > > > > Thanks - Lynn > > > > > > > > > > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > Lynn Carol Johnson lcj34@cornell.edu > > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > > Date: Wednesday, March 8, 2017 at 3:19 PM > > To: Communication channel for MonetDB users users-list@monetdb.org > > Subject: DEC2016-SP2 and BINARY bulk load > > > > > > > > > > > > Hi all – > > > > > > > > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > > genetic data into a monetdb table. With 135 columns, it has been blindingly > > fast. > > > > > > > > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > > it could clean up properly). I then started the load again, thinking perhaps > > the problem was related to the new columns I was adding. > > > > I have since dropped the table and remade it using the same data and scripts > > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > > really chugging along – I’m up to 30 minutes and counting. I don’t have access > > to the sql log files, but the Merovingian.log shows nothing. > > > > > > > > I do notice that previously the binary files, once loaded, were removed from the > > loading directly. This does not happen now. Were these files previously “moved” > > and now they are copied? > > > > > > > > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > > Commands? > > > > > > > > Thanks - Lynn > > > > _______________________________________________ > > users-list mailing list > > users-list@monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-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) | > _______________________________________________ > 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

-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

HI Stefan, Martin –

We are open to any suggestions.

Stephan, I understand your reasons for making the change from “steal” to “copy”. Is it possible to have both options? Before loading the new tables I do a “copy *.bin” from the “saved” directory to the “loading” directory. This takes less than an hour. Are there multiple steps involved in the monetdb copy that results in it not finishing overnight?

From the perspective of a user that deals with very large data, a big selling point of monetdb is the ability to create data a column at a time and load using the COPY BINARY command. I have often bragged on monetdb’s ability to reload the entire table of 2 billion rows, 100+ columns in 3-4 minutes. I would still brag if it took an hour.

Our data base contains a row for each of the 2 billion positions on the maize genome. Each row has a chromosome and position column as well as many other traits scored for this position. We expect to continue to add columns as more data is curated. The data is then used for genomic analysis. While I’m not presented with new data every week, I generally gather data once a month that needs to be added. Having the db down for several days to reload can be problematic.

Let me know if there is other data I can collect to help analyze this problem.

Thanks - Lynn

On 3/9/17, 7:49 AM, "users-list on behalf of Martin Kersten" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of martin@monetdb.org> wrote:

Hi Stefan, Lynn

One way to move forward is to move the old functionality into the DataVault. There a directory of MonetDB compliant binary data can potentially be handled without the overhead incurred by copying.

On 09/03/2017 13:41, Stefan Manegold wrote: > Hi Lynn, > > I understood and understand that your machine exclusively used by MonetDB. > > My question was more to investigate (using iostat & top) what CPU and/or IO > load MonetDB creates while loading the data, in other words trying to understand > whether the experienced performace degradiation can be realted to either increased > I/O or CPU activity. > Afterall, copying 726 GB does cost some time, expecially since this does not > fit into RAM (and thus into file system caches) ... > > I also understood and understand that you're loading into empty tables. > The resaons for us to discontinue the "move feature" include that > (a) it only worked if both to be loaded data and dbfarm are on the same filesystem, > (b) and it only worked when loading into empty tables. > While both seem yo (must) hold for you to get the fast performance, they do not > hold in many other cases. Moreover, people ded not like the fact the (some of) > the files they loaded from "were gone" after the load, even if the load did not > succeed ... > > You could consider downgrading (well, most probabaly you'd then first have to dump > your entire DB(s) and then re-load them after the downgrade), > but it's very unlikely, that we revive the move feature. > hence, you'd never be able to upgrade again. > > I'd prefer that we could understand where the significant degrading comes from, > and whether it's something we could fix ... > > Best, > Stefan > > ps: you dbfarm is not necesarily where your monetdb installation (program) is, > but rather where you find the merovingian.log > > ----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > >> HI Stephen - >> >> Copying the machine administrators on this email. >> >> The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 >> storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. >> When I run “top” I see nothing little else running on it. No other IO going >> on. The access to this machine is limited and I generally know who other than >> myself is on it. >> >> Before loading new columns I ALWAYs do a “drop table”. Then I run “create table >> …” with the new columns added to the list. Follow this with a “COPY BINARY >> INTO …” . So I monetdb is always loading into an empty table. >> >> On disk, the binary files take up 726G of space. >> >> Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives >> in /local/monetdb. Are these the same file system? >> >> Stephen: I the last load command around 3:30 yesterday afternoon and it was >> still not complete this morning. We’ve been very happy with monetdb, but this >> is quite troubling. Is it a problem to downgrade the monetdb load from >> dec2016-SP2 back to jun2016-sp2? >> >> Thanks - Lynn >> >> On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" >> <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of >> Stefan.Manegold@cwi.nl> wrote: >> >> Hi Lynn Carol, >> >> COPY BINARY INTO now indeed copies the data, >> and that can add some cost to it, >> in particular in case your data is large >> and your dbfarm is on the same I/O system, >> or even the same (single) disk. >> >> To better understand your situation, >> could you possibly share how big your (binary) data >> is that you load (i.e., how much diskspace do the 135 files >> with 2 billions values each occupy), >> and whether you I/O system is a single hard disk, or a RAID system, >> or an SSD? >> Also, how much RAM does you machine have? >> >> Given that you used to exploit the old COPY BINARY INTO's "move feature", >> I assume your data and your dbfarm are on the same filesystem. >> >> (NB. in the old version, we could do the "move trick" only when bulk-loading >> into an empty table; when loading more data into a non-empty table, we also >> had to copy the data ...) >> >> In case your machine has more than one filesystem, each on a different >> hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one >> and your dbfarm on the other, spreading the I/O load over both >> (one mostly reading, the other mostly writing). >> >> You can also inspect you systems I/O activity during the load, e.g., >> using iostat. >> >> Best, >> Stefan >> >> ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: >> >> > BTW, my 135 binary files together are 726G. I note the dec2016 release says: >> > >> > >> > >> > BATattach now copies the input file instead of "stealing" it. >> > >> > >> > >> > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My >> > files and monetdb are on the same machine – no network access. And “top” shows >> > nothing of significance running on the machine except mserver5. >> > >> > >> > >> > I loved the speed with which I could add new columns to my table by dropping it, >> > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this >> > back, or an idea on what could be wrong. >> > >> > >> > >> > Thanks - Lynn >> > >> > >> > >> > >> > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of >> > Lynn Carol Johnson lcj34@cornell.edu >> > Reply-To: Communication channel for MonetDB users users-list@monetdb.org >> > Date: Wednesday, March 8, 2017 at 3:19 PM >> > To: Communication channel for MonetDB users users-list@monetdb.org >> > Subject: DEC2016-SP2 and BINARY bulk load >> > >> > >> > >> > >> > >> > Hi all – >> > >> > >> > >> > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row >> > genetic data into a monetdb table. With 135 columns, it has been blindingly >> > fast. >> > >> > >> > >> > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads >> > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so >> > it could clean up properly). I then started the load again, thinking perhaps >> > the problem was related to the new columns I was adding. >> > >> > I have since dropped the table and remade it using the same data and scripts >> > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is >> > really chugging along – I’m up to 30 minutes and counting. I don’t have access >> > to the sql log files, but the Merovingian.log shows nothing. >> > >> > >> > >> > I do notice that previously the binary files, once loaded, were removed from the >> > loading directly. This does not happen now. Were these files previously “moved” >> > and now they are copied? >> > >> > >> > >> > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. >> > Commands? >> > >> > >> > >> > Thanks - Lynn >> > >> > _______________________________________________ >> > users-list mailing list >> > users-list@monetdb.org >> > https://www.monetdb.org/mailman/listinfo/users-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) | >> _______________________________________________ >> 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 >

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

Regarding the “copy” suggesiont. I have a folder /workdir/lcj34/monetdb/savedBinaries_fullGenome and a folder /workdir/lcj34/monetdb/fullGenome_binariesToLoad.

There are 2 folders because monetdb used to delete (steal) the binaries when it was done copying. Because I always drop the table and reload it, the “saved” dir has all the previously working binaries. I add the new ones to it, copy everything to the folder from which they will be loaded, and copy from there. There are no differences in the files, and they are on the same file system, same disk, etc.

I appreciate your help with this.

On 3/9/17, 12:00 PM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn,

see my comments inlined below

----- On Mar 9, 2017, at 5:50 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> HI Stefan, Martin – > > We are open to any suggestions. > > Stephan, I understand your reasons for making the change from “steal” to “copy”. > Is it possible to have both options?

We might consider that ...

> Before loading the new tables I do a > “copy *.bin” from the “saved” directory to the “loading” directory. This > takes less than an hour. Are there multiple steps involved in the monetdb > copy that results in it not finishing overnight?

are your "saved" and "loading" directories on different file systems (or even different I/O sub systems / disks/ raids? Could you try loading directly from the "Saved" rather than copying to the "loading" and loading from there? That might help (a lot?) in case "saved" is on another i/o subsystem than you dbfarm. while "loading" is on the same as your dbfarm ...

> From the perspective of a user that deals with very large data, a big selling > point of monetdb is the ability to create data a column at a time and load > using the COPY BINARY command. I have often bragged on monetdb’s ability to > reload the entire table of 2 billion rows, 100+ columns in 3-4 minutes. I > would still brag if it took an hour. > > Our data base contains a row for each of the 2 billion positions on the maize > genome. Each row has a chromosome and position column as well as many other > traits scored for this position. We expect to continue to add columns as more > data is curated. The data is then used for genomic analysis. While I’m not > presented with new data every week, I generally gather data once a month that > needs to be added. Having the db down for several days to reload can be > problematic.

In fact, I recently made the case in the group that we need (more) efficient way(s) to extend a table by one (or more) column(s) an load extra data in that column(s) efficiently (i.e., without for loading into a new table and that copying into the extended table using a "alter table" with a join ...). Your senario confirms that we should increase the priority to pursue this direction; now only find the time / manpower to implement it ...

> Let me know if there is other data I can collect to help analyze this problem.

I might / will, but need some more time (I'm "on the road" as we speak ...)

Best, Stefan

> Thanks - Lynn > > On 3/9/17, 7:49 AM, "users-list on behalf of Martin Kersten" > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > martin@monetdb.org> wrote: > > Hi Stefan, Lynn > > One way to move forward is to move the old functionality into the DataVault. > There a directory of MonetDB compliant binary data can potentially be > handled without the overhead incurred by copying. > > > On 09/03/2017 13:41, Stefan Manegold wrote: > > Hi Lynn, > > > > I understood and understand that your machine exclusively used by MonetDB. > > > > My question was more to investigate (using iostat & top) what CPU and/or IO > > load MonetDB creates while loading the data, in other words trying to understand > > whether the experienced performace degradiation can be realted to either > > increased > > I/O or CPU activity. > > Afterall, copying 726 GB does cost some time, expecially since this does not > > fit into RAM (and thus into file system caches) ... > > > > I also understood and understand that you're loading into empty tables. > > The resaons for us to discontinue the "move feature" include that > > (a) it only worked if both to be loaded data and dbfarm are on the same > > filesystem, > > (b) and it only worked when loading into empty tables. > > While both seem yo (must) hold for you to get the fast performance, they do not > > hold in many other cases. Moreover, people ded not like the fact the (some of) > > the files they loaded from "were gone" after the load, even if the load did not > > succeed ... > > > > You could consider downgrading (well, most probabaly you'd then first have to > > dump > > your entire DB(s) and then re-load them after the downgrade), > > but it's very unlikely, that we revive the move feature. > > hence, you'd never be able to upgrade again. > > > > I'd prefer that we could understand where the significant degrading comes from, > > and whether it's something we could fix ... > > > > Best, > > Stefan > > > > ps: you dbfarm is not necesarily where your monetdb installation (program) is, > > but rather where you find the merovingian.log > > > > ----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > >> HI Stephen - > >> > >> Copying the machine administrators on this email. > >> > >> The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 > >> storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. > >> When I run “top” I see nothing little else running on it. No other IO going > >> on. The access to this machine is limited and I generally know who other than > >> myself is on it. > >> > >> Before loading new columns I ALWAYs do a “drop table”. Then I run “create table > >> …” with the new columns added to the list. Follow this with a “COPY BINARY > >> INTO …” . So I monetdb is always loading into an empty table. > >> > >> On disk, the binary files take up 726G of space. > >> > >> Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives > >> in /local/monetdb. Are these the same file system? > >> > >> Stephen: I the last load command around 3:30 yesterday afternoon and it was > >> still not complete this morning. We’ve been very happy with monetdb, but this > >> is quite troubling. Is it a problem to downgrade the monetdb load from > >> dec2016-SP2 back to jun2016-sp2? > >> > >> Thanks - Lynn > >> > >> On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" > >> <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > >> Stefan.Manegold@cwi.nl> wrote: > >> > >> Hi Lynn Carol, > >> > >> COPY BINARY INTO now indeed copies the data, > >> and that can add some cost to it, > >> in particular in case your data is large > >> and your dbfarm is on the same I/O system, > >> or even the same (single) disk. > >> > >> To better understand your situation, > >> could you possibly share how big your (binary) data > >> is that you load (i.e., how much diskspace do the 135 files > >> with 2 billions values each occupy), > >> and whether you I/O system is a single hard disk, or a RAID system, > >> or an SSD? > >> Also, how much RAM does you machine have? > >> > >> Given that you used to exploit the old COPY BINARY INTO's "move feature", > >> I assume your data and your dbfarm are on the same filesystem. > >> > >> (NB. in the old version, we could do the "move trick" only when bulk-loading > >> into an empty table; when loading more data into a non-empty table, we also > >> had to copy the data ...) > >> > >> In case your machine has more than one filesystem, each on a different > >> hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one > >> and your dbfarm on the other, spreading the I/O load over both > >> (one mostly reading, the other mostly writing). > >> > >> You can also inspect you systems I/O activity during the load, e.g., > >> using iostat. > >> > >> Best, > >> Stefan > >> > >> ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > >> > >> > BTW, my 135 binary files together are 726G. I note the dec2016 release says: > >> > > >> > > >> > > >> > BATattach now copies the input file instead of "stealing" it. > >> > > >> > > >> > > >> > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > >> > files and monetdb are on the same machine – no network access. And “top” shows > >> > nothing of significance running on the machine except mserver5. > >> > > >> > > >> > > >> > I loved the speed with which I could add new columns to my table by dropping it, > >> > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > >> > back, or an idea on what could be wrong. > >> > > >> > > >> > > >> > Thanks - Lynn > >> > > >> > > >> > > >> > > >> > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > >> > Lynn Carol Johnson lcj34@cornell.edu > >> > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > >> > Date: Wednesday, March 8, 2017 at 3:19 PM > >> > To: Communication channel for MonetDB users users-list@monetdb.org > >> > Subject: DEC2016-SP2 and BINARY bulk load > >> > > >> > > >> > > >> > > >> > > >> > Hi all – > >> > > >> > > >> > > >> > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > >> > genetic data into a monetdb table. With 135 columns, it has been blindingly > >> > fast. > >> > > >> > > >> > > >> > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > >> > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > >> > it could clean up properly). I then started the load again, thinking perhaps > >> > the problem was related to the new columns I was adding. > >> > > >> > I have since dropped the table and remade it using the same data and scripts > >> > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > >> > really chugging along – I’m up to 30 minutes and counting. I don’t have access > >> > to the sql log files, but the Merovingian.log shows nothing. > >> > > >> > > >> > > >> > I do notice that previously the binary files, once loaded, were removed from the > >> > loading directly. This does not happen now. Were these files previously “moved” > >> > and now they are copied? > >> > > >> > > >> > > >> > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > >> > Commands? > >> > > >> > > >> > > >> > Thanks - Lynn > >> > > >> > _______________________________________________ > >> > users-list mailing list > >> > users-list@monetdb.org > >> > https://www.monetdb.org/mailman/listinfo/users-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) | > >> _______________________________________________ > >> 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 > > > > _______________________________________________ > 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

-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Hi Stefan:

The load is still running (the one started at 8:52 am yesterday, I am in the US in the Eastern time zone, it is currently 7:15am).

Requested ommand results:

[lcj34@cbsudc01 ~]$ df -h df: ‘/home/mm2842/.gvfs’: Permission denied df: ‘/lustre1/home/mm2842/.gvfs’: Permission denied df: ‘/home/zrm22/.gvfs’: Permission denied df: ‘/lustre1/home/zrm22/.gvfs’: Permission denied df: ‘/home/esb33/.gvfs’: Permission denied df: ‘/lustre1/home/esb33/.gvfs’: Permission denied df: ‘/home/kls283/.gvfs’: Permission denied df: ‘/lustre1/home/kls283/.gvfs’: Permission denied Filesystem Size Used Avail Use% Mounted on /dev/sda3 500G 40G 435G 9% / devtmpfs 126G 0 126G 0% /dev tmpfs 126G 560K 126G 1% /dev/shm tmpfs 126G 42M 126G 1% /run tmpfs 126G 0 126G 0% /sys/fs/cgroup /dev/sda1 488M 100M 353M 23% /boot /dev/md0 939G 687G 205G 78% /SSD /dev/sdb1 15T 9.7T 4.4T 70% /local tmpfs 26G 12K 26G 1% /run/user/42 128.84.3.177@tcp1:128.84.3.176@tcp1:/lustre1 702T 428T 275T 61% /home cbsugfs1:/home 313T 234T 71T 77% /glusterfs/home tmpfs 26G 0 26G 0% /run/user/0 //andersonii.maize.cornell.edu/admin 28T 24T 3.8T 87% /fs/andersonii.maize.cornell.edu/admin tmpfs 26G 0 26G 0% /run/user/956 tmpfs 26G 0 26G 0% /run/user/1719 [lcj34@cbsudc01 ~]$

And:

[lcj34@cbsudc01 ~]$ ps auxwwwf | grep mserver5 lcj34 1606 0.0 0.0 112644 960 pts/0 S+ 07:12 0:00 _ grep --color=auto mserver5 root 14623 230 88.4 760728724 233261332 ? Ssl Mar09 3096:54 _ /usr/bin/mserver5 --dbpath=/local/monetdb/dbfarmBL/maizeFullGenomeDB --set merovingian_uri mapi:monetdb://cbsudc01:50000/maizeFullGenomeDB --set mapi_open false --set mapi_port 0 --set mapi_usock /local/monetdb/dbfarmBL/maizeFullGenomeDB/.mapi.sock --set monet_vault_key /local/monetdb/dbfarmBL/maizeFullGenomeDB/.vaultkey --set gdk_nr_threads 24 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes [lcj34@cbsudc01 ~]$

I create the table using this command from a .sql file:

CREATE TABLE fullGenomeStable (chr int, pos int, ref_allele tinyint,two_bp_upstream int, two_bp_downstream int, three_bp_upstream int, three_bp_center int, three_bp_downstream int, four_bp_upstream int, four_bp_downstream int, five_bp_upstream int,five_bp_center int, five_bp_downstream int,gc_content tinyint,gerp_neutral_tree_length real, gerp_score real, gerp_non_negative_score real, gerp_conserved boolean, cMethylation real, cpg_methylation_1bk_moving_average real, chg_methylation_1bk_moving_average real, chh_methylation_1bk_moving_average real, mnase_low_minus_high_rpm_shoots real, mnase_bayes_factor_shoots real, mnase_hotspot_shoots boolean, mnase_low_minus_high_rpm_roots real, mnase_bayes_factor_roots real, mnase_hotspot_roots boolean, allele_total_depth int, site_presence int, conservedgene_presence boolean, minor_allele tinyint, maf real, combined_site_uniqueness_score real, site_uniqueness_score_sd real,expected_invariant_site boolean,codon_position tinyint,within_cds boolean, within_gene boolean, within_transcript boolean, within_exon boolean, within_five_prime_utr boolean, within_three_prime_utr boolean, within_mirna boolean, line_family int, sine_family int, ltr_family int, helitron boolean, carot_common_support_interval tinyint, toco_common_support_interval tinyint,B73_uniqueness_score real, CML247_uniqueness_score real, PH207_uniqueness_score real, W22_uniqueness_score real,Coelorachis_anc tinyint, Vossia_anc tinyint, Sorghum_anc tinyint, Oryza_anc tinyint, Setaria_anc tinyint, Brachypodium_anc tinyint, Hordeum_anc tinyint, Musa_anc tinyint, populus_anc tinyint, vitis_anc tinyint, arabidopsis_anc tinyint, panicum_anc tinyint,hmp32CDS_alt_allele tinyint, hmp32CDS_minor_allele tinyint, hmp32CDS_maf real, hmp32CDS_site_depth int, hmp32CDS_hetCount int, hmp32CDS_mutation_class tinyint, hmp32CDS_sift real, eli_nam_xo_mean real, eli_nam_xo_corrected real, Ames_BLUP_DTA_cand50kbp_top79_add_p smallint,Ames_BLUP_DTA_cand50kbp_top918_add_p smallint,Ames_BLUP_DTS_cand50kbp_top79_add_p smallint,Ames_BLUP_DTS_cand50kbp_top918_add_p smallint,Ames_QOnly_DTA_cand50kbp_top79_add_p smallint,Ames_QOnly_DTA_cand50kbp_top918_add_p smallint,Ames_QOnly_DTS_cand50kbp_top79_add_p smallint, Ames_QOnly_DTS_cand50kbp_top918_add_p smallint, cand_Yongxiang_add_p smallint,cand_DongPlusZCN_add_p smallint,H3K27me2_DevEars_peak boolean, H3K27me2_DevEars_rpm real, H3K27me2_DevEars_minuslog10qvalue real, H3K27me3_Root_peak boolean, H3K27me3_Root_rpm real, H3K27me3_Root_minuslog10qvalue real, H3K27me3_Shoot_highconf_peak boolean, H3K27me3_Shoot_highconf_peaks_rpm real, H3K27me3_Shoot_highconf_peak_qvalue real, H3K36me3_Shoot_peak boolean, H3K36me3_Shoot_rpm real, H3K36me3_Shoot_minuslog10qvalue real, H3K4me3_Base_peak boolean, H3K4me3_Base_rpm real, H3K4me3_Base_minuslog10qvalue real, H3K4me3_Maturing_peak boolean, H3K4me3_Maturing_rpm real, H3K4me3_Maturing_minuslog10qvalue real, H3K4me3_Root_peak boolean, H3K4me3_Root_rpm real, H3K4me3_Root_minuslog10qvalue real, H3K4me3_Shoot_peak boolean, H3K4me3_Shoot_rpm real, H3K4me3_Shoot_minuslog10qvalue real, H3K9ac_Base_peak boolean, H3K9ac_Base_rpm real, H3K9ac_Base_minuslog10qvalue real, H3K9ac_Maturing_peak boolean, H3K9ac_Maturing_rpm real, H3K9ac_Maturing_minuslog10qvalue real, H3K9ac_Root_peak boolean, H3K9ac_Root_rpm real, H3K9ac_Root_minuslog10qvalue real, H3K9ac_Shoot_peak boolean, H3K9ac_Shoot_rpm real, H3K9ac_Shoot_minuslog10qvalue real, H3K9me2_DevEars_peak boolean, H3K9me2_DevEars_rpm real, H3K9me2_DevEars_minuslog10qvalue real, H3K9me2_Seedlings_peak boolean, H3K9me2_Seedlings_rpm real, H3K9me2_Seedlings_minuslog10qvalue real, SW_maxScore_sorghum real, SW_meanScore_sorghum real, SW_maxScore_rice real, SW_meanScore_rice real, SW_NumberOfAlign_sorghum int, SW_NumberOfAlign_rice int, ZmAGPv3_highconf_tss boolean, ZmAGPv3_highconf_tss_strand int);

And copy using this from a .sql file: COPY binary into fullGenomeStable from ('/workdir/lcj34/monetdbFiles/fullGenome_binariesToLoad/chrFile.bin', (etc) )

Thanks - Lynn

On 3/9/17, 5:35 PM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn,

to understand your problem in more detail, could you possibly share the schema of your table (i.e., the types of all columns), as well as the output of the following two commands on your machine:

df -h ps auxwwwf | grep mserver5

These would help me/us to understand where your data and dbfarm are located.

Feel free to send these info by private to me in case you think this list is "too public" --- I'd appreciate, though, if you'd to keep the general discussion on this list for others to jump in and/or benefit.

Best, Stefan

----- On Mar 9, 2017, at 6:15 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> Regarding the “copy” suggesiont. I have a folder > /workdir/lcj34/monetdb/savedBinaries_fullGenome and a folder > /workdir/lcj34/monetdb/fullGenome_binariesToLoad. > > There are 2 folders because monetdb used to delete (steal) the binaries when it > was done copying. Because I always drop the table and reload it, the “saved” > dir has all the previously working binaries. I add the new ones to it, copy > everything to the folder from which they will be loaded, and copy from there. > There are no differences in the files, and they are on the same file system, > same disk, etc. > > I appreciate your help with this. > > On 3/9/17, 12:00 PM, "users-list on behalf of Stefan Manegold" > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > Stefan.Manegold@cwi.nl> wrote: > > > Hi Lynn, > > see my comments inlined below > > ----- On Mar 9, 2017, at 5:50 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > HI Stefan, Martin – > > > > We are open to any suggestions. > > > > Stephan, I understand your reasons for making the change from “steal” to “copy”. > > Is it possible to have both options? > > We might consider that ... > > > Before loading the new tables I do a > > “copy *.bin” from the “saved” directory to the “loading” directory. This > > takes less than an hour. Are there multiple steps involved in the monetdb > > copy that results in it not finishing overnight? > > are your "saved" and "loading" directories on different file systems (or even > different I/O sub systems / disks/ raids? > Could you try loading directly from the "Saved" rather than copying to the > "loading" > and loading from there? > That might help (a lot?) in case "saved" is on another i/o subsystem than you > dbfarm. > while "loading" is on the same as your dbfarm ... > > > From the perspective of a user that deals with very large data, a big selling > > point of monetdb is the ability to create data a column at a time and load > > using the COPY BINARY command. I have often bragged on monetdb’s ability to > > reload the entire table of 2 billion rows, 100+ columns in 3-4 minutes. I > > would still brag if it took an hour. > > > > Our data base contains a row for each of the 2 billion positions on the maize > > genome. Each row has a chromosome and position column as well as many other > > traits scored for this position. We expect to continue to add columns as more > > data is curated. The data is then used for genomic analysis. While I’m not > > presented with new data every week, I generally gather data once a month that > > needs to be added. Having the db down for several days to reload can be > > problematic. > > In fact, I recently made the case in the group that we need (more) efficient > way(s) > to extend a table by one (or more) column(s) an load extra data in that > column(s) > efficiently (i.e., without for loading into a new table and that copying into > the > extended table using a "alter table" with a join ...). > Your senario confirms that we should increase the priority to pursue this > direction; > now only find the time / manpower to implement it ... > > > Let me know if there is other data I can collect to help analyze this problem. > > I might / will, but need some more time (I'm "on the road" as we speak ...) > > Best, > Stefan > > > Thanks - Lynn > > > > On 3/9/17, 7:49 AM, "users-list on behalf of Martin Kersten" > > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > martin@monetdb.org> wrote: > > > > Hi Stefan, Lynn > > > > One way to move forward is to move the old functionality into the DataVault. > > There a directory of MonetDB compliant binary data can potentially be > > handled without the overhead incurred by copying. > > > > > > On 09/03/2017 13:41, Stefan Manegold wrote: > > > Hi Lynn, > > > > > > I understood and understand that your machine exclusively used by MonetDB. > > > > > > My question was more to investigate (using iostat & top) what CPU and/or IO > > > load MonetDB creates while loading the data, in other words trying to understand > > > whether the experienced performace degradiation can be realted to either > > > increased > > > I/O or CPU activity. > > > Afterall, copying 726 GB does cost some time, expecially since this does not > > > fit into RAM (and thus into file system caches) ... > > > > > > I also understood and understand that you're loading into empty tables. > > > The resaons for us to discontinue the "move feature" include that > > > (a) it only worked if both to be loaded data and dbfarm are on the same > > > filesystem, > > > (b) and it only worked when loading into empty tables. > > > While both seem yo (must) hold for you to get the fast performance, they do not > > > hold in many other cases. Moreover, people ded not like the fact the (some of) > > > the files they loaded from "were gone" after the load, even if the load did not > > > succeed ... > > > > > > You could consider downgrading (well, most probabaly you'd then first have to > > > dump > > > your entire DB(s) and then re-load them after the downgrade), > > > but it's very unlikely, that we revive the move feature. > > > hence, you'd never be able to upgrade again. > > > > > > I'd prefer that we could understand where the significant degrading comes from, > > > and whether it's something we could fix ... > > > > > > Best, > > > Stefan > > > > > > ps: you dbfarm is not necesarily where your monetdb installation (program) is, > > > but rather where you find the merovingian.log > > > > > > ----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > > > >> HI Stephen - > > >> > > >> Copying the machine administrators on this email. > > >> > > >> The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 > > >> storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. > > >> When I run “top” I see nothing little else running on it. No other IO going > > >> on. The access to this machine is limited and I generally know who other than > > >> myself is on it. > > >> > > >> Before loading new columns I ALWAYs do a “drop table”. Then I run “create table > > >> …” with the new columns added to the list. Follow this with a “COPY BINARY > > >> INTO …” . So I monetdb is always loading into an empty table. > > >> > > >> On disk, the binary files take up 726G of space. > > >> > > >> Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives > > >> in /local/monetdb. Are these the same file system? > > >> > > >> Stephen: I the last load command around 3:30 yesterday afternoon and it was > > >> still not complete this morning. We’ve been very happy with monetdb, but this > > >> is quite troubling. Is it a problem to downgrade the monetdb load from > > >> dec2016-SP2 back to jun2016-sp2? > > >> > > >> Thanks - Lynn > > >> > > >> On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" > > >> <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > >> Stefan.Manegold@cwi.nl> wrote: > > >> > > >> Hi Lynn Carol, > > >> > > >> COPY BINARY INTO now indeed copies the data, > > >> and that can add some cost to it, > > >> in particular in case your data is large > > >> and your dbfarm is on the same I/O system, > > >> or even the same (single) disk. > > >> > > >> To better understand your situation, > > >> could you possibly share how big your (binary) data > > >> is that you load (i.e., how much diskspace do the 135 files > > >> with 2 billions values each occupy), > > >> and whether you I/O system is a single hard disk, or a RAID system, > > >> or an SSD? > > >> Also, how much RAM does you machine have? > > >> > > >> Given that you used to exploit the old COPY BINARY INTO's "move feature", > > >> I assume your data and your dbfarm are on the same filesystem. > > >> > > >> (NB. in the old version, we could do the "move trick" only when bulk-loading > > >> into an empty table; when loading more data into a non-empty table, we also > > >> had to copy the data ...) > > >> > > >> In case your machine has more than one filesystem, each on a different > > >> hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one > > >> and your dbfarm on the other, spreading the I/O load over both > > >> (one mostly reading, the other mostly writing). > > >> > > >> You can also inspect you systems I/O activity during the load, e.g., > > >> using iostat. > > >> > > >> Best, > > >> Stefan > > >> > > >> ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > >> > > >> > BTW, my 135 binary files together are 726G. I note the dec2016 release says: > > >> > > > >> > > > >> > > > >> > BATattach now copies the input file instead of "stealing" it. > > >> > > > >> > > > >> > > > >> > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > > >> > files and monetdb are on the same machine – no network access. And “top” shows > > >> > nothing of significance running on the machine except mserver5. > > >> > > > >> > > > >> > > > >> > I loved the speed with which I could add new columns to my table by dropping it, > > >> > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > > >> > back, or an idea on what could be wrong. > > >> > > > >> > > > >> > > > >> > Thanks - Lynn > > >> > > > >> > > > >> > > > >> > > > >> > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > >> > Lynn Carol Johnson lcj34@cornell.edu > > >> > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > > >> > Date: Wednesday, March 8, 2017 at 3:19 PM > > >> > To: Communication channel for MonetDB users users-list@monetdb.org > > >> > Subject: DEC2016-SP2 and BINARY bulk load > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > Hi all – > > >> > > > >> > > > >> > > > >> > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > > >> > genetic data into a monetdb table. With 135 columns, it has been blindingly > > >> > fast. > > >> > > > >> > > > >> > > > >> > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > > >> > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > > >> > it could clean up properly). I then started the load again, thinking perhaps > > >> > the problem was related to the new columns I was adding. > > >> > > > >> > I have since dropped the table and remade it using the same data and scripts > > >> > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > > >> > really chugging along – I’m up to 30 minutes and counting. I don’t have access > > >> > to the sql log files, but the Merovingian.log shows nothing. > > >> > > > >> > > > >> > > > >> > I do notice that previously the binary files, once loaded, were removed from the > > >> > loading directly. This does not happen now. Were these files previously “moved” > > >> > and now they are copied? > > >> > > > >> > > > >> > > > >> > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > > >> > Commands? > > >> > > > >> > > > >> > > > >> > Thanks - Lynn > > >> > > > >> > _______________________________________________ > > >> > users-list mailing list > > >> > users-list@monetdb.org > > >> > https://www.monetdb.org/mailman/listinfo/users-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) | > > >> _______________________________________________ > > >> 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 > > > > > > > _______________________________________________ > > 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 > > -- > | Stefan.Manegold@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@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

-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

HI Stefan –

I followed your suggestion to load from a different device. Performed this test over the weekend. It did not help. It still took 30+ hours to load this large table.

Do you have any further thoughts on how to improve the performance? Will your team consider making both loading options (“steal” or “copy”) available in a future release? As I expected, my boss has requested we roll back to the June-2016 SP-2 release. I have argued against this and we remain on the Dec2016-SP2 load, hoping there will be a solution in a future release.

Thanks - Lynn

On 3/10/17, 3:46 PM, "users-list on behalf of Lynn Carol Johnson" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of lcj34@cornell.edu> wrote:

FYI – the load finally finished. It took 30 hours for the 726G to be copied and loaded.

Stephan, I’ll try loading from /SSD next week (to a testDB – won’t mess with the real one) and see if it matters when on a different device.

Thanks for your support - Lynn

On 3/10/17, 7:15 AM, "users-list on behalf of Lynn Carol Johnson" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of lcj34@cornell.edu> wrote:

Hi Stefan:

The load is still running (the one started at 8:52 am yesterday, I am in the US in the Eastern time zone, it is currently 7:15am).

Requested ommand results:

[lcj34@cbsudc01 ~]$ df -h df: ‘/home/mm2842/.gvfs’: Permission denied df: ‘/lustre1/home/mm2842/.gvfs’: Permission denied df: ‘/home/zrm22/.gvfs’: Permission denied df: ‘/lustre1/home/zrm22/.gvfs’: Permission denied df: ‘/home/esb33/.gvfs’: Permission denied df: ‘/lustre1/home/esb33/.gvfs’: Permission denied df: ‘/home/kls283/.gvfs’: Permission denied df: ‘/lustre1/home/kls283/.gvfs’: Permission denied Filesystem Size Used Avail Use% Mounted on /dev/sda3 500G 40G 435G 9% / devtmpfs 126G 0 126G 0% /dev tmpfs 126G 560K 126G 1% /dev/shm tmpfs 126G 42M 126G 1% /run tmpfs 126G 0 126G 0% /sys/fs/cgroup /dev/sda1 488M 100M 353M 23% /boot /dev/md0 939G 687G 205G 78% /SSD /dev/sdb1 15T 9.7T 4.4T 70% /local tmpfs 26G 12K 26G 1% /run/user/42 128.84.3.177@tcp1:128.84.3.176@tcp1:/lustre1 702T 428T 275T 61% /home cbsugfs1:/home 313T 234T 71T 77% /glusterfs/home tmpfs 26G 0 26G 0% /run/user/0 //andersonii.maize.cornell.edu/admin 28T 24T 3.8T 87% /fs/andersonii.maize.cornell.edu/admin tmpfs 26G 0 26G 0% /run/user/956 tmpfs 26G 0 26G 0% /run/user/1719 [lcj34@cbsudc01 ~]$

And:

[lcj34@cbsudc01 ~]$ ps auxwwwf | grep mserver5 lcj34 1606 0.0 0.0 112644 960 pts/0 S+ 07:12 0:00 _ grep --color=auto mserver5 root 14623 230 88.4 760728724 233261332 ? Ssl Mar09 3096:54 _ /usr/bin/mserver5 --dbpath=/local/monetdb/dbfarmBL/maizeFullGenomeDB --set merovingian_uri mapi:monetdb://cbsudc01:50000/maizeFullGenomeDB --set mapi_open false --set mapi_port 0 --set mapi_usock /local/monetdb/dbfarmBL/maizeFullGenomeDB/.mapi.sock --set monet_vault_key /local/monetdb/dbfarmBL/maizeFullGenomeDB/.vaultkey --set gdk_nr_threads 24 --set max_clients 64 --set sql_optimizer default_pipe --set monet_daemon yes [lcj34@cbsudc01 ~]$

I create the table using this command from a .sql file:

CREATE TABLE fullGenomeStable (chr int, pos int, ref_allele tinyint,two_bp_upstream int, two_bp_downstream int, three_bp_upstream int, three_bp_center int, three_bp_downstream int, four_bp_upstream int, four_bp_downstream int, five_bp_upstream int,five_bp_center int, five_bp_downstream int,gc_content tinyint,gerp_neutral_tree_length real, gerp_score real, gerp_non_negative_score real, gerp_conserved boolean, cMethylation real, cpg_methylation_1bk_moving_average real, chg_methylation_1bk_moving_average real, chh_methylation_1bk_moving_average real, mnase_low_minus_high_rpm_shoots real, mnase_bayes_factor_shoots real, mnase_hotspot_shoots boolean, mnase_low_minus_high_rpm_roots real, mnase_bayes_factor_roots real, mnase_hotspot_roots boolean, allele_total_depth int, site_presence int, conservedgene_presence boolean, minor_allele tinyint, maf real, combined_site_uniqueness_score real, site_uniqueness_score_sd real,expected_invariant_site boolean,codon_position tinyint,within_cds boolean, within_gene boolean, within_transcript boolean, within_exon boolean, within_five_prime_utr boolean, within_three_prime_utr boolean, within_mirna boolean, line_family int, sine_family int, ltr_family int, helitron boolean, carot_common_support_interval tinyint, toco_common_support_interval tinyint,B73_uniqueness_score real, CML247_uniqueness_score real, PH207_uniqueness_score real, W22_uniqueness_score real,Coelorachis_anc tinyint, Vossia_anc tinyint, Sorghum_anc tinyint, Oryza_anc tinyint, Setaria_anc tinyint, Brachypodium_anc tinyint, Hordeum_anc tinyint, Musa_anc tinyint, populus_anc tinyint, vitis_anc tinyint, arabidopsis_anc tinyint, panicum_anc tinyint,hmp32CDS_alt_allele tinyint, hmp32CDS_minor_allele tinyint, hmp32CDS_maf real, hmp32CDS_site_depth int, hmp32CDS_hetCount int, hmp32CDS_mutation_class tinyint, hmp32CDS_sift real, eli_nam_xo_mean real, eli_nam_xo_corrected real, Ames_BLUP_DTA_cand50kbp_top79_add_p smallint,Ames_BLUP_DTA_cand50kbp_top918_add_p smallint,Ames_BLUP_DTS_cand50kbp_top79_add_p smallint,Ames_BLUP_DTS_cand50kbp_top918_add_p smallint,Ames_QOnly_DTA_cand50kbp_top79_add_p smallint,Ames_QOnly_DTA_cand50kbp_top918_add_p smallint,Ames_QOnly_DTS_cand50kbp_top79_add_p smallint, Ames_QOnly_DTS_cand50kbp_top918_add_p smallint, cand_Yongxiang_add_p smallint,cand_DongPlusZCN_add_p smallint,H3K27me2_DevEars_peak boolean, H3K27me2_DevEars_rpm real, H3K27me2_DevEars_minuslog10qvalue real, H3K27me3_Root_peak boolean, H3K27me3_Root_rpm real, H3K27me3_Root_minuslog10qvalue real, H3K27me3_Shoot_highconf_peak boolean, H3K27me3_Shoot_highconf_peaks_rpm real, H3K27me3_Shoot_highconf_peak_qvalue real, H3K36me3_Shoot_peak boolean, H3K36me3_Shoot_rpm real, H3K36me3_Shoot_minuslog10qvalue real, H3K4me3_Base_peak boolean, H3K4me3_Base_rpm real, H3K4me3_Base_minuslog10qvalue real, H3K4me3_Maturing_peak boolean, H3K4me3_Maturing_rpm real, H3K4me3_Maturing_minuslog10qvalue real, H3K4me3_Root_peak boolean, H3K4me3_Root_rpm real, H3K4me3_Root_minuslog10qvalue real, H3K4me3_Shoot_peak boolean, H3K4me3_Shoot_rpm real, H3K4me3_Shoot_minuslog10qvalue real, H3K9ac_Base_peak boolean, H3K9ac_Base_rpm real, H3K9ac_Base_minuslog10qvalue real, H3K9ac_Maturing_peak boolean, H3K9ac_Maturing_rpm real, H3K9ac_Maturing_minuslog10qvalue real, H3K9ac_Root_peak boolean, H3K9ac_Root_rpm real, H3K9ac_Root_minuslog10qvalue real, H3K9ac_Shoot_peak boolean, H3K9ac_Shoot_rpm real, H3K9ac_Shoot_minuslog10qvalue real, H3K9me2_DevEars_peak boolean, H3K9me2_DevEars_rpm real, H3K9me2_DevEars_minuslog10qvalue real, H3K9me2_Seedlings_peak boolean, H3K9me2_Seedlings_rpm real, H3K9me2_Seedlings_minuslog10qvalue real, SW_maxScore_sorghum real, SW_meanScore_sorghum real, SW_maxScore_rice real, SW_meanScore_rice real, SW_NumberOfAlign_sorghum int, SW_NumberOfAlign_rice int, ZmAGPv3_highconf_tss boolean, ZmAGPv3_highconf_tss_strand int);

And copy using this from a .sql file: COPY binary into fullGenomeStable from ('/workdir/lcj34/monetdbFiles/fullGenome_binariesToLoad/chrFile.bin', (etc) )

Thanks - Lynn

On 3/9/17, 5:35 PM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn,

to understand your problem in more detail, could you possibly share the schema of your table (i.e., the types of all columns), as well as the output of the following two commands on your machine:

df -h ps auxwwwf | grep mserver5

These would help me/us to understand where your data and dbfarm are located.

Feel free to send these info by private to me in case you think this list is "too public" --- I'd appreciate, though, if you'd to keep the general discussion on this list for others to jump in and/or benefit.

Best, Stefan

----- On Mar 9, 2017, at 6:15 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> Regarding the “copy” suggesiont. I have a folder > /workdir/lcj34/monetdb/savedBinaries_fullGenome and a folder > /workdir/lcj34/monetdb/fullGenome_binariesToLoad. > > There are 2 folders because monetdb used to delete (steal) the binaries when it > was done copying. Because I always drop the table and reload it, the “saved” > dir has all the previously working binaries. I add the new ones to it, copy > everything to the folder from which they will be loaded, and copy from there. > There are no differences in the files, and they are on the same file system, > same disk, etc. > > I appreciate your help with this. > > On 3/9/17, 12:00 PM, "users-list on behalf of Stefan Manegold" > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > Stefan.Manegold@cwi.nl> wrote: > > > Hi Lynn, > > see my comments inlined below > > ----- On Mar 9, 2017, at 5:50 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > HI Stefan, Martin – > > > > We are open to any suggestions. > > > > Stephan, I understand your reasons for making the change from “steal” to “copy”. > > Is it possible to have both options? > > We might consider that ... > > > Before loading the new tables I do a > > “copy *.bin” from the “saved” directory to the “loading” directory. This > > takes less than an hour. Are there multiple steps involved in the monetdb > > copy that results in it not finishing overnight? > > are your "saved" and "loading" directories on different file systems (or even > different I/O sub systems / disks/ raids? > Could you try loading directly from the "Saved" rather than copying to the > "loading" > and loading from there? > That might help (a lot?) in case "saved" is on another i/o subsystem than you > dbfarm. > while "loading" is on the same as your dbfarm ... > > > From the perspective of a user that deals with very large data, a big selling > > point of monetdb is the ability to create data a column at a time and load > > using the COPY BINARY command. I have often bragged on monetdb’s ability to > > reload the entire table of 2 billion rows, 100+ columns in 3-4 minutes. I > > would still brag if it took an hour. > > > > Our data base contains a row for each of the 2 billion positions on the maize > > genome. Each row has a chromosome and position column as well as many other > > traits scored for this position. We expect to continue to add columns as more > > data is curated. The data is then used for genomic analysis. While I’m not > > presented with new data every week, I generally gather data once a month that > > needs to be added. Having the db down for several days to reload can be > > problematic. > > In fact, I recently made the case in the group that we need (more) efficient > way(s) > to extend a table by one (or more) column(s) an load extra data in that > column(s) > efficiently (i.e., without for loading into a new table and that copying into > the > extended table using a "alter table" with a join ...). > Your senario confirms that we should increase the priority to pursue this > direction; > now only find the time / manpower to implement it ... > > > Let me know if there is other data I can collect to help analyze this problem. > > I might / will, but need some more time (I'm "on the road" as we speak ...) > > Best, > Stefan > > > Thanks - Lynn > > > > On 3/9/17, 7:49 AM, "users-list on behalf of Martin Kersten" > > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > martin@monetdb.org> wrote: > > > > Hi Stefan, Lynn > > > > One way to move forward is to move the old functionality into the DataVault. > > There a directory of MonetDB compliant binary data can potentially be > > handled without the overhead incurred by copying. > > > > > > On 09/03/2017 13:41, Stefan Manegold wrote: > > > Hi Lynn, > > > > > > I understood and understand that your machine exclusively used by MonetDB. > > > > > > My question was more to investigate (using iostat & top) what CPU and/or IO > > > load MonetDB creates while loading the data, in other words trying to understand > > > whether the experienced performace degradiation can be realted to either > > > increased > > > I/O or CPU activity. > > > Afterall, copying 726 GB does cost some time, expecially since this does not > > > fit into RAM (and thus into file system caches) ... > > > > > > I also understood and understand that you're loading into empty tables. > > > The resaons for us to discontinue the "move feature" include that > > > (a) it only worked if both to be loaded data and dbfarm are on the same > > > filesystem, > > > (b) and it only worked when loading into empty tables. > > > While both seem yo (must) hold for you to get the fast performance, they do not > > > hold in many other cases. Moreover, people ded not like the fact the (some of) > > > the files they loaded from "were gone" after the load, even if the load did not > > > succeed ... > > > > > > You could consider downgrading (well, most probabaly you'd then first have to > > > dump > > > your entire DB(s) and then re-load them after the downgrade), > > > but it's very unlikely, that we revive the move feature. > > > hence, you'd never be able to upgrade again. > > > > > > I'd prefer that we could understand where the significant degrading comes from, > > > and whether it's something we could fix ... > > > > > > Best, > > > Stefan > > > > > > ps: you dbfarm is not necesarily where your monetdb installation (program) is, > > > but rather where you find the merovingian.log > > > > > > ----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > > > >> HI Stephen - > > >> > > >> Copying the machine administrators on this email. > > >> > > >> The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 > > >> storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. > > >> When I run “top” I see nothing little else running on it. No other IO going > > >> on. The access to this machine is limited and I generally know who other than > > >> myself is on it. > > >> > > >> Before loading new columns I ALWAYs do a “drop table”. Then I run “create table > > >> …” with the new columns added to the list. Follow this with a “COPY BINARY > > >> INTO …” . So I monetdb is always loading into an empty table. > > >> > > >> On disk, the binary files take up 726G of space. > > >> > > >> Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives > > >> in /local/monetdb. Are these the same file system? > > >> > > >> Stephen: I the last load command around 3:30 yesterday afternoon and it was > > >> still not complete this morning. We’ve been very happy with monetdb, but this > > >> is quite troubling. Is it a problem to downgrade the monetdb load from > > >> dec2016-SP2 back to jun2016-sp2? > > >> > > >> Thanks - Lynn > > >> > > >> On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" > > >> <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > >> Stefan.Manegold@cwi.nl> wrote: > > >> > > >> Hi Lynn Carol, > > >> > > >> COPY BINARY INTO now indeed copies the data, > > >> and that can add some cost to it, > > >> in particular in case your data is large > > >> and your dbfarm is on the same I/O system, > > >> or even the same (single) disk. > > >> > > >> To better understand your situation, > > >> could you possibly share how big your (binary) data > > >> is that you load (i.e., how much diskspace do the 135 files > > >> with 2 billions values each occupy), > > >> and whether you I/O system is a single hard disk, or a RAID system, > > >> or an SSD? > > >> Also, how much RAM does you machine have? > > >> > > >> Given that you used to exploit the old COPY BINARY INTO's "move feature", > > >> I assume your data and your dbfarm are on the same filesystem. > > >> > > >> (NB. in the old version, we could do the "move trick" only when bulk-loading > > >> into an empty table; when loading more data into a non-empty table, we also > > >> had to copy the data ...) > > >> > > >> In case your machine has more than one filesystem, each on a different > > >> hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one > > >> and your dbfarm on the other, spreading the I/O load over both > > >> (one mostly reading, the other mostly writing). > > >> > > >> You can also inspect you systems I/O activity during the load, e.g., > > >> using iostat. > > >> > > >> Best, > > >> Stefan > > >> > > >> ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > >> > > >> > BTW, my 135 binary files together are 726G. I note the dec2016 release says: > > >> > > > >> > > > >> > > > >> > BATattach now copies the input file instead of "stealing" it. > > >> > > > >> > > > >> > > > >> > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > > >> > files and monetdb are on the same machine – no network access. And “top” shows > > >> > nothing of significance running on the machine except mserver5. > > >> > > > >> > > > >> > > > >> > I loved the speed with which I could add new columns to my table by dropping it, > > >> > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > > >> > back, or an idea on what could be wrong. > > >> > > > >> > > > >> > > > >> > Thanks - Lynn > > >> > > > >> > > > >> > > > >> > > > >> > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > >> > Lynn Carol Johnson lcj34@cornell.edu > > >> > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > > >> > Date: Wednesday, March 8, 2017 at 3:19 PM > > >> > To: Communication channel for MonetDB users users-list@monetdb.org > > >> > Subject: DEC2016-SP2 and BINARY bulk load > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > Hi all – > > >> > > > >> > > > >> > > > >> > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > > >> > genetic data into a monetdb table. With 135 columns, it has been blindingly > > >> > fast. > > >> > > > >> > > > >> > > > >> > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > > >> > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > > >> > it could clean up properly). I then started the load again, thinking perhaps > > >> > the problem was related to the new columns I was adding. > > >> > > > >> > I have since dropped the table and remade it using the same data and scripts > > >> > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > > >> > really chugging along – I’m up to 30 minutes and counting. I don’t have access > > >> > to the sql log files, but the Merovingian.log shows nothing. > > >> > > > >> > > > >> > > > >> > I do notice that previously the binary files, once loaded, were removed from the > > >> > loading directly. This does not happen now. Were these files previously “moved” > > >> > and now they are copied? > > >> > > > >> > > > >> > > > >> > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > > >> > Commands? > > >> > > > >> > > > >> > > > >> > Thanks - Lynn > > >> > > > >> > _______________________________________________ > > >> > users-list mailing list > > >> > users-list@monetdb.org > > >> > https://www.monetdb.org/mailman/listinfo/users-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) | > > >> _______________________________________________ > > >> 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 > > > > > > > _______________________________________________ > > 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 > > -- > | Stefan.Manegold@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@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

-- | Stefan.Manegold@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@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

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

Martin et al.,

I guess I'd perfer an option / switch to CPOY BINAY INTO than a data vault ...

Stefan

----- On Mar 9, 2017, at 1:49 PM, Martin Kersten martin@monetdb.org wrote:

Hi Stefan, Lynn

One way to move forward is to move the old functionality into the DataVault. There a directory of MonetDB compliant binary data can potentially be handled without the overhead incurred by copying.

On 09/03/2017 13:41, Stefan Manegold wrote:

...

Hi Lynn,

I understood and understand that your machine exclusively used by MonetDB.

My question was more to investigate (using iostat & top) what CPU and/or IO load MonetDB creates while loading the data, in other words trying to understand whether the experienced performace degradiation can be realted to either increased I/O or CPU activity. Afterall, copying 726 GB does cost some time, expecially since this does not fit into RAM (and thus into file system caches) ...

I also understood and understand that you're loading into empty tables. The resaons for us to discontinue the "move feature" include that (a) it only worked if both to be loaded data and dbfarm are on the same filesystem, (b) and it only worked when loading into empty tables. While both seem yo (must) hold for you to get the fast performance, they do not hold in many other cases. Moreover, people ded not like the fact the (some of) the files they loaded from "were gone" after the load, even if the load did not succeed ...

You could consider downgrading (well, most probabaly you'd then first have to dump your entire DB(s) and then re-load them after the downgrade), but it's very unlikely, that we revive the move feature. hence, you'd never be able to upgrade again.

I'd prefer that we could understand where the significant degrading comes from, and whether it's something we could fix ...

Best, Stefan

ps: you dbfarm is not necesarily where your monetdb installation (program) is, but rather where you find the merovingian.log

----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

HI Stephen -

Copying the machine administrators on this email.

The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. When I run “top” I see nothing little else running on it. No other IO going on. The access to this machine is limited and I generally know who other than myself is on it.

Before loading new columns I ALWAYs do a “drop table”. Then I run “create table …” with the new columns added to the list. Follow this with a “COPY BINARY INTO …” . So I monetdb is always loading into an empty table.

On disk, the binary files take up 726G of space.

Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives in /local/monetdb. Are these the same file system?

Stephen: I the last load command around 3:30 yesterday afternoon and it was still not complete this morning. We’ve been very happy with monetdb, but this is quite troubling. Is it a problem to downgrade the monetdb load from dec2016-SP2 back to jun2016-sp2?

Thanks - Lynn

On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn Carol,

COPY BINARY INTO now indeed copies the data, and that can add some cost to it, in particular in case your data is large and your dbfarm is on the same I/O system, or even the same (single) disk.

To better understand your situation, could you possibly share how big your (binary) data is that you load (i.e., how much diskspace do the 135 files with 2 billions values each occupy), and whether you I/O system is a single hard disk, or a RAID system, or an SSD? Also, how much RAM does you machine have?

Given that you used to exploit the old COPY BINARY INTO's "move feature", I assume your data and your dbfarm are on the same filesystem.

(NB. in the old version, we could do the "move trick" only when bulk-loading into an empty table; when loading more data into a non-empty table, we also had to copy the data ...)

In case your machine has more than one filesystem, each on a different hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one and your dbfarm on the other, spreading the I/O load over both (one mostly reading, the other mostly writing).

You can also inspect you systems I/O activity during the load, e.g., using iostat.

Best, Stefan

----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

BTW, my 135 binary files together are 726G. I note the dec2016 release says:

BATattach now copies the input file instead of "stealing" it.

Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My files and monetdb are on the same machine – no network access. And “top” shows nothing of significance running on the machine except mserver5.

I loved the speed with which I could add new columns to my table by dropping it, re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this back, or an idea on what could be wrong.

Thanks - Lynn

From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Lynn Carol Johnson lcj34@cornell.edu Reply-To: Communication channel for MonetDB users users-list@monetdb.org Date: Wednesday, March 8, 2017 at 3:19 PM To: Communication channel for MonetDB users users-list@monetdb.org Subject: DEC2016-SP2 and BINARY bulk load

Hi all –

I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row genetic data into a monetdb table. With 135 columns, it has been blindingly fast.

Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so it could clean up properly). I then started the load again, thinking perhaps the problem was related to the new columns I was adding.

I have since dropped the table and remade it using the same data and scripts that worked in just over 3 minutes in February on the jun2016-SP2 load. It is really chugging along – I’m up to 30 minutes and counting. I don’t have access to the sql log files, but the Merovingian.log shows nothing.

I do notice that previously the binary files, once loaded, were removed from the loading directly. This does not happen now. Were these files previously “moved” and now they are copied?

Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. Commands?

Thanks - Lynn

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-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) | _______________________________________________ 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

---

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

Oh – and here is iostat:

[lcj34@cbsudc01 monetdb_problems]$ iostat Linux 3.10.0-229.el7.x86_64 (cbsudc01) 03/09/2017 _x86_64_ (24 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle 0.82 0.00 1.31 0.11 0.00 97.76

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda 0.57 4.96 5.16 2499854 2600556 sdb 51.16 7272.50 4965.66 3663544367 2501465522 sdc 0.00 0.01 0.03 5919 16872 sdd 0.00 0.01 0.03 2582 15404 md0 0.00 0.01 0.06 5489 32276 dm-0 0.00 0.00 0.00 1472 0

[lcj34@cbsudc01 monetdb_problems]$

On 3/9/17, 7:41 AM, "users-list on behalf of Stefan Manegold" <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of Stefan.Manegold@cwi.nl> wrote:

Hi Lynn,

I understood and understand that your machine exclusively used by MonetDB.

My question was more to investigate (using iostat & top) what CPU and/or IO load MonetDB creates while loading the data, in other words trying to understand whether the experienced performace degradiation can be realted to either increased I/O or CPU activity. Afterall, copying 726 GB does cost some time, expecially since this does not fit into RAM (and thus into file system caches) ...

I also understood and understand that you're loading into empty tables. The resaons for us to discontinue the "move feature" include that (a) it only worked if both to be loaded data and dbfarm are on the same filesystem, (b) and it only worked when loading into empty tables. While both seem yo (must) hold for you to get the fast performance, they do not hold in many other cases. Moreover, people ded not like the fact the (some of) the files they loaded from "were gone" after the load, even if the load did not succeed ...

You could consider downgrading (well, most probabaly you'd then first have to dump your entire DB(s) and then re-load them after the downgrade), but it's very unlikely, that we revive the move feature. hence, you'd never be able to upgrade again.

I'd prefer that we could understand where the significant degrading comes from, and whether it's something we could fix ...

Best, Stefan

ps: you dbfarm is not necesarily where your monetdb installation (program) is, but rather where you find the merovingian.log

----- On Mar 9, 2017, at 1:26 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

> HI Stephen - > > Copying the machine administrators on this email. > > The machine on which monetdb lives has 256GB RAM, 24 fast cores, 16TB of RAID10 > storage and 1TB of SSD scratch space. This machine is used mostly for monetdb. > When I run “top” I see nothing little else running on it. No other IO going > on. The access to this machine is limited and I generally know who other than > myself is on it. > > Before loading new columns I ALWAYs do a “drop table”. Then I run “create table > …” with the new columns added to the list. Follow this with a “COPY BINARY > INTO …” . So I monetdb is always loading into an empty table. > > On disk, the binary files take up 726G of space. > > Jarek – I load from a directory in /workdir/lcj34/monetdbFiles. Monetdb lives > in /local/monetdb. Are these the same file system? > > Stephen: I the last load command around 3:30 yesterday afternoon and it was > still not complete this morning. We’ve been very happy with monetdb, but this > is quite troubling. Is it a problem to downgrade the monetdb load from > dec2016-SP2 back to jun2016-sp2? > > Thanks - Lynn > > On 3/9/17, 2:00 AM, "users-list on behalf of Stefan Manegold" > <users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > Stefan.Manegold@cwi.nl> wrote: > > Hi Lynn Carol, > > COPY BINARY INTO now indeed copies the data, > and that can add some cost to it, > in particular in case your data is large > and your dbfarm is on the same I/O system, > or even the same (single) disk. > > To better understand your situation, > could you possibly share how big your (binary) data > is that you load (i.e., how much diskspace do the 135 files > with 2 billions values each occupy), > and whether you I/O system is a single hard disk, or a RAID system, > or an SSD? > Also, how much RAM does you machine have? > > Given that you used to exploit the old COPY BINARY INTO's "move feature", > I assume your data and your dbfarm are on the same filesystem. > > (NB. in the old version, we could do the "move trick" only when bulk-loading > into an empty table; when loading more data into a non-empty table, we also > had to copy the data ...) > > In case your machine has more than one filesystem, each on a different > hard disk / SSD / RAID, you coudl try to have your to-be-loaded data on one > and your dbfarm on the other, spreading the I/O load over both > (one mostly reading, the other mostly writing). > > You can also inspect you systems I/O activity during the load, e.g., > using iostat. > > Best, > Stefan > > ----- On Mar 8, 2017, at 10:12 PM, Lynn Carol Johnson lcj34@cornell.edu wrote: > > > BTW, my 135 binary files together are 726G. I note the dec2016 release says: > > > > > > > > BATattach now copies the input file instead of "stealing" it. > > > > > > > > Could this be why it’s gone from 3 minutes to over 3 hours to load this data? My > > files and monetdb are on the same machine – no network access. And “top” shows > > nothing of significance running on the machine except mserver5. > > > > > > > > I loved the speed with which I could add new columns to my table by dropping it, > > re-create the table, COPY BINARY INTO table. Hoping you have ideas to get this > > back, or an idea on what could be wrong. > > > > > > > > Thanks - Lynn > > > > > > > > > > From: users-list users-list-bounces+lcj34=cornell.edu@monetdb.org on behalf of > > Lynn Carol Johnson lcj34@cornell.edu > > Reply-To: Communication channel for MonetDB users users-list@monetdb.org > > Date: Wednesday, March 8, 2017 at 3:19 PM > > To: Communication channel for MonetDB users users-list@monetdb.org > > Subject: DEC2016-SP2 and BINARY bulk load > > > > > > > > > > > > Hi all – > > > > > > > > I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row > > genetic data into a monetdb table. With 135 columns, it has been blindingly > > fast. > > > > > > > > Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads > > are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so > > it could clean up properly). I then started the load again, thinking perhaps > > the problem was related to the new columns I was adding. > > > > I have since dropped the table and remade it using the same data and scripts > > that worked in just over 3 minutes in February on the jun2016-SP2 load. It is > > really chugging along – I’m up to 30 minutes and counting. I don’t have access > > to the sql log files, but the Merovingian.log shows nothing. > > > > > > > > I do notice that previously the binary files, once loaded, were removed from the > > loading directly. This does not happen now. Were these files previously “moved” > > and now they are copied? > > > > > > > > Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. > > Commands? > > > > > > > > Thanks - Lynn > > > > _______________________________________________ > > users-list mailing list > > users-list@monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-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) | > _______________________________________________ > 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

-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list