- users-list - monetdb.org

MonetDB with OpenCL externsion (Ocelot) query fails trying to cache an empty BAT
by Edward Ching 25 Nov '13

25 Nov '13

I'm running into a problme with Ocelot, the MonetDB with OpenCL extension. I apologize if this is not the right forum to post. I'm seeing a query failure which was caused by MoneDB-OpenCL trying to cache an empty BAT and ended up calling clCreateBuffer with a 0 size buffer and failed. Any select with a where clause would trigger it. Has anyone seen this too? It fails whether I use 32 bit or 64 bit oid, Nvidia or AMD. I'm trying to figure if it's a real issue or if I'm missing anything. Any input greatly appreciated. The database was generated by TPC-H dbgen (details below). The query I used was "select c_custkey, c_name from customer where c_custkey < 2", and here's a trace of the sequence of calls leading to the failure starting from the MAL statement: 1) X_52 := ocelot.thetasubselect(r1_10,A0,"<",0);(see log below) 2) ocl_select.c::ocl_subselect() line 142: ocl_cacheBAT 3) ocl_memmanager.c::ocl_cacheBAT() line 771: allocateBuffer 4) ocl_memmanager.c::allocateBuffer() line 432: clCreateBuffer ===== Source version and environment ===== 1) Ocelot src installed from zip downloaded from monetdb-opencl: msaecker-monetdb-opencl-b513b35eb84a.zip 2) Ocelot configure options: ./configure --prefix=/usr/local --enable-oid32 --enable-optimize --disable-test ing --disable-developer --disable-debug --disable-assert --enable-sql --enable-m onetdb5 --with-opencl=/usr 3) Operating System: Fedora 17 kernel 3.9.10-100 4) Hardware: NVIDIA GTX570 on Intel i7-3820 w/ 16GB mem 5) OpenCL: Nvidia OpenCL that came with CUDA 5.5 SDK 6) Database: generated w/ TPC-H dbgen using size factor s=0.01 ===== operation log follows ===== [root@gen3-2 ocelot]# mclient -ddb001 -lmal mal>ocelot.init(); mal>ocelot.listDevices(); # Device 0: NVIDIA Corporation - GeForce GTX 570 (default device) mal>\q [root@gen3-2 ocelot]# mclient -ddb001 -lsql sql>\d TABLE sys.customer TABLE sys.lineitem TABLE sys.nation TABLE sys.orders TABLE sys.part TABLE sys.partsupp TABLE sys.region TABLE sys.supplier sql>\d customer CREATE TABLE "sys"."customer" ( "c_custkey" INTEGER NOT NULL, "c_name" VARCHAR(25) NOT NULL, "c_address" VARCHAR(40) NOT NULL, "c_nationkey" INTEGER NOT NULL, "c_phone" VARCHAR(15) NOT NULL, "c_acctbal" DECIMAL(15,2) NOT NULL, "c_mktsegment" VARCHAR(10) NOT NULL, "c_comment" VARCHAR(117) NOT NULL, CONSTRAINT "customer_c_custkey_pkey" PRIMARY KEY ("c_custkey"), CONSTRAINT "customer_c_nationkey_fkey" FOREIGN KEY ("c_nationkey") REFERENCES "sys"."nation" ("n_nationkey") ); sql>select count(*) from customer; +------+ | L1 | +======+ | 1500 | +------+ 1 tuple (1.311ms) sql>select c_custkey, c_name from customer where c_custkey < 2; +-----------+--------------------+ | c_custkey | c_name | +===========+====================+ | 1 | Customer#000000001 | +-----------+--------------------+ 1 tuple (1.776ms) sql>set optimizer='cl_pipe'; operation successful (0.981ms) sql>select c_custkey, c_name from customer where c_custkey < 2; invalid/unknown response from server, ignoring output ? #Could not allocate OpenCL buffer of size: 0. Error caching input BAT. sql> sql>explain select c_custkey, c_name from customer where c_custkey < 2; +------------------------------------------------------------------------------+ | mal +==============================================================================+ | function user.s9_1{autoCommit=true}(A0:int):void; | | ocelot.beginQuery(); | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","customer","c_custkey",0); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","customer"); | | X_51 := ocelot.thetasubselect(X_7,X_4,A0,"<",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","customer","c_custkey",2); | | X_52 := ocelot.thetasubselect(r1_10,A0,"<",0); | | X_13 := sql.bind(X_3,"sys","customer","c_custkey",1); | | X_53 := ocelot.thetasubselect(X_13,X_4,A0,"<",0); | | ocelot.sync(X_51); | | ocelot.sync(X_52); | | ocelot.sync(X_53); | | X_15 := sql.subdelta(X_51,X_4,X_10,X_52,X_53); | | X_17 := sql.projectdelta(X_15,X_7,X_10,r1_10,X_13); | | X_18 := sql.bind(X_3,"sys","customer","c_name",0); | | (X_20,r1_23) := sql.bind(X_3,"sys","customer","c_name",2); | | X_22 := sql.bind(X_3,"sys","customer","c_name",1); | | X_23 := sql.projectdelta(X_15,X_18,X_20,r1_23,X_22); | | X_24 := sql.resultSet(2,1,X_17); | | sql.rsColumn(X_24,"sys.customer","c_custkey","int",32,0,X_17); | | sql.rsColumn(X_24,"sys.customer","c_name","varchar",25,0,X_23); | | X_32 := io.stdout(); | | sql.exportResult(X_32,X_24); | | ocelot.endQuery(); | | end s9_1; | | # querylog.define("explain select c_custkey, c_name from customer where c_cu | : stkey < 2;","cl_pipe") : +------------------------------------------------------------------------------+ 27 tuples (1.408ms) sql> =======================================

2 3

monetdbd hangs from Python subprocess
by Christopher Nelson 23 Nov '13

23 Nov '13

Hi, I am writing some automation to control monetdbd programmatically from Python. While the rest of the monetdbd commands work fine, the command: monetdbd start <farm> never returns control to Python. I have tried this on both CPython 2.7 and PyPy 2.0.4. cnelson@hpvm:~/workspace/cql-db-py/cql $ python store.py DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set passphrase=letmein /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set control=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set discovery=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set port=60000 /tmp/test_warehouse' INFO:root:Starting warehouse at '/tmp/test_warehouse' cnelson@hpvm:~/workspace/cql-db-py/cql $ ps a PID TTY STAT TIME COMMAND 31608 pts/3 S 0:00 python store.py 31613 pts/3 Z 0:00 [monetdbd] <defunct> You can see that the process appears to have finished, but never returns control to Python. If I run the 'stop' command from the prompt, everything clears up and finishes: cnelson@hpvm:~/workspace/cql-db-py/cql $ /home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse cnelson@hpvm:~/workspace/cql-db-py/cql $ DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd start /tmp/test_warehouse' INFO:root:Stopping warehouse at '/tmp/test_warehouse' ERROR:root:Unable to run command: '/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse' DEBUG:root:unable to open merovingian.pid: No such file or directory ERROR:root:None Traceback (most recent call last): File "store.py", line 198, in <module> w.stop() File "store.py", line 192, in stop self._control_warehouse("stop") File "store.py", line 98, in _control_warehouse raise subprocess.CalledProcessError(p.returncode, cmd, out) subprocess.CalledProcessError: Command '['/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd', 'stop', '/tmp/test_warehouse']' returned non-zero exit status 1 The error here occurs because the next thing that the script will try to do is 'stop' the monetdbd instance. Which of course I already did by hand. The point is that once I stop the monetdbd instance OOB, the original monetdbd where 'start' was called finally does return control to Python. Which indicates that it is probably not a Python problem. Any ideas?

2 1

LIMIT clause in sub-query?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, I'm trying to use a LIMIT clause in a sub-query but MonetDB doesn't seem to be able to handle it. A very simple example query would be: SELECT COUNT(*) AS gt_result FROM ( SELECT * FROM nyc_buildings LIMIT 10 ) AS temp; The result of this query should be 10 and works fine in PostgreSQL. However MonetDB gives the following error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "<original query>" Is this a bug in MonetDB? Any way to work around it? Best regards, Dennis Pallett

2 1

Enum data type?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, Does MonetDB support some kind of enum type, similar to MySQL's enum type (http://dev.mysql.com/doc/refman/5.0/en/enum.html) i.e. a column that only allows a limited set of values? I couldn't find it in the documentation and I'm unable to simulate it with triggers or constraints. Best regards, Dennis Pallett

2 2

CREATE CLUSTERED INDEX fails
by Michael Wittig 20 Nov '13

20 Nov '13

Hi, I am just getting started with monetdb. I have a table like this: CREATE TABLE values ( "date" DATE, "datetime" TIMESTAMP, "origin" VARCHAR(30), "value" REAL ); Normally I want to do queries like SELECT date, AVG(value) FROM values WHERE date>='2013-01-01' AND origin='sensor1' GROUP BY date; date and datetime are sorted so I would assume that an index on origin will be useful. The possible values of origin are limited so I tried: (Version Feb2013-SP5) CREATE CLUSTERED INDEX index_origin ON values (origin); But the result is syntax error, unexpected IDENT, expecting INDEX in "create clustered" What am I missing? Or is there even a better solution for data that looks like this where origin is sorted within a date. date | datetime | origin | value 2013-01-01 | 2013-01-01 10:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 12:00:00.000 | sensor1 | 12 2013-01-01 | 2013-01-01 14:00:00.000 | sensor1 | 15 2013-01-01 | 2013-01-01 16:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 10:00:00.000 | sensor2 |111 2013-01-01 | 2013-01-01 12:00:00.000 | sensor2 | 222 2013-01-01 | 2013-01-01 14:00:00.000 | sensor2 | 333 2013-01-01 | 2013-01-01 16:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 10:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 12:00:00.000 | sensor1 | 12 2013-01-02 | 2013-01-02 14:00:00.000 | sensor1 | 15 2013-01-02 | 2013-01-02 16:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 10:00:00.000 | sensor2 | 111 2013-01-02 | 2013-01-02 12:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 14:00:00.000 | sensor2 | 333 2013-01-02 | 2013-01-02 16:00:00.000 | sensor2 | 222 Regards, Michael

2 2

COPY INTO with foreign key
by Treffer, Arian 20 Nov '13

20 Nov '13

Hello, I want to import data into a table which has a foreign key to itself: CREATE TABLE "Type" ( "id" INT NOT NULL , "name" VARCHAR(255) NOT NULL , "file" VARCHAR(255) NULL , "componentTypeId" INT NULL , PRIMARY KEY ("id") , CONSTRAINT "fk_Type_componentType" FOREIGN KEY ("componentTypeId" ) REFERENCES "Type" ("id" ) ON DELETE NO ACTION ON UPDATE NO ACTION) ; Now, if I try to import some data, eg: 0;a;a;NULL 1;b;b;0 the second line causes an error: sql>COPY INTO "Type" FROM 'C:/data.csv' USING DELIMITERS ';','\n'; INSERT INTO: FOREIGN KEY constraint 'Type.fk_Type_componentType' violated What is the correct way to import the data? Since I have a lot of tables, I would rather not have to drop and re-create all constraints. Best regards, Arian

2 1

User Defined Aggregations in MonetDB?
by Alastair McKinley 20 Nov '13

20 Nov '13

Dear all, I was wondering if it is possible to build a User Defined Aggregation in MonetDB. For example, rather than do SUM(myfunction(a,b)) I would like to do MYAGG(a,b) where MYAGG internally holds a small buffer of a and b values so that they can be calculated in a batch fashion with a high performance batch implementation of the function. Could MYAGG be implemented in C? Best regards, Alastair

3 6

User Defined Functions returning null
by Alastair McKinley 13 Nov '13

13 Nov '13

Hi all, I am trying to evaluate the performance of MonetDB for an analytical workload. I am using MonetDB-11.15.17 built from source on Ubuntu 13.04. I have encountered a problem with user using defined functions in SQL which I have reduced to this test case. I appears that whenever I have over 200,000 rows in the table, the UDF returns null. First create a test data set with the following python: import random with open("test_data.csv","w") as f: for i in range(0,1000000): f.write("%s,%s\n" % (random.random(),random.random())) Then I use the following to load the data, and try using a simple UDF in SQL. CREATE TABLE TEST ( x float, y float ); COPY 200000 RECORDS INTO TEST from '/home/alastair/test_data.csv' USING DELIMITERS ',','\n','"' NULL AS ''; CREATE FUNCTION MY_UDF (x float, y float) RETURNS float BEGIN DECLARE ret float; set ret = LOG(x/y); RETURN ret; END; SELECT 'udf alone',MY_UDF(0.1,0.2); SELECT 'udf from table',MY_UDF(t.x,t.y) FROM TEST as t limit 1; -- this returns NULL with 200000 records DROP FUNCTION MY_UDF; DROP TABLE TEST; The value returned my MY_UDF is null with 200,000 records, changing to 199,999 records in COPY INTO produces the correct result. If I use a UDF built in C, the problem does not exist at all. I feel like I might have missed something silly but I can't think of anything. Best regards, Alastair

3 5

Spatial reference ID not stored/returned correctly?
by Dennis Pallett 12 Nov '13

12 Nov '13

Hello all, I'm working with the GeoSpatial extension of MonetDB and I might have run into a bug with regards to the spatial reference ID of objects. When adding a new object I specify the spatial reference ID but when retrieving the same object the correct spatial reference ID is not returned. The following queries demonstrate the bug: # Create a new spatial table CREATE TABLE trees (id INT, shape POINT); # insert a new Point object in spatial reference system 4326 (WGS 84) INSERT INTO trees VALUES ( 1, PointFromText('POINT (30 10)', 4326) ); # Retrieve the Spatial Reference ID from the just inserted Point object SELECT SRID(shape) FROM trees; I expect the result to be 4326 but instead it is always 0. Is this a bug in MonetDB / GeoSpatial or am I misunderstanding something? Best regards, Dennis Pallett

2 2

How to reclaim disk space used by intermediates
by Ashish Kumar Singh 10 Nov '13

10 Nov '13

Hi , While going through below section @ http://www.monetdb.org/book/export/html/242 : I understand that monetdb can create a lot of intermediates over the period if time, and when memory is limited can keep on consuming hard disk space forever. Is there a way to reclaim this space if I want to? Running out of diskspace One of the features of MonetDB's execution model is that all intermediates are materialized as memory mapped files. A consequence of this approach is that when memory is too small to keep them around, they will be swapped to disk by the operating system. This can be seen as a decaying free space and ultimately a full disk. In turn this (should) lead to a single transaction abort and removing its disk claim. Evidently, filling your disk depends on the number of users and the complexity of their queries. It aids to the cost of running COPY INTO and queries concurrently. If you have limited resources for concurrent access then the monetdb funnel may be an option to serialize the user requests. A single server can have multipled funnels. Bets Regards, Ashish

2 1