Re: [MonetDB-users] 64bit MonetDB, JDBC Insert via RJDBC, >300 million rows

16 Mar 2009

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