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

17 Mar 2009

      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