July 2019 - users-list

Indexing support on Json DataType
by Chatchawan Triperm 22 Jul '19

22 Jul '19

Hi All, Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing. Regards, Chatchawan

3 4

Unexpected query result with merge tables and view after upgrade
by Enrico Maria Carmona 22 Jul '19

22 Jul '19

2 2

Questions on embedding R in MonetDB and client pacakge in R
by Andor 18 Jul '19

18 Jul '19

Hello everyone there, I am a beginner user of MonetDB , mainly working with database from R, new to this email list. Is there a seach function somewhere in this list? I can only see the archieve webpage. I have some general user questions, and would greatly appreciate your comments and suggestions. the embedding R in MonetDB.The very latest materials I could find on this topic on MonetDB websites, dated backed in 10/31/2014, I am wondering where I can find more embedding R in MonetDB related information? tutorial, documents etc the client package working with MonetDB from R I only see monetDB.R but not MonetDBLite in the CRAN packages index, yet in MonetDB site , both packages are introduced. any comments on the MonetDBLite, will it be there in the future? Thanks! Andor

1 0

yum upgrade doesn't allow existing db use new window functions
by Andy Allaway 09 Jul '19

09 Jul '19

Hi, I ran: yum upgrade MonetDB-SQL-server5 MonetDB-client When I create a new db I can call: select first_value(col1) over (partition by col2) from analytics; as per the blog monetdb.org/blog/extended_sql_window_functions However a db that existed before the update returns: Function first_value not found. I can see first_value in sys.functions. Any suggestions? Thanks!

2 2

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 04 Jul '19

04 Jul '19

Hi Stefan, Thanks for your reply! We have run the query a few times with different size of data. There we used 16G RAM(actually 13.5G was used), and find the size of 10G's data is the critical point that can run the query. All of the data files' size are listed below, each file name is a table name(there are only a few tables are refered -- store_sales, date_dim, item, customer, catalog_sales, web_sales): 7.4K call_center.dat 1.6M catalog_page.dat 212M catalog_returns.dat 2.9G catalog_sales.dat 27M customer_address.dat 64M customer.dat 77M customer_demographics.dat 9.9M date_dim.dat 77B dbgen_version.dat 149K household_demographics.dat 328B income_band.dat 2.6G inventory.dat 28M item.dat 61K promotion.dat 1.7K reason.dat 1.1K ship_mode.dat 27K store.dat 323M store_returns.dat 3.8G store_sales.dat 4.9M time_dim.dat 1.2K warehouse.dat 19K web_page.dat 98M web_returns.dat 1.5G web_sales.dat 12K web_site.dat So we guess that the monetdb has no memory management? For the output of `mserver5 --version` is: MonetDB 5 server v11.27.13 "Jul2017-SP4" (64-bit, 128-bit integers) Copyright (c) 1993 - July 2008 CWI Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved Visit https://www.monetdb.org/ for further information Found 17.0GiB available memory, 40 available cpu cores Libraries: libpcre: 8.38 2015-11-23 (compiled with 8.38) openssl: OpenSSL 1.0.2g 1 Mar 2016 (compiled with OpenSSL 1.0.2g 1 Mar 2016) libxml2: 2.9.3 (compiled with 2.9.3) Compiled by: monetdb(a)MonetDB-0.0 (x86_64-pc-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 And the size of processes is not limited. To let you reproduce the problem conveniently, I'll provide more details here: you can get tpc-ds from its website(we use version 2.6.0). Install the tpc-ds, access the directory v2.6.0/tools and run `./dsdgen -scale 10 -dir /home/monetdb/tpc-ds_test_data10G` to generate the data. When data has been generated, using the script /expe.sh/ to create tables and load the data. The query script is 123.tpcds.23.sql.(The syntaxs of other queries that tpc-ds generates is not suitable for monetdb all, we don't modify them all when the problem occurred). One more question, I can't get your reply email, so I don't know how to reply you, for this case, I could only send a new mail echo time. Thanks! Regards, Rancho

4 3

Question on communications path between mserver5 and the MonetDB API (libmapi)
by Gotwisner, Dave 04 Jul '19

04 Jul '19

We are transferring a lot of data as the result of some of our queries (20-30 columns of integers and floats) for about 50,000 records, aggregating these from multiple MonetDB instances (on different machines). For this pattern of query, the largest amount of time spent on the query (from the network onwards, ignoring Mserver5 lookup time) is on the data conversion. When running "perf top" on our application, we notice we are spending a significant amount of time (the second most samples per perf top item at 14%) converting the data returned by libmapi from "const char *" to int64_t, int32_t, double, etc. We know the type of the data, and we have very few string columns in the database. Is there an option to MonetDB that allows it to pass binary data rather than textual data back and forth on the wire? Is there an API that will give the data back in the native type rather than in the on-the-wire string type? Both client and server are always running on 64-bit X86 boxes running Linux (meaning, little endian is guaranteed)? We use serialized protobufs whenever we send data on the wire when communicating between our various programs, and the data stream is significantly smaller than sending integers back and forth in ascii. If not, would switching to ODBC / JDBC give us better performance in this area? Thanks, Dave

2 1

Q: Logical plan
by Hanfeng Chen 04 Jul '19

04 Jul '19

Hello, A quick question. Is the logical plan generated by the keyword "plan" is the plan before it is sent to the SQL optimizer *OR* the output plan of the optimizer? >From the online documentation ( https://www.monetdb.org/Documentation/Manuals/SQLreference/PlanSQL) this sentence "*The relational plan used in the SQL optimizer* can be obtained by prepending the query with the keyword PLAN." is not clear to me. Thanks, Hanfeng Chen

2 1