October 2012 - users-list

MAPI - check if connection is usable
by Tapomay Dey 23 Oct '12

23 Oct '12

I am using MAPI C library to talk to multiple monetdb databases. I cache the connection (struct mapi) to reduce overhead. However if a database/machine restarts then the connection is not usable. How do I determine if a cached connection is usable before actually using it. I have tried mapi_is_connected() and mapi_ping(). But mapi_ping always returns 0 while mapi_is_connected always returns 1. Thansk and Regards, Tapomay. _______________________________________________ … [View More]

2 1

PHP Connectivity
by Jeremy O'Sullivan 23 Oct '12

23 Oct '12

Hello, I have some issues connecting to monetDB, using PHP, see below. ==== <?php require '/usr/local/monetdb/share/php/monetdb/php_monetdb.php'; $db = monetdb_connect("sql", "localhost", 50000, "monetdb", "monetdb", "wifi_db"); $result = monetdb_query('SELECT count(*) FROM Summary_Stats'); ?> ==== I get the following error message in the browser: Fatal error: Call-time pass-by-reference has been removed in /usr/local/monetdb/share/php/monetdb/php_monetdb.php on line 302 Has anyone … [View More]

2 1

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","… [View More]

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: 地址：深圳市龙岗区坂田华为基… [View More]

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 … [View More]

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. … [View More]

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-/… [View More]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 [View Less]

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 … [View More]

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? (… [View More]

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 … [View More]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 [View Less]

2 1