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....

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

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 <yuesheng8@gmail.com> 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 <Stefan.Manegold@cwi.nl> 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