March 2016 - users-list

Speeding SELECT for a small number of fields OVER large number of record in Perl DBD::monetdb
by Puthick Hok 31 Mar '16

31 Mar '16

Hi, I'm using Perl DBD::monetdb from MonetDB source for close to a year now. Recently, I discovered using SQL SELECT for one or two fields for a large number of records, for example, 100000 records is too slow compared to how faster MonetDB engine is. The same SQL SELECT is way faster in MySQL due to C implementation of DBD::mysql. MonetDB connector in Python is still way faster than Perl DBD::monetdb because it allows user to tell the connector the maximum number of records the user needs and the connector uses Xexport command straightaway and only once. In Perl, DBD::monetdb calls Mapi.pm via getReply which issues Xexport command many times depending on the number of records in the SELECT statement. Each time, it retrieves only 100 records. For 100000 records, it needs to call Xexport 1000 times. I did my own patch to make getReply call Xexport only once a the rest of the records after the initial request. With the patch, the time it takes to select 2 fields for 101024 records drops from more 24 seconds to under 4 seconds. My patch is: <pre> sub getReply { my ($self)= @_; if ($self->{active} == 0) { return $self->getBlock(); } elsif ($self->{next} < $self->{replysize} + $self->{skip}) { return $self->getRow(); } elsif (${self}->{offset} + $self->{replysize} < $self->{count}) { # get next slice my $rs = $self->{replysize}; my $offset = $self->{offset} + $rs; my $therest = $self->{count} - $self->{replysize}; $self->putblock("Xexport $self->{id} $offset $therest"); # FAST #$self->putblock("Xexport $self->{id} $offset $rs"); # SLOW return $self->getBlock(); } else { # close large results, but only send on next query if ($self->{id} > 0 && $self->{count} != $self->{replysize}) { push @{$self->{piggyback}}, "Xclose $self->{id}"; $self->{skip_in}++; } $self->{active} = 0; } return $self->{active}; } </pre> I hope this could help other people. Regards, Puthick

1 0

Re: Temporary duplicated rows
by Alex Manaev 30 Mar '16

30 Mar '16

Effect is reproduced with console on the fresh database. Here is real time console log. Can anybody explain what happens? sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | +------+--------+ 3 tuples (2.551ms) <--- result of previous experiments sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | +------+--------+ 1 tuple (2.746ms) sql>update morgoth_reportquery set status = 2 where id = 6880; 1 affected row (3.306ms) sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | +------+--------+ 1 tuple (2.216ms) sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | | 6880 | 4 | +------+--------+ 4 tuples (2.634ms) sql>select id, status from morgoth_reportquery where status = 4 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (2.337ms) sql>select id, status from morgoth_reportquery where status = 1 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (3.294ms) sql>select id, status from morgoth_reportquery where status = 4 and id = 6880; +----+--------+ | id | status | +====+========+ +----+--------+ 0 tuples (2.416ms) sql>select id, status from morgoth_reportquery where id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 2 | +------+--------+ 1 tuple (2.186ms) sql>select id, status from morgoth_reportquery where status <> 4 and id = 6880; +------+--------+ | id | status | +======+========+ | 6880 | 2 | +------+--------+ 1 tuple (4.145ms) sql> Alexey Manaev, Head of software development CJSC "RUTUBE" On 30.03.2016 17:43, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Temporary duplicated rows (Alex Manaev) > 2. UDF returning 0 as a result (Shmagi Kavtaradze) > 3. Re: UDF returning 0 as a result (Mark Raasveldt) > 4. Re: UDF returning 0 as a result (Sjoerd Mullender) > 5. Re: Find all running processes? (Pierre-Adrien Coustillas) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Wed, 30 Mar 2016 14:18:16 +0300 > From: Alex Manaev <amanaev(a)rutube.ru> > To: <users-list(a)monetdb.org> > Subject: Temporary duplicated rows > Message-ID: <56FBB5F8.7060309(a)rutube.ru> > Content-Type: text/plain; charset="utf-8"; format=flowed > > Hi, > > I have been faced with strange behavior. > > Sometimes after update that changes field "status", a select query > returns multiple identical rows. > > > sql>select * from morgoth_reportquery where not status = 2 and id = > 9472 order by id desc limit 5; > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > | id | repo | name | created_date > | ready_date | stat | hash | > params | user | appr | temp | noti | > : : rt_i : : > : : us : > : : _id : oved : late : fica : > : : d : : > : : : > : : : : _id : tion : > : : : : > : : : > : : : : : _ema : > : : : : > : : : > : : : : : ils : > +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > 2 tuples (8.446ms) !2 fields truncated! > > > In same time another queries return only one row (normal state) > > sql>select * from morgoth_reportquery where status = 1 and id = 9472 > order by id desc limit 5; > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > | id | repo | name | created_date > | ready_date | stat | hash | > params | user | appr | temp | noti | > : : rt_i : : > : : us : > : : _id : oved : late : fica : > : : d : : > : : : > : : : : _id : tion : > : : : : > : : : > : : : : : _ema : > : : : : > : : : > : : : : : ils : > +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ > | 9472 | 13 | ??????????? ??????: ??????? | 2015-12-01 12:28:59.000000 > | null | 1 | 5f34b8459f4834867a2621f7d6b | - > !!python/tuple [from_date | 1 | 0 | 8 | | > : : : ???????? 2016-02-01 - 2016- : > : : : 65c8a : , > 2016-02-01] : : : : : > : : : 02-11 : > : : : : - > !!python/tuple [to_date, : : : : : > : : : : > : : : : > 2016-02-11] : : : : : > : : : : > : : : : - > !!python/tuple [update_me : : : : : > : : : : > : : : : diainfo, > 0] : : : : : > : : : : > : : : : - > !!python/tuple : : : : : > : : : : > : : : : - > agreements... > : : : : > +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ > 1 tuple (6.474ms) !1 field truncated! > > > Another queries without filter by field `status` give a normal result > (one row). > > Effect is eliminated in several minutes after update by itself. > > > DBMS : MonetDB v11.21.17 (Jul2015-SP3) > OS: CentOS 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 21:19:08 UTC > 2015 x86_64 x86_64 x86_64 GNU/Linux > > Issue was reproduced for the first time with version SP-2. > > Database was created on SP-1. Issue had not appeared with this version. > > > >

1 0

Can no connect to Monetdb database
by Shmagi Kavtaradze 30 Mar '16

30 Mar '16

I compiled Monetdb-python version over regular Monetdb source compilation and after it I receive error: 2016-03-30 20:14:11 MSG merovingian[23154]: starting database 'db', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-30 20:14:11 MSG db[23176]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/db --set merovingian_uri=mapi:monetdb://infdbserv02:50000/db --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/db/.mapi.sock --set monet_vault_key=/home/shmagi/db/db/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-30 20:14:11 MSG merovingian[23154]: database 'db' (23176) has crashed (dumped core) 2016-03-30 20:14:16 ERR merovingian[23154]: client error: database 'db' appears to shut itself down after starting, check monetdbd's logfile for possible hints I tried ro ./configure, make clean, make , make install from pure monetdb source (without python), but still receive these errors. I tried to use prefix to install in another directory, tried to delete manually all files I found, but still same error. Any ideas how to fix this problem?

1 0

UDF returning 0 as a result
by Shmagi Kavtaradze 30 Mar '16

30 Mar '16

I wrote a function bitand. When I do "select bitand(1111,1010);" it gives "0" instead of "0.5". My function: --UDF.c-- str UDFbitand(long long int a, long long b) { long long int weirdAnd(unsigned int a, unsigned int b) { long long int result = 0; int coef = 1; while (a || b) { result += ((a % 10) && (b % 10)) * coef; coef *= 10; a /= 10; b /= 10; } return result; } long long int temp = weirdAnd(a, b); long long int temp2 = weirdAnd(a, b); int length; int count; while (temp != 0) { temp/=10; length++; } while (temp2 != 0) { if (temp2 % 10 == 1) count++; temp2 /= 10; } float final = count/(float)length; return MAL_SUCCEED; } --UDF.mal-- command bitand(a:lng,b:lng):flt address UDFbitand comment "bitand"; --80_UDF.sql-- create function bitand(a BIGINT,b BIGINT) returns REAL external name udf.bitand; When I test my C code in netbeans it works as supposed. Any ideas? Thanks in advance.

3 3

Temporary duplicated rows
by Alex Manaev 30 Mar '16

30 Mar '16

Hi, I have been faced with strange behavior. Sometimes after update that changes field "status", a select query returns multiple identical rows. sql>select * from morgoth_reportquery where not status = 2 and id = 9472 order by id desc limit 5; +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ | id | repo | name | created_date | ready_date | stat | hash | params | user | appr | temp | noti | : : rt_i : : : : us : : : _id : oved : late : fica : : : d : : : : : : : : : _id : tion : : : : : : : : : : : : : _ema : : : : : : : : : : : : : ils : +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ 2 tuples (8.446ms) !2 fields truncated! In same time another queries return only one row (normal state) sql>select * from morgoth_reportquery where status = 1 and id = 9472 order by id desc limit 5; +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ | id | repo | name | created_date | ready_date | stat | hash | params | user | appr | temp | noti | : : rt_i : : : : us : : : _id : oved : late : fica : : : d : : : : : : : : : _id : tion : : : : : : : : : : : : : _ema : : : : : : : : : : : : : ils : +======+======+=============================+============================+============================+======+=============================+=============================+======+======+======+======+ | 9472 | 13 | Ежемесячные отчеты: Медиате | 2015-12-01 12:28:59.000000 | null | 1 | 5f34b8459f4834867a2621f7d6b | - !!python/tuple [from_date | 1 | 0 | 8 | | : : : хнологии 2016-02-01 - 2016- : : : : 65c8a : , 2016-02-01] : : : : : : : : 02-11 : : : : : - !!python/tuple [to_date, : : : : : : : : : : : : : 2016-02-11] : : : : : : : : : : : : : - !!python/tuple [update_me : : : : : : : : : : : : : diainfo, 0] : : : : : : : : : : : : : - !!python/tuple : : : : : : : : : : : : : - agreements... > : : : : +------+------+-----------------------------+----------------------------+----------------------------+------+-----------------------------+-----------------------------+------+------+------+------+ 1 tuple (6.474ms) !1 field truncated! Another queries without filter by field `status` give a normal result (one row). Effect is eliminated in several minutes after update by itself. DBMS : MonetDB v11.21.17 (Jul2015-SP3) OS: CentOS 2.6.32-573.12.1.el6.x86_64 #1 SMP Tue Dec 15 21:19:08 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux Issue was reproduced for the first time with version SP-2. Database was created on SP-1. Issue had not appeared with this version. -- Alexey Manaev, Head of software development CJSC "RUTUBE"

1 0

Controlling --dbextras Using MonetDB(D)
by Vincent Sheffer 29 Mar '16

29 Mar '16

mserver5 has an argument called —dbextras. I prefer to control mserver5 using monetdbd, but I don't see any way of specifying this option in either monetdb or monetdbd. Is there, say, a property that can be set in monetdbd or monetdb. The latter probably makes more sense, as I think I would want to control it at the DB level not the dbfarm level. Thanks, Vince 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.

1 0

simultaneously-created tables not generating
by Anthony Damico 29 Mar '16

29 Mar '16

i have three clients connected to one mserver. they are simultaneously running these three commands, which share table `z` but not the x or y tables-- CREATE TABLE y1 AS SELECT x1.* FROM x1 LEFT JOIN SELECT col FROM Z ON x1.id = z.id WITH DATA ; CREATE TABLE y2 AS SELECT x2.* FROM x2 LEFT JOIN SELECT col FROM Z ON x2.id = z.id WITH DATA ; CREATE TABLE y3 AS SELECT x3.* FROM x3 LEFT JOIN SELECT col FROM Z ON x3.id = z.id WITH DATA ; when all three commands complete, tables y2 and y3 do not exist if i run the commands sequentially, everything works. am i making a mistake in how sql transactions should behave, or is this potentially a bug that i should figure out how to reproduce for bugzilla? thanks!

2 4

Ways to avoid OOMKiller problem?
by Vijay Krishna 29 Mar '16

29 Mar '16

Hi, We came across this - https://www.monetdb.org/Documentation/OOMkiller and we were thinking on some ways to overcome this situation. 1. Is there any configuration in MonetDB to restrict the maximum RAM usage of the mserver process? 2. Is there any configuration in MonetDB to restrict the maximum RAM used by a single thread? 3. Is there any suggested solution for the OOMKiller problem? If you have explored anything else on this, please share your experience with MonetDB server Memory management. Any help much appreciated. Thanks in advance. Regards, Vijay.

3 3

speed cost of stacking tables that do not need to be stacked
by Anthony Damico 27 Mar '16

27 Mar '16

just some UNION ALL speed tests that others might find useful.. i have one big table for each year of data. the year-tables can be analyzed separately - because all of my desired results are going to be within-year averages and counts. since each table has a single year of data, these two statements give equivalent results: query #1: SELECT * FROM ( ( SELECT yr , AVG( value ) FROM y1 GROUP BY yr ) UNION ALL ( SELECT yr , AVG( value ) FROM y2 GROUP BY yr ) ) AS zzz ; query #2: SELECT yr , AVG( value ) FROM ( ( SELECT yr , value FROM y1 ) UNION ALL ( SELECT yr , value FROM y2 ) ) AS zzz GROUP BY yr ; query #1: 290 seconds query #2: 390 seconds query #1: 22 seconds query #2: 87 seconds fwiw- this would take about 24 hours in anything other than monetdb ;)

1 0

database has crashed (dumped core)
by Shmagi Kavtaradze 25 Mar '16

25 Mar '16

I recomplied monetdb several times (make & make install). Everything was fine. Then I downloaded monetdb_python. It was not able to install and I decided to recompile and install again regular Monetdb, but when I "mclient -u monetdb -d voc" I get error in the log file: 2016-03-25 17:26:18 MSG merovingian[49368]: starting database 'voc', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-25 17:26:18 MSG voc[49379]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/voc --set merovingian_uri=mapi:monetdb://infdbserv02:50000/voc --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/voc/.mapi.sock --set monet_vault_key=/home/shmagi/db/voc/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-25 17:26:18 MSG merovingian[49368]: database 'voc' (49379) has crashed (dumped core) 2016-03-25 17:26:18 ERR merovingian[49368]: client error: database 'voc' appears to shut itself down after starting, check monetdbd's logfile for possible hints 2016-03-25 17:26:18 MSG merovingian[49368]: starting database 'voc', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2016-03-25 17:26:19 MSG voc[49382]: arguments: /usr/local/bin/mserver5 --dbpath=/home/shmagi/db/voc --set merovingian_uri=mapi:monetdb://infdbserv02:50000/voc --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/shmagi/db/voc/.mapi.sock --set monet_vault_key=/home/shmagi/db/voc/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-03-25 17:26:19 MSG merovingian[49368]: database 'voc' (49382) has crashed (dumped core) 2016-03-25 17:26:23 ERR merovingian[49368]: client error: database 'voc' appears to shut itself down after starting, check monetdbd's logfile for possible hints. Any ideas how to fix or delete all files of Monetdb to install from the scratch?

1 0