Hi All,
I am analysing the monetdb memory access pattern to identify various strides in LLC accesses. But most of the accesses per object are being observed as non-strided which is not what I expected. As monetdb involves BATs and these are arrays so I was thinking that there would be more strided pattern rather than random accesses unless BAT arrays are being treated as trees and random access is being used to access the items. To understand it further, I have now retrieved the execution plan of TPCH Q1 and the plan has various segments like follows:
X_455:bat[:oid,:str] := sql.bind(X_7,"sys","lineitem","l_linestatus",2,5,16);
X_1346 := algebra.kdifference(X_400,X_455);
X_1379 := algebra.kunion(X_1346,X_455);
X_1412 := algebra.leftjoin(X_1180,X_1379);
(X_1452,X_1453) := group.multicolumns(X_1293,X_1412);
X_1514 := bat.mirror(X_1452);
X_1516 := algebra.leftjoin(X_1514,X_1412);
I have two questions:
1) As there are many repeated segments like above in whole query plan, so what is the context behind multiple repeated segments please.
2) I want to understand what is the input to most common operators like leftjoin, kdifference, kunion and whether they process those inputs sequentially or randomly. What is the best way to get this understanding please? From the plan it is obvious that the input is the BAT(s), but how do the operators access the BAT is really a question.
Thanks for the help.
Kind Regards, Ahmad
I have searched entire archive, and this question have been asked many
times i.e. to get the space back from monetdb after deleting some rows in
monetdb.
The answer I found was not consistent
one of them was
COPY SELECT * FROM <table> INTO <file>;
DELETE FROM <table>;
COPY INTO <table> FROM <file>;
Which suggest that deleting rows (or cleaning??) table will get back the space.
One was from this question
http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html
1) copy all table contents to another table, drop old table, copy again
I have tried both deleting rows and then droping the table but the
result was inconsistant.
May be monetdb cleans up after some interval
Other Related Questions were
1. http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html
2. FEB 23, 2010
Reeves, Matthew J. wrote:
>* Hello,*>* *>* I have a MonetDB database that contains a table of rolling events.*>* There are 200 million records with 500,000 bulk loaded each day with the*>* oldest 500,000 deleted at midnight. I'm finding the database gets slower*>* as time goes on. It gets to the point of being almost unusable after a*>* week. Is there something I should be doing different?*Hello Matthew,
Thanks for using MonetDB. Interesting application target.
This is a known issue. The system keeps the deleted tuples and
rebuilds the table.
A vacuum-like feature is needed and on the wishlist. For now, taking a copy
of the table would 'solve' your problem.
regards, Martin
>* *>* Thanks,*>* Matt*>*
*
Gaurav Meena
Hello,
I'm working on MonetDB under win7, and I encountered some problems that hinder me
1 - I need to make an amendment on the inner join;
2 - To do this, I ask you to explain how the inner join works;
3 - In what part of the program (source code) is implemented the inner join.
Cordially.
Hi,
It looks like the sql.clients() function was removed (changeset
45a4e2205363). I was wondering why, and whether there is a suitable
alternative?
Regards,
Pete.
Hi, I'm using MonetDB on Windows with the MonetDB.R connector. I'm getting
some very strange errors on a particular column (called `rx_cso_num`) where
*some* of the math functions work and others do not? I tested the median()
function using mclient - I get the same error - so this has nothing to do
with the fact that I'm connecting through R.
I'm running these queries on a confidential data set, so unfortunately I
cannot provide a reproducible example with this.. I'm sorry for the
hassle, and hoping the error messages below might provide enough of a clue
to resolve the issue? If this really isn't enough, I can send some snipped
mclient -X output and/or try my best to create some fake data that also
creates some of these problems?
Thanks!! :)
Here's a list of functions that *work* on the column without a problem:
"count","distinct","min", "max",
"sum","avg","abs","sqrt","floor","ceiling","exp","log","cos","sin","tan","acos","asin","atan","cosh","sinh","tanh"
Here's the errors I get for each of the functions that do not:
[1] "median"
Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) :
Unable to execute statement 'select median(rx_cso_num) from x07'. Server
says '!22003!overflow in conversion of 133 to bte.'.
[1] "sign"
Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) :
Unable to execute statement 'select sign(rx_cso_num) from x07'. Server
says '!TypeException:user.s5_1[25]:'bat.insert' undefined in: _93:any :=
bat.insert(_83:bat[:oid,:int], _91:oid, _90:bte)'.
[1] "stddev_pop"
Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) :
Unable to execute statement 'select stddev_pop(rx_cso_num) from x07'.
Server says '!SELECT: no such unary operator 'stddev_pop(wrd)''.
[1] "stddev"
Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) :
Unable to execute statement 'select stddev(rx_cso_num) from x07'. Server
says '!SELECT: no such unary operator 'stddev(wrd)''.
[1] "prod"
Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) :
Unable to execute statement 'select prod(rx_cso_num) from x07'. Server
says '!22003!overflow in conversion of 133 to bte.'.
Here's my mserver window:
# MonetDB 5 server v11.15.3 "Feb2013-SP1"
# Serving database 'medicare_sample', using 8 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 7.860 GiB available main-memory.
# Copyright (c) 1993-July 2008 CWI.
# Copyright (c) August 2008-2013 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:49800/
# MonetDB/JAQL module loaded
# MonetDB/SQL module loaded
>
Thanks!!!
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below.
I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that
row is clearly missing. But I'm getting a
'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it
me or is this error message wrong?
sql>\d sys.aggregation_book
CREATE TABLE "sys"."aggregation_book" (
"id" INTEGER NOT NULL DEFAULT next value for
"sys"."seq_6205",
"isbn" VARCHAR(9) NOT NULL,
"name" VARCHAR(255) NOT NULL,
"pages" INTEGER NOT NULL,
"rating" DOUBLE NOT NULL,
"price" DECIMAL(6,2) NOT NULL,
"contact_id" INTEGER NOT NULL,
"publisher_id" INTEGER NOT NULL,
"pubdate" DATE NOT NULL,
CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"),
CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY
("contact_id") REFERENCES "sys"."aggregation_author" ("id"),
CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY
("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id")
);
sql>select * from aggregation_publisher;
+------+--------+------------+
| id | name | num_awards |
+======+========+============+
| 1 | Apress | 3 |
+------+--------+------------+
1 tuple (0.588ms)
sql>select * from aggregation_author;
+----+------+-----+
| id | name | age |
+====+======+=====+
+----+------+-----+
0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages",
more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1,
more>'159059725', 'The Definitive Guide to Django: Web Development Done
more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06');
INSERT INTO: FOREIGN KEY constraint
'aggregation_book.aggregation_book_publisher_id_fkey' violated
--
Gijs Molenaar
http://www.astro.uva.nl/people/gijs-molenaar/
as string-related bug has recently been fixed (see MonetDB bug tracker; I don't recall the bug ID right now) and will be shipped with the upcoming Feb2013-SP2 release, a preview of which is avilable from the testing repository.
Stefan
Pete Hollobon <hollobon(a)gmail.com> wrote:
Hi Hannes,
Thanks for looking into this.
I'm using a 64-bit build. Here's my uname and the versions from the
MonetDB binaries.
~% uname -a
Linux lx1 3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012
x86_64 x86_64 x86_64 GNU/Linux
~% /usr/bin/monetdbd version
MonetDB Database Server v1.7 (Feb2013-SP1)
~% /usr/bin/monetdb version
MonetDB Database Server Toolkit v1.1 (Feb2013-SP1)
~% /usr/bin/mclient --version
mclient, the MonetDB interactive terminal (Feb2013-SP1)
support for command-line editing compiled-in
character encoding: UTF-8
However, I realised that my local Feb2013 build was using the shared
libraries from the Ubuntu debs. I fixed this, and that build now seems
to be working properly.
I can still reproduce it with the distributed binaries though.
Whatever bug it was must have been fixed already - apologies for
wasting your time.
Regards,
Pete.
On 17 April 2013 12:56, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote:
> Hello Pete,
>
> thank you for your bug report and the effort you have put into making this
> reproducible for us. However, having followed your instructions with both
> the Feb2013 version as well as the current development branch, I was not
> able to also provoke this issue.
>
> I got the following results (Both Feb2013 and default branch):
>
> ➜ ~ mclient bug -s "select a, count(*) from test where b = 20227 group by
> a"
> +------+--------+
> | a | L1 |
> +======+========+
> | 2T | 193058 |
> | 1T | 202267 |
> | 5T | 87038 |
> | 4T | 100302 |
> | 3T | 142326 |
> | 15T | 95 |
> | 20T | 95 |
> | 30T | 14277 |
> | 7T | 767 |
> | 6T | 22863 |
>
> | 10T | 127 |
> | 6V | 216594 |
> | 9T | 104 |
> | 8T | 88 |
> +------+--------+
> 14 tuples
>
> ➜ ~ mclient bug -s "select a, count(*) from test group by a"
>
> +------+---------+
> | a | L1 |
> +======+=========+
> | 2T | 5799418 |
> | 1T | 6072825 |
> | 5T | 2605374 |
> | 4T | 2998157 |
> | 3T | 4262607 |
> | 15T | 2703 |
> | 20T | 2818 |
> | 30T | 497657 |
> | 7T | 22460 |
> | 6T | 686789 |
> | 10T | 3496 |
> | 6V | 6550540 |
> | 9T | 1853 |
> | 8T | 1639 |
> +------+---------+
> 14 tuples
>
>
> Can you perhaps give more information abuont your environment? For example,
> this a 64 or 32 bit build? You could also try the development default branch
> and see if the problem also occurs there.
>
> Best,
>
> Hannes
>
>
>
>
>
> On 17/04/13 10:33, Pete Hollobon wrote:
>>
>> Hi,
>>
>> I think I've found a bug in MonetDB. It only occurs when grouping by a
>> text column and filtering the results; with no "where" clause, the
>> results are fine. The table is fairly large (~30 million rows).
>>
>> ~% ./generate_test_csv.py test.csv
>> ~% mclient bug -s "create table test (b integer, a varchar(5))"
>> operation successful
>> ~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS
>> ','"
>> 29508336 affected rows
>>
>> There are no nulls or empty strings in column "a", however:
>>
>> ~% mclient bug -s "select a, count(*) from test where b = 20227 group by
>> a"
>> +------+--------+
>> | a | L1 |
>> +======+========+
>> | | 763088 |
>> | 10T | 127 |
>> | 6V | 216594 |
>> | 9T | 104 |
>> | 8T | 88 |
>> +------+--------+
>>
>> When the "where" clause is removed, no nulls are returned:
>>
>> ~% mclient bug -s "select a, count(*) from test group by a"
>> +------+---------+
>> | a | L1 |
>> +======+=========+
>> | 2T | 5799418 |
>> | 1T | 6072825 |
>> | 5T | 2605374 |
>> | 4T | 2998157 |
>> | 3T | 4262607 |
>> | 15T | 2703 |
>> | 20T | 2818 |
>> | 30T | 497657 |
>> | 7T | 22460 |
>> | 6T | 686789 |
>> | 10T | 3496 |
>> | 6V | 6550540 |
>> | 9T | 1853 |
>> | 8T | 1639 |
>> +------+---------+
>> 14 tuples
>>
>> I have tested with the latest MonetDB Ubuntu debs and also with a
>> local build of the Hg Feb2013 branch tip. Both exhibit the same
>> problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb
>> RAM. I've tried with minimal_pipe and the results are the same.
>>
>> Please let me know if any other information would be useful.
>>
>> Regards,
>> Pete.
>>
>>
>>
>> _______________________________________________
>> users-list mailing list
>> users-list(a)monetdb.org
>> http://mail.monetdb.org/mailman/listinfo/users-list
>>
>
> _______________________________________________
> users-list mailing list
> users-list(a)monetdb.org
> http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list(a)monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list
Hi,
I think I've found a bug in MonetDB. It only occurs when grouping by a
text column and filtering the results; with no "where" clause, the
results are fine. The table is fairly large (~30 million rows).
~% ./generate_test_csv.py test.csv
~% mclient bug -s "create table test (b integer, a varchar(5))"
operation successful
~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS ','"
29508336 affected rows
There are no nulls or empty strings in column "a", however:
~% mclient bug -s "select a, count(*) from test where b = 20227 group by a"
+------+--------+
| a | L1 |
+======+========+
| | 763088 |
| 10T | 127 |
| 6V | 216594 |
| 9T | 104 |
| 8T | 88 |
+------+--------+
When the "where" clause is removed, no nulls are returned:
~% mclient bug -s "select a, count(*) from test group by a"
+------+---------+
| a | L1 |
+======+=========+
| 2T | 5799418 |
| 1T | 6072825 |
| 5T | 2605374 |
| 4T | 2998157 |
| 3T | 4262607 |
| 15T | 2703 |
| 20T | 2818 |
| 30T | 497657 |
| 7T | 22460 |
| 6T | 686789 |
| 10T | 3496 |
| 6V | 6550540 |
| 9T | 1853 |
| 8T | 1639 |
+------+---------+
14 tuples
I have tested with the latest MonetDB Ubuntu debs and also with a
local build of the Hg Feb2013 branch tip. Both exhibit the same
problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb
RAM. I've tried with minimal_pipe and the results are the same.
Please let me know if any other information would be useful.
Regards,
Pete.
Hi,
I'm new to the MonetDB, and trying MonetDB on Windows.
At first, I have installed MonetDB on Windows with using
MonetDB5-SQL-Installer-x86_64-20130227.msi.
http://dev.monetdb.org/downloads/Windows/Feb2013-SP1/
Then, I attempted to follow the Getting Started guide.
http://www.monetdb.org/Documentation/Tutorial
However, I couldn't find monetdb.exe(bat?) and
monetdbd.exe(bat?) in my monetdb installed directory.
What can I do for the first step? Any comments?
Regards,
--
Satoshi Nagayasu <snaga(a)uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp