- users-list - monetdb.org

Performance issue related to dropping (empty) tables
by Gotwisner, Dave 27 Aug '19

27 Aug '19

I am seeing a performance issue related to schema upgrades. This is from a C++ program that uses the MAPI library. We try and keep 30 days worth of data, imported at a fairly high data rate with lots of columns. We use multiple threads to ingest data (bulk insert), and the number of threads is stored in a configuration table. For 20 days worth of data, this works out to around 9000 tables (plus merge tables). We have roughly 6 sets of tables per hour, each of which has 4 tables and a merge table connecting them. Because this is a schema change, we wrap the entire upgrade in a transaction. For the purpose of the test, we had no data in the tables, but only had empty tables. If we change our setting from 4 parallel tables to 2, we end up dropping 9000 tables. About half of the "drop table" commands take 0 seconds. The rest take anywhere between 4 and 12 seconds each. Here is a plot of the time per delete, y access is seconds, x is the statement. The X access is NOT time based: [cid:image001.png@01D5568E.A5389030] Any idea on how we can reduce the time? The total time to delete 9000 tables was 480 minutes. Thanks, Dave

2 4

memory control on large results
by Daniel Zvinca 15 Aug '19

15 Aug '19

Hello, I plan to use the embedded library (custom build) for a project that will likely address very large tables. While the control over statements is handled to the end user, I need an advice how to control memory usage on fetching huge amount of data. Specifically, I already skipped the default "monetdb_result_fetch" and implemented a compressed storage (which optional can be stored on disk), but I wasn't able to see a way of releasing the associated memory for already processed columns (persistent or transient bat). Unless the bat contents is shared between different res_col's, I would like to be able to release that memory to the system before proceeding to the next column. Any suggestion? Thank you, Dan

1 0

Embedded Python Initialization Issue
by Sharma, Sreejith 14 Aug '19

14 Aug '19

All, I am using an Python based UDF and have enabled embedpy=true for DB with Python version 2.7.5. This works fine with CentOS 7, but the same doesn't wok on Red Hat Ent Linux version. Is there any dependency on this? Is it due to OS version? Am getting below error though Python path is proper in Red Hat OS version server. Embedded Python is enabled but an error was thrown during initialization. Can anyone provide some help on this? Both uses same version of MonetDB server version. MonetDB server on which function works fine [user@lnx1535 ~]$ cat /etc/os-release NAME="CentOS Linux" VERSION="7 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="7" PRETTY_NAME="CentOS Linux 7 (Core)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:7" HOME_URL="https://www.centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-7" CENTOS_MANTISBT_PROJECT_VERSION="7" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="7" CENTOS_MANTISBT_PROJECT="CentOS-7" CENTOS_MANTISBT_PROJECT_VERSION="7" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="7" MonetDB server on Azure where function is not working [user@alcazimdbd01 ~]$ cat /etc/os-release NAME="Red Hat Enterprise Linux Server" VERSION="7.5 (Maipo)" ID="rhel" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.5" PRETTY_NAME="Red Hat Enterprise Linux" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:redhat:enterprise_linux:7.5:GA:server" HOME_URL="https://www.redhat.com/" BUG_REPORT_URL="https://bugzilla.redhat.com/" REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7" REDHAT_BUGZILLA_PRODUCT_VERSION=7.5 REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux" REDHAT_SUPPORT_PRODUCT_VERSION="7.5" Regards, Sreejith

2 1

Type of integers used to calculate a SUM
by Jan, Di-An 13 Aug '19

13 Aug '19

With hugeint enabled, it seems that the SUM of INT (32-bit) is done using HUGEINT (128-bit) while the SUM of BIGINT (64-bit) is done using BIGINT (64-bit). That seems weird. Is SUM supposed to use the largest type or use the same type as the input or something else? Database: MonetDB v11.33.3 (Apr2019) sql>CREATE TABLE test (bi BIGINT, i INT); sql>INSERT INTO test VALUES (0x7000000000000000, 0x70000000); sql>INSERT INTO test VALUES (0x7000000000000000, 0x70000000); sql>INSERT INTO test VALUES (1, 3); sql>INSERT INTO test VALUES (2, 4); sql>SELECT SUM(i) FROM test; -- larger than an INT -- +------------+ | L3 | +============+ | 3758096391 | +------------+ 1 tuple sql>SELECT SUM(bi) FROM test; -- larger than a BIGINT -- overflow in calculation. sql>EXPLAIN SELECT SUM(i) FROM test; +------------------------------------------------------------------------------------------------------+ | function user.s10_22():void; | | X_1:void := querylog.define("explain select sum(i) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:hge := aggr.sum(X_17:bat[:int]); | | exit X_74:bit; | | sql.resultSet("sys.L3":str, "L3":str, "hugeint":str, 128:int, 0:int, 7:int, X_18:hge); | | end user.s10_22; | +------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(bi) FROM test; +-------------------------------------------------------------------------------------------------------+ | function user.s12_22():void; | | X_1:void := querylog.define("explain select sum(bi) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:lng] := sql.bind(X_4:int, "sys":str, "test":str, "bi":str, 0:int); | | X_17:bat[:lng] := algebra.projection(C_5:bat[:oid], X_8:bat[:lng]); | | X_18:lng := aggr.sum(X_17:bat[:lng]); | | exit X_74:bit; | | sql.resultSet("sys.L3":str, "L3":str, "bigint":str, 64:int, 0:int, 7:int, X_18:lng); | | end user.s12_22; | +-------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(i AS HUGEINT)) FROM test; +-----------------------------------------------------------------------------------------------------------------------+ | function user.s20_22():void; | | X_1:void := querylog.define("explain select sum(cast(i as hugeint)) from test;":str, "default_pipe":str, 13:int); | | barrier X_75:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:bat[:hge] := batcalc.hge(X_17:bat[:int]); | | X_19:hge := aggr.sum(X_18:bat[:hge]); | | exit X_75:bit; | | sql.resultSet("sys.L4":str, "L4":str, "hugeint":str, 128:int, 0:int, 7:int, X_19:hge); | | end user.s20_22; | +-----------------------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(bi AS BIGINT)) FROM test; +-----------------------------------------------------------------------------------------------------------------------+ | function user.s8_22():void; | | X_1:void := querylog.define("explain select sum(cast(bi as bigint)) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:lng] := sql.bind(X_4:int, "sys":str, "test":str, "bi":str, 0:int); | | X_17:bat[:lng] := algebra.projection(C_5:bat[:oid], X_8:bat[:lng]); | | X_18:lng := aggr.sum(X_17:bat[:lng]); | | exit X_74:bit; | | sql.resultSet("sys.L4":str, "L4":str, "bigint":str, 64:int, 0:int, 7:int, X_18:lng); | +-----------------------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(i AS INT)) FROM test; +-----------------------------------------------------------------------------------------------------------+ | function user.s10_22():void; | | X_1:void := querylog.define("select sum(cast(i as int)) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:hge := aggr.sum(X_17:bat[:int]); | | exit X_74:bit; | | sql.resultSet("sys.L4":str, "L4":str, "hugeint":str, 128:int, 0:int, 7:int, X_18:hge); | | end user.s10_22; | +-----------------------------------------------------------------------------------------------------------+ sql>SELECT SUM(CAST(i AS INT)) FROM test; +------------+ | L4 | +============+ | 3758096391 | +------------+ 1 tuple Also, the cast in SUM(CAST(bi AS BIGINT)) seems to be optimized out while SUM(CAST(i AS HUGEINT)) has one more BAT operation than SUM(i) even though they seem to do the same thing, and the cast in SUM(CAST(i AS INT)) seems to have no effect. Di-an Jan

1 0

Books about Monetdb?
by Nobody Noone 09 Aug '19

09 Aug '19

Hi, Are there any books published about Monetdb? This listserv, monetdb.org and stackoverflow.com seem to be all I can find. Thanks!

2 1

understand how monetdb choose the best query plan
by DEMBELE Simon Pierre 08 Aug '19

08 Aug '19

Hello, J'm new in the use of monetdb, i want to understand how monetdb choose the best query plan. Is the model cost is used for that or what. Please if someone can clarify me. Thank yours time. -- Simon

2 2

Questions on upgrading tables based upon the system tables.
by Gotwisner, Dave 08 Aug '19

08 Aug '19

I am writing code to migrate from one version of our schema to another (such as if we add tables or add columns). The from version is whatever is physically being used, and the new version is compiled into data structures in our code. I can successfully create or remove tables, and add and drop columns. We have multiple tables that we then bind to a merge table, so the schemas must be exact. Our tables have a primary key. For the table and column based changes, querying sys.schemas, sys.tables, and sys.columns gives us everything we need. I can also figure out how to add the primary key on the new table (via alter table commands). However, I can't figure out how to find the set of columns in the primary key, to be able to build up the primary key. Here is the table definition for our tables that go into the merge table (%04d goes from 0000 to the number of threads we send bulk data with): CREATE TABLE if not exists data_availability__%04d ( interval_start BIGINT, source INT, PRIMARY KEY(interval_start, source) ); alter table data_availability add table data_availability__%04d; Looking at system tables gives the following information (before adding this table): sql>select * from sys.tables where name = 'data_availability'; +------+-------------------+------+------+------+-------+------+-------+-------+ | id | name | sche | quer | type | syste | comm | acces | tempo | : : : ma_i : y : : m : it_a : s : rary : : : : d : : : : ctio : : : : : : : : : : n : : : +======+===================+======+======+======+=======+======+=======+=======+ | 8889 | data_availability | 8800 | null | 3 | false | 0 | 0 | 0 | +------+-------------------+------+------+------+-------+------+-------+-------+ 1 tuple sql>select * from sys.keys where table_id = 8889; +------+----------+------+----------------------------------------------+------+--------+ | id | table_id | type | name | rkey | action | +======+==========+======+==============================================+======+========+ | 8887 | 8889 | 0 | data_availability_interval_start_source_pkey | -1 | -1 | +------+----------+------+----------------------------------------------+------+--------+ 1 tuple sql>select * from sys.key_types; +-------------+---------------+ | key_type_id | key_type_name | +=============+===============+ | 0 | Primary Key | | 1 | Unique Key | | 2 | Foreign Key | +-------------+---------------+ 3 tuples sql>select * from sys.idxs where table_id = 8889; +------+----------+------+----------------------------------------------+ | id | table_id | type | name | +======+==========+======+==============================================+ | 8888 | 8889 | 0 | data_availability_interval_start_source_pkey | +------+----------+------+----------------------------------------------+ 1 tuple sql>select * from sys.index_types; +---------------+-----------------------+ | index_type_id | index_type_name | +===============+=======================+ | 0 | Hash | | 1 | Join | | 2 | Order preserving hash | | 3 | No-index | | 4 | Imprint | | 5 | Ordered | +---------------+-----------------------+ 6 tuples sql>select * from sys.columns where table_id = 8889; +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ | id | name | type | type_digits | type_scale | table_id | default | null | number | storage | +======+================+========+=============+============+==========+=========+=======+========+=========+ | 8885 | interval_start | bigint | 64 | 0 | 8889 | null | false | 0 | null | | 8886 | source | int | 32 | 0 | 8889 | null | false | 1 | null | +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ 2 tuples The above data is for the merge table that we will bind the (new) table to. I can get the name and id of the primary key (which is also an index). I could not find a table that gives a column -> key binding. Is there such a table? If not, how do I get this information from MonetDB? Thanks, Dave

2 1

Question on querying two specific columns from sys.columns
by Gotwisner, Dave 07 Aug '19

07 Aug '19

I am trying to query the default and null columns from sys.columns. Both of these are sql key words, so queries either don't work or return the wrong value. Doing a "select default from ss.columns;" returns "syntax error, unexpected DEFAULT in: "select default"" error. Doing a "select null from sys.columns;" returns null for all records, even though it is a Boolean column with true/false values. I am currently working around this by doing "select *" and pulling out the relevant fields, but, I want to protect against a future release possibly changing the column ordering or changing the number of columns in this table (without also requiring an update to our code). Is there a way to escape the column names in a way that gives me what I want? Thanks, Dave

2 1

Re: Alter question about MonetDB
by Ying Zhang 06 Aug '19

06 Aug '19

Hai, This question should have been sent to the users mailing list, so I added that e-mail address. I can confirm this problem with an Apr2019 refreshed yesterday. Please see the 1st attachment. Also, after having restarted both mclients, I can still only query “tbl5” using the mclient on the left side. Please see the 2nd attachment. Can you please fire a bug report for this? Thanks! Jennie > On 6 Aug 2019, at 08:55, 角 <3089454366(a)qq.com> wrote: > > Hi,All: > I am a MonetDB user from China. I have some troubles when I was using MonetDB. I use mclient and enter this sql : "alter table t1 rename to t2；", while this mclient is alive,I use anthor mclient to connect the same database as the former and I enter this sql : "select * from t2" then I get an error : no such table. So how can I use alter command to make all mclients take effect? > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list

1 0

Question on missing BAT file
by Sharma, Sreejith 06 Aug '19

06 Aug '19

All, Is there a way to figure out which BAT files constitutes a specific table in MonetDB? I know the location in sys.storage represents it, but I see one of the tail file is showing missing which is not available neither in sys.storage not in DB folder. We get this error during migration from older version (MonetDB 11.27.3) to MonetDB 11.33.3 version. 2019-07-31 10:43:54 MSG TSV_PROD_DB_A[161617]: !ERROR: BBPcheckbats: cannot stat file /monet_data01/DBFARM_TSV/TSV_PROD_DB_A/bat/10/45/104502.tail Regards, Sreejith

2 2