- users-list - monetdb.org

Re: Order indexes: used or not?
by Stefan Manegold 20 Feb '18

20 Feb '18

Hi Torsten, To start mserver5 manually on a database created by monetdb/monetdb, you need to use the same command line (-options) the monedtd use to start mserver5, in particular the valley option. You find the details in the merovingian.log in your dbfarm Best,Stefan -------- Original message --------From: Torsten Grust <torsten.grust(a)gmail.com> Date: 2/20/18 17:08 (GMT+01:00) To: Lefteris <lsidir(a)gmail.com> Cc: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Order indexes: used or not? Hi Lefteris, that's very interesting. Thanks! I was about to experiment with this here, but I stumbled. When you start a database via mserver5 directly (instead of using monetdbd which, as I understand, is the recommend method), does the method of authentication change? I fail to login to my scratch database: $  mserver5 --dbpath=<path>/scratch # MonetDB 5 server v11.27.13 "Jul2017-SP4" # Serving database 'scratch', using 4 threads # [...] # MonetDB/SQL module loaded > [switch to other terminal] $ mclient -d scratch -u monetdb password: <supply password "monetdb"> InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb' Whereas the login works just fine below: $ monetdbd start <path> $ mclient -d scratch -u monetdb password: <supply password "monetdb"> Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.27.13 (Jul2017-SP4), 'mapi:monetdb://closure:50000/scratch' [...] sql> I must be doing something stupid. Cheers, —Torsten On Tue, Feb 20, 2018 at 3:40 PM Lefteris <lsidir(a)gmail.com> wrote: Hi Torsten, the reason why you don't see anything in the EXPLAIN statement is that the use of an index is decided at runtime by the respective operator. The algebra.sort code will have an if statement that determines at query evaluation time if there is a usable ordered index and use it. The best way (for now) to see if the index is used or not is to start the mserver5 with the --algorithms flag. This flag will produce alot of output on the runtime decissions. For your example, I get the following output: #BATproject(l=tmp_523#3-sorted-key,r=tmp_433#3[int]-revsorted-key) #BATproject(l=tmp_523,r=tmp_433)=tmp_323#3-revsorted-key #BATcheckorderidx: reusing persisted orderidx 283 #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_417#3-key 11us #BATgroup(b=tmp_417#3[int],s=NULL#0,g=NULL#0,e=NULL#0,h=NULL#0,subsorted=1): trivial case: 1 element per group #BATprojectchain with 3 (2) BATs, size 3, type int #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_523#3-key 9us And the third line says "BATcheckorderidx: reusing persisted orderidx 283" which is an indication that MonetDB has found the index and is using it. As a side note, I think in this case it has also figured out that x is revsorted-key (aka unique reverse sorted) but I dont know which one wins over the other:) So in short, if you start mserver5 with --algorithms, redirect the console output in a file and search for BATcheckorderidx you will see if the ordered index is used. Please let us know if there are cases that ordered index should be used but it is not. lefteris On Fri, Feb 16, 2018 at 10:28 AM, Martin Kersten <martin.kersten(a)cwi.nl> wrote: > Hi > Same holds for ordered indices > > On 16/02/2018 10:27, Torsten Grust wrote: >> >> Hi Martin, >> >> thanks for the super-quick reply. You are talking of /imprints/ here but >> I assume that the same is true for /order indexes/ (these are separate >> concepts, right?). >> >> Best wishes, >> —Torsten >> >> >> On Fri, Feb 16, 2018 at 9:55 AM Martin Kersten <martin.kersten(a)cwi.nl >> <mailto:martin.kersten@cwi.nl>> wrote: >> >> Dear Torsten >> >> The use of an imprint is only part of the algebra.select >> implementation >> and depends on actual several properties. >> >> In this particular query, the SQL compiler when encountering an order >> by >> issues a sort statement, even if this is strictly not needed. That >> will be detected >> by the sort implementation. >> >> regards, Martin >> On 15/02/2018 23:31, Torsten Grust wrote: >> > Dear all, >> > >> > given a table definition and (explained) SQL query as follows: >> > >> > |CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), >> (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON >> foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; | >> > >> > In the |EXPLAIN| output, am I supposed to see that the order index >> is indeed used? I currently do see an |algebra.sort| operation, of course, >> but do not see any reference to the |foo_x| index. I had expected to find an >> |bat.getorderidx| operation in the >> > MAL program. >> > >> > Can anyone shed light on this? I'd be grateful. >> > >> > Cheers, >> > >> > —Torsten >> > >> > -- >> > | Prof. Dr. Torsten Grust >> > | Database Systems — Universität Tübingen (Germany) >> > | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> <mailto:torsten.grust@uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de>> >> > | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> <http://db.inf.uni-tuebingen.de> >> > >> > >> > _______________________________________________ >> > users-list mailing list >> > users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> > https://www.monetdb.org/mailman/listinfo/users-list >> > >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> >> https://www.monetdb.org/mailman/listinfo/users-list >> >> -- >> | Prof. Dr. Torsten Grust >> | Database Systems — Universität Tübingen (Germany) >> | ✉︎ torsten.grust(a)uni-tuebingen.de >> <mailto:torsten.grust@uni-tuebingen.de> >> | db.inf.uni-tuebingen.de <http://db.inf.uni-tuebingen.de> >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany)| ✉︎ torsten.grust(a)uni-tuebingen.de| db.inf.uni-tuebingen.de

2 1

Order indexes: used or not?
by Torsten Grust 20 Feb '18

20 Feb '18

Dear all, given a table definition and (explained) SQL query as follows: CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1), (2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; In the EXPLAIN output, am I supposed to see that the order index is indeed used? I currently do see an algebra.sort operation, of course, but do not see any reference to the foo_x index. I had expected to find an bat.getorderidx operation in the MAL program. Can anyone shed light on this? I'd be grateful. Cheers, —Torsten -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust(a)uni-tuebingen.de | db.inf.uni-tuebingen.de

3 5

matrix manipulation
by Jiří Fejfar 20 Feb '18

20 Feb '18

Dear MonetDB users! I would like to do some simple calculations (matrix multiplication and inversion) with large matrices (cca 40k by 40k or more). We are using PostgreSQL & R but we are facing problems when matrix doesn't fit memory. My initial idea was to use SciQL, but probably it is not merged into MonetDB yet [1]? I am sending also short presentation [2] illustrating nature of our data and calculation (slide 6). Thank you for your suggestions! Jiří. [1] https://www.monetdb.org/pipermail/users-list/2014-May/007319.html [2] https://drive.google.com/open?id=1P4Nuo_YuqhNyxoBqQHeIw6WiXe28I8kE

1 0

Select from remote table leaves session open
by Николай Чернов 19 Feb '18

19 Feb '18

I have two databases (v11.27.13 both) on two Debian8 machines. One with corrupted table (in my case it got corrupted due to bug in Jul2017-SP1) and another with remote table to the corrupted table. When I run the SELECT query from the remote table I get an error. And the session, opened by this query to the first DB did not close. I see it by querying 'sys.sessions' table. And every time I run this SELECT I get additional record in 'sys.sessions'. This happens only if en error occurs, on successful SELECT the session immediately disappears. How can I force this sessions to close or is it a bug?

1 0

Converting a timestamp to a UNIX timestamp
by Bernát Marcell 15 Feb '18

15 Feb '18

Dear MonetDB users, I'm looking for a way to convert a timestamp (or timestamp string) like "2018-02-14 23:59:59" to a UNIX timestamp using MonetDB's functions. In MySQL this can be done using the function UNIX_TIMESTAMP(), so I'm looking for the MonetDB equivalent of that, if it exists. I found sys.epoch(), but that does the opposite of what I need. Thanks in advance. Best regards, Marcell

1 1

Announcement: New Jul2017-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 12 Feb '18

12 Feb '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2017-SP4 bugfix release (11.27.13) Build Environment * Added the .lib and .h files needed for building extensions to the Windows installer. Bug Fixes * 3470: Support setClob without length restrictions * 6468: JDBC 2.27 fails with year < 1000 * 6482: Query failures on order by on union * 6483: Monetdb crashes, on query * 6487: UNION of NULLs with several tables fails * 6488: Semijoin returns duplicate values from a column with unique values * 6489: Sqlitelogictest - Wrong result set of complex conditional query * 6490: Sqlitelogictest - Select query with an IN clause parse error * 6491: SELECT IN returns NULL instead of false when not found * 6492: Persistent hashes stored and then ignored. Storage info not in sync with actual indices. * 6493: Sqlitelogictest - Aggregation query on empty table with DISTINCT clause * 6494: Sqlitelogictest - Algebra operators priority in select query * 6495: Sqlitelogictest - Omitting AS in a result set column alias name * 6496: Sqlitelogictest - Select interval comparisons between floating-points and NULL * 6497: Sqlitelogictest - Select not between query producing wrong results * 6498: large virtual memory spike on BLOB column COUNT * 6499: Crash when trying to replace a function defined in sys from a different schema * 6502: Query with multiple limit clauses does not return anything * 6508: Segmentation fault in mserver5 on Python2 UDF with TIMESTAMP column input that has NULL values (conversion.c:438, PyNullMask_FromBAT) * 6510: Sqlitelogictest: Wrong output in aggregation query * 6512: Monetdb crashes on query with limit after sort with case * 6514: Sqlitelogictest: Range query between NULL values not possible * 6515: Insert null second interval value results in 0 * 6516: Sqlitelogictest unknown bat append operation * 6517: Sqlitelogictest overflow in conversion during MAL plan execution * 6518: Sqlitelogictest: count aggregation with not in operator * 6519: Sqlitelogictest: algebra join between lng and int BATs undefined * 6520: UPDATE with correlated subquery causes assertion (or segfault) * 6522: Sqlitelogictest: IN operator return a single column * 6523: Sqlitelogictest: Case statement subquery missing * 6524: Sqlitelogictest: Crash in aggregation query with IN operator * 6527: Crash using order by alias in subquery

1 0

MonetDB Connection drops issue on concurrency
by Sharma, Sreejith 12 Feb '18

12 Feb '18

Hi All, We have a situation where we are trying to emulate a scenario where we have multiple clients hitting into MonetDB instance in parallel, I mean with concurrency of for say 12 users. Queries are going through fine, however we notice connection drops in some cases and queries get cancelled. We assume, this happens when port is idle and when trying to reconnect there is a SQLConnect failure issues. If it's queries are firing uninterruptedly no issues spotted, if there is any idle situation the issue occurs. Can the "forward" - "redirect" option help here? If not, what other options? Regards, Sreejith

1 0

Diacritic removal
by Roberto Cornacchia 09 Feb '18

09 Feb '18

Just wondering, before I do it myself, whether "normalizing" strings by removing diacritics is a feature that you guys would consider interesting to implement. (I am aware that results of this "normalization" can be unsatisfactory in some cases - I'm ok with most obvious cases covered) Basically, something like what iconv does here: $ echo "Ã é ç" | iconv -f UTF-8 -t ASCII//TRANSLIT A e c I can imagine that these above would be pretty easy to solve using the same hash lookup approach used for case conversion. These, however, are example that produce more than one symbol in output. So that would be a little more complicated. $ echo "æ ß" | iconv -f UTF-8 -t ASCII//TRANSLIT ae ss So I have two questions: 1) Is this something you guys (MonetDB developers) would be interested in implementing? 2) If not - in that case I'll give it a go myself, would you advise an approach similar to the case conversion or to use the iconv lib? Thanks for your input. Rboerto

2 2

Install on Linux with no access to Internet
by ALEX OSSIPOV 08 Feb '18

08 Feb '18

I am trying to install MonetDB with client on the host without access to Internet. Are there any instructions to follow? Thanks Alex

3 5

MonetDB Evaluation - Excessive slowness in a particular query
by Alfio Bettini 06 Feb '18

06 Feb '18

Hi all, we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software. We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu). All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed: SELECT id, count(distinct value) FROM table GROUP BY id; The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types. The fields used in the query have these data types: id: int value: bigint Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow? Kind Regards, Alfio Bettini The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA<http://www.eidosmedia.com>

2 3