- users-list - monetdb.org

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

Using the Monet API to connect to MonetDB if MonetDB comes up AFTER our application
by Gotwisner, Dave 24 Jun '19

24 Jun '19

I am currently trying to update my code that uses the Mapi API to retry connections if we start an application when MonetDB is down. Our programs do not run interactively, so detecting the failure, terminating the program, and restarting the application is problematic. There are a couple of issues here that I can use help with. What we are doing is calling mapi_connect(). Mapi_connect returns what appears to be a pointer (it's non-zero). So we call mapi_error with this return value. If we are not connected, we get an MERROR, not an MTIMEOUT, which is what I would expect. With an MERROR failure, we call mapi_error_str() to see what kind of error. We then must do a string compare (actually, a strstr() call looking for a "Connection refused" pattern inside the string. Is there a way to get an enum or #define value instead, with a different function call? Sort of like looking at errno rather than calling strerror(errno) and doing a string compare on the result. This would be way more efficient if we could do this. Yes, errors should be rare, but determining the type of error (or protecting against a string text change on an RPM update) shouldn't be expensive. We then call mapi_explain() to stderr. Is there a routine that returns the value of the output of explain() to a std::string or a "const char *"? We have a logger that does stuff with the strings before logging (timestamping, __line__ __file__, putting error codes, etc. Having the multiline output of the explain() information would be very useful for us. The above process is executed multiple times until we connect. Since the failed connects return what appears to be a pointer, do we need to do anything to free the pointers up? It doesn't appear that we have a memory leak (I ran valgrind). Note that if the connect fails, calling mapi_destroy() crashes. From the documentation, "mapi_destroy()" does "Free handle resources", so it seems reasonable to call it after a failed (and non-null) mapi_connect(). It should probably do nothing, it should not crash. Also, it appears that if I connect multiple times without disconnecting (I had a bug in my code), I can't connect more than 65 times (simultaneous connections). Is this an API issue or a server issue? The reason I ask, is that if it a client issue, we will be having a single client that potentially does queries against hundreds of servers, and aggregates the result. If it is a server issue, is there a parameter that we can tweak to increase this? We do multiple queries in parallel, each on their own connections. Thanks, Dave

2 2

Guidelines for MonetDB in production environments
by Roberto Cornacchia 20 Jun '19

20 Jun '19

Hi all, I'm struggling with optimizing resource sharing of MonetDB in production environments (see also: https://www.monetdb.org/pipermail/users-list/2018-June/010276.html) We run MonetDB instances for several projects / customers on each of our servers. Each MonetDB instance is a docker container (used mainly because of ease of deployment and environment isolation). It is not unusual to have 5-10 MonetDB containers on the same server. In principle, Docker does not even have much to do with this, but it puts everything in a realistic context. ** Memory mserver5 checks the system memory and calibrates on that. When 10 instances are running, they all assume they have the whole memory for themselves. Docker allows to set limits on the container memory. It does that by using cgroups (so Docker just makes things easier, but it's really about cgroups). However, memory limits set by cgroups are not namespaced ( https://ops.tips/blog/why-top-inside-container-wrong-memory/#memory-limits-… ). This means that each container will still see the whole memory and will simply get killed when the container limit has been reached (definitely not a solution). So far, the only mechanism I know to obtain the correect behavior is to run actual VMs for MonetDB. But this is very cumbersome and I want to avoid that as much as possible. Should we let 10 instances believe they each have the whole memory, and let them fight for it? (well, that's what's happening now, and I know for sure it's bad). Perhaps the solution can be as easy as allowing an explicit max memory setting, together with some documentation on the consequences of using low / high values. ** CPU Again, Docker allows to set quotas per container. I think cgroups CPU limits are namespaced, so perhaps this would just work well, I haven't really tried yet. ** I/O Same issue. It would be ideal to be able to set priorities, so that mserver5 instances that do background work get a lower I/O priority than instances serving online queries. Also, recommendations on swap settings would be interesting. How much swap? How to tune swappiness kernel settings? I am very aware that there is no simple answer to most of these questions. Many variables are in the picture. Still, some general thoughts from the developers would be appreciated. I think I have read pretty much everything has ever been written about MonetDB, but when it comes to resource utilization I have always bumped into the very unrealistic assumption that each MonetDB instance has a whole server for itself. As I mentioned above, things could get already much better with simple improvements, like allowing to set the maximum memory usable by each instance. But more in general, I feel there is much need for some guidelines for production environments. Or at least, to start the discussion. Best regards, Roberto

3 5

Error Upgrading to 11.33.3 (Apr2019)
by Ariel Abadi 19 Jun '19

19 Jun '19

Hi! I have a database that It started on version Dec2016-SP5. I have been upgrading from version to version without any problem. Now, Im trying to do the last upgrade (this means from Aug2018-SP2 to Apr2019. But Im facing an error. This is what I see on the log: 2019-06-06 15:17:55 ERR cli_gm[2188]: #main thread:!ERROR: BBPcheckbats: file /opt/monetdb/cli_gm/bat/50/65/506571.tail too small (expected 127992, actual 125580) 2019-06-06 15:17:55 MSG cli_gm[2188]: !ERROR: BBPcheckbats: file /opt/monetdb/cli_gm/bat/50/65/506571.tail too small (expected 127992, actual 125580) 2019-06-06 15:17:55 ERR cli_gm[2188]: !FATAL: BBPinit: BBPcheckbats failed 2019-06-06 15:17:58 MSG merovingian[2180]: database 'cli_gm' (2188) has exited with exit status 1 2019-06-06 15:17:58 ERR control[2180]: (local): failed to fork mserver: database 'cli_gm' appears to shut itself down after starting, check monetdbd's logfile (merovingian.log) for possible hints Is there anything I can do to solve this problem? Thks in advance Ariel PD.If I go back to version Aug2018-SP2 the database keeps working with no problem.

2 2

Questions about using COPY INTO when using GRANTS on tables
by Gotwisner, Dave 14 Jun '19

14 Jun '19

Our application was working quite well when using COPY INTO when inserting data into our tables as the default MonetDB user "monetdb" into the system schema (sys). We have subsequently added users and granted table access to the various users. At this point, INSERT works successfully, but COPY INTO does not. The error message for the COPY INTO statement indicated that we needed to use "ON CLIENT" in our load. That makes no difference. Here are the relevant commands to the system (note the user names, passwords, schema names, and tables names were modified for this example): Program that sets up our basic database does this: START TRANSACTION; CREATE USER admin WITH UNENCRYPTED PASSWORD '***' NAME 'Admin User' SCHEMA sys; CREATE SCHEMA test AUTHORIZATION admin; ALTER USER admin SET SCHEMA test; CREATE USER update WITH UNENCRYPTED PASSWORD '***' NAME 'Update User' SCHEMA test; COMMIT; Then, the program that creates the tables does the following: START TRANSACTION; CREATE MERGE TABLE x (...); GRANT ALL ON x TO "update"; CREATE TABLE x0 (...); GRANT ALL ON x0 TO "update"; CREATE TABLE x1 (...); GRANT ALL ON x1 TO "update"; CREATE TABLE x2 (...); GRANT ALL ON x2 TO "update"; CREATE TABLE x3 (...); GRANT ALL ON x3 TO "update"; COMMIT; And we insert (or try to) (4 threads ingest into their own table, the merge table is used to combine the result set on queries): START TRANSACTION; COPY 100000 OFFSET 2 RECORDS INTO x0 FROM '/dev/shm/MonetDB/foo.csv' (columnlist) ON CLIENT DELIMETERS ',','\n'; This returns an error: !/dev/shm/MonetDB/foo.csv': cannot retrieve files. Because of company policy, I can't provide the schema, but the columns in the csv file and the schema differ, which is why I am using the (...) and (columnlist) in the example. Again, when I omit the CREATE USER, CREATE SCHEMA, and GRANT clauses, everything works correctly. Any ideas on what is needed to make it work? Right now, the ingest application and MonetDB are on the same server. In the future, that might not be true. We are running 11.33.3 on CentOS 7. Thanks, Dave

2 1

FW: Cancel query
by Zingman, Jonathan 13 Jun '19

13 Jun '19

Yes, sys.queue(), sorry. A couple of clarifications. The cookbook shows mclient, but we're using the API and we assume you can do the same thing from the API as well. Also, we use autogenerated queries that can be hundreds if not thousands of characters long. Matching in a case independent way would be very resource intensive. It's fairly clear from the documentation that MonetDB doesn't stop immediately in the middle of whatever it is doing, so that's understood. But cancelling queries has to be part of a robust database system. My model of how this might work is that the handle that’s returned when we issue a query could be used to associate with some data returned by sys.queue(), preferably tag or start time since those should be unique and fairly short. Then we stash then in some structure, and if a cancel request comes in, we do a much simpler search of the sys.queue() table that's guaranteed to work. JONATHAN ZINGMAN | Manager – TrueCall Data | DIRECT: 510.809.0423 | CELL: 510.823.5638 | www.netscout.com -----Original Message----- From: users-list <users-list-bounces+jonathan.zingman=netscout.com(a)monetdb.org> On Behalf Of Martin Kersten Sent: Tuesday, June 11, 2019 1:56 PM To: users-list(a)monetdb.org Subject: Re: Cancel query This message originated outside of NETSCOUT. Do not click links or open attachments unless you recognize the sender and know the content is safe. On 11/06/2019 22:38, Ying (Jennie) Zhang wrote: > > >> On 11 Jun 2019, at 00:58, Zingman, Jonathan <Jonathan.Zingman(a)netscout.com> wrote: >> >> In the cookbook, you give a good way to find out the status of queries by using the sys.query() call, > > Hai, Jonathan, > > MonetDB doesn’t have a function called sys.query(). Did you mean sys.queue() instead? > >> which returns the list of queries tagged in various ways. Our question is whether there is any way to access, e.g., the tag value. In our application, we need to occasionally cancel queries from our UI. The UI has no direct knowledge of the query. While we might be able to find the query we want to cancel by comparing the query text, our automatically generated queries can be very long, and if there’s some internal reformatting by Monetdb, we might not get an exact match. So is there anything in the handle or elsewhere that we can use to identify the query we want to cancel? > > Correct me if I’m wrong, but I’m not aware of a feature with which you can query the qtag of a query. Some code study will be needed to see if it’s feasible to add such feature, and how. > > You can probably get away with string matching, since MonetDB doesn’t do much/any reformatting next to to-lowercase. Not sure how much text the “query” field of sys.queue() holds. > > In addition, please be aware that > i) sys.queue() is originally develop for debugging purpose… > ii) the minimal cancellation entity is a function, i.e., when MonetDB is busy executing a very long running function, a cancellation will only take effect *after* that function has finished => then MonetDB won’t continue executing the next function. Small correction here, with function we mean a MAL function call. So, if the system is executing a very time consuming join, it will *not* be interrupted, but rather at the end of it the query in which is rans is terminated. > > Regards, > Jennie > >> >> JONATHAN ZINGMAN >> Manager – TrueCall Data >> >> <image001.png> >> 2855 Telegraph Ave, Suite 200 >> Berkeley, CA, 94705-1169 >> >> DIRECT: 510.809.0423 >> CELL: 510.823.5638 >> http://www.netscout.com >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_mailma… > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_mailma… > _______________________________________________ users-list mailing list users-list(a)monetdb.org https://urldefense.proofpoint.com/v2/url?u=https-3A__www.monetdb.org_mailma…

2 1

Cancel query
by Zingman, Jonathan 11 Jun '19

11 Jun '19

In the cookbook, you give a good way to find out the status of queries by using the sys.query() call, which returns the list of queries tagged in various ways. Our question is whether there is any way to access, e.g., the tag value. In our application, we need to occasionally cancel queries from our UI. The UI has no direct knowledge of the query. While we might be able to find the query we want to cancel by comparing the query text, our automatically generated queries can be very long, and if there's some internal reformatting by Monetdb, we might not get an exact match. So is there anything in the handle or elsewhere that we can use to identify the query we want to cancel? JONATHAN ZINGMAN Manager - TrueCall Data [logos] 2855 Telegraph Ave, Suite 200 Berkeley, CA, 94705-1169 DIRECT: 510.809.0423 CELL: 510.823.5638 www.netscout.com<http://www.netscout.com/>

3 2