October 2010 - users-list

[MonetDB-users] MonetDB performance question
by Tudor-Ioan Salomie 18 Oct '10

18 Oct '10

Hi, I have recently tried doing some experiments with MonetDB, measuring its performance for the following dataset+workload: * 1 table (50 attributes), 1GB, comprising 3.000.000 tuples * the attributes are fixed length strings and integer types * the workload is query (SELECT) only, where each query has the form: SELECT <list_of_projections> FROM MyTable WHERE <list_of_predicates>; * the list of predicates is a conjunction of equality operators * more than 99% of the queries predicate on the same attribute (let's call it A1) that has a *very* good selectivity * the average number of predicated attributes per query is 8 * for this workload, averaging over 4000 queries, there is 1 result tuple per query (actual result tuples per query are in the range 0-10) I have installed MonetDB on a 64-bit Debian machines, using the .deb files provided on the internet: * libmonetdb1_1.38.5-20100824_amd64.deb * libmonetdb5-server5_5.20.5-20100824_amd64.deb * libmonetdb5-sql2_2.38.5-20100824_amd64.deb * libmonetdb-client1_1.38.5-20100824_amd64.deb * monetdb5-server_5.20.5-20100824_amd64.deb * monetdb5-sql_2.38.5-20100824_amd64.deb * monetdb-client_1.38.5-20100824_amd64.deb For running the queries I use the SQL front end. I have run the queries both through "mclient" (directly on the server) and through the JDBC driver (from another client machine). The MonetDB server is bound to run on only one core on the machine (though I have not seen any difference if running it on multiple cores). To my surprise, the throughput for MonetDB for this workload is very low. I get about 3 queries per second, which seems to indicate that MonetDB does full table scanning only. Am I correct in this assumption? Is there anything that can be done to speed it up? Is this not a workload suitable for MonetDB? Looking forward to your input! Regards, Tudor.

2 1

[MonetDB-users] FW: Comment via CWI webform MonetDB
by Peter Boncz 15 Oct '10

15 Oct '10

Narayan Sujay, Thanks for your interest in MonetDB. Rather than directing technical questions to the CWI organization webmaster, you are supposed to post on one of the relevant mailing lists such as this one (monetdb-user(a)lists.sourceforge.net) Peter -----Original Message----- From: Minnie Middelberg [mailto:Minnie.Middelberg@cwi.nl] Sent: Friday, October 15, 2010 9:50 AM To: Peter Boncz Subject: Fwd: Comment via CWI webform MonetDB Hallo Peter, Onderstaand is binnengekomen via het CWI Comment formulier en het betreft MonetDB. Wetende dat jij en Marting Kersten achter MonetDB zitten forward ik het maar naar jou. Mocht er iemand zijn die dit soort vragen belast is dan hoor ik het graag van je. Mvg Minnie ----- Forwarded Message ----- From: "Narayan Sujay" <316sujay(a)gmail.com> To: voorlichting(a)cwi.nl, webmaster(a)cwi.nl Sent: Thursday, 14 October, 2010 6:15:15 PM Subject: Comment via CWI webform Submitted on 14.10.2010 - 18:15 Submitted by anonymous user: [198.134.51.12] Submitted values are: Name sender: Narayan Sujay Email address: 316sujay(a)gmail.com Telephone number: 4193780532 Your comment: Hello, I have downloaded MonetDB and the native client and server work well. I can write simple queries in the client cmd window. I am trying to connect MonetDB with one of the SQL frontends like DB visualizer etc and I am not able to do this. Please help me out with instructions. I followed the instructions on the webpage but still getting errors connecting through db visualizer. I think it is the problem with the JDBC drivers. Please let me know excatly where to find the driver on the local computer after I have installed Monet DB. My Main goal here is to load large Data sets in MonetDB. Please tell me a way to do this for Monet DB. The error I get in DB Visualizer is: Long Message: Cannot connect to ORB Details: Type: javax.naming.CommunicationException Stack Trace: javax.naming.CommunicationException: Cannot connect to ORB [Root exception is org.omg.CORBA.COMM_FAILURE: vmcid: SUN minor code: 201 completed: No] at com.sun.jndi.cosnaming.CNCtx.setOrbAndRootContext(Unknown Source) at com.sun.jndi.cosnaming.CNCtx.initOrbAndRootContext(Unknown Source) at com.sun.jndi.cosnaming.CNCtx.<init>(Unknown Source) at com.sun.jndi.cosnaming.CNCtxFactory.getInitialContext(Unknown Source) at javax.naming.spi.NamingManager.getInitialContext(Unknown Source) at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source) at javax.naming.InitialContext.init(Unknown Source) at javax.naming.InitialContext.<init>(Unknown Source) at com.onseven.dbvis.db.A.G.Ą(Z:3155) at com.onseven.dbvis.db.A.G.Ĭ(Z:2675) at com.onseven.dbvis.db.A.E.Ĭ(Z:98) at com.onseven.dbvis.db.A.D.ā(Z:2056) at se.pureit.swing.A.O$2.run(Z:1192) at java.lang.Thread.run(Unknown Source) Caused by: org.omg.CORBA.COMM_FAILURE: vmcid: SUN minor code: 201 completed: No at com.sun.corba.se.impl.logging.ORBUtilSystemException.connectFailure(Unknown Source) at com.sun.corba.se.impl.logging.ORBUtilSystemException.connectFailure(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.<init>(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.<init>(Unknown Source) at com.sun.corba.se.impl.transport.SocketOrChannelContactInfoImpl.createConnection(Unknown Source) at com.sun.corba.se.impl.protocol.CorbaClientRequestDispatcherImpl.beginRequest(Unknown Source) at com.sun.corba.se.impl.protocol.CorbaClientDelegateImpl.request(Unknown Source) at com.sun.corba.se.impl.resolver.BootstrapResolverImpl.invoke(Unknown Source) at com.sun.corba.se.impl.resolver.BootstrapResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.resolver.CompositeResolverImpl.resolve(Unknown Source) at com.sun.corba.se.impl.orb.ORBImpl.resolve_initial_references(Unknown Source) ... 14 more Caused by: java.net.ConnectException: Connection refused: connect at sun.nio.ch.Net.connect(Native Method) at sun.nio.ch.SocketChannelImpl.connect(Unknown Source) at java.nio.channels.SocketChannel.open(Unknown Source) at com.sun.corba.se.impl.transport.DefaultSocketFactoryImpl.createSocket(Unknown Source) ... 25 more The results of this submission may be viewed at: http://www.cwi.nl/en/node/757/submission/3152

1 0

[MonetDB-users] upgrade notes: Feb2010 --> Jun2010
by Mark Bucciarelli 07 Oct '10

07 Oct '10

Hi, I'd like to upgrade to the June release. I'm running Feb 2010. Can I just build the June release and expect it will just work with my database? Or do I need to upgrade the data somehow? I poked around dev.monetdb.org/hg, looking for release notes, changelist, etc. but didn't find anything. Thanks, m

2 3

[MonetDB-users] bug #2634 in Jun2010? [was: upgrade notes: Feb2010 --> Jun2010]
by Mark Bucciarelli 07 Oct '10

07 Oct '10

On Thu, Oct 7, 2010 at 9:15 AM, Sjoerd Mullender <sjoerd(a)acm.org> wrote: > On 2010-10-07 14:41, Mark Bucciarelli wrote: > >> I poked around dev.monetdb.org/hg, looking for release >> notes, changelist, etc. but didn't find anything. > > http://monetdb.cwi.nl/Development/Releases/Jun2010/ > Thanks. Was the commit in bug #2634 in the release? I don't see it in the SQL notes. He closed as NEEDS MORE INFO (I think---I can't tell from ticket history) because I did not include a test case. But he did commit some change that fixed the issue (I was able to upload a test case this week, and verified his fix solved my case). Thanks, m http://bugs.monetdb.org/show_bug.cgi?id=2634

2 1

[MonetDB-users] Performance improvements over equal queries limits to operators?
by Stefan de Konink 06 Oct '10

06 Oct '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hello, I was quite surprised about the performance of some queries, that I expected to be heavy weighted, but actually performed pretty well. To show some things: sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (259.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (259.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (258.000ms) sql>select * from kvk where postcode like '2265CA' and adres like '%7%'; 3 tuples (257.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (11.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (6.000ms) sql>select * from kvk where postcode = '2265CA' and adres like '%7%'; 3 tuples (6.000ms) sql>select * from kvk where kvk = 273121520000; 1 tuple (473.000ms) sql>select * from kvk where kvk = 273121520000; 1 tuple (5.000ms) I wonder: what makes the 11ms to 6ms possible in the like vs = scenario? While a repeat of the like operator itself doesn't give a true improvement? As you can observe the like argument doesn't have any modifiers, so it should actually become a normal equal. (I noticed this later, goes beyond my original point, will file a bug for enhancement.) Also I'm quite surprised that the primary key lookup still takes a fair amount of time, could be i/o related obviously, but the performance on the second run is again huge, even for other arguments. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.16 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkysWw8ACgkQYH1+F2Rqwn19AgCgk9lkRBLcWdylxxz9M4B8a8f5 5JUAn1TOU1VxpvVfVWJMLwlwp9Pz4a0x =IqCY -----END PGP SIGNATURE-----

2 1

[MonetDB-users] Dump schema w/ mclient?
by Mark Bucciarelli 05 Oct '10

05 Oct '10

Is there a way to user mclient to dump the db schema without dumping the data? I tried creating a one line SQL file with \d <tablename> and piping to mclient stdin, but it didn't work. Thanks, m

3 2

[MonetDB-users] add a unique constraint, need to create index?
by Mark Bucciarelli 04 Oct '10

04 Oct '10

Hi, I added a unique constraint on two fields, and noticed there was not an index created on those two fields. Do I need to do a CREATE INDEX or is it implicit? I will be using those two fields a lot in WHERE clauses. Thanks, m

1 1

[MonetDB-users] Large XML instances with MonetDB/XQuery 4.38.5
by Christian Grün 02 Oct '10

02 Oct '10

Hi everyone, is it still possible to shred large XML instances with MonetDB/XQuery, or do I need more memory for shredding? I was just trying to the 1GB instance of XMark with WinXP 32Bit and 2GB RAM, and this is what I got: MonetDB>shred_doc("1gb.xml", "xmark"); #GDKmmap(536870912) fails, try to free up space [memory in use=36143780,virtual memory in use=1015480320] #GDKmmap(536870912) result [mem=26016940,vm=1005387776] !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: HEAPextend: failed to extend to 536870912 for 06\632theap !ERROR: shredBAT_append_str: APPEND-STR[_prop_text]( follow murders prov shoes p resent eager delivered defy bend viol here marquis powers complexion pillar cran ts agate ), BUNappend fails !ERROR: CMDshred_url: operation failed. pf:add-doc(...) produces the same output; the XML document itself is well-formed. Thanks, Christian

2 6