October 2012 - users-list

how to load a table in MAL client
by Liwen -George- Zhang 23 Oct '12

23 Oct '12

Hi, When using monetdb, one questions that I need your suggestions is how to load a table column to MAL client and manipulate it.For example,sql>create table gzhang_test (intcol1 int,varcharcol1 varchar(50));sql>insert into gzhang_test values (1,'value1'); sql>insert into gzhang_test values (2,'value2');I know how to load a BAT by name in MAL, but for this table, I don't know how to get the name.If I do:mal>X_3 := sql.mvc();mal>X_4:bat[:oid,:int] := sql.bind(X_3,"midas","gzhang_test","intcol1",0); mal>io.print(X_4);I will get ERROR = !SQLException:mvc:SQL module not initialized. Should I load the SQL module in a MAL session, what's the correct way to get the column if not? Thanks,George Zhang _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

How accurate and updated is the comparison: MonetDB-vs-PostgreSQL?
by Yinhe Cao 23 Oct '12

23 Oct '12

Hi, Recently I came across the following comparison: http://database-management-systems.findthebest.com/compare/28-43/MonetDB-vs… But I did not find out which version of MonetDB is used in the comparison. It seems to me that a good fraction of features listed there as unsupported are actually supported in MonetDB5. Any comments on the comparison? Thanks, Yinhe ________________________________ 华为技术有限公司 Huawei Technologies Co., Ltd. [Company_logo] Phone: Fax: Mobile: Email: 地址：深圳市龙岗区坂田华为基地邮编：518129 Huawei Technologies Co., Ltd. Bantian, Longgang District,Shenzhen 518129, P.R.China http://www.huawei.com ________________________________ 本邮件及其附件含有华为公司的保密信息，仅限于发送给上面地址中列出的个人或群组。禁止任何其他人以任何形式使用（包括但不限于全部或部分地泄露、复制、或散发）本邮件中的信息。如果您错收了本邮件，请您立即电话或邮件通知发件人并删除本邮件！ This e-mail and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Re: users-list Digest, Vol 2, Issue 17
by Masood Mortazavi 22 Oct '12

22 Oct '12

If you're building on Unix flavors (Mac OS-X, Linux and any Unix, which I've tried), I think HowToStart.rst provides good information regarding the default settings. The sql front end is already enabled by default, as far as I can tell. I built with the default configuration settings (on Unix-based systems), and was able to run the sql front end and use it. Most sql I've written so far against the test database seem to work, and the explain command is fantastic, and now I would like to learn more about the internals. (VOC database here: http://www.monetdb.org/Downloads/Tutorial) Most significant issue to which I ran in building MonetDB (from a clone of its mercurial repository) on Unix flabors was ensuring that the right libraries were generally not already installed or only partially installed. On Mac OS-X, one uses MacPorts. On Linux/Debian, apt-get worked fine. It is time-consuming to install all the needed libraries on a brand-new, clean system. On Mac OS-X, I also had to change a symbolic link to a library because the library had two instances and the build system was picking it up from a place where the instance was compiled for the wrong architecture (under Lion). The "otool" command was useful in determining the architecture of the libraries on Mac, e.g. otool -fv /usr/lib/libiconv.dylib Best regards, Masood Message: 1 > Date: Sun, 21 Oct 2012 09:36:36 -0400 > From: Medha Atre <medha.atre(a)gmail.com> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: --enable-sql option at the time of compilation > Message-ID: > < > CAPsfZ1Tb7BioyJVrdsBx4V6G2iBM-UPRZmMCMLMOpPCCRi4wpg(a)mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, > > Is it necessary to give --enable-sql option at the time of compilation > if one wants to use SQL as the query language or does it have some > other connotation? The reason for asking is, if I give --enable-sql > option, the configure script compels me to give --enable-monetdb5 > option too. > > I am not sure how these options make difference internally to the > MonetDB server, since as per the MonetDB website SQL is the de facto > language for querying DBs and that is what is used by MonetDB as well. > > Can someone please enlighten? > > Thanks. > Medha > > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

--enable-sql option at the time of compilation
by Medha Atre 21 Oct '12

21 Oct '12

Hi, Is it necessary to give --enable-sql option at the time of compilation if one wants to use SQL as the query language or does it have some other connotation? The reason for asking is, if I give --enable-sql option, the configure script compels me to give --enable-monetdb5 option too. I am not sure how these options make difference internally to the MonetDB server, since as per the MonetDB website SQL is the de facto language for querying DBs and that is what is used by MonetDB as well. Can someone please enlighten? Thanks. Medha _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 3

Re: Improving performance of "select * from table" ?
by Stefan Manegold 17 Oct '12

17 Oct '12

Hi Gordon, indeed, your experiments confirm that most of the time is spent in serializing, sending, and re-parsing your queries huge result. I am wondering what kind of application needs to see the entire database table? No user can seriously handle millions of result tuples with tens (or more) of columns? What is the purpose of using a DBMS if all it needs to do is select * from table ? In my/our opinion, as much as possible of the application logic should be expressed in SQL (plus domain-/application specific extension, if required) and (thus) performed in the DBMS, resulting in only small results that need to be sent back to the client/application. Then the efficiency and performance of the DBMS (server) itself is dominating and more important than the server-client communication. Best, Stefan Assaf Gordon <gordon(a)cshl.edu> wrote: Hello Stefan, > Stefan de Konink wrote, On 10/16/2012 06:49 PM: >> [...] >> What about: the time taken to serialise the results? For MonetDB this is done in a very 'plain text' wire-format. >> [...] >> Are you aware of the PostgreSQL \timing command? In that way you can see outputs similar to that of MonetDB with respect of time taken for a query. > I re-run one test using psql's and mclient's internal timing. === ## Postgres: Select all columns from table $ psql test test=# \o /dev/null test=# \timing Timing is on. test=# select * from bc251; Time: 5506.768 ms test=# select * from bc251; Time: 5446.689 ms test=# ## MonetDB: Select all columns from table $ mclient -d voc sql>\>/dev/null sql>\w-1 sql>select * from bc251; 1657032 tuples (3.8s) sql>select * from bc251; 1657032 tuples (3.8s) sql> === As you said, the reported durations are much shorter than in my previous timings (monetDb: 3.8s vs 2m16s, postgres 5.5s vs 21s). However, those numbers don't reveal the entire story: mclient reports 3.8 seconds, but it took more than 2 minutes (of wall time) for the result message to be printed. I understand that this quite possibly indicates a serialization/transfer bottle-neck, and it could be of lesser interest to the developers, but as an application developer I worry about the end-to-end timing of the query - the time it takes for my application to get the data back. If there are any recommendation on improving those queries, it would be much appreciated. Thanks, -gordon _______________________________________________ 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

3 4

RE: problem with monetdb functionality
by Stefan Manegold 17 Oct '12

17 Oct '12

Shajon, 1. Do you have any evidence in terms of experiments that shows that MonetDB is slow(er) due to not allowing to set multiple variables in a select clause? Can you give example queries and their performance? 2. >From you rather short description, we cannot say much about this. Beware that your systems state may have a significant influence on performance, e.g., - for point (single value select) predicates, the first query builds a has table, while subsequent queries benefit from this hash table; - the first query needs to load the data from disk, while subsequent queries might benefit from (parts of) the data already/still being in memory; in MonetDB itself while the server is running, or in the OS's filesystem cache even after a server restart; - etc. Best, Stefan Shajon <alam(a)finEngine.com> wrote: From: Shajon [mailto:alam@finEngine.com] Sent: Tuesday, October 09, 2012 1:47 PM To: 'users-list(a)monetdb.org' Subject: FW: problem with monetdb functionality From: Shajon [mailto:alam@finEngine.com] Sent: Tuesday, October 09, 2012 12:49 PM To: 'info(a)monetdb.com' Subject: problem with monetdb functionality Hi, I am using monetdb to process millions of records. now I am getting two problem 1. I could not set multiple variable in select query in a function or procedure. For example: select @var1=col1, @var2=col2 from table_name. as like sql server or Sybase database. If I could do ,it would reduce considerable amount of time for some function execution. So do I need to write MAL or any other way? 2. Sometimes same query takes different time in different moment. For example a query at the starting time of server takes 2ms and 10 hours later it takes 14s. if I close & restart server & client its ok. Can anyone suggest me what can I do? Thanks Shajon _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

Improving performance of "select * from table" ?
by Assaf Gordon 16 Oct '12

16 Oct '12

Hello, I'm getting phenomenal performance with MonetDB with almost all of my query types, except one: The most basic "SELECT * FROM mytable WHERE (SOMETHING)" query takes much longer than other RDBMs (mysql, postgresql). Even "SELECT * FROM mytable LIMIT 10;" takes significantly more time than with other databases (and the more columns the table has, the more time it takes). I understand this is not the scenario that MonetDB was designed to address, but perhaps there's a way to improve it? (It's a requirement for my project to be able to export some/all of the data in a table.) Thanks, -gordon _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 5

Re: Improving performance of "select * from table" ?
by Stefan Manegold 16 Oct '12

16 Oct '12

Assaf, do you have a specific reason to use an arbitrary snapshot of the instable development branch of MonetDB? If not, I'd strongly recommend to use the latest Jun2012-SP2 release, respectively the Jun2012 release branch. We'd be curious to learn whether the latest release work better for your data & queries than the development branch. Best, Stefan Assaf Gordon <gordon(a)cshl.edu> wrote: Hello Jennie, Ying Zhang wrote, On 10/16/2012 04:46 PM: > > How many columns and rows does you table have? How many rows does the first query return? what are the execution times of this query on mysql, postgresql and monetdb, respectively? > Below are some test results. Both servers (postgres and mserver) and clients (psql and mclient) are running on the same machine, with 8 cores and 16GB of RAM. The machine was not overloaded during the tests. The databases are stored on the same disk. I've run each test (except 5) couple of times, and listed most common result (so it's not about caching). Using PostgreSQL 9.2.1 (compiled from source) and MonetDB v11.14.0/unreleased (compiled from source). I can provide more technical details if needed. Assuming the "user" time is the time "wasted" by the client program (psql/mclient), and assuming "sys" time is negligible, then subtracting the "user" time from the "real" time gives an indication of how much time the server took to complete the query and transfer the results. It seems in most of those queries, the MonetDB server takes longer than PostgreSQL to complete. One striking difference is test #6 that needs to return just one line: the execution time for MonetDB is 9 seconds - I guess accessing 138 columns takes its toll... ## ## First table (bc251): 1,657,032 rows, 13 columns ## ## MonetDB Table size sql>select "schema", "table", sum("size") as size from storage() group by "schema","table" having "table" = 'bc251' ; +--------+-------+-----------+ | schema | table | size | +========+=======+===========+ | sys | bc251 | 132775936 | +--------+-------+-----------+ ## PostgreSQL Table Sql test=# SELECT pg_size_pretty(pg_relation_size('bc251')); pg_size_pretty ---------------- 212 MB (1 row) ## Test 1: select ONE column ## MonethDB $ time mclient -d voc -s "select seq from bc251;" > /dev/null real 0m15.981s user 0m5.653s sys 0m0.106s ## PostgreSQL $ time echo "select seq from bc251" | psql test > /dev/null real 0m3.565s user 0m2.698s sys 0m0.154s ## Test 2: select ALL columns ## MonetDB $ time mclient -d voc -s "select * from bc251;" > /dev/null real 2m16.388s user 0m38.468s sys 0m0.448s ## PostgreSQL $ time echo "select * from bc251" | psql test > /dev/null real 0m21.771s user 0m17.045s sys 0m0.843s ## Test 3: Select ONE row ## MonetDB $ time mclient -d voc -s "select * from bc251 limit 1" > /dev/null real 0m0.053s user 0m0.003s sys 0m0.004s ## PostgreSQL $ time echo "select * from bc251 limit 1" | psql test > /dev/null real 0m0.072s user 0m0.013s sys 0m0.009s ## ## Second table (NR102): 3,042,983 rows, 138 columns ## ## MonetDB Table Size sql>select "schema", "table", sum("size") as size from storage() group by "schema","table" having "table" = 'nr102' ; +--------+-------+------------+ | schema | table | size | +========+=======+============+ | sys | nr102 | 1408630784 | +--------+-------+------------+ 1 tuple (5.567ms) ## PostgreSQL Table Size test=# SELECT pg_size_pretty(pg_relation_size('NR102')); pg_size_pretty ---------------- 1850 MB (1 row) ## Test 4: Select ONE column ## MonetDB $ time mclient -d voc -s "select seq from NR102;" > /dev/null real 0m29.882s user 0m7.574s sys 0m0.107s ## PostgreSQL $ time echo "select seq from NR102" | psql test > /dev/null real 0m6.925s user 0m4.906s sys 0m0.268s ## Test 5: Select ALL columns ## MonetDB $ time mclient -d voc -s "select * from NR102;" > /dev/null real 39m58.618s user 10m9.066s sys 0m4.468s ## PostgreSQL $ time echo "select * from NR102" | psql test > /dev/null real 4m0.840s user 3m20.866s sys 0m12.592s ## Test 6: Select ONE row ## MonetDB $ time mclient -d voc -s "select * from NR102 limit 1" > /dev/null real 0m9.055s user 0m0.004s sys 0m0.008s ## PostgreSQL $ time echo "select * from NR102 limit 1" | psql test > /dev/null real 0m0.160s user 0m0.016s sys 0m0.006s _______________________________________________ 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

2 1

How to enable Datacell?
by Chang Ge 16 Oct '12

16 Oct '12

Hi, I am using MonetDB 11.11.11, and I am very interested in the data cell externsion. I followed the doc (http://www.monetdb.org/Documentation/Extensions/Streams), but at the first step when I tried to set datacell optimizer, it said sql>set optimizer='datacell_pipe'; SyntaxException:parseError: ';' expected Syntax error detected.See documentation for details Does anyone know how to use datacell? Thanks very much! Best regards, Chang _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Confusion about concurrency with regards to DDL and Insert Into / Select From
by Percy Wegmann 14 Oct '12

14 Oct '12

I'm very new to MonetDB and am just starting to play around with it. One of the things I'm trying to get my head around is the concurrency model. I've observed some things that make sense and others that I don't understand. 1. Concurrent COPY commands into the same table will cause one or the other to roll back. This makes sense as MonetDB seems to lock at the table level. 2. SELECTing data from a table that concurrently has a COPY command running against it doesn't seem to produce any concurrency errors 3. However, if I INSERT INTO table_B (SELECT ... FROM table_A) while a COPY command is running against table_A, I do get concurrency conflicts 4. Also, if I have a COPY command running into table_A and concurrently issue DDL creating a new table_B, I get a concurrency conflict as well Observations 3+4 seem to indicate that DDL operations and INSERT ... SELECT FROM ... use some sort of database-wide lock. This begs the following questions: a) Is there in fact database-wide locking going on here? b) Is there a workaround that would allow me to issue DDL and make also internal copies of tables while concurrently loading data into other tables? c) Is there something fundamental about the architecture of the database that requires database-wide locking for these ops or is this just a detail of the current implementation that could potentially be ironed out? Thanks! Percy _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2