- users-list - monetdb.org

Table size does not decrease despite records deletion
by Manish gupta 26 Mar '17

26 Mar '17

Dear All, Is there a way to truncate the table size when deleting some of the records? In my application, I delete and insert ~1 million records daily. But effectively, it should result in few hundreds additional records. But, I see the size of the table increases by a millions ( that can be seen in storage(), as well as hard disk size ). While the "select count(*) from table" reflects correct number. Is there a way, I can stop increase in table size in my scheme? Regards, Manish

3 10

Memory profiling of monet database
by Manish gupta 24 Mar '17

24 Mar '17

Dear all, I am looking at some memory profiling of monet database. Basically, the database size at disk is ~160 GB ( Although I am not very convinced with this big data size, there are 200M records with ~70 columns distributed among two big tables and several smaller ( relatively ) tables) . Right now, I have 64GB physical RAM dedicated machine for this database, but soon after firing queries on these tables ( with all sort of permutations on columns, but no joins between tables ), the memory is almost fully occupied, and resource crunch kills the performance. Is there any utility, which shows which all columns are in-memory and what is the size? And better some setting through which I can guide which columns should remain in memory and which should immediately trimmed after query returns? I am using windows 16 core machine on 64 bit architecture. Regards, Manish

2 4

Merge table performance on single machine with 4 cores
by Manish gupta 23 Mar '17

23 Mar '17

Dear all, I am checking the performance of merge table vs single table on single machine with 4 cores. The fact table contains ~10million records. I am using # MonetDB 5 server v11.25.9 "Dec2016-SP2" on windows machine ( core i7 16GB physical ram ). I have created a merge table which contains fact table and 3 blank copies of fact table. I am seeing surprisingly slow performance of merge table queries compared to base table queries. Base table query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from t3760_279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (44.2s) Merge Table Query ================= sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from mt279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10; +-----------+------+---------+---------+ | L2 | L5 | f_1_1_7 | f_1_1_5 | +===========+======+=========+=========+ | 125885351 | 14 | SALE | 8084869 | | 125629268 | 15 | SALE | 6852518 | | 121733416 | 16 | SALE | 5440605 | | 110328868 | 13 | SALE | 4842411 | | 100848236 | 11 | SALE | 8068509 | | 100719291 | 16 | SALE | 5822736 | | 93127875 | 11 | SALE | 6529612 | | 91034094 | 13 | SALE | 3401293 | | 90766181 | 10 | SALE | 8084900 | | 88848574 | 10 | SALE | 2660811 | +-----------+------+---------+---------+ 10 tuples (1m 51s) For the same results ( because merge table is containing the base table and 3 blank copies of base table ), merge table query is taking more than 2.5 times the base table runtime. Why is it so? Thanks, Mohit

5 11

How to execute a query string in MonetDB
by Luis Larios 20 Mar '17

20 Mar '17

Hi, I'm attempting to declare a query string to execute in MonetDB. The documentation is not clear if this is possible, but other engines allow this functionality (such as MySQL and MS SQL Sever). Trying the following execute 'select * from tables'; fails with the message Error: syntax error, unexpected STRING, expecting IDENT or sqlINT The reason behind this is that I need to declare a variable *schema name*, such that I can execute a query in the following way: declare s varchar(32); set s = 'the_schema'; execute 'select * from ' || the_schema || '.the_table'; Please let me know if this is even possible in MonetDB or if you have some tips about that. I tried with a *prepared statement*, as given in MonetDB's documentation -- but the following code cannot execute prepare 'select * from ' || the_schema || '.the_table'; because prepare expects an actual query, not a string. I'd like to achieve this with only SQL stored functions. The client I have has to execute the SQL functions directly, and there's no intermediate Java/PHP/etc scripts to build the SQL on the fly before they're being sent to MonetDB server. Thus, I should create a function like so: create function getData( dataSchema varchar(32) ) returns bigint begin declare query varchar(128); set query = 'select count(*) from ' || dataSchema || '.the_table'; return( execute query ); end; My function is more complicated than this, with other tables and even functions. The issue is that functions belong to one schema, and data tables belong to another schema, which is not known in 'compile' time. Thanks for the help! Luis Ángel The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

3 5

weird mserver startup failure on windows
by Anthony Damico 16 Mar '17

16 Mar '17

hi, i am trying to test something with this version of mserver on windows: http://monetdb.cwi.nl/testweb/web/63169:4438fea68169/MonetDB5-SQL-Installer… it installed fine, but when i load up an mserver session, it says: # MonetDB 5 server v11.26.0 "default-4438fea68169" # Serving database 'demo', using 8 threads # Compiled for x86_64-pc-winnt/64bit # Found 15.914 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information !SyntaxException:parseError: !LoaderException:loadLibrary:Loading error failed to open library pyapi (from within file 'C:\Program Files\MonetDB\MonetDB5\lib\monetdb5\lib_pyapi.dll'): The specified module could not be found. !SyntaxException:parseError: !<identifier> expected > and then if i click on the window, it disappears. if i try to connect to an mserver session through MonetDB.R, the mserver window repeats these two lines forever: # SQL catalog created, loading sql scripts once # loading sql script: 09_like.sql here's a screenshot: http://tinypic.com/r/s6rtjd/9 any ideas about what i should try? maybe installing python? but i've never had to do so before, so maybe i'm doing something else dumb.. thanks!

2 3

Checking for a table's existence from a Python connection. (mapi ? sql?)
by Omri Schwarz 13 Mar '17

13 Mar '17

Hi, all. I’m trying to add a MonetDB interface to VoltTron, a Python based server for building and energy management systems. To that end I have to be able to use some of the mclient commands from it, for example, to check if a table exists (\d ), from Python. IIUC, monetdb.mapi.Connection objects should enable me to do that, but I have no idea how. A second question is about performance. The existing databse schema for this product looks like this in MonetDB: CREATE TABLE "voc"."data_test" ( "ts" TIMESTAMP(3) NOT NULL, "topic_id" INTEGER NOT NULL, "value_string" CHARACTER LARGE OBJECT NOT NULL, CONSTRAINT "data_test_topic_id_ts_unique" UNIQUE ("topic_id", "ts") ); There’s a time stamp and topic ID, and SELECT queries are almost always for a narrow range in time, and a small set of topic IDs (usually a set of one). The “value_string” is there because values in the topics can range in type, but are almost always floats. Can I expect MonetDB’s performance to be good For this use case? Or should I force the value_string to a float?

2 1

monetdb to remote monetdbd makes fun of me
by Roberto Cornacchia 09 Mar '17

09 Mar '17

I'm running a dbfarm on a remote server serverX The dbfarm has been created this way (passphrase changed): monetdbd create /var/lib/monetdb/dbfarm monetdbd set control=yes /var/lib/monetdb/dbfarm monetdbd set port=50000 /var/lib/monetdb/dbfarm monetdbd set listenaddr=0.0.0.0 /var/lib/monetdb/dbfarm monetdbd set passphrase=roberto /var/lib/monetdb/dbfarm Port 50000 is open. >From another machine on the same subnet, I request a status: $ monetdb -hserverX -p50000 -Proberto status status: =OK It does connect (using a different port gives a connection refused). However, it just acknowledges the command, without performing it. Whatever command I try, it behaves the same: $ monetdb -hserverX -p50000 -Proberto create x create: =OK $ monetdb -hserverX -p50000 -Proberto create x create: =OK Notice that the second one should have failed. This confirms that the commands are not executed at all. I have had this issue for a long time, it's nothing new. I just never asked before, because I normally solve it with ssh tunneling a port and using it locally. In this case, I cannot use this. Am I missing something, or is it a bug? Roberto

2 2

on windows, is it possible to limit the amount of RAM available to mserver.exe?
by Anthony Damico 04 Mar '17

04 Mar '17

https://www.monetdb.org/Documentation/mserver5-man-page makes it look like i should be able to do this, but i have tried a few different things and none of them have worked. in the mserver window, i just want this line to be reduced to 50MB or something very small. # Found 15.913 GiB available main-memory. mserver window: # MonetDB 5 server v11.25.5 "Dec2016-SP1" # Serving database 'mydb', using 8 threads # Compiled for x86_64-pc-winnt/64bit # Found 15.913 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ # MonetDB/SQL module loaded here's what my database.bat file looks like: @echo off setlocal set MONETDB=C:\Program Files\MonetDB\MonetDB5 set PATH=%MONETDB%\bin;%MONETDB%\lib;%MONETDB%\lib\MonetDB5;%PATH% "%MONETDB%\bin\mserver5.exe" --daemon=yes --set "prefix=%MONETDB%" --set "exec_prefix=%MONETDB%" "--dbpath=C:\Users\damico\AppData\Local\Temp\RtmpgBoQzw\mydb" %* --set mapi_port=50001 if ERRORLEVEL 1 pause endlocal thanks!

1 0

merovingian.log too verbose?
by Roberto Cornacchia 03 Mar '17

03 Mar '17

Is there a way to make monetdbd a bit quieter? In a setting with many connections, it quickly becomes several GB of this: 2016-03-01 10:25:03 MSG merovingian[94243]: proxying client localhost:52272 for database 'xxx' to mapi:monetdb:///var/lib/monetdb/dbfarms/yyy/xxx/.mapi.sock?database=xxx 2016-03-01 10:25:03 MSG merovingian[94243]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying

2 4

Announcement: New Dec2016-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Mar '17

03 Mar '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP2 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/>. Dec 2016-SP2 bugfix release Bug Fixes * 4034: argnames array in rapi.c has fixed length (that was too short) * 6080: mserver5: rel_bin.c:2391: rel2bin_project: Assertion `0' failed. * 6081: Segmentation fault (core dumped) * 6082: group.subgroup is undefined if group by is used on an expression involving only constants * 6111: Maximum number of digits for hge decimal is listed as 39 in sys.types. Should be 38 as DECIMAL(39) is not supported. * 6112: Crash upgrading Jul2015->Jun2016 * 6130: Query rewriter crashes on a NULL pointer when having a correlated subquery * 6133: A crash occurs when preparing an INSERT on joined tables during the query semantic phase * 6141: Getting an error message regarding a non-GROUP-BY column rather than an unknown identifier * 6177: Server crashes * 6186: Null casting causes no results (silent server crash?) * 6189: Removing a NOT NULL constraint from a PKey column should NOT be allowed * 6190: CASE query crashes database * 6191: MT_msync failed with "Cannot allocate memory" * 6192: Numeric column stores wrong values after adding large numbers * 6193: converting to a smaller precision (fewer or no decimals after decimal point) should round/truncate consistently * 6194: splitpart returns truncated last part if it contains non ascii caracters * 6195: Cast from huge decimal type to smaller returns wrong results * 6196: Database crashes after generate_series query * 6198: COALESCE could be more optimized * 6201: MonetDB completely giving up on certain queries - no error and no result * 6202: querying a table with an ordered index on string/varchar column crashes server and makes server unrestartable! * 6203: copy into: Failed to import table Leftover data 'False' * 6205: Integer addition overflow * 6206: casting strings with more than one trailing zero ('0') to decimal goes wrong * 6209: Aggregation over complex OR expressions produce wrong results * 6210: Upgrading a database from Jun2015 or older crashes the server * 6213: SQLsmith causes server to crash * 6217: Segfault in rel_optimizer (sqlsmith) * 6218: grouped quantiles with all null group causes following groups to return null * 6224: mal_parser: cannot refer to types containing an underscore

1 0