April 2008 - users-list

[MonetDB-users] very slow query execution
by ewilde 22 Apr '08

22 Apr '08

hello, this is how my table - table1(int,int,varchar) - looks like: recid | ft | val --------------------------------------- 1001 | 301 | a 1001 | 302 | b 1001 | 303 | c 1002 | 301 | d 1002 | 302 | e 1003 | 303 | f 1003 | 301 | g 1003 | 302 | h 1004 | 303 | i in reality this table has a few millions of records. for the following query: select t1.val as val1 , t2.val as val2 from table1 t1 left join table1 t2 on t1.recid=t2.recid and t2.ft=303 where t1.recid=1002 and t1.ft=301 i get the expected result: val1 | val2 --------------------- d | f the execution time of this query is very slow: ~1.5 seconds - in comparison to MSSql:0.016 seconds. the kind of a query that i really need in my production application doesn't have only one such a "left join...", but may have even 10. with 5 such "left join..."s the query execution time is ~25 seconds, which is so far slower than MSSql: 0.156 more info: 1. i run the queries from SQuirreL SQL Client. the MonetDB5 is installed\run on the same machine. the MSSql server was running on another machine in the same LAN. 2. the primary key of table1 is : recid,ft,val 3. at the beginning i had another problem, that i have got an empty result when running a simple query like this: select * from table1 t1 where t1.recid=1002 and t1.ft=301 i'm not sure about this, but i guess that this problem had been solved by creating a view such as: create view v1 as select recid,ft,val from table1 i have no clue whether these two problems related to each other, but i mentioned it since it might give you a hint for solving the other problem. thanks in advanced for any help, ewilde.

2 1

[MonetDB-users] merovingian : could not retrieve uplog information ... Too many open files
by McKennirey.Matthew 21 Apr '08

21 Apr '08

Environment MSG BUSINESS[7403]: # MonetDB server v5.4.1, based on kernel v1.22.1 MSG BUSINESS[7403]: # Serving database 'BUSINESS' MSG BUSINESS[7403]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked During the course of software development using MonetDB for our application database, after running for a week or ten days without incident, the merovingian daemon will suddenly stop accepting connections. The merovingian log shows MSG merovingian[19349]: database 'BUSINESS' already running since 2008-04-11 14:42:27, up min/avg/max: 0/0/0, crash average: 0.00 0.00 0.00 (1-0=0) MSG merovingian[19349]: redirecting client 192.168.1.1:29378 for database 'BUSINESS' to mapi:monetdb://dev04:50001/ ERR merovingian[19349]: client error: could not retrieve uplog information: IOException:sabaoth.getUplogInfo:unable to open file /monetdb/var/MonetDB5/dbfarm/BUSINESS/.uplog: Too many open files ERR merovingian[19349]: client error: IOException:sabaoth.getStatus:unable to open directory /monetdb/var/MonetDB5/dbfarm: Too many open files # examine the .uplog file ? dev04:/monetdb/var/MonetDB5/dbfarm/BUSINESS # cat .uplog 1207290718 1207844298 1207844305 1207846979 1207846986 1208461825 1208461935 1208463257 1208463466 There is lots of space on the disk. /monetdb status shows the database is up and running netstat shows the daemon and databases listening on their ports Although it is not possible to connect to the database via the daemon, we can connect using mclient, # connect locally using mclient /mclient -dBUSINESS -umonetdb -Pmonetdb -lsql sql> And we can run, via mclient, the same sort of queries that were being run when the daemon stopped accepting connections. It is difficult to determine the exact query that was run as the application is generating lots of different queries frequently. And we can connect to the database from other clients on the network by connecting directly to the database, bypassing the daemon (which is what we are doing for now)

2 4

Re: [MonetDB-users] [Question] data loading to MonetDB
by Martin Kersten 19 Apr '08

19 Apr '08

ks038c wrote: > How can I load large dump data into MonetDB > > I mean... > > I have "test.txt" file with "csv" format like. > > 00001|"name1"|"address1" > 00002|"name2"|"address2" > ..... > > and want to load this LARGE data into my table > > TEST( ID NUMERIC(5), NAME VARCHAR(10), ADDR VARCHAR(20) ) > > I found word "tablet.load" or "tablet.SQLload" in m5manual > but don't know whether it is the one I need or not and don't know how > to use it. > > I beg yout answer... > > > > > http://monetdb.cwi.nl/projects/monetdb/MonetDB/Version4/Documentation/Modul…

2 1

[MonetDB-users] alternate dbfarm location not working
by mobigital1 18 Apr '08

18 Apr '08

i am trying to setup the dbfarm location to be on a different share from /opt/monetdb/var directory. i modified the /opt/monetdb/etc/monetdb5.conf file to reflect the alternate path to dbfarm location, and also sql-log location. i am able to start the server with >> merogivian start <dbname> but it is very unstable and crashes when i try to import some data. I have been able to import the same data when dbfarm was under /opt/monetdb/var directory. is there something else I need to do to relocate dbfarm to another folder? the new folder is mounted to another disk. (ext3 partition) any advice would be appreciated. -- View this message in context: http://www.nabble.com/alternate-dbfarm-location-not-working-tp16715294p1671… Sent from the monetdb-users mailing list archive at Nabble.com.

3 17

[MonetDB-users] Invalid Credentials for user when restarted mserver5
by mobigital1 18 Apr '08

18 Apr '08

I imported some data into database from 6 files or so. I restarted database after each file. on latest file, after stopping the database, I decided to start it up manually and ran like this: /opt/monetdb/bin/mserver5 -dbinit="include sql;" --dbname=<bbb> --set mapi_open=true when opened mclient, it said: !InvalidCredentialsException:checkCredentials:Invalid credentials for user '....' HOWEVER, as last hope I decided to try starting again via merovigian, and I think it worked, except that it's taking its time now to sync up sql_log into the dbfarm. so for some reason starting database via merovigian and manually like above is not the same thing. Does merovigian pass some environment variables to the mserver5 process? -- View this message in context: http://www.nabble.com/Invalid-Credentials-for-user-when-restarted-mserver5-… Sent from the monetdb-users mailing list archive at Nabble.com.

3 4

[MonetDB-users] corruption on db restart
by mobigital1 17 Apr '08

17 Apr '08

I imported some millions of rows into a table: sql>select part, count(*) from frag group by part order by part; +------+-----------+ | part |count_node | +======+===========+ | 1 | 25205085 | | 2 | 25618423 | | 3 | 27380780 | | 4 | 26963069 | | 5 | 25661196 | | 6 | 27021186 | | 7 | 26058891 | | 8 | 28092822 | | 9 | 27528646 | | 10 | 26429092 | | 11 | 25194504 | | 12 | 25136210 | | 13 | 25105453 | | 14 | 24860071 | | 16 | 20672348 | | 17 | 19138139 | +------+-----------+ then I stopped the database, and wanted to start it back up to see how quickly it would come up. it took a few minutes to come up while it was comitting uncomitted transactions. When i started it up the space usage looked like this: 251721152 ./var/MonetDB5/dbfarm (251gb) 24181152 ./var/MonetDB5/sql_logs (24gb) after a few minutes it became like this: 249855172 ./var/MonetDB5/dbfarm (249gb) 12 ./var/MonetDB5/sql_logs however when it came up, i guess the data became corrupted because look at what happened to my query results now: sql>select part, count(*) from frag group by part order by part; +------+-----------+ | part |count_node | +======+===========+ | 1 | 25205085 | | 0 | 361722691 | | 17 | 19138139 | | 18 | 21414794 | +------+-----------+ there are total about 427 M rows, of which 361.7M are corrupted. There is nothing in the merovingian.log file at all since stop/start of database. Can someone advise what to do with this? 1. if possible to recover data, please let me know 2. otherwise what can I do to prevent this in the future? this is a huge roadblock. -- View this message in context: http://www.nabble.com/corruption-on-db-restart-tp16737747p16737747.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 7

[MonetDB-users] SQLException in executeQuery() =Query did not produce a result set
by sateesh 17 Apr '08

17 Apr '08

Hi, When i try to execute a Query using Prepared Statement some times i am getting the below listed Error. It seems like when no rows are returned it throws this Exception.. I am hoping that it is a safely ignorable one.. When i use Statement i don't get this Error!! Regards Sateesh [08-59-15 09:59] ERROR com.lmco.dpa.core.services.persistance.QueryPerformer (129)- SQLException in executeQuery() =Query did not produce a result set at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeQuery(MonetPreparedStatement.java:228) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at com.lmco.dpa.core.services.persistance.QueryPerformer.executeQuery(QueryPerformer.java:86) at com.lmco.dpa.storage.rdf.framework.DPARepositoryModelBuilder.getModelProperty(DPARepositoryModelBuilder.java:279) at com.lmco.dpa.storage.rdf.framework.DPARepositoryModelBuilder.getMetadataModelsMap(DPARepositoryModelBuilder.java:121) at com.lmco.dpa.storage.rdf.framework.DPAModelMetadataManager.init(DPAModelMetadataManager.java:185) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1237) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1203) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1167) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:427) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:339) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:267) at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:110) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1095) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:857) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:423) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:291) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352) at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:122) at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:76) at com.lmco.dpa.ui.common.dao.rdf.impl.UIRepositoryFacadeDaoImplTest.onSetUp(UIRepositoryFacadeDaoImplTest.java:38) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.internal.runners.ClassRoadie.runBefores(ClassRoadie.java:49) at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:36) at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) -- View this message in context: http://www.nabble.com/SQLException-in-executeQuery%28%29-%3DQuery-did-not-p… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] db log shrink
by Colin Foss 17 Apr '08

17 Apr '08

Is there any method, besides restarting the database, of shrinking the sql log file? When I am performing a large set of large inserts (via COPY INTO) the log file gets very large. I wish there was a way to cycle the log file. Also during these large batches of inserts the database server becomes less and less responsive until I am forced to restart the server. Upon restart and the normal recovery monet returns to a very responsive state.

3 2

[MonetDB-users] COPY INTO of data containing hebrew letters
by ewilde 16 Apr '08

16 Apr '08

hello again. now i have another problem. following your suggestion of using COPY INTO for the migration of my database from MSSql to MonetDB5, i indeed succeeded in copying some tables, enjoying very good performances (for example, one table with ~8 millions of records in ~13 seconds). the new problem i'm facing now is that using COPY INTO for data with hebrew letters ends up with error. i tried to supply COPY INTO a UNICODE enoded file, but than i have not succeeded in defining the tuple and record seperators. the MonetDB5 itself is capable of storing and retrieving hebrew letters. i can tell this because i have no problem inserting such data through JDBC (actually, right now i have a java process working hard and slowly on copying such a table). so the problem is probably somewhere in the mclient application. is there any workaround for that? thanks, ewilde.

2 1

Re: [MonetDB-users] how to completely remove the MonetDB5installation
by ewilde 16 Apr '08

16 Apr '08

hello! thanks for the latest info. i indeed found all the stuff in %APPDATA%, and i removed it, and the MonetDB5 has come back to life again. if you think i may be able to supply more info about that bug i described in the first place, don't hesitate to contact me. ewilde.

1 0