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 <dariuszs@svnp.com> 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 <Martin.Kersten@cwi.nl> 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 <dariuszs@svnp.com <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 <dariuszs@svnp.com <mailto:dariuszs@svnp.com> > <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 <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 <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 |