Re: [MonetDB-users] loading big table

9 Apr 2009

      On Wed, Apr 08, 2009 at 11:28:21PM -0700, Yue Sheng wrote:
...
Hi, just to inform that our Linux box runs the Feb2009-SP1 version in 64bit.
The table loads fine.
What about MacOS, then?

 However we find that query performance is factor of
...
260 slower than MySQL. This is of course a seperate issue. We understand
  ^^^^^^^^^^^^^^^^^^^^^
as far as I recall, you only reported (tested?) this for one single query, right?
In case you have performed a broader performance comparison of MonetDB with
MySQL or other DBMSs (or plan to do so), we are more than interested in the
detailed results.
...
that MonetDB builds index based on query. Could this explain a 260 fold
difference? So we need to run every query once? Then the exactly query would
be faster? 260 times faster?
MonetDB has originally been designed mainly aiming at OLAP workloads, and
gains its performance advantage for such workloads from carefully focussing
on the required functionality, while omitting inherent overheads --- like
constructing and maintaining (under updates) traditional indices --- as far
as possible, i.e., wherever these are not beneficial for OLAP workloads.

The query for which you report the 260x performance difference is an
OLTP-like single-value point lookup. Here, MySQL benefits from the created
(B-Tree?) index --- at the expense of creating it upfront, and maintaining
it under updates. The current production release of MonetDB does not yet
come with either persistent hash indices to support point lookups, nor
workload-driven incremental index creation (and maintenance), i.e.,
"database cracking"; hence --- unless your data happens to be sorted, and
MonetDB recognizes this during bulk load ---, it needs to perform a column
scan to evaluate your query.

The production release of MonetDB internally builds (transient)
(hash-)indices for more complex operations like, e.g., joins, grouping and
aggregation, i.e., those cases where the investment of constructing such
indices on-the-fly is know/expected to pay-off within the same query.

Please understand that while we are constantly working on improving (also)
the support of OLTP-like workloads in MonetDB, we need to ensure that we do
not compromise MonetDB's main reputation, i.e., its OLAP performance.

We are very interested in learning more about the detailed use cases and
workloads that MonetDB users do or plan to use MonetDB for. This information
is crucial for us to extend and improve the functionality and performance of
MonetDB.

Best regards,
Stefan
...
Thanks.
On Wed, Apr 8, 2009 at 4:52 PM, dariuszs  wrote:
...
Hi,
I've just upgraded the software, I'll let you know the results tomorrow.
 Thanks. Dariusz.
...
Hi all,
in addition to Lefteris' & Martin's statments:
I noticed that both Yue & Dariuszs use the Feb2009 release, but not yet
...
latest Feb2009-SP1 bugfix release.
One significat difference between the two releases is that we fixed some
memory management problems in the kernel, that mainly affected 32-bit
Windows systems --- however, it could possibly be that our fixes also
have
positive effects on the 64-bit Windows & MacOS problems reported, here.
Hence, I'd recommend both Yue & Dariuszs to upgrade to the latest
Feb2009-SP1 release, try their large-volume bulk-loading, again, and
report
their observations and experiences.
Stefan
On Wed, Apr 08, 2009 at 10:40:41PM +0200, Lefteris wrote:
...
Hi all,
I just want to point out that these problems occur in Windows
(dariuszs) and Mac OS (Yue) while they do not appear in Linux (as
reported by Yue). It is true that I have never notice such problems
with Monet on Linux, but I have seen them with other DBMS's on Linux.
In these situations I witness no IO or CPU activities and the only
logical explanation I could give is "starving" IO scheduling. I have
also noticed Monet/Xquery to fall in a "busy wait" (because of
deadlock) but that would give 100% CPU and at the end, we fixed that
since it was a bug.  What I am trying to say is that if dariuszs and
Yue do not experience either 100% cpu or IO activity (through iostat)
then the problem calls for a serious investigation in the corporation
of modern DBMS's and Operating Systems.
Lefteris
On Wed, Apr 8, 2009 at 9:10 PM, Martin Kersten 
wrote:
...
With interest i follow the discussion of these threads.
Hardware is just one of the many ingredients to consider.
The loading method (staged/single shot), the correctness
of the input, the load on the machine,....and many other issues play a
role.
Furthermore, specifics of the reports can not trigger an action here.
They lack the details a system administrator and database administrator
need to isolate a problem in the system or the way in which it is used.
What does "getting stuck" mean? System crashed? Operating system shows
a cpu load of <3%?  iostat shows no or excessive IO? What does 'top'
tell? is the process priority punished by your OS? Can you connect to
the serve with mclient -lsql still? Does it work with single threaded
server instance (--set gdk-nr-threads=1)? All standard questions for
...
...
...
DBA to isolate the issue.
The report of Dariuz is a step into the direction. In that case
i would ask for the predicted load time based on a multi-step
experiment, e.g. load 10M, 20M 40M, first. Second, the size in
relationship to main memory and swapspace would be investigated.
And, finally, i would run here with single threaded to isolate/
detect a possible internal concurrency conflict.
regards, Martin
Yue Sheng wrote:
...
I have 32gb, 8core, macpro, 2tb harddisk. So hardware wise pretty
top-end I guess. In my case, we thought it might be a Mac issue.
Having
said that, we tried the same insert on a Linux box, and it worked. So
it
might be a Mac issue. Interesting to hear you have very similar
...
...
...
...
on non-Mac box...
On Wed, Apr 8, 2009 at 11:19 AM, dariuszs mailto:dariuszs@svnp.com> wrote:
Hi,
    How much memory do you have? Dariusz.
Yue Sheng wrote:
     > I have similar problem when loading large dataset. I'm trying
to load
     > 650million by 10. It gets stuck after ~350million.
Interestingly not
     > always at the same point...
     >
     > On Wed, Apr 8, 2009 at 6:10 AM, dariuszs mailto:dariuszs@svnp.com
     > mailto:dariuszs@svnp.com>> wrote:
     >
     >     Hi,
     >     Can anybody help me with this issue? Thanks. Dariusz.
     >
     >
     >
     >     dariuszs wrote:
     >     > Hi,
     >     > Specs:
     >     > MonetDB Server 5.10.0, based on kernel v1.28.0
     >     > Windows x64 Server 2003 Enterprise
     >     > Intel Dual Xenon 5470 server, SAS drives, 48GB of memory
/
    220GB
     >     swap
     >     > space
     >     >
     >     > CREATE TABLE xxx(
     >     > C1 VARCHAR(16),
     >     > C2 VARCHAR(15),
     >     > C3 VARCHAR(1),
     >     > C4 VARCHAR(4),
     >     > C5 VARCHAR(1),
     >     > C6 VARCHAR(10),
     >     > C7 VARCHAR(2),
     >     > C8 VARCHAR(1),
     >     > C9 VARCHAR(1),
     >     > C10 VARCHAR(1),
     >     > C11 VARCHAR(1),
     >     > C12 VARCHAR(1),
     >     > C13 VARCHAR(2),
     >     > C14 VARCHAR(1),
     >     > C15 VARCHAR(2),
     >     > C16 VARCHAR(30),
     >     > C17 VARCHAR(16),
     >     > C18 VARCHAR(2),
     >     > C19 VARCHAR(5),
     >     > C20 VARCHAR(4),
     >     > C21 VARCHAR(3),
     >     > C22 VARCHAR(2),
     >     > C23 VARCHAR(1),
     >     > C24 VARCHAR(2),
     >     > C25 VARCHAR(1),
     >     > C26 VARCHAR(2),
     >     > C27 VARCHAR(1),
     >     > C28 VARCHAR(2),
     >     > C29 VARCHAR(3),
     >     > C30 VARCHAR(4),
     >     > C31 VARCHAR(10),
     >     > C32 VARCHAR(1),
     >     > C33 VARCHAR(2),
     >     > C34 VARCHAR(4),
     >     > C35 VARCHAR(1),
     >     > C36 VARCHAR(1),
     >     > C37 VARCHAR(6),
     >     > C38 VARCHAR(6),
     >     > C39 VARCHAR(2),
     >     > C40 VARCHAR(6),
     >     > C41 VARCHAR(1),
     >     > C42 VARCHAR(6),
     >     > C43 VARCHAR(2),
     >     > C44 VARCHAR(1),
     >     > C45 VARCHAR(1),
     >     > C46 VARCHAR(1),
     >     > C47 VARCHAR(1),
     >     > C48 VARCHAR(1),
     >     > C49 VARCHAR(1),
     >     > C50 VARCHAR(1),
     >     > C51 VARCHAR(2),
     >     > C52 VARCHAR(6),
     >     > C53 VARCHAR(2),
     >     > C54 VARCHAR(2),
     >     > C55 VARCHAR(6),
     >     > C56 VARCHAR(2),
     >     > C57 VARCHAR(1),
     >     > C58 VARCHAR(1),
     >     > C59 VARCHAR(1),
     >     > C60 VARCHAR(1),
     >     > C61 VARCHAR(1),
     >     > C62 VARCHAR(1),
     >     > C63 VARCHAR(1),
     >     > C64 VARCHAR(4),
     >     > C65 VARCHAR(1),
     >     > C66 VARCHAR(1),
     >     > C67 VARCHAR(1),
     >     > C68 VARCHAR(3),
     >     > C69 VARCHAR(2),
     >     > C70 VARCHAR(2),
     >     > C71 VARCHAR(1),
     >     > C72 VARCHAR(1),
     >     > C73 VARCHAR(1),
     >     > C74 VARCHAR(1),
     >     > C75 VARCHAR(2),
     >     > C76 VARCHAR(8),
     >     > C77 VARCHAR(8),
     >     > C78 VARCHAR(8),
     >     > C79 VARCHAR(1),
     >     > C80 VARCHAR(1),
     >     > C81 VARCHAR(1),
     >     > C82 VARCHAR(1),
     >     > C83 VARCHAR(6),
     >     > C84 VARCHAR(6),
     >     > C85 VARCHAR(6),
     >     > C86 VARCHAR(1),
     >     > C87 VARCHAR(1),
     >     > C88 VARCHAR(1),
     >     > C89 VARCHAR(1),
     >     > C90 VARCHAR(1),
     >     > C91 VARCHAR(1),
     >     > C92 VARCHAR(1),
     >     > C93 VARCHAR(1),
     >     > C94 VARCHAR(1),
     >     > C95 VARCHAR(1),
     >     > C96 VARCHAR(1),
     >     > C97 VARCHAR(1),
     >     > C98 VARCHAR(1),
     >     > C99 VARCHAR(1),
     >     > C100 VARCHAR(1),
     >     > C101 VARCHAR(1),
     >     > C102 VARCHAR(1),
     >     > C103 VARCHAR(1),
     >     > C104 VARCHAR(4),
     >     > C105 VARCHAR(4),
     >     > C106 VARCHAR(6),
     >     > C107 VARCHAR(8),
     >     > C108 VARCHAR(8),
     >     > C109 VARCHAR(1),
     >     > C110 VARCHAR(1),
     >     > C111 VARCHAR(8),
     >     > C112 VARCHAR(6),
     >     > C113 VARCHAR(1),
     >     > C114 VARCHAR(1),
     >     > C115 VARCHAR(1),
     >     > C116 VARCHAR(1),
     >     > C117 VARCHAR(1),
     >     > C118 VARCHAR(1),
     >     > C119 VARCHAR(1),
     >     > C120 VARCHAR(3),
     >     > C121 VARCHAR(1),
     >     > C122 VARCHAR(1),
     >     > C123 VARCHAR(1),
     >     > C124 VARCHAR(6),
     >     > C125 VARCHAR(6),
     >     > C126 VARCHAR(1),
     >     > C127 VARCHAR(1),
     >     > C128 VARCHAR(1),
     >     > C129 VARCHAR(22),
     >     > C130 VARCHAR(27));
     >     > copy 160000000 records into xxx from
'm:/monetdb_data/xxx.txt';
     >     > commit;
     >     >
     >     > OK, so the server loads the file for about 4 hours and I
    see CPU
     >     > activity, I see memory usage going up (47GB) and down
    several times,
     >     > but then after 4 hours everything sits - also I see that
server
     >     > creates a files in the sql_logs directory 'log.179' which
    grew up to
     >     > 47GB and then it stopped.
     >     >
     >     > I was able to successfully load 130 million records using
    the same
     >     > file and the same system in about 3 hours. Thanks.
Dariusz.
     >     >
     >     >
     >     >
     >     >
     >     > Stefan Manegold wrote:
     >     >> Which version of MonetDB?
     >     >> Which OS?
     >     >> Which HW arcitecture?
     >     >> Which table schema?
     >     >> What happens during "sits" (CPU bound, I/O bound, ...)?
     >     >> How do you "load"?
     >     >>
     >     >> Stefan
     >     >>
     >     >> On Fri, Apr 03, 2009 at 02:40:10PM -0400, dariuszs
wrote:
     >     >>
     >     >>> Hi,
     >     >>> I've got a table with 130 fields. When I load 130
million
    records
     >     >>> into that table (about 50G) - it's not a problem, but
when I
     >     try to
     >     >>> load 160 million records (about 60G of data) into it
Stefan Manegold wrote:
the
the
problem
this
...
...
...
...
>     thing just
     >     >>> sits for hours with no error messages.  Can somebody
help me?
...
>     >>> Thanks.  Dariusz.
     >     >>>
     >     >>>
     >     >>>
     >     >>>
     >

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

...
...
...
...
>     This SF.net email is sponsored by:
     >     High Quality Requirements in a Collaborative Environment.
     >     Download a free trial of Rational Requirements Composer
Now!
...
>     http://p.sf.net/sfu/www-ibm-com
     >     _______________________________________________
     >     MonetDB-users mailing list
     >     MonetDB-users@lists.sourceforge.net
    mailto:MonetDB-users@lists.sourceforge.net
     >     mailto:MonetDB-users@lists.sourceforge.net>
     >     https://lists.sourceforge.net/lists/listinfo/monetdb-users
     >
     >
     >

...
...
...
...
>
     >

...
...
...
...
> This SF.net email is sponsored by:
     > High Quality Requirements in a Collaborative Environment.
     > Download a free trial of Rational Requirements Composer Now!
     > http://p.sf.net/sfu/www-ibm-com
     >

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

...
...
...
...
This SF.net email is sponsored by:
    High Quality Requirements in a Collaborative Environment.
    Download a free trial of Rational Requirements Composer Now!
    http://p.sf.net/sfu/www-ibm-com
    _______________________________________________
    MonetDB-users mailing list
    MonetDB-users@lists.sourceforge.net
    mailto:MonetDB-users@lists.sourceforge.net
    https://lists.sourceforge.net/lists/listinfo/monetdb-users

...
...
...
...

...
...
...
...
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com

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

...
...
...
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users

...
...
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
...
------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-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       |