Hi all –
I have always used the COPY BINARY INTO … commands to load my 2.0 Billion row genetic data into a monetdb table. With 135 columns, it has been blindingly fast.
Last week I moved from the June2016-SP2 release to dec2016-SP2. My binary loads are taking WAY longer. I killed one after 3 hours (via “call sys.stop(pid)” so it could clean up properly). I then started the load again, thinking perhaps the problem was related to the new columns I was adding.
I have since dropped the table and remade it using the same data and scripts that worked in just over 3 minutes in February on the jun2016-SP2 load. It is really chugging along – I’m up to 30 minutes and counting. I don’t have access to the sql log files, but the Merovingian.log shows nothing.
I do notice that previously the binary files, once loaded, were removed from the loading directly. This does not happen now. Were these files previously “moved” and now they are copied?
Has anyone see this performance issue with Dec2016-SP2 COPY BINARY INTO …. Commands?
Thanks - Lynn
Just getting back into trying MonetDB after many years. Got the
environment set up pretty well with the test DB `VOC` loaded up and able
to do all expected in the command line tools. So I figured I would give
a couple of the mentioned GUI tools a shot and see how they work against
the server I just set up on a virtual box.
I picked up the JDBC drivers and installed on my Window's 10 install of
SQuirrel SQL and DBeaver. I got both of the drivers works sort of... On
DBeaver I can execute and see queries in the tool but can't do much more
than that.
Overall looks like an issue with accessing the system table
`sys.environment`, not sure if anything related to set up or rights.
Using the `voc` user as created with the example db in the getting
started. Any help or thought on where to start?
Thanks!
Sandy
-------- Errors
* DBeaver 4.0.8
On inspection of some of the logs I see things like -
`Error loading object metadata from database`
`SQL Error [22000]: SELECT: access denied for voc to table 'sys.environment'
java.sql.SQLException: SELECT: access denied for voc to table
'sys.environment'`
* SQuirreL SQL Client 3.7.1
java.sql.SQLException: SELECT: access denied for voc to table
'sys.environment'
-------- Driver and DB versions
I'm using this JAR for the drivers -
C:\Users\Squidward\Downloads\monetdb-jdbc-2.25.jar, 2.25 (Liberica
20170330 based on MCL v1.15)
MonetDB version - MonetDB v11.25.23 (Dec2016-SP5),
'mapi:monetdb://sandy-VirtualBox-18:50000/voc'
Hi!
May be someone can help me, Im facing the following problem.
Im trying to pull some information from the field nrm_connect_time, I tried
almost all the date/timestamp functions, but all of them failed.
Finally, I decided to CAST as STRING, and see what happends...
If I do a SELECT DISTINCT .. then I have the correct information, if I dont
include the DISTINCT what I see has nonsense...
sql>SELECT nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10) FROM
NRM_20170609 LIMIT 5;
+----------------------+-----------------------+
| nrm_connect_time | left_nrm_connect_time |
+======================+=======================+
| 2017-06-09 00:05:00 | -1-01-01 0 |
| 2017-06-09 00:05:00 | -1-01-01 0 |
| 2017-06-09 00:05:01 | -1-01-01 0 |
| 2017-06-09 00:05:03 | -1-01-01 0 |
| 2017-06-09 00:05:03 | -1-01-01 0 |
+----------------------+-----------------------+
5 tuples (29.802ms)
sql>*SELECT DISTINCT* nrm_connect_time, LEFT(CAST(nrm_connect_time AS
STRING),10) FROM NRM_20170609 LIMIT 5;
+----------------------+------------+
| nrm_connect_time | L2 |
+======================+============+
| 2017-06-09 00:05:00 | 2017-06-09 |
| 2017-06-09 00:05:01 | 2017-06-09 |
| 2017-06-09 00:05:03 | 2017-06-09 |
| 2017-06-09 00:05:04 | 2017-06-09 |
| 2017-06-09 00:05:05 | 2017-06-09 |
+----------------------+------------+
5 tuples (424.306ms)
*The "nrm_connect_time" is defined as TIMESTAMP(0).*
Let me show you a differente example:
sql>*SELECT DISTINCT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS
STRING),10), timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609
LIMIT 5;
+----------------------+------------+------+
| nrm_connect_time | L2 | L3 |
+======================+============+======+
| 2017-06-09 00:05:00 | 2017-06-09 | 2017 |
| 2017-06-09 00:05:01 | 2017-06-09 | 2017 |
| 2017-06-09 00:05:03 | 2017-06-09 | 2017 |
| 2017-06-09 00:05:04 | 2017-06-09 | 2017 |
| 2017-06-09 00:05:05 | 2017-06-09 | 2017 |
+----------------------+------------+------+
5 tuples (707.217ms)
sql>*SELECT *nrm_connect_time, LEFT(CAST(nrm_connect_time AS STRING),10),
timestamp_to_str(nrm_connect_time, '%Y') FROM NRM_20170609 LIMIT 5;
TypeException:user.s16_55[123]:'mtime.timestamp_to_str' undefined in:
mtime.timestamp_to_str(X_196:oid,A1:str);
program contains errors
Regards
Ariel
Hi, all,
Consider the following scenario. I have a bunch of user defined functions, f_i, that create, populate, and use a local temporary table with the same name "res":
create function f_i()
returns table( ... )
begin
# Result stats table.
create local temporary table res(
param1 int,
param2 int
);
/* Populate res */
/* Use res in some queries */
return table(
/* Query involving res */
);
end;
My first question is: Does "create local temporary table x" create a table that is disposed/dropped as soon as the function where it was declared finishes execution? I assumed this was the case, but I'm now skeptical as we are running into all sorts of concurrency issues that point to the "res" table not being cleared out properly from memory.
This takes me to the second question: How do I drop a temporary table if such table exists? The reason for this is to prevent "table create" exceptions (which we are currently running into). I'd like to achieve something along these lines:
create function f_i()
returns table( ... )
begin
# Drop local temporary table if such table exists.
drop local temporary table res; <-------- How do I do this in MonetDB?
# Result stats table.
create local temporary table res(
param1 int,
param2 int
);
/* Populate res */
/* Use res in some queries */
return table(
/* Query involving res */
);
end;
Where, as you can see, I want to explicitly drop the local temporary table "res" if such exists. If I leave the "local temporary" markers in the drop statement, MonetDB complains that it found unexpected "LOCAL" or "TEMPORARY". I wouldn't like to remove these markers because a statement like:
drop table res;
will drop any table in the default function schema with that name.
Any help and hints are well appreciated!
Thanks for the help in advance
~ Luis Angel
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.
All,
I know I've skipped multiple releases so this is a difficult question to answer, but should I expect the Dec2016-SP5 version of MonetDB to be faster than/equal to the Jul2015-SP4 version? I have a parallel set of servers (on the same box, 24 cores 192GB ram, listening to different ports) operating on identical data sets, and the Dec2016 version is slower than the Jul2015 version (~15-20%). My queries are basic joins between two tables with grouping. I have top running on the same box, and I can see that the Jul2015 server uses all 24 cores, but the Dec2016 server barely cracks 2-3.
The MAL plans obtained by explain are totally different (which partly explains the cores being utilized because I can see blocks of 24 commands) and the length of the Dec2016 one is a fraction of the Jul2015 version.
I guess the older version was inefficiently using all 24 cores, but the new version efficiently uses a smaller set of cores??
Thanks,
Dave
All,
Is it expected behavior if adding "insert into xxxx" at the beginning of a sql query completely alters the output of explain? Without the "insert into", explain returns 1490 tuples, but when I add it, explain only returns 88. I was expecting that the number would go up, i.e. that the original plan would be wrapped in some additional commands which would implement the "insert into" part.
Here is my sql:
explain
insert into aggr
select
'CHANNEL',
s.product,
s.coupon,
s.productionyear,
s.tpoflag,
sum(1),
sum(d.scheduled)/1000000,
sum(d.realized )/1000000,
round(100*(1-power(sum(d.realized)/sum(d.scheduled),12)),3) as cpr
from
stt s, dyn d
where
s.loanseqnum = d.loanseqnum
group by
s.product,
s.coupon,
s.productionyear,
s.tpoflag
having
sum(d.scheduled) > 0
;
Typo in formula:does * columns * (average) width per columnmust readrows * columns * (average) width per column,
-------- Original message --------From: Stefan Manegold <stefan.manegold(a)cwi.nl> Date: 6/17/17 12:33 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space
Hi Amir,
May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore), I.e. 1 cr = 10 million = 10^7 = 10,000,000 ?
The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns.
Your 196 GB is far from that.
Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest?
Best,Stefan
-------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space
Hai,
MonetDB doesn’t provide any user definable compressions.
What do you mean with “5cr rows”? Compressed Rows?
198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column).
Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages.
Did you happen to have put larges BLOBs or CLOBs in some columns?
Which MonetDB version and OS do you use?
Regards,
Jennie
> On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote:
>
> Hi,
>
> i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ??
>
> _______________________________________________
> 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
Hi Amir,
May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore), I.e. 1 cr = 10 million = 10^7 = 10,000,000 ?
The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns.
Your 196 GB is far from that.
Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest?
Best,Stefan
-------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space
Hai,
MonetDB doesn’t provide any user definable compressions.
What do you mean with “5cr rows”? Compressed Rows?
198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column).
Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages.
Did you happen to have put larges BLOBs or CLOBs in some columns?
Which MonetDB version and OS do you use?
Regards,
Jennie
> On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote:
>
> Hi,
>
> i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ??
>
> _______________________________________________
> 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
