- users-list - monetdb.org

Error with concurrent inserts via PymonetDB mapi
by Ioannis Foufoulas 13 Jul '20

13 Jul '20

Hi, I have a weird error when I am sending the following queries in parallel from 2 different python clients with MAPI: insert into table1 select numpy_count(c1) as c1 from view; insert into table2 select numpy_count(c1) as c1 from view2; where numpy_count is: CREATE or replace AGGREGATE numpy_count(val INTEGER) RETURNS INTEGER LANGUAGE PYTHON { import time time.sleep(3) return val.size }; The error is: pymonetdb.exceptions.OperationalError: GDK reported error: GDKreleasesem: semctl failed: Invalid argument However, if I run these 2 queries in parallel from 2 instances of mclient they work and AFTER doing this, they also work from python’s client with mapi. Is there any hint on this? Best, Yannis

2 2

mserver5 options
by - - 12 Jul '20

12 Jul '20

Hi, How to pass/set mserver5 options via monetdb(d) ? I’m trying to get started monetdb in in-memory mode. mserver5 has option --in-memory Run database in-memory only however all attempts to pass it were unsuccessful. -- - -

2 4

Many aggregated columns / using custom aggregation function for pivot
by Daniel Glöckner 10 Jul '20

10 Jul '20

Hi, today I checked out how to implement custom aggregation functions in Python. Works pretty neat and scales well when you have few columns which you want to aggregate, however I have many :) Imagine that the following aggregate functions has variants which perform a similar iteration but call a different function (not sum). The call to numpy.unique() and the iteration would have to be performed for each aggregated column which is bad in terms of performance. Background: I have records in a key / value like structure so there's a key column and a value column. I want to group by primary key and then create a column for each key. It's like pivot but without aggregation. Any ideas how to do this with SQL or custom functions in MonetDB? Kind regards, Daniel CREATE AGGREGATE python_aggregate(val INTEGER) RETURNS INTEGER LANGUAGE PYTHON { try: unique = numpy.unique(aggr_group) x = numpy.zeros(shape=(unique.size)) for i in range(0, unique.size): x[i] = numpy.sum(val[aggr_group==unique[i]]) except NameError: # aggr_group doesn't exist. no groups, aggregate on all data x = numpy.sum(val) return(x) };

1 0

Killing long running SQL queries
by Daniel Glöckner 09 Jul '20

09 Jul '20

Hi, sometimes things just go wrong ;) Is there a way to 1) list (long) running queries and 2) kill them? Kind regards, Daniel

2 1

Re: Re: Bit of documentation lacking (indexes, performance)
by gloeckner.daniel＠gmail.com 09 Jul '20

09 Jul '20

Hi, Thanks Martin Kersten, Alex Ossipov and Martin van Dinther for the valuable advice! It’s actually quite impressive to see how well MonetDB being a “knobless” system performs without any performance tuning. That’s what you do most of your day when using “big data” technologies from my experience 😉 but it’s of course not what you’re ultimately aiming to do… I think it could be considered as a unique selling point for MonetDB. Maybe the website should highlight this more and improve new user’s confidence level (indexes are not missing features but tuned automatically (how?)…., etc.). I’ll give PKs and FKs a shot to see how they affect performance. General advice, hire a working student and have her or him revamp the website 😃 There’s quite a bit of information but sometimes outdated and hard to navigate. My journey into MonetDB land continues, no blockers so far. Kind regards, Daniel

2 1

Bit of documentation lacking (indexes, performance)
by Daniel Glöckner 08 Jul '20

08 Jul '20

Hi, I'm currently in the process of evaluating MonetDB. Hats off for this great product! Very impressed by ease-of-use and performance so far. Looking for information what indexes are used by MonetDB I found a pretty empty page only: https://www.monetdb.org/Documentation/SQLReference/Indexes During our performance evaluation I was searching for common recipes to achieve good performance. Following page is unfortunately also under construction. https://www.monetdb.org/Documentation/SQLReference/PerformanceOptimization/… Is there any performance handbook available? I also did not find information regarding (performance) parameters for the DB server. Kind regards, Daniel

4 3

Huge merovingian.log after update
by Lynn Carol Johnson 07 Jul '20

07 Jul '20

HI all – A week or so ago we ran an upgrade to the lastest monetdb June-2020 patch. Our db was 2 patches old. While the log showed no specific problems, the upgrade effected our databases in that 2 of them would crash when they were accessed, and the third one (the largest one) I could access but could not see any tables. I ended up “destroying” all the dbs and recreating them fomr the saved binary files. Now, we are getting disk full messages (the db is only read – no one is adding to it). I look at the file sizes and there is a huge mervingian.log file: Is it a problem if I delete this file? What will it hurt? Should I “tail” it and save the end of it? Please advise. root@cbsudc01 dbfarmBL]# du -h --max-depth=1 1.3M ./testDB_lynn 52G ./maizeChrom10DB 955G ./maizeFullGenomeDB 1.3M ./testDB 6.9T . [lcj34@cbsudc01 dbfarmBL]$ l -ltr total 6320304952 -rw-rw---- 1 root buckler 339559112 Oct 9 2018 merovingian.log.old drwx------ 4 root root 121 Jun 26 13:54 testDB_lynn/ drwx------ 4 root root 121 Jul 1 17:23 maizeChrom10DB/ drwx------ 4 root root 121 Jul 1 17:23 testDB/ -rw-r--r-- 1 root root 6 Jul 1 17:23 merovingian.pid drwx------ 4 root root 4096 Jul 1 17:24 maizeFullGenomeDB/ -rw-r--r-- 1 root buckler 6471651893248 Jul 6 14:43 merovingian.log [lcj34@cbsudc01 dbfarmBL]$ Thanks - Lynn

3 5

inadvertantly last schema
by Lynn Carol Johnson 29 Jun '20

29 Jun '20

Hi all – I have a monetdb instance with a lot of tables and a lot of data. This data is organized into schemas. While logging on today as the admistrator, I inadvertently typed: sql>schema maizeFullGenome; syntax error, unexpected SCHEMA in: "schema" sql> Instead of “set schema maizeFullGenome;” This has apparently overwritten the data I had in my maizeFullGenome Schema. Is there anyway to get this back besides finding an old copy of the db? The following shows the schema as now gone: sql>set schema maizeFullGenome; operation successful sql>\d sql> Yes, a very stupid mistake. Lynn

2 5

Grant access to sql system catalog
by Suresh Devarakonda 29 Jun '20

29 Jun '20

Hi, I just wanted to check if there is a way to grant access to system tables and views such as sys.tables and sys.schemas to the users other than granting "sysadmin" role. I am not sure what kind of privileges the sysadmin role possess so would like to find if there are other roles or privileges available to access system tables and views. [cid:image001.jpg@01D64952.B2EE5CC0] Suresh Devarakonda 84.51<http://www.8451.com/>° TDO - Database Support Team D: 513.632.3692 M: 859.512.1839 A: 100 W. 5th Street, Cincinnati, Ohio 45202 Cincinnati | Chicago | New York | Portland Facebook<https://www.facebook.com/8451group/?ref=aymt_homepage_panel> LinkedIn<https://www.linkedin.com/company/84-51/?trk=biz-companies-cym> Instagram<https://www.instagram.com/84.51/> Twitter<https://twitter.com/8451group> Please consider the environment before printing this email.

2 1

Predicates in UDFs
by Oxana Dolmatova 25 Jun '20

25 Jun '20

Hello, Could you please explain why predicates with subqueries in UDFs do not work? For example, I have the following statements: create table itab (x int, y int); insert into itab values (1, 0); insert into itab values (2, 1); create function test1(n int) returns int begin while (n > 9) do set n = n -1; end while; return n; end; create function test2(n int) returns int begin while (n > (select 9 from itab) ) do set n = n -1; end while; return n; end; create function test3(n int) returns int begin while (exists (select x from itab where x < n)) do set n = n -1; end while; return n; end; The function test1 can be successfully created and executed, but functions test2 and test3 cannot be created and executed. Thank you and best regards, Oksana

2 3