November 2015 - users-list

Chinese string and varchar size
by Mathieu Raillard 16 Nov '15

16 Nov '15

Hi all, Last week we find a strange case with chinese character and VARCHAR size. We have in our database a field defined with VARCHAR(5) and a chinese string "不要让早把" to be inserted. If we insert the string with SQL insert everything works find, but if we try to insert via BULK monet throw an error. I made several test and i need to extend the varchar from 5 to 10 to get the bulk insert working. It looks like BULK somehow counts bits and not characters ? I give you some infos if you want to reproduce the case: create table test.varcharsize4 ( id int, varchar105 varchar(4), primary key (id) ); create table test.varcharsize5 ( id int, varchar106 varchar(5), primary key (id) ); create table test.varcharsize9 ( id int, varchar200 varchar(8), primary key (id) ); create table test.varcharsize10 ( id int, varchar200 varchar(10), primary key (id) ); *Insert SQL :* insert into test.varcharsize4 values (1,'不要让早把'); insert into test.varcharsize5 values (1,'不要让早把'); insert into test.varcharsize9 values (1,'不要让早把'); insert into test.varcharsize10 values (1,'不要让早把'); *BULK Line to insert :* "1","不要让早把" *BULK executions :* COPY 10 RECORDS INTO test.varcharsize4 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(4)' expected in '不要让早把' ------ COPY 10 RECORDS INTO test.varcharsize5 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(5)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize89 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; Result : Failed to import table line 1 field 2 'varchar(9)' expected in '不要让早把' ------- COPY 10 RECORDS INTO test.varcharsize10 FROM '/root/test_varchar_chinois' USING DELIMITERS ',','\n','\"' NULL AS '\\N' ; OK Regards Mathieu

2 1

Merge table; partition pruning?
by Bryan Senseman 13 Nov '15

13 Nov '15

Ok, I was all excited about MERGE TABLEs moving up into the SQL layer, unfortunately my enthusiasm might be misplaced as I don’t see any improvement in query performance. Let me explain: I have a fact table with ~300 M rows with 5 years of data. I created 5 yearly tables, ordering by my date_key (and also created a year column). Checked storage to verify MonetDB knew they were sorted. Created a Merge table over all 5 yearly tables. I then ran test queries with various year subsets (using direct fact column filter and joining from date dimension), from 1 to all 5 and well I didn’t see any improvement in query speed when a subset of the 5 years was queried. In fact I usually saw 10-20% degradation of performance. I am no MAL expert, but looking at the PLAN and the EXPLAIN/TRACE outputs it appears to me tables that shouldn’t have data for the query are still scanned. I ran this both on a 16GB 4 core Windows box and a 32GB 8 core EC2 Linux box, same behavior both places, using both July2015 and July2015SP1. Is this expected behavior? Am I missing some magic to have MonetDB do the Oracle concept of Partition Elimination? Or are Merge tables really just meant for data maintenance? Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

4 3

Error starting a production database
by Juan Carlos Gastélum 13 Nov '15

13 Nov '15

Hi I'm getting the following error when a I try to start our production database: # builtin opt gdk_dbpath = /var/monetdb5/dbfarm/demo # builtin opt gdk_debug = 0 # builtin opt gdk_vmtrim = no # builtin opt monet_prompt = > # builtin opt monet_daemon = no # builtin opt mapi_port = 50000 # builtin opt mapi_open = false # builtin opt mapi_autosense = false # builtin opt sql_optimizer = default_pipe # builtin opt sql_debug = 0 # cmdline opt gdk_dbpath = /usr/local/monetdb/alm_dat_far # cmdline opt merovingian_uri = mapi:monetdb://farser07:50000/alm_dat_far # cmdline opt mapi_open = false # cmdline opt mapi_port = 0 # cmdline opt mapi_usock = /usr/local/monetdb/alm_dat_far/.mapi.sock # cmdline opt monet_vault_key = /usr/local/monetdb/alm_dat_far/.vaultkey # cmdline opt gdk_nr_threads = 6 # cmdline opt max_clients = 64 # cmdline opt sql_optimizer = default_pipe # cmdline opt monet_daemon = yes # cmdline opt gdk_debug = 10 # MonetDB 5 server v11.21.11 "Jul2015-SP1" # Serving database 'alm_dat_far', using 6 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked # Found 39.298 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for UNIX domain connection requests on mapi:monetdb:///usr/local/monetdb/alm_dat_far/.mapi.sock # Start processing logs sql/sql_logs version 52200 !FATAL: Logger_new: cannot create seqs bats What can I do to solve it? I have been trying to recover a backup of the virtual machine where the instance of monetdb is but until now I have not been able to recover this database. Thanks in advance. Juan Carlos Gastelum

3 9

Merge and remote table errors
by Bart Lenoir 12 Nov '15

12 Nov '15

Hi, Just adding another finding: Using 1 MERGE TABLE (on Mac) with 2 REMOTE TABLEs (on Linux [physically remote, in another network]) COUNT, MIN, MAX are fine but AVG, MEDIAN, QUANTILE etc. make the server crash. Using 1 MERGE TABLE (on Linux) with 1 REMOTE TABLE (on Linux [physically together, in the same network]) and 1 local TABLE, all the above functions are fine except for the occasional error. sql>select sys.median(doubleDataType) from xxxxxxx; (mapi:monetdb://monetdb@192.168.1.212/xxxxxxx) 'user.l2' undefined in: (rmt3853_X_62_bat_oid_str:bat[:oid,:str],rmt3854_X_65_bat_oid_str:bat[:oid,:str],rmt3855_X_66_bat_oid_str:bat[:oid,:str ],rmt3856_X_67_bat_oid_str:bat[:oid,:str],rmt3857_X_68_bat_oid_dbl:bat[:oid,:dbl],rmt3858_X_70_bat_oid_dbl:bat[:oid,:d bl],rmt3859_X_71_bat_oid_dbl:bat[:oid,:dbl],rmt3860_X_72_bat_oid_dbl:bat[:oid,:dbl],rmt3861_X_73_bat_oid_dbl:bat[:oid ,:dbl],rmt3862_X_74_bat_oid_dbl:bat[:oid,:dbl],rmt3863_X_75_bat_oid_dbl:bat[:oid,:dbl],rmt3864_X_76_bat_oid_dbl:bat[: oid,:dbl],rmt3865_X_77_bat_oid_dbl:bat[:oid,:dbl],rmt3866_X_78_bat_oid_dbl:bat[:oid,:dbl],rmt3867_X_79_bat_oid_dbl:ba t[:oid,:dbl],rmt3868_X_80_bat_oid_dbl:bat[:oid,:dbl],rmt3869_X_81_bat_oid_dbl:bat[:oid,:dbl],rmt3870_X_82_bat_oid_dbl: bat[:oid,:dbl],rmt3871_X_83_bat_oid_dbl:bat[:oid,:dbl],rmt3872_X_84_bat_oid_dbl:bat[:oid,:dbl],rmt3873_X_85_bat_oid_d bl:bat[:oid,:dbl],rmt3874_X_86_bat_oid_dbl:bat[:oid,:dbl],rmt3875_X_87_bat_oid_dbl:bat[:oid,:dbl],rmt3876_X_88_bat_oid _dbl:bat[:oid,:dbl],rmt3877_X_89_bat_oid_dbl:bat[:oid,:dbl],rmt3878_X_90_bat_oid_dbl:bat[:oid,:dbl],rmt3879_X_91_bat_o id_dbl:bat[:oid,:dbl],rmt3880_X_92_bat_oid_dbl:bat[:oid,:dbl],rmt3881_X_93_bat_oid_dbl:bat[:oid,:dbl],rmt3882_X_94_ba t_oid_dbl:bat[:oid,:dbl],rmt3883_X_95_bat_oid_dbl:bat[:oid,:dbl],rmt3884_X_97_bat_oid_dbl:bat[:oid,:dbl],rmt3885_X_98_ bat_oid_dbl:bat[:oid,:dbl],rmt3886_X_99_bat_oid_dbl:bat[:oid,:dbl],rmt3887_X_100_bat_oid_dbl:bat[:oid,:dbl],rmt3888_X_ 101_bat_oid_dbl:bat[:oid,:dbl],rmt3889_X_103_bat_oid_dbl:bat[:oid,:dbl],rmt3890_X_104_bat_oid_dbl:bat[:oid,:dbl],rmt38 91_X_105_bat_oid_dbl:bat[:oid,:dbl],rmt3892_X_106_bat_oid_dbl:bat[:oid,:dbl],rmt3893_X_107_bat_oid_dbl:bat[:oid,:dbl],r mt3894_X_108_bat_oid_dbl:bat[:oid,:dbl],rmt3895_X_109_bat_oid_dbl:bat[:oid,:dbl],rmt3896_X_110_bat_oid_dbl:bat[:oid,: dbl],rmt3897_X_111_bat_oid_dbl:bat[:oid,:dbl],rmt3898_X_112_bat_oid_dbl:bat[:oid,:dbl],rmt3899_X_113_bat_oid_dbl:bat[: oid,:dbl],rmt3900_X_114_bat_oid_dbl:bat[:oid,:dbl],rmt3901_X_115_bat_oid_dbl:bat[:oid,:dbl],rmt3902_X_116_bat_oid_dbl: bat[:oid,:dbl],rmt3903_X_117_bat_oid_dbl:bat[:oid,:dbl],rmt3904_X_118_bat_oid_dbl:bat[:oid,:dbl],rmt3905_X_119_bat_oid _dbl:bat[:oid,:dbl],rmt3906_X_120_bat_oid_dbl:bat[:oid,:dbl],rmt3907_X_121_bat_oid_dbl:bat[:oid,:dbl],rmt3908_X_122_ba t_oid_dbl:bat[:oid,:dbl],rmt3909_X_123_bat_oid_dbl:bat[:oid,:dbl],rmt3910_X_124_bat_oid_dbl:bat[:oid,:dbl],rmt3911_X_12 5_bat_oid_dbl:bat[:oid,:dbl],rmt3912_X_126_bat_oid_bit:bat[:oid,:bit],rmt3913_X_128_bat_oid_bit:bat[:oid,:bit],rmt3914_X_ 130_bat_oid_oid:bat[:oid,:oid]) := user.l2(); Server version on Mac and Linux machines: MonetDB 5 server v11.21.5 "Jul2015" (64-bit, 64-bit oids, 128-bit integers) Thanks. Kind regards, Bart

1 0

Duplicate views problem
by Dennis Pallett 11 Nov '15

11 Nov '15

Hi all, We're having a problem whereby it's somehow possible in a MonetDB database to have multiple views with the same name. See attached screenshot of an example. Any ideas on how this is possible? Seems like a bug but unfortunately we can't reliably reproduce it. It just happens every now and then. Best regards, Dennis

2 2

Usage of pool
by poornima＠apakau.com 11 Nov '15

11 Nov '15

Hi, I am trying to use monetdb with Hikari Pool. I have set the connection timeout to 4500. I have 4 max connections. The query execution takes at the max 20 ms. I am testing with 250 threads and 100000 requests. But I get a connection time out as follows : 11 09:40:24 PST.DEBUG118*HikariPool~logPoolState@339: Timeout failure pool HikariPool-0 stats (total=4, active=3, idle=1, waiting=1) The waiting number increases to a large number as 139. The idle drops to 0 in just 4 such messages. My Hikari pool configuration is as follows _dataSource = new HikariDataSource(); _dataSource.setDriverClassName("nl.cwi.monetdb.jdbc.MonetDriver"); _dataSource.setJdbcUrl("jdbc:monetdb://" + _reg.getStrProp("tdbUrl")); _dataSource.setUsername(_reg.getStrProp("tdbUsr")); _dataSource.setPassword(_reg.getStrProp("tdbPswd")); _dataSource.setMaximumPoolSize(4); _dataSource.setConnectionTimeout(4500); _dataSource.addDataSourceProperty("cachePrepStmts", "true"); _dataSource.addDataSourceProperty("prepStmtCacheSize", "250"); _dataSource.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); Do I need to change the configuration ? Or am I supposed to change the pool ? Which is the pool that is used with monetdb JDBC driver ? Regards, Poornima.

1 0

thanks for BEST EFFORT
by Anthony Damico 11 Nov '15

11 Nov '15

1 0

Trying to understand select performance when returning many columns
by Ben Jeffery 09 Nov '15

09 Nov '15

I have a table of 93 columns and ~90M rows which is a 60GB "bat" directory on disk. Mostly I'm doing things with a few columns - Monet has been _amazing_ for those use cases so thanks for all your hard work. However sometimes I need to return all columns for a small subset of rows and I can't quite understand what Monet is doing or why it is taking a long time. Here's some timings for 'select * from table order by "unsorted_numerical_column" limit N;' caches all flushed before each query: N=1 907ms N=100 3105ms N=200 6782ms N=500 14656ms N=1000 30100ms N=10000 71093ms For the last two my 32GB ram is being completely filled with the cache. I'm clearly missing something in my understanding as I don't see why that should be needed. Surely you only need to read the whole of the sort column and then grab only N rows from all the other columns? Is there anything I can do to reduce the amount of data loaded? The output of "explain" is at https://gist.github.com/benjeffery/30e9f6173b625f5d0c31 <https://gist.github.com/benjeffery/30e9f6173b625f5d0c31if>. Thanks, Ben

1 0

MonetDB JDBC driver and IntelliJ IDEA 15
by Endre Palatinus 09 Nov '15

09 Nov '15

Dear MonetDB users, I would like to access my MonetDB (latest version: v1.7 (Jul2015-SP1)) database through JDBC (MonetDB JDBC driver version 2.18) using IntelliJ IDEA 15.0 under Mac OS X 10.9.5. The "Test connection" function succeeds, but the "Refresh" function crashes in the Database tool of the IDE with the Exception below. The same problem happened when I was trying to connect to PostgreSQL 9.4 with a PostgreSQL JDBC driver older than JDBC version 4.2. I guess that MonetDB's JDBC driver has JDBC version 4.0 only. Any ideas on how to fix this? Best regards, Endre Palatinus ===== Exception: ====== TABLE_TYPE unavailable java.lang.NullPointerException at nl.cwi.monetdb.jdbc.MonetResultSet.getObject(MonetResultSet.java:1945) at com.intellij.database.remote.jdbc.impl.RemoteResultSetImpl.getObject(RemoteResultSetImpl.java:77) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323) at sun.rmi.transport.Transport$1.run(Transport.java:200) at sun.rmi.transport.Transport$1.run(Transport.java:197) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.Transport.serviceCall(Transport.java:196) at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$254(TCPTransport.java:683) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler$$Lambda$2/490517473.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276) at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253) at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162) at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:194) at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:148) at com.sun.proxy.$Proxy130.getObject(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:124) at com.intellij.execution.rmi.RemoteUtil.access$100(RemoteUtil.java:36) at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:229) at com.sun.proxy.$Proxy131.getObject(Unknown Source) at com.intellij.database.util.JdbcUtil.resultSetToListSafe(JdbcUtil.java:61) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.getAllTableTypes(DatabaseModelLegacyLoader.java:966) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadDataSourceStateInner(DatabaseModelLegacyLoader.java:127) at com.intellij.database.dataSource.DatabaseModelLegacyLoader.loadInner(DatabaseModelLegacyLoader.java:88) at com.intellij.database.dataSource.DatabaseModelLoader.performLegacyIntrospection(DatabaseModelLoader.java:197) at com.intellij.database.dataSource.DatabaseModelLoader.performIntrospection(DatabaseModelLoader.java:99) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:61) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:36) at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:34) at com.intellij.database.dataSource.AbstractDataSource.performJdbcOperation(AbstractDataSource.java:116) at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:34) at com.intellij.database.dataSource.DataSourceUiUtil$2.run(DataSourceUiUtil.java:188) at com.intellij.openapi.progress.impl.CoreProgressManager$TaskRunnable.run(CoreProgressManager.java:563) at com.intellij.openapi.progress.impl.CoreProgressManager$2.run(CoreProgressManager.java:142) at com.intellij.openapi.progress.impl.CoreProgressManager.a(CoreProgressManager.java:446) at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:392) at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:54) at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:127) at com.intellij.openapi.progress.impl.ProgressManagerImpl$1.run(ProgressManagerImpl.java:126) at com.intellij.openapi.application.impl.ApplicationImpl$8.run(ApplicationImpl.java:365) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at org.jetbrains.ide.PooledThreadExecutor$1$1.run(PooledThreadExecutor.java:55) and 8 duplicate reports

1 0

setting up db with storagemodel.sql
by Lynn Carol Johnson 08 Nov '15

08 Nov '15

Hi all - I am new to monetdb. I am running on a MAC (OS X 10.9.5) I have installed monetdb, I have setup a dbfarm, created a couple of databases, and loaded tables. I would like to run some analysis on the amount of storage used for some of my tables. I have seen the description of autoloading scripts here: https://www.monetdb.org/book/export/html/242 And examples of running the storage() command here: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/storage-model But I get "no such operator 'storagemodlinit' when I try to execute "call storagemodelinit()" . What command must be run to include the .sql extensions with the database? Is something run when we create the dbfarm? When we create the individual databases? I can see the .sql extensions in my /usr/local/monetdb/lib/monetdb5/createdb folder on my MAC. I'd appreciate any pointers on how to set this up. Googling has not provided me with any answers. Thanks - Lynn

3 7