April 2016 - users-list

Problem with COPY INTO
by Knezevic Nikola 15 Apr '16

15 Apr '16

Hi all, I've lost a significant time trying to get data from mysql database into monetdb. Since I have a lot of data, I wanted to make it as fast and automated as possible, but to no avail. There are several issues I encountered, and I'd appreciate any help on any of them. I'm running the latest monetDB on CentOS: % mclient --version mclient, the MonetDB interactive terminal (Jul2015-SP4) support for command-line editing compiled-in character encoding: UTF-8 1. I tried following the last example on https://www.monetdb.org/Documentation/SQLreference/Permissions in order to get another user to be able to perform COPY INTO, but it fails: % mclient -u monetdb -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>GRANT COPY FROM TO copy_role; syntax error, unexpected COPY in: "grant copy" 2. Ok, since I can't run COPY INTO FROM FILE, I decided to go with FROM STDIN version. To do so, I dump the mysql database and import that in monetdb. That also fails: % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;"|mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" % mclient -u writer -d some_db password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://internal:50000/some_db' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\d errortable CREATE TABLE "some_db"."errortable" ( "rejectreason" INTEGER NOT NULL, "text" CHARACTER LARGE OBJECT ); Another try also fails (still using STDIN): % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" <test.out Failed to import table Column value 1 missing % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'';" - <test.out Failed to import table Column value 1 missing syntax error, unexpected sqlINT in: "100" (note different error messages, and also note that 100 is a perfectly good sqlINT) If I try the following, this succeeds. However, this is something I'm trying to avoid, as I'd had to create huge files (100+GB), which is an absolute waste of space and time. % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM errortable;" >test.out % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" COPY INTO: insufficient privileges: COPY INTO from file(s) requires database administrator rights, use 'COPY INTO "errortable" FROM STDIN' instead # obviously, fails due to (1) % mclient -u monetdb -d some_db -s "COPY 15 RECORDS INTO some_db.errortable FROM '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';" password: 15 affected rows The file in question is super simple, a list of integers with textual description: 100 Some limit exceeded 101 Another limit exceeded 404 Service temporarily not available 405 Service not available 500 Server not available 700 End of world as we know it 701 User already logged in 10000 Book not found 10001 Price reasonability doubtfull 10002 Client is well known 20001 Activation in progress 20002 Pre-load limit exceeded 30000 Pre-load limit exceeded again 1 test 5 Invalid value 0 to divide with Any hints on how to solve this super annoying problem? Thanks, Nikola

6 12

Re: Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi Martin, Thanks for the reply. Is there any workaround to restrict the memory used by a single query or client connection - like some configuration variables? I see a lot of worker threads executing a single query in parallel. I studied that the number of worker threads can be controlled by the gdk_threads variable. But is there anyway to restrict the maximum memory that can be used by any particular worker thread? Or can this be controlled at the OS level? We thought of restricting the memory used by the entire mserver with ulimit, but that still doesn't solve the problem of the possibility of a single query causing a trouble. Is there any other way that this can be controlled as the OS level? @Martin and Others, If both are NO, then what are the safety measures to be followed in production to avoid such problems? (Or, I may sound little dumb - is this not a production problem at all?) Has anyone faced any such issues? Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305. On Thu, Apr 14, 2016 at 1:20 PM, Vijay Krishna <vijayakrishna55(a)gmail.com> wrote: > Hi Martin, > > Thanks for the reply. > > Is there any workaround to restrict the memory used by a single query or > client connection - like some configuration variables? > > I see a lot of worker threads executing a single query in parallel (please > refer screenshot attached). I studied that the number of worker threads can > be controlled by the gdk_threads variable. But is there anyway to restrict > the maximum memory that can be used by any particular worker thread? > > Or can this be controlled at the OS level? > We thought of restricting the memory used by the entire mserver with > ulimit, but that still doesn't solve the problem of the possibility of a > single query causing a trouble. Is there any other way that this can be > controlled as the OS level? > > @Martin and Others, > If both are NO, then what are the safety measures to be followed in > production to avoid such problems? > (Or, I may sound little dumb - is this not a production problem at all?) > Has anyone faced any such issues? > > Thanks in advance. > > > > > Thanks & Regards, > > Vijayakrishna.P. > Mobile : (+91) 9500402305. > > On Thu, Apr 14, 2016 at 12:15 PM, Martin Kersten <martin.kersten(a)cwi.nl> > wrote: > >> On 14/04/16 08:30, Vijay Krishna wrote: >> >>> Hi, >>> >>> As per MonetDB's architecture, the memory allocated to a single query >>> (thread) is left with the OS and is not controlled by the mserver. >>> >>> If so, I am wondering how MonetDB would react to the following scenario. >>> >>> I receive a client connection with a single costly query whose execution >>> would require all of the RAM's memory. >>> >> Hi >> >> Both queries will run concurrently and fight for the total memory. >> However, after some time, the oldest query is put on lower priority >> >> >>> Now, when I receive a second connection that runs a similar costly >>> operation, will MonetDB start processing this second query by using the >>> available meagre memory (RAM leftovers or from disk swap) or will MonetDB >>> wait till the first query releases some of >>> its memory? >>> >>> >>> >>> Thanks & Regards, >>> >>> Vijayakrishna.P. >>> Mobile : (+91) 9500402305. >>> >>> >>> _______________________________________________ >>> 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 >> > >

1 0

Can MonetDB be slowed down by a single query?
by Vijay Krishna 14 Apr '16

14 Apr '16

Hi, As per MonetDB's architecture, the memory allocated to a single query (thread) is left with the OS and is not controlled by the mserver. If so, I am wondering how MonetDB would react to the following scenario. I receive a client connection with a single costly query whose execution would require all of the RAM's memory. Now, when I receive a second connection that runs a similar costly operation, will MonetDB start processing this second query by using the available meagre memory (RAM leftovers or from disk swap) or will MonetDB wait till the first query releases some of its memory? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Strange behaviour of epoch() when converting from Unix time
by Marc Ballarin 12 Apr '16

12 Apr '16

Hi, I am observing a surprising behaviour of the epoch function: The following works as expected: SELECT epoch(now()) AS "NOW", epoch(now() - INTERVAL '5' MINUTE) AS "5 MIN AGO"; +------------+------------+ | NOW | 5 MIN AGO | +============+============+ | 1460453860 | 1460453560 | +------------+------------+ The inverse does not: SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | +----------------------------+----------------------------+ The "now" part is fine, the "300 seconds ago" part is obviously not what I expect. Am I missing something or is this a bug? Regards, Marc

2 1

Check reads per sec and CPU utilization
by Shmagi Kavtaradze 11 Apr '16

11 Apr '16

I was not able to find any information how to check CPU utilization and read per sec for a query. Any ideas how to get these results? Thanks in advance.

3 3

Re: How to do profiling in MAL interface?
by Constantine Feng 11 Apr '16

11 Apr '16

Hi Martin, Thanks. stethoscope does do some work. But since it is for online monitoring, it is inconvenient for offline analysis of trace profiles. After reading more docs on the web, I find Tachograph comes closest to what I want. A minor problem is that the schema of the generated files (.trace, .csv, .json) is not given. Somehow I have to guess. And seems that each file format covers a different subset of attributes. - Neo > > ------------------------------ > > Message: 2 > Date: Sat, 9 Apr 2016 08:17:15 +0200 > From: Martin Kersten <martin.kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: How to do profiling in MAL interface? > Message-ID: <57089E6B.7050504(a)cwi.nl> > Content-Type: text/plain; charset=windows-1252; format=flowed > > You better consider using the stethoscope for doing this kind of analysis. > The documentation need some updates. > > > On 09/04/16 06:07, Constantine Feng wrote: > > Hi all, > > > > I know I can use the TRACE SQL command. But I want to do it in the > "-lmal" interface (because I massaged the MAL plans a bit to test my > research ideas). > > > > Particularly, I find the information given on this website ( > https://www.monetdb.org/Documentation/Manuals/MonetDB/Modules/profiler ) > doesn't match the source code. I can't find the profiler.activate() or > profiler.deactivate(), for example. > > > > > > mal>manual.help("profiler.*"); > > profiler.cpuload > > profiler.cpustats > > profiler.cleanup > > profiler.closeStream > > profiler.dumpTrace > > profiler.getSystemTime > > profiler.getUserTime > > profiler.getDiskWrites > > profiler.getDiskReads > > profiler.getTrace > > profiler.noop > > profiler.openStream > > profiler.reset > > profiler.setHeartbeat > > profiler.stop > > profiler.stethoscope > > profiler.tomograph > > > > > > Version: (Jul2015-SP3). Build from source. > > > > - Neo > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > >

1 0

Announcement: New Jul2015-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 11 Apr '16

11 Apr '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP4 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/>. Jul 2015-SP4 bugfix release MonetDB Common * Fixed a bug that caused various instances where old data returned or where crashes occurred. The problem was that internally data wasn't always marked dirty when it was being changed, causing later processing to not deal with the changed data correctly. Bug Fixes * 3905: MonetDB doesn't handle ANY/SOME/ALL operator correctly * 3929: R aggregate not recognized when using 3 or more parameters * 3965: Not possible to quote/escape single quote character in the name of the file to load. * 3968: Missing double use of column names

1 0

Can not enable python
by Shmagi Kavtaradze 10 Apr '16

10 Apr '16

I created function in python, but when tried to call, I receive : Embedded Python has not been enabled. Start server with --set embedded_py=true I stopped farm. used mserver5 --set embedded_py=true and got in mserver5 console output: # MonetDB/SQL module loaded # MonetDB/Python module loaded Then I started farm and still getting same error. Then I did this: monetdb stop dbname monetdb set embedpy=true dbname monetdb start dbname Still getting same. Any ideas what I am doing wrong?

2 2

Fwd: Function does not complete
by Shmagi Kavtaradze 09 Apr '16

09 Apr '16

Thanks for answer. I have bits of size 50, so I need bigint/long long int. Problem is in while loop. When I use bitand(a,b) and numbers of digits of a and b both are 18-19, the system enters endless loop I think. Bigint maximum value is 19 digits long.I have mistakes in code when using unsigned int and I changed, but same problem. As I know from data types page, monetdb uses only signed integers, so I use only signed ones. Any ideas why does it hang on 18-19 digits and not on 1-17 digits long numbers?

1 0

How to do profiling in MAL interface?
by Constantine Feng 09 Apr '16

09 Apr '16

Hi all, I know I can use the TRACE SQL command. But I want to do it in the "-lmal" interface (because I massaged the MAL plans a bit to test my research ideas). Particularly, I find the information given on this website ( https://www.monetdb.org/Documentation/Manuals/MonetDB/Modules/profiler ) doesn't match the source code. I can't find the profiler.activate() or profiler.deactivate(), for example. mal>manual.help("profiler.*"); profiler.cpuload profiler.cpustats profiler.cleanup profiler.closeStream profiler.dumpTrace profiler.getSystemTime profiler.getUserTime profiler.getDiskWrites profiler.getDiskReads profiler.getTrace profiler.noop profiler.openStream profiler.reset profiler.setHeartbeat profiler.stop profiler.stethoscope profiler.tomograph Version: (Jul2015-SP3). Build from source. - Neo

2 1