November 2012 - users-list

Support of nulls first / last in order clause
by Matthieu Guamis 23 Nov '12

23 Nov '12

Hello, "NULLS LAST" seems to be part of the SQL 99 standardbut is not implemented in Monetdb. Do you think it couldbe implemented in Monetdb? Can I fill a bug?Can I help you in some way? The syntax is as follows: ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST] In different DBs, the sorting of nulls relative to other values is handled differently. PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 - Higher Oracle - Higher MySQL - Lower Monetdb - Lower Thanks -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 22 bis, rue des Chazots 63170 Aubière/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

database failing to start
by Flavio Coelho 22 Nov '12

22 Nov '12

Hi I had monetdb setup working for few months and recently, all of a sudden the database would not start any more. I have tried everything I could find in the docs: checking permissons reinstalling the packages, deleting and recreating the databases, Farm,, etc... Now I can't even start an new empty database. Here is the erro I get in the log: 2012-11-22 08:31:08 MSG control[15663]: (local): served status list 2012-11-22 08:31:08 MSG merovingian[15663]: startup of database under maintenance 'sapo' forced 2012-11-22 08:31:08 MSG merovingian[15663]: starting database 'sapo', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2012-11-22 08:31:08 MSG sapo[16066]: arguments: /usr/bin/mserver5 --set gdk_dbfarm=/Ldisco1/Monet_dbs/ SAP_farm --dbname=sapo --set merovingian_uri=mapi:monetdb:// bon051.cloud.fgv.br:50000/sapo --set mapi_open=false --set mapi_port=0 --set mapi_usock=/Ldisco1/Monet_dbs/SAP_farm/sapo/.mapi.sock --set monet_vault_key=/Ldisco1/Monet_dbs/SAP_farm/sapo/.vaultkey --set gdk_nr_threads=16 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-11-22 08:31:08 MSG merovingian[15663]: database 'sapo' (16066) has exited with exit status 127 2012-11-22 08:31:08 ERR control[15663]: (local): failed to fork mserver: database 'sapo' appears to shut itself down after starting, check monetdbd's logfile for possible hints Any ideas why this problem may be happening? I have another Ubuntu64 machine with the same versions of monet and it works normally. I must be overlooking some detail of configuration. It may be a good Idea to add a FAQ to the docs with possible causes for well known crashes. I am on an Ubuntu 64 bits with latest MOnetdb: $ uname -a Linux bon051.cloud.fgv.br 3.2.0-26-generic #41-Ubuntu SMP Thu Jun 14 17:49:24 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux administrador@bon051:~$ monetdb -v MonetDB Database Server Toolkit v1.0 (Oct2012-SP1) administrador@bon051:~$ monetdbd -v MonetDB Database Server v1.6 (Oct2012-SP1) thanks, -- Flávio Codeço Coelho ================ +55(21) 3799-5567 Professor Escola de Matemática Aplicada Fundação Getúlio Vargas Praia de Botafogo, 190 sala 312 Rio de Janeiro - RJ 22250-900 Brasil _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 11

Distributing databases/tables
by Pedro Salgueiro 22 Nov '12

22 Nov '12

Greetings! We have been testing MonetDB for use in AWS in the last couple of weeks and we are surprised with its overall performance, even when dealing with large tables and under-powered machines. Still, we were wondering if its possible in anyway to improve the performance by distributing the databases/tables through several machines. While looking at MonetDB documentation and the mailing list archives, I get some mixed answers about this topic and nothing very concrete. We noticed that it's possible to create a cluster of MonetDB instances, but it seems that it's main purpose is to ease the administration of several MonetDB instances and provide a single entrance point to several MonetDB instances. Still, we still have some questions about its use: Is it possible to partition a table across several MonetDB instances in order to improve the performance? Best regards, Pedro Salgueiro _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Aggregation according to multiple criteria at once
by Адель Ризаев 22 Nov '12

22 Nov '12

Hello to Everybody, I'm doing an averaging over one column using some specific range in another column multiple times. Actually the query looks like: select avg(value) from myTable where time<N and time>M. i'm calling that query from the python script multiple times. Can i call the query once in order to reduce the number of requests to the monetDB? Best regards, Adel _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 3

Announcement: New Oct2012-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 21 Nov '12

21 Nov '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2012-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Oct 2012-SP1 bugfix release Bug Fixes * 3155: Provide an option for setting discovery to false * 3161: invalid tuple received from server, got 138 columns, expected 476, ignoring * 3182: When using aggregate SQL functions, precision & decimal metadata for numeric/decimal type returned is 0,0 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Monet Upgrade--Degraded performance
by Jerry Ramey 19 Nov '12

19 Nov '12

Begin forwarded message: > From: Jerry Ramey <jerry.ramey(a)codefutures.com> > Subject: Monet Upgrade--Degraded performance > Date: November 19, 2012 11:51:34 AM MST > To: users-list(a)monetdb.org > > Hello, > > We had previously asked some advice on some slow Monet performance: > http://mail.monetdb.org/pipermail/users-list/2012-September/005765.html > > As per suggestion we upgraded to the Oct2012 release and we are seeing severely degraded performance. Many queries taking over an hour to complete--mostly they are not especially complicated SELECTs with some arithmetical computation, some concurrently though mostly in succession. > > The database is relationally spread across two servers, the most predominant table with approximately 300 million rows per server, the instances are 68.40 GB with 26 (8 cores x 3.25 units) and CentOS 5.4 x64. Each instance is overloaded with IO wait and minimal CPU usage. The available memory is quickly used and doesn't appear to be released. > > Any advice would be appreciated. Let me know what additional information you require. > > Thanks! > Drew M. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Cross-column time computation doesn't appear to work in Oct 2012 release
by Percy Wegmann 19 Nov '12

19 Nov '12

I couldn't find any existing bug logs for the below issue and want to make sure that it's not a known issue before I log it. Basically, performing time computation across columns doesn't work. percy@percy-sandbox:~$ uname -a Linux percy-sandbox 3.2.0-33-generic #52-*Ubuntu* SMP Thu Oct 18 16:29:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux percy@percy-sandbox:~$ monetdbd -v *MonetDB Database Server v1.6 (Oct2012)* * * percy@percy-sandbox:~$ mclient -u monetdb -d click password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012) Database: *MonetDB v11.13.3 (Oct2012)*, 'mapi:monetdb://percy-sandbox:50000/click' Type \q to quit, \? for a list of available commands auto commit mode: on sql>*create table time_table (atime timestamp, btime timestamp, ctime date); * operation successful (33.944ms) sql>*insert into time_table values(timestamp '1970-JAN-1', timestamp '1980-DEC-31', date '2012-JAN-1');* 1 affected row (20.649ms) sql>select * from time_table; +----------------------------+----------------------------+------------+ | atime | btime | ctime | +============================+============================+============+ | 1970-01-01 00:00:00.000000 | 1980-12-31 00:00:00.000000 | 2012-01-01 | +----------------------------+----------------------------+------------+ *1 tuple* (1.402ms) sql>select btime <> atime from time_table; +----------+ | <>_btime | +==========+ | true | +----------+ *1 tuple* (1.208ms) sql>select btime - timestamp '1975-JAN-01' from time_table; +---------------+ | sql_sub_btime | +===============+ | 189302400000 | +---------------+ *1 tuple* (1.389ms) sql>select btime - atime from time_table; +---------------+ | sql_sub_btime | +===============+ +---------------+ *0 tuples* (1.450ms) Expected result: All queries return 1 tuple Actual result: The query that performs date subtraction between two columns returns 0 tuples. Thanks, -- Percy Wegmann _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Monetdb bulk copy crash
by Medha Atre 19 Nov '12

19 Nov '12

Hi, I am using the latest MonetDB (Oct 2012 release) and creating an RDF database of 1.33 billion triples as follows: $ monetdbd start /path/to/dbfarm $ monetdb create rdf1b $ monetdb release rdf1b $ monetdb start rdf1b _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 4

How do I interpret testweb listing for the latest patches the next release?
by Brandon Jackson 17 Nov '12

17 Nov '12

45868:949c8b8db28d Was suggested to me for fixing the recent error with aggregates and scaling. Which area is the best one to go to for receiving the patches going into the next release? Thanks for the guidance. Brandon _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Client PHP too slow with many results
by Eduardo Oliveira 16 Nov '12

16 Nov '12

Hello, I'm finding php client too slow returning big result sets. I'm using php client i i downloaded from http://dev.monetdb.org/downloads/sources/Latest/ MonetDB-11.13.3.tar.bz2 Some benchmark down, anyway of get the results faster? LIMIT 50K [root@monetdb examples]# php simple_query.php *9* [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 50000' > res.txt password: real 0m4.566s <- already counting with some time to password input user 0m1.272s sys 0m0.132s ---- LIMIT 100K [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 100000' > res.txt password: *real 0m7.184s* user 0m2.545s sys 0m0.266s [root@monetdb examples]# php simple_query.php *18* ---------- Code: [root@monetdb examples]# cat simple_query.php <?php require '../lib/php_monetdb.php'; $db = monetdb_connect("sql", '127.0.0.1', 50000, 'adctest', 'adctest', 'adctest') or trigger_error(monetdb_last_error()); $start_query_monetdb = time(); $res = monetdb_query($db, monetdb_escape_string('SELECT * FROM summary_sent LIMIT 100000')) or trigger_error(monetdb_last_error()); while ( $row = monetdb_fetch_object($res) ) { } $time = time() - $start_query_monetdb; echo $time . "\n"; /* Free the result set */ monetdb_free_result($res); /* Disconnect from the database */ if (monetdb_connected($db)) { monetdb_disconnect($db); } -- *Eduardo Oliveira * *IT* ***Email:* eduardo.oliveira(a)adclick.pt <nuno.morais(a)adclick.pt> *Web: * www.adclickint.com ** <http://www.adclickint.com/> * * <http://www.adclickint.com/> <http://www.adclickint.com/> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

4 5