How exactly do you load your data (via RJDBC)? A single COPY 600000000 RECORDS INTO <table> FROM <file> USING DELIMITERS <...>; or a single INSERT statement per row? Does your table have constraints (keys, NOT NULL, ...) defined? Stefan On Thu, Mar 12, 2009 at 01:07:00AM -0700, Yue Sheng wrote:

I have a new 1TB on the Machine, it has more than 800GB free; much more than what's needed... So that cannot be the problem.

On Thu, Mar 12, 2009 at 12:26 AM, Martin Kersten wrote:

...

Yue Sheng wrote:

...

I'm been trying to insert from file for over 322 files, totally 12GB with over 600 million rows. The RJDBC connection hangs after 350 million row. I noticed the VSIZE in Activity Monitor is 30.85GB, closer to 32GB system memory. Is this the reason that I cannot connect to the database? What should I do?

Error message I get from MonetDB server:

# MonetDB server v5.8.4, based on kernel v1.26.4 # Serving database 'bigdata', using 8 threads # Compiled for i386-apple-darwin9.6.0/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008- MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /Users/someuser/MonetDB64/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ http://127.0.0.1:50000/ # MonetDB/SQL module v2.26.4 loaded

...

!ERROR: GDKsave: failed name=24/2460, ext=tail, mode 0 !ERROR: GDKsave: failed name=23/2317, ext=tail, mode 0 !ERROR: GDKsave: failed name=25/2503, ext=tail, mode 0 !ERROR: GDKsave: failed name=26/2657, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2274, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2224, ext=tail, mode 0 !ERROR: GDKsave: failed name=01/112, ext=head, mode 0 !ERROR: logger_exit: could not open /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log This looks as if the device on which you attempt to write is full.

System: MacPro Dual Quad Xenon Leopard OS X 32GB RAM R 2.8.1 32Bit MonetDB v5.8.4 complied for i386-apple-darmin9.6.0/64bit

Many thanks.

Yuesheng

------------------------------------------------------------------------

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

could it be due to sql config settings? need bigger log file size perhaps? just guessing now... On Sat, Mar 14, 2009 at 8:56 PM, Yue Sheng wrote:

I don't have any indexing on the table.

On Thu, Mar 12, 2009 at 1:24 AM, Stefan Manegold wrote:

...

How exactly do you load your data (via RJDBC)? A single COPY 600000000 RECORDS INTO <table> FROM <file> USING DELIMITERS <...>; or a single INSERT statement per row?

Does your table have constraints (keys, NOT NULL, ...) defined?

Stefan

...

I have a new 1TB on the Machine, it has more than 800GB free; much more

On Thu, Mar 12, 2009 at 01:07:00AM -0700, Yue Sheng wrote: than

...

what's needed... So that cannot be the problem.

On Thu, Mar 12, 2009 at 12:26 AM, Martin Kersten

...

Yue Sheng wrote:

...

I'm been trying to insert from file for over 322 files, totally 12GB with over 600 million rows. The RJDBC connection hangs after 350 million row. I noticed the VSIZE in Activity Monitor is 30.85GB, closer to 32GB system memory. Is this the reason that I cannot connect to the database? What should I do?

Error message I get from MonetDB server:

# MonetDB server v5.8.4, based on kernel v1.26.4 # Serving database 'bigdata', using 8 threads # Compiled for i386-apple-darwin9.6.0/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008- MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /Users/someuser/MonetDB64/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb:// 127.0.0.1:50000/ http://127.0.0.1:50000/ # MonetDB/SQL module v2.26.4 loaded

...

!ERROR: GDKsave: failed name=24/2460, ext=tail, mode 0 !ERROR: GDKsave: failed name=23/2317, ext=tail, mode 0 !ERROR: GDKsave: failed name=25/2503, ext=tail, mode 0 !ERROR: GDKsave: failed name=26/2657, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2274, ext=tail, mode 0 !ERROR: GDKsave: failed name=22/2224, ext=tail, mode 0 !ERROR: GDKsave: failed name=01/112, ext=head, mode 0 !ERROR: logger_exit: could not open /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log This looks as if the device on which you attempt to write is full.

System: MacPro Dual Quad Xenon Leopard OS X 32GB RAM R 2.8.1 32Bit MonetDB v5.8.4 complied for i386-apple-darmin9.6.0/64bit

Many thanks.

Yuesheng

---

...

...

...

---

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

---

...

...

...

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Anyone has any tips please? This is halting our whole development. Please help! Thanks! On Mon, Mar 16, 2009 at 9:58 AM, Yue Sheng wrote:

625GB available. Way bigger than the data set. So disk space no problem

On Mon, Mar 16, 2009 at 9:56 AM, Yue Sheng wrote:

...

could it be due to sql config settings? need bigger log file size perhaps? just guessing now...

On Sat, Mar 14, 2009 at 8:56 PM, Yue Sheng wrote:

...

I don't have any indexing on the table.

On Thu, Mar 12, 2009 at 1:24 AM, Stefan Manegold

...

wrote:

...

How exactly do you load your data (via RJDBC)? A single COPY 600000000 RECORDS INTO <table> FROM <file> USING DELIMITERS <...>; or a single INSERT statement per row?

Does your table have constraints (keys, NOT NULL, ...) defined?

Stefan

On Thu, Mar 12, 2009 at 01:07:00AM -0700, Yue Sheng wrote:

...

I have a new 1TB on the Machine, it has more than 800GB free; much more than what's needed... So that cannot be the problem.

On Thu, Mar 12, 2009 at 12:26 AM, Martin Kersten < Martin.Kersten@cwi.nl>wrote:

...

Yue Sheng wrote: > I'm been trying to insert from file for over 322 files, totally 12GB > with over 600 million rows. The RJDBC connection hangs after 350 million > row. I noticed the VSIZE in Activity Monitor is 30.85GB, closer to 32GB > system memory. Is this the reason that I cannot connect to the database? > What should I do? > > Error message I get from MonetDB server: > > # MonetDB server v5.8.4, based on kernel v1.26.4 > # Serving database 'bigdata', using 8 threads > # Compiled for i386-apple-darwin9.6.0/64bit with 64bit OIDs dynamically > linked > # Copyright (c) 1993-July 2008 CWI. > # Copyright (c) August 2008- MonetDB B.V., all rights reserved > # Visit http://monetdb.cwi.nl/ for further information > #warning: please don't forget to set your vault key! > #(see /Users/someuser/MonetDB64/etc/monetdb5.conf) > # Listening for connection requests on mapi:monetdb:// 127.0.0.1:50000/ > http://127.0.0.1:50000/ > # MonetDB/SQL module v2.26.4 loaded > >!ERROR: GDKsave: failed name=24/2460, ext=tail, mode 0 > !ERROR: GDKsave: failed name=23/2317, ext=tail, mode 0 > !ERROR: GDKsave: failed name=25/2503, ext=tail, mode 0 > !ERROR: GDKsave: failed name=26/2657, ext=tail, mode 0 > !ERROR: GDKsave: failed name=22/2274, ext=tail, mode 0 > !ERROR: GDKsave: failed name=22/2224, ext=tail, mode 0 > !ERROR: GDKsave: failed name=01/112, ext=head, mode 0 > !ERROR: logger_exit: could not open > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log This looks as if the device on which you attempt to write is full. > > > > System: > MacPro Dual Quad Xenon > Leopard OS X > 32GB RAM > R 2.8.1 32Bit > MonetDB v5.8.4 complied for i386-apple-darmin9.6.0/64bit > > > Many thanks. > > Yuesheng > > >

---

...

...

> >

---

...

...

> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > >

---

...

...

> > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

This is the loop I used, as I'm inserting a file at a time. drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver", "/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar") conn <- dbConnect(drv, "jdbc:monetdb://localhost/bigdata","username", "password") for (i in 1:length(filenames)) { str <- paste("copy into usrtable from '", wholename[i],"' using delimiters '|';", sep="") try(dbSendUpdate(conn, str)) } where filesnames are file names and wholename are same as filenames but has directory info as well. Thanks. On Thu, Mar 12, 2009 at 3:27 AM, Tom H wrote:

Yue Sheng wrote:

...

> !ERROR: logger_exit: could not open > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log This looks as if the device on which you attempt to write is full.

How big is the /Users partition on the disk? # df -h

Tom

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

I could give an upper bound but cannot give absolute. Because I used error suppression in my R code such that it "skips" row that give insert error (such as missing column). This suppression method seems to work well for all other inserts. So shouldn't be the cause of problem Would it be possible to tell MonteDB to "pre-allocate" more memory then it currently does? the files have a upper bound of how much rows it will have. Thanks. On Mon, Mar 16, 2009 at 10:20 AM, Stefan Manegold wrote:

On Thu, Mar 12, 2009 at 09:15:17AM -0700, Yue Sheng wrote:

...

This is the loop I used, as I'm inserting a file at a time.

drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver", "/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar")

conn <- dbConnect(drv, "jdbc:monetdb://localhost/bigdata","username", "password")

for (i in 1:length(filenames)) {

str <- paste("copy into usrtable from '", wholename[i],"' using delimiters '|';", sep="")

try(dbSendUpdate(conn, str))

}

Could you try giving the first "copy into" a hint of how many records you plan to insert in total, e.g.,

COPY <N> RECORDS INTO ...

where <N> is the sum of all records from all files you load,

(and optionally give each subsequent "copy into" likewise a hint of how many records the respective file holds).

This could help MonetDB memory management ...

Stefan

...

where filesnames are file names and wholename are same as filenames but

has

...

directory info as well.

Thanks.

On Thu, Mar 12, 2009 at 3:27 AM, Tom H wrote:

...

Yue Sheng wrote:

...

> !ERROR: logger_exit: could not open > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log This looks as if the device on which you attempt to write is full.

How big is the /Users partition on the disk? # df -h

Tom

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

(1) How do I tell MonetDB to use a lower bound for insert?(2) Do you think this might be the problem why I have problem inserting large files? Thanks. On Mon, Mar 16, 2009 at 11:08 AM, Stefan Manegold wrote:

On Mon, Mar 16, 2009 at 10:40:58AM -0700, Yue Sheng wrote:

...

I could give an upper bound but cannot give absolute. Because I used error suppression in my R code such that it "skips" row that give insert error (such as missing column). This suppression method seems to work well for all other inserts. So shouldn't be the cause of problem

Would it be possible to tell MonteDB to "pre-allocate" more memory then it currently does? the files have a upper bound of how much rows it will have.

giving an upper bound is possibile --- it might be counter-productive, though, in case it is significantly larger than the actual value; in that case, a close lower bound (if possible) might do a better job ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 10:20 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

On Thu, Mar 12, 2009 at 09:15:17AM -0700, Yue Sheng wrote:

...

This is the loop I used, as I'm inserting a file at a time.

drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver", "/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar")

conn <- dbConnect(drv, "jdbc:monetdb://localhost/bigdata","username", "password")

for (i in 1:length(filenames)) {

str <- paste("copy into usrtable from '", wholename[i],"' using delimiters '|';", sep="")

try(dbSendUpdate(conn, str))

}

Could you try giving the first "copy into" a hint of how many records you plan to insert in total, e.g.,

COPY <N> RECORDS INTO ...

where <N> is the sum of all records from all files you load,

(and optionally give each subsequent "copy into" likewise a hint of how many records the respective file holds).

This could help MonetDB memory management ...

Stefan

...

where filesnames are file names and wholename are same as filenames

but has

...

directory info as well.

Thanks.

On Thu, Mar 12, 2009 at 3:27 AM, Tom H wrote:

...

Yue Sheng wrote:

...

> !ERROR: logger_exit: could not open >

/Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log

...

This looks as if the device on which you attempt to write is full.

How big is the /Users partition on the disk? # df -h

Tom

---

...

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

---

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

I'm under the impression that <N> needs to be the exact number of rows in the file. So I can set <N> to anything? So what happens if (a) N is smaller than # of row of the file I try to insert (b) N is larger than # of row of the file I try to insert Sorry if these question seems naive... On Mon, Mar 16, 2009 at 12:23 PM, Stefan Manegold wrote:

On Mon, Mar 16, 2009 at 11:58:49AM -0700, Yue Sheng wrote:

...

(1) How do I tell MonetDB to use a lower bound for insert?

as I suggested (and just as you would set an upper bound): use COPY <N> RECORDS INTO ... with the first COPY INTO command (per table)

...

(2) Do you think this might be the problem why I have problem inserting large files? ^^^^^ yes, that's why I proposed to give MonetDB a hint about how many records you are about to insert. --- there might also be other reasons, though, but we can only tell more once we know the effect of giving the suggested hint ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 11:08 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

...

I could give an upper bound but cannot give absolute. Because I used error suppression in my R code such that it "skips" row that give insert error (such as missing column). This suppression method seems to work well for all other inserts. So shouldn't be the cause of problem

Would it be possible to tell MonteDB to "pre-allocate" more memory

On Mon, Mar 16, 2009 at 10:40:58AM -0700, Yue Sheng wrote: then it

...

currently does? the files have a upper bound of how much rows it will have.

giving an upper bound is possibile --- it might be counter-productive, though, in case it is significantly larger than the actual value; in that case, a close lower bound (if possible) might do a better job ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 10:20 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

On Thu, Mar 12, 2009 at 09:15:17AM -0700, Yue Sheng wrote:

...

This is the loop I used, as I'm inserting a file at a time.

drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver",

"/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar")

...

conn <- dbConnect(drv,

"jdbc:monetdb://localhost/bigdata","username",

...

"password")

for (i in 1:length(filenames)) {

str <- paste("copy into usrtable from '", wholename[i],"' using delimiters '|';", sep="")

try(dbSendUpdate(conn, str))

}

Could you try giving the first "copy into" a hint of how many records you plan to insert in total, e.g.,

COPY <N> RECORDS INTO ...

where <N> is the sum of all records from all files you load,

(and optionally give each subsequent "copy into" likewise a hint of how many records the respective file holds).

This could help MonetDB memory management ...

Stefan

...

where filesnames are file names and wholename are same as

filenames but has

...

directory info as well.

Thanks.

On Thu, Mar 12, 2009 at 3:27 AM, Tom H wrote:

> Yue Sheng wrote: > > > !ERROR: logger_exit: could not open > > > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log > > This looks as if the device on which you attempt to write > > is full. > > How big is the /Users partition on the disk? > # df -h > > Tom > > >

---

...

...

...

...

...

> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users >

...

---

...

...

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

---

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Thanks. That's clear. I'll give rough estimate as sum of upper bounds as N for first insert, then give individual upper bounds for subsequent inserts.... Will report result later.... On Mon, Mar 16, 2009 at 1:16 PM, Stefan Manegold wrote:

On Mon, Mar 16, 2009 at 01:02:08PM -0700, Yue Sheng wrote:

...

I'm under the impression that <N> needs to be the exact number of rows in the file. So I can set <N> to anything? So what happens if

(a) N is smaller than # of row of the file I try to insert

only N records will be inserted

...

(b) N is larger than # of row of the file I try to insert

room for N records will be allocated, but (obviously) only as many records as present in the file will be inserted.

that's why I suggested to have the first COPY INTO give the sum of all to be inserted files as N, to allocate enouch room for all coming record of this and all following COPY INTO statements (obviously per table), and hence avoids later (potentially expensive) storage space extensions; all subsequent COPY INTO statements then can (optionally) give the number of records from that file.

If loading (COPY INTO?) somehow omit some (few) records as you mentioned, the counts will be a slight over estimate, but that should be fine ...

Stefan

...

Sorry if these question seems naive...

On Mon, Mar 16, 2009 at 12:23 PM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

On Mon, Mar 16, 2009 at 11:58:49AM -0700, Yue Sheng wrote:

...

(1) How do I tell MonetDB to use a lower bound for insert?

as I suggested (and just as you would set an upper bound): use COPY <N> RECORDS INTO ... with the first COPY INTO command (per table)

...

(2) Do you think this might be the problem why I have problem inserting large files? ^^^^^ yes, that's why I proposed to give MonetDB a hint about how many records you are about to insert. --- there might also be other reasons, though, but we can only tell more once we know the effect of giving the suggested hint ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 11:08 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

...

I could give an upper bound but cannot give absolute. Because I used error suppression in my R code such that it "skips" row that give insert error (such as missing column). This suppression method seems to work well for all other inserts. So shouldn't be the cause of problem

Would it be possible to tell MonteDB to "pre-allocate" more memory

On Mon, Mar 16, 2009 at 10:40:58AM -0700, Yue Sheng wrote: then it

...

currently does? the files have a upper bound of how much rows it will have.

giving an upper bound is possibile --- it might be counter-productive, though, in case it is significantly larger than the actual value; in that case, a close lower bound (if possible) might do a better job ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 10:20 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

> On Thu, Mar 12, 2009 at 09:15:17AM -0700, Yue Sheng wrote: > > This is the loop I used, as I'm inserting a file at a time. > > > > > > drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver", > > "/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar") > > > > conn <- dbConnect(drv, "jdbc:monetdb://localhost/bigdata","username", > > "password") > > > > > > for (i in 1:length(filenames)) { > > > > str <- paste("copy into usrtable from '", wholename[i],"' using > delimiters > > '|';", sep="") > > > > try(dbSendUpdate(conn, str)) > > > > } > > > > Could you try giving the first "copy into" a hint of how many records you > plan to insert in total, e.g., > > COPY <N> RECORDS INTO ... > > where <N> is the sum of all records from all files you load, > > (and optionally give each subsequent "copy into" likewise a hint of how > many > records the respective file holds). > > This could help MonetDB memory management ... > > Stefan > > > > > where filesnames are file names and wholename are same as filenames but > has > > directory info as well. > > > > > > Thanks. > > > > > > > > On Thu, Mar 12, 2009 at 3:27 AM, Tom H

...

wrote:

...

...

...

> > > > > Yue Sheng wrote: > > > > > !ERROR: logger_exit: could not open > > > > > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log > > > > This looks as if the device on which you attempt to write > > > > is full. > > > > > > How big is the /Users partition on the disk? > > > # df -h > > > > > > Tom > > > > > > > > > >

---

...

...

...

...

...

> > > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > > > powering Web 2.0 with engaging, cross-platform capabilities. Quickly > and > > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > development > > > software that enables intelligent coding and step-through debugging. > > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > > _______________________________________________ > > > MonetDB-users mailing list > > > MonetDB-users@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > > >

---

...

...

...

...

...

> > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > development > > software that enables intelligent coding and step-through debugging. > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > MonetDB-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > -- > | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | > | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | > | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | > | The Netherlands | Fax : +31 (20) 592-4312 | > > >

---

...

...

...

...

...

> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users >

...

---

...

...

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

---

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

On Mon, Mar 16, 2009 at 04:15:53PM -0700, Yue Sheng wrote:

No Luck.

So I(i) allocated the upper bound (700 million) as in: COPY 700000000 RECORDS INTO .... the vsize went up to just less than 120GB then came back down to 60GB after the first insert (18K rows) was done

(ii) for all subsequent inserts, I give the upper bound for that *particular* file as N_i, i.e. COPY N_i RECORDS INTO ...

It worked fine for about 300 million inserts, then the whole things just hangs....

"hangs" --- or is busy with either CPU or I/O (swapping) or both?

No error message from server, no error message from R, just hangs... (VSize: 46.7GB, RSize: 3.12GB)

How much physical memory does your machine have? Did/could you try to exclude JDBC as a problem/bottleneck by just using "plain" mclient? Stefan

When I use SQuirreL is get line count, deadend (i.e. take forever to return a count) ....

On Mon, Mar 16, 2009 at 1:27 PM, Yue Sheng wrote:

...

Thanks. That's clear. I'll give rough estimate as sum of upper bounds as N for first insert, then give individual upper bounds for subsequent inserts.... Will report result later....

On Mon, Mar 16, 2009 at 1:16 PM, Stefan Manegold wrote:

...

On Mon, Mar 16, 2009 at 01:02:08PM -0700, Yue Sheng wrote:

...

I'm under the impression that <N> needs to be the exact number of rows in the file. So I can set <N> to anything? So what happens if

(a) N is smaller than # of row of the file I try to insert

only N records will be inserted

...

(b) N is larger than # of row of the file I try to insert

room for N records will be allocated, but (obviously) only as many records as present in the file will be inserted.

that's why I suggested to have the first COPY INTO give the sum of all to be inserted files as N, to allocate enouch room for all coming record of this and all following COPY INTO statements (obviously per table), and hence avoids later (potentially expensive) storage space extensions; all subsequent COPY INTO statements then can (optionally) give the number of records from that file.

If loading (COPY INTO?) somehow omit some (few) records as you mentioned, the counts will be a slight over estimate, but that should be fine ...

Stefan

...

Sorry if these question seems naive...

On Mon, Mar 16, 2009 at 12:23 PM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

...

On Mon, Mar 16, 2009 at 11:58:49AM -0700, Yue Sheng wrote:

...

(1) How do I tell MonetDB to use a lower bound for insert?

as I suggested (and just as you would set an upper bound): use COPY <N> RECORDS INTO ... with the first COPY INTO command (per table)

...

(2) Do you think this might be the problem why I have problem inserting large files? ^^^^^ yes, that's why I proposed to give MonetDB a hint about how many records you are about to insert. --- there might also be other reasons, though, but we can only tell more once we know the effect of giving the suggested hint ...

Stefan

...

Thanks.

On Mon, Mar 16, 2009 at 11:08 AM, Stefan Manegold < Stefan.Manegold@cwi.nl>wrote:

> On Mon, Mar 16, 2009 at 10:40:58AM -0700, Yue Sheng wrote: > > I could give an upper bound but cannot give absolute. Because I used > error > > suppression in my R code such that it "skips" row that give insert error > > (such as missing column). > > This suppression method seems to work well for all other inserts. So > > shouldn't be the cause of problem > > > > Would it be possible to tell MonteDB to "pre-allocate" more memory then > it > > currently does? the files have a upper bound of how much rows it will > have. > > giving an upper bound is possibile --- it might be counter-productive, > though, in case it is significantly larger than the actual value; in that > case, a close lower bound (if possible) might do a better job ... > > Stefan > > > Thanks. > > > > On Mon, Mar 16, 2009 at 10:20 AM, Stefan Manegold < > Stefan.Manegold@cwi.nl>wrote: > > > > > On Thu, Mar 12, 2009 at 09:15:17AM -0700, Yue Sheng wrote: > > > > This is the loop I used, as I'm inserting a file at a time. > > > > > > > > > > > > drv <- JDBC("nl.cwi.monetdb.jdbc.MonetDriver", > > > > "/Users/someuser/monetDB64/share/MonetDB/lib/monetdb-1.9-jdbc.jar") > > > > > > > > conn <- dbConnect(drv, "jdbc:monetdb://localhost/bigdata","username", > > > > "password") > > > > > > > > > > > > for (i in 1:length(filenames)) { > > > > > > > > str <- paste("copy into usrtable from '", wholename[i],"' using > > > delimiters > > > > '|';", sep="") > > > > > > > > try(dbSendUpdate(conn, str)) > > > > > > > > } > > > > > > > > > > Could you try giving the first "copy into" a hint of how many records > you > > > plan to insert in total, e.g., > > > > > > COPY <N> RECORDS INTO ... > > > > > > where <N> is the sum of all records from all files you load, > > > > > > (and optionally give each subsequent "copy into" likewise a hint of how > > > many > > > records the respective file holds). > > > > > > This could help MonetDB memory management ... > > > > > > Stefan > > > > > > > > > > > where filesnames are file names and wholename are same as filenames > but > > > has > > > > directory info as well. > > > > > > > > > > > > Thanks. > > > > > > > > > > > > > > > > On Thu, Mar 12, 2009 at 3:27 AM, Tom H < tom@limepepper.co.uk> wrote: > > > > > > > > > Yue Sheng wrote: > > > > > > > !ERROR: logger_exit: could not open > > > > > > > > /Users/someuser/MonetDB64/var/MonetDB5/sql_logs/bigdata/sql/log > > > > > > This looks as if the device on which you attempt to write > > > > > > is full. > > > > > > > > > > How big is the /Users partition on the disk? > > > > > # df -h > > > > > > > > > > Tom > > > > > > > > > > > > > > > > > > >

---

...

...

...

> > > > > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) > are > > > > > powering Web 2.0 with engaging, cross-platform capabilities. > Quickly > > > and > > > > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > > > development > > > > > software that enables intelligent coding and step-through > debugging. > > > > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > > > > _______________________________________________ > > > > > MonetDB-users mailing list > > > > > MonetDB-users@lists.sourceforge.net > > > > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > > > > > > > > > > > >

---

...

...

...

> > > > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) > are > > > > powering Web 2.0 with engaging, cross-platform capabilities. Quickly > and > > > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > > > development > > > > software that enables intelligent coding and step-through debugging. > > > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > > > _______________________________________________ > > > > MonetDB-users mailing list > > > > MonetDB-users@lists.sourceforge.net > > > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > > > > > -- > > > | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | > > > | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | > > > | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | > > > | The Netherlands | Fax : +31 (20) 592-4312 | > > > > > > > > > >

---

...

...

...

> > > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > > > powering Web 2.0 with engaging, cross-platform capabilities. Quickly > and > > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > development > > > software that enables intelligent coding and step-through debugging. > > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > > _______________________________________________ > > > MonetDB-users mailing list > > > MonetDB-users@lists.sourceforge.net > > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > > > >

---

...

...

...

> > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > > easily build your RIAs with Flex Builder, the Eclipse(TM)based > development > > software that enables intelligent coding and step-through debugging. > > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > > _______________________________________________ > > MonetDB-users mailing list > > MonetDB-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > -- > | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | > | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | > | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | > | The Netherlands | Fax : +31 (20) 592-4312 | > > >

---

...

...

...

> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users >

...

---

...

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

---

...

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

Also, no error this time on the monetdb server size. is there any diagnostic command i can do at the ">" prompt? Also, any config setting i should look into? On Mon, Mar 16, 2009 at 4:56 PM, Yue Sheng wrote:

no i/o, swapping, no activity in CPU monitor 32GB

haven't tried. manual loading >600 files then see when it break is an option, albert a painful one...

That's great, would you mind illustrating how to deal with (1) having mixture of text and .gz files in the same directory (2) dealing with error (mostly the rare encounter of "non-standard" columns) both (1) & (2) are currently taken care of in R (for (2), i just ignore the error message and press on), but I'm not sure how to deal with them in shell script. hints also welcome. Just need pointers on where to start. thanks. On Mon, Mar 16, 2009 at 5:25 PM, Stefan Manegold wrote:

On Mon, Mar 16, 2009 at 04:56:31PM -0700, Yue Sheng wrote:

...

no i/o, swapping, no activity in CPU monitor 32GB

haven't tried. manual loading >600 files then see when it break is an option, albert a painful one...

assiming your files are in .../DIR/*.csv:

rows="`cat .../DIR/*.csv | wc -l`" for i in .../DIR/*.csv ; do if [ "$rows" ] ; then mclient -lsql -s"copy $ROWS records into usrtable from '$i' using delimiters '|';" rows='' else mclient -lsql -s"copy `cat $i | wc -l` records into usrtable from '$i' using delimiters '|';" fi done

Stefan

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Table currently has around 500 million records. Now it hangs even if I try to run a single copy in command line: ./mclient -lsql -s "copy 3000000 records into... " no activity in CPU monitor, Vsize constant at 40.25GB, R Size at 759MB. no error from server. i can't even kill the server. this is very frustrating. could someone please tell me: (1) if the DB can handle rows that are in the billions? (2) if the config file needs to be tweaked? thanks. On Mon, Mar 16, 2009 at 5:25 PM, Stefan Manegold wrote:

On Mon, Mar 16, 2009 at 04:56:31PM -0700, Yue Sheng wrote:

...

no i/o, swapping, no activity in CPU monitor 32GB

haven't tried. manual loading >600 files then see when it break is an option, albert a painful one...

assiming your files are in .../DIR/*.csv:

rows="`cat .../DIR/*.csv | wc -l`" for i in .../DIR/*.csv ; do if [ "$rows" ] ; then mclient -lsql -s"copy $ROWS records into usrtable from '$i' using delimiters '|';" rows='' else mclient -lsql -s"copy `cat $i | wc -l` records into usrtable from '$i' using delimiters '|';" fi done

Stefan

...

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

I wiped everything clean, created a new database, and then restarted using shell script mclient copy. Now it is again stuck at the 281st file (similar place where it got stuck when copying using JDBC) top:PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 3634 top 3.1% 0:02.37 1 18 29 1048K 188K 1640K 18M 3625 bash 0.0% 0:00.00 1 14 19 308K 632K 944K 18M 3624 login 0.0% 0:00.01 1 17 55 308K 268K 1080K 19M 3613 mdworker 0.0% 0:00.11 3 66 35 620K 2188K 2136K 31M 3612 SecurityAg 0.0% 0:00.56 5 113 166 1396K 13M 6252K 610M 3611 authorizat 0.0% 0:00.01 1 31 39 476K 1152K 1560K 29M 3610 mdworker 0.0% 0:00.12 3 67 43 272K 2540K 2272K 30M 2457 mclient 0.0% 0:00.00 1 14 35 268K 424K 804K 21M 2105 AppleSpell 0.0% 0:00.01 1 22 35 404K 2840K 1908K 34M 2013 JavaApplic 1.5% 11:11.84 20 868 470 78M 18M 97M 894M 1295 pmTool 0.0% 3:50.93 1 24 29 440K 1128K 1332K 27M 1291 Activity M 0.3% 6:01.13 11 137 326 11M 22M 24M+ 670M 1204 Safari 2.6% 62:04.13 19 185 2026 243M 14M 161M 997M 114 R 0.2% 2:16.16 18 196+ 321 13M+ 42M 50M+ 671M+ 113 mserver5 0.0% 30:22.73 14 47 614 18G 428K 2224M 44G 102 bash 0.0% 0:00.00 1 14 20 280K 632K 940K 18M 101 login 0.0% 0:00.01 1 17 57 268K 268K 1080K 19M 100 Terminal 1.4% 0:01.22 3 99- 153 2368K 7688K 9796K 617M 89 Finder 0.0% 0:06.15 7 157+ 224 1952K+ 12M 13M+ 625M+ 88 SystemUISe 0.0% 0:01.09 14 298 345 2484K 6856K 9424K 615M 87 coreaudiod 0.0% 0:00.05 2 139 57 536K 340K 1728K 20M 86 pboard 0.0% 0:00.00 1 15 27 116K 184K 588K 19M 85 ATSServer 0.0% 0:00.72 2 99 131 976K 5644K 4552K 73M 84 Dock 0.0% 0:00.64 3 126 288 2052K 42M 11M 648M 83 UserEventA 0.0% 0:00.13 2 158 118 364K 1724K 2240K 533M 82 Spotlight 0.0% 0:00.10 8 86 102 768K 2960K 4124K 551M 78 AirPort Ba 0.0% 0:00.05 1 59 99 604K 3268K 2944K 579M 62 launchd 0.0% 0:00.35 3 153 26 192K 296K 524K 18M 50 WindowServ 1.3% 3:37.31 11 249 1122 16M 77M 102M+ 743M 45 blued 0.0% 0:00.20 1 91 45 488K 1624K 2156K 28M 44 coreservic 0.0% 0:00.62 2 140 101 1360K 17M 11M 29M 41 distnoted 0.0% 0:00.45 1 49 22 236K 184K 736K 18M 39 socketfilt 0.0% 0:00.46 3 35 29 568K 196K 1548K 18M 37 autofsd 0.0% 0:00.01 1 21 22 104K 184K 668K 18M 34 configd 0.0% 0:00.68 3 177 79 252K 304K 1892K 19M 33 CoreRAIDSe 0.0% 2:53.29 2 65 43 260K 1132K 2216K 30M 32 DirectoryS 0.0% 0:03.88 5 66 41 1560K 188K 3300K 21M 31 diskarbitr 0.0% 0:00.23 1 112 20 328K 184K 964K 18M iostat: disk0 cpu load average KB/t tps MB/s us sy id 1m 5m 15m 514.68 16 8.10 2 1 97 0.10 0.07 0.12 ps: PID TTY TIME CMD 102 ttys000 0:00.01 -bash 113 ttys000 30:22.74 ./mserver5 --dbname=usrdb --dbinit include sql; 3625 ttys001 0:00.01 -bash Thanks. Good to know that Monet can handle 12B rows. Now just need to work out why it keeps failing when it reaches a certain size... On Mon, Mar 16, 2009 at 10:57 PM, Martin Kersten wrote:

Yue Sheng wrote:

...

Table currently has around 500 million records. Now it hangs even if I try to run a single copy in command line:

./mclient -lsql -s "copy 3000000 records into... "

no activity in CPU monitor, Vsize constant at 40.25GB, R Size at 759MB. no error from server. does 'top' tell mserver is not consuming cpu? or just minor? does 'iostat' is telling there is no io? does 'ps' tell that the OS has put the process on the lowest priority?

...

i can't even kill the server.

victimized by the OS, swapping out all other processes, which then have to be brought back in.

...

this is very frustrating.

could someone please tell me:

(1) if the DB can handle rows that are in the billions?

the largest database running at our site has a table of 12B rows 6 cols and 400M rows 500 columns. Loaded using a schema without (referential) constraints, which are added after the fact using an ALTER statement.

...

(2) if the config file needs to be tweaked? no

...

thanks.

On Mon, Mar 16, 2009 at 5:25 PM, Stefan Manegold mailto:Stefan.Manegold@cwi.nl> wrote:

On Mon, Mar 16, 2009 at 04:56:31PM -0700, Yue Sheng wrote: > no i/o, swapping, no activity in CPU monitor > 32GB > > haven't tried. manual loading >600 files then see when it break is

an

...

> option, albert a painful one...

assiming your files are in .../DIR/*.csv:

rows="`cat .../DIR/*.csv | wc -l`" for i in .../DIR/*.csv ; do if [ "$rows" ] ; then mclient -lsql -s"copy $ROWS records into usrtable from '$i' using delimiters '|';" rows='' else mclient -lsql -s"copy `cat $i | wc -l` records into usrtable from '$i' using delimiters '|';" fi done

Stefan

>

------------------------------------------------------------------------------

...

> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through

debugging.

...

> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net mailto:MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM)

are

...

powering Web 2.0 with engaging, cross-platform capabilities. Quickly

and

...

easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net mailto:MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------

------------------------------------------------------------------------------

...

Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users