December 2013 - users-list

Moving monetdb data to another directory
by Rémi Charpentier 09 Dec '13

09 Dec '13

I am trying to change the default data directory of MonetDB. I installed monetdb using the ubuntu package, by default the data is stored in: /var/lib/monetdb I am running out of space, and I like to migrate the data to another folder. Does anyone know how to do that? I would welcome a solution that doesn't involve compiling monetdb from sources... Thanks in advance Remi

2 2

Listing database objects in schema
by Lee Hachadoorian 06 Dec '13

06 Dec '13

Is there a way to list tables in a particular schema or even in all schemas? \d and \dt seem to only return user tables in the sys schema. Also, what does \ds return? I have \dS system tables \dt user tables \dv views \ds ????? \df functions \dn schemas Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu/<http://freecity.commons.gc.cuny.edu/>

2 2

Bulk loading file with non-UTF8 characters
by Lee Hachadoorian 06 Dec '13

06 Dec '13

I need to bulk load some files with characters like ñ and é. I would do this using the LATIN1 encoding. I read that bulk loading from file assumes UTF8, but bulk loading from STDIN will use encoding of mclient. I would like to know: (a) How do I determine and change the client encoding? (b) What is an example for piping STDIN to mclient? Regarding the latter, I tried: cat /my/file.csv | mclient -d mydb -u monetdb -s "COPY INTO mytable FROM (STDIN) USING DELIMITERS ',', '\n' , '"' NULL AS '';" I wasn't sure how the embedded " would be handled, but the terminal basically became unresponsive until I ctl-C'ed out of it. Note the the file was similar in size to several others (without non-UTF8 characters) that loaded in seconds within mclient. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

3 4

Load file MonetDB Client
by Gaetano Mele 06 Dec '13

06 Dec '13

Hello i'm an italian student. I am loading a file .sql with MonetDB Client. But, during the file's loading, happens that the Client suddenly stops working. Why does this happen? Thanks

3 6

Strange behavior: dumping results from SELECT query (or inserting it into a table) does not contain the proper data until table is dumped/reloaded
by Bas Kaptijn 06 Dec '13

06 Dec '13

Hi, I can reproduce a MonetDB 2013 - SP5 database which gets in such a state the following first query does not return a result with a record containing '20130510199500000099' as value for the field "ida" whereas the second query (with only a little extra at the end) does while clearly this should never be the case. Moreover: the problem disappears when I dump, drop and reload the table analysis_04_1 ! testdpe99.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ); testdpe99-2.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ) AND A.identificatierecord = '20130510199500000099'; The table analysis_04_1 is the result of a SELECT query after which it is altered and updated quite some times adding extra fields etc. No errors seem to be reported in that process. Before reloading the table analysis_04_1 I get: bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.sql | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.sql | grep 20130510199500000099 bkaptijn@DevRef1:~/DDDB$ But after reloading the table (using in mclient: \> dumpfile, \D analysis_04_1, DROP TABLE analysis_04_1;, \< dumpfile): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ Is there a known issue which does play a role here or what can I do to investigate this further / give more information? Sincerely, Bas Kaptijn

1 0

Pathological behaviour when building large hash index
by Will Muldrew 06 Dec '13

06 Dec '13

Hi I've been investigating very slow queries on my 350M row dataset when selecting for equality on a non-unique string id. e.g. select count(*) from t_order where orderId = 'FOO'; This query takes ~4.5 hours to build a thash index. Subsequent repeats hit the cache and are sub milli. select count(*) from t_order where time > '1970-01-01' and orderId = 'FOO' This second one is consistently finished in a few seconds or less, and doesn't create any indexes. The first query is a bit of a show-stopper. It spends a huge about of time in BAThash (gdk_search.c), and incurs a lot of page faults while reading and writing at random across a multi GB mmaped thash file. Additionally these page faults are hampered by the OS trying to write dirty pages out in the background. I've got plenty of RAM overall (130GB), but the 'active' page proportion for the thash file seems to be stuck at about 25% - giving me a 0.75 probability of a miss. * Is there some way to make my OS less keen to evict pages? (swappiness = 0 already) I should have plenty of room to have the whole thash file resident. Additionally, allowing it to sit on dirty pages longer would reduce the total write IO. * Is there some way to simply prevent large thash creation? For many applications I'd rather have slower consistent queries, then incur a massively slow query following a restart (not great in a prod environment!) * Is there some way to generate the hash index with a more sympathetic algo that doesn't degrade so steeply. e.g some hash + sort. (I have literally no experience with this!) -Will Muldrew

2 12

Re: Pathological behaviour when building large hash index
by Roberto Cornacchia 06 Dec '13

06 Dec '13

Sorry for the quick intrusion, I just urged to say this is one of the most interesting threads of the year ;-) On Fri, Dec 6, 2013 at 10:06 AM, Roberto Cornacchia <roberto(a)spinque.com>wrote: > Sorry for the quick intrusion, I just urged to say this is one of the most > interesting threads of the year ;-) > > > > > > On Thu, Dec 5, 2013 at 8:57 PM, Will Muldrew <will.muldrew(a)gmail.com>wrote: > >> No index! >> On 5 Dec 2013 17:05, "Stefan Manegold" <Stefan.Manegold(a)cwi.nl> wrote: >> >>> hm, that's with the index in place? >>> >>> If so: >>> good for our optimizer, >>> but unfortunately not so good for you ... >>> >>> If not: >>> - good for you! >>> - impressive for our "execution engine" (scan) >>> (- "embarrassing" for our optimizer ... well, it's a minor detail ...) >>> >>> ;-) >>> >>> Stefan >>> >>> ----- Original Message ----- >>> > Sorry! >>> > >>> > sql>select count(*) from t_order2 where orderId between 'AAAAAA' and >>> > 'AAAAAA'; >>> > +------+ >>> > | L1 | >>> > +======+ >>> > | 0 | >>> > +------+ >>> > 1 tuple (596.202ms) >>> > >>> > >>> > >>> > On 5 December 2013 16:49, Stefan Manegold < Stefan.Manegold(a)cwi.nl > >>> wrote: >>> > >>> > >>> > Hi Will, >>> > >>> > thank for reminding me: the optimizer still sees your query as point >>> query >>> > and thus prevents parallelization in order to build/use the hash index. >>> > >>> > Maybe you can "fool" the optimizer (and I hope noone else is reading >>> what I'm >>> > writing now ... ;-)) by "disguising" the point predicate as range >>> predicate, >>> > say, "where orderId between 'FOO' and 'FOO'" --- in fact, I rather >>> hope (for >>> > us) >>> > that is will not work ... >>> > >>> > Stefan >>> > >>> > ----- Original Message ----- >>> > > Hi Stefan >>> > > >>> > > I've added that hash = FALSE patch, and now my simple query returns >>> > > reliably >>> > > in 6 seconds. It's still a lot slower than the indexed one >>> (obviously), and >>> > > 12x slower than the one with the time > '1970-01-01' optimiser nudge >>> (which >>> > > causes parallelism), but it'll do for now. >>> > > >>> > > Thanks >>> > > >>> > > -Will >>> > > >>> > > >>> > > On 5 December 2013 16:32, Will Muldrew < will.muldrew(a)gmail.com > >>> wrote: >>> > > >>> > > >>> > > >>> > > Hi Stefan >>> > > >>> > > Here are my version details: >>> > > >>> > > MonetDB-11.15.17 >>> > > CentOS release 6.3 >>> > > kernel 3.6.2 >>> > > >>> > > I think I might proceed with a patched version with the hash >>> disabled. >>> > > That's >>> > > totally fine for me right now, though obviously it'd be nice to be >>> on the >>> > > trunk! >>> > > >>> > > W.r.t. a less random access hash generation. This is all very >>> hand-wavey >>> > > but >>> > > how about: 1) generate a sequence of (hash,oid) pairs to some file or >>> > > mmapped area. 2). use some well-known "external sort" algo to sort >>> these >>> > > pairs by hash (e.g. split, qsort, merge). 3). build your final hash >>> by >>> > > iterating through this sorted datastructure. >>> > > >>> > > Your memory writing would then be sequential and you'd get good page >>> hit >>> > > characteristics. You just need to choose a decent sort algo - >>> ideally which >>> > > works transparently over memmapped data. >>> > > >>> > > What do you think? I might even have a crack at it myself :) >>> > > >>> > > -Will >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > On 5 December 2013 15:54, Stefan Manegold < Stefan.Manegold(a)cwi.nl> wrote: >>> > > >>> > > >>> > > Hi Will, >>> > > >>> > > could you please also share with us which version of MonetDB you are >>> using, >>> > > and OS you are running? >>> > > >>> > > Pleass find more comments & questions inlined below. >>> > > >>> > > ----- Original Message ----- >>> > > > Hi >>> > > > >>> > > > I've been investigating very slow queries on my 350M row dataset >>> when >>> > > > selecting for equality on a non-unique string id. e.g. >>> > > > >>> > > > select count(*) from t_order where orderId = 'FOO'; >>> > > >>> > > Is your orderId column unique, or are there duplicate values? >>> > > >>> > > > This query takes ~4.5 hours to build a thash index. Subsequent >>> repeats >>> > > > hit >>> > > > the cache and are sub milli. >>> > > > >>> > > > select count(*) from t_order where time > '1970-01-01' and orderId >>> = >>> > > > 'FOO' >>> > > > >>> > > > This second one is consistently finished in a few seconds or less, >>> and >>> > > > doesn't create any indexes. >>> > > > >>> > > > The first query is a bit of a show-stopper. It spends a huge about >>> of >>> > > > time >>> > > > in >>> > > > BAThash (gdk_search.c), and incurs a lot of page faults while >>> reading and >>> > > > writing at random across a multi GB mmaped thash file. >>> Additionally these >>> > > > page faults are hampered by the OS trying to write dirty pages out >>> in the >>> > > > background. I've got plenty of RAM overall (130GB), but the >>> 'active' page >>> > > > proportion for the thash file seems to be stuck at about 25% - >>> giving me >>> > > > a >>> > > > 0.75 probability of a miss. >>> > > > >>> > > > * Is there some way to make my OS less keen to evict pages? >>> (swappiness = >>> > > > 0 >>> > > > already) I should have plenty of room to have the whole thash file >>> > > > resident. >>> > > > Additionally, allowing it to sit on dirty pages longer would >>> reduce the >>> > > > total write IO. >>> > > >>> > > In fact, if there is sufficient memory in total, there would be no >>> need to >>> > > flush >>> > > dirty (non-persistent) pages at all, but we have not yet found a >>> good way >>> > > to >>> > > hit >>> > > this to the OS --- madvice() did not have much/any useful efficet >>> for us on >>> > > Linux. >>> > > >>> > > Maybe some of the issues dicsussed here play a role: >>> > > >>> http://engineering.linkedin.com/performance/optimizing-linux-memory-managem… >>> > > ? >>> > > >>> > > While we have not yet encountered the NUMA problem reported there, >>> > > we have encountered the transparent huge page problems, and usually >>> > > disable them as also suggested in the above posting. >>> > > >>> > > > * Is there some way to simply prevent large thash creation? For >>> many >>> > > > applications I'd rather have slower consistent queries, then incur >>> a >>> > > > massively slow query following a restart (not great in a prod >>> > > > environment!) >>> > > >>> > > Not easily right now. >>> > > However, you can either add the "fake" time predicate as you did >>> above, >>> > > which (in this case) triggers MonetDB's optimizer to push the time >>> > > predicate >>> > > (either because it's in timestamp (int) rather then varchar >>> (string), or >>> > > because the time column happens to be ordered) below the orderId >>> predicate >>> > > and this avoid the hash build, >>> > > or change the following line(s) in gdk/gdk_select.c (in case you >>> built >>> > > MonetDB yourself from source): >>> > > >>> > > hash = b->batPersistence == PERSISTENT && >>> > > (size_t) ATOMsize(b->ttype) > sizeof(BUN) / 4 && >>> > > BATcount(b) * (ATOMsize(b->ttype) + 2 * sizeof(BUN)) < >>> GDK_mem_maxsize / 2; >>> > > -> >>> > > hash = FALSE; >>> > > >>> > > We'll consider whether we can provide a better solution in a future >>> > > release. >>> > > >>> > > > * Is there some way to generate the hash index with a more >>> sympathetic >>> > > > algo >>> > > > that doesn't degrade so steeply. e.g some hash + sort. (I have >>> literally >>> > > > no >>> > > > experience with this!) >>> > > >>> > > We'll also look into this. However, building a hash index inherently >>> incurs >>> > > random access, so chances are slim ... >>> > > >>> > > Best, >>> > > Stefan >>> > > >>> > > > -Will Muldrew >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > _______________________________________________ >>> > > > users-list mailing list >>> > > > users-list(a)monetdb.org >>> > > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > > >>> > > >>> > > -- >>> > > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> > > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> > > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> > > >>> > > _______________________________________________ >>> > > 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 >>> > > >>> > >>> > -- >>> > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> > >>> > _______________________________________________ >>> > 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 >>> > >>> >>> -- >>> | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> >>> _______________________________________________ >>> 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

Mantente en contacto conmigo a través de LinkedIn
by Edgar Mejia silva 03 Dec '13

03 Dec '13

LinkedIn ------------ Communication: Me gustaría añadirte a mi red profesional en LinkedIn. -Edgar Edgar Mejia silva Ingeniero consultor de sistemas de gestion y BI (Business intelligent) en ITFusion Colombia Confirma que conoces a Edgar Mejia silva: https://www.linkedin.com/e/-id7iby-hor6db4w-59/isd/18454372725/QZ9x6cIx/?hs… -- Estás recibiendo invitaciones a conectar. Haz clic para darte de baja: http://www.linkedin.com/e/-id7iby-hor6db4w-59/d3mz0XtXuHhwR56ylf8k7AOIRLhmN… (c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, EE.UU.

1 0

Announcement: New Feb2013-SP6 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Dec '13

03 Dec '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP6 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/>. Feb 2013-SP6 bugfix release MonetDB5 Server * Fixed a possible buffer overflow in the COPY INTO code. * Fixed a problem that when the server is using all available threads for a query, it was not possible to attach another client and have it execute even the smallest query. This is fixed by creating extra threads for each client entering the fray at the cost of having more threads that execute queries. But at least there is guaranteed progress for all clients (modulo the operating system scheduler). See bug #3258. Merovingian * monetdbd(1) now refuses to startup if it cannot read the properties from the dbfarm, bug #3398 Client Package * mclient: Fixed a bug where the -H option only worked if the readline history file already existed. Now we properly create and use the history file. * ODBC: Fixed interpretation SQL_C_SLONG/SQL_C_ULONG/SQL_C_LONG to refer to a 32 bit integer always (i.e. "int" on 64 bit architectures despite the name and the Microsoft documentation). This seems to be the consensus. * ODBC: Fixed transaction level: MonetDB only supports the highest level (SQL_TXN_SERIALIZABLE), so setting the transaction level can be accepted and ignored. MonetDB Common * Stopped using the deprecated sbrk() system call. * Fixed a problem when reverse sorting a sorted column. * Fixed bugs that deal with problems that could possibly occur when transactions are aborted and the server is restarted. See bug #3243. * A bug was fixed in the handling of grouped aggregates when all values in a group are null. See bug #3388. SQL * Fixed a bug where the server at some point stopped saving compiled queries in the SQL query cache. Bug Fixes * 3243: Segmentation fault (possible data corruption) after clean shutdown * 3258: Scheduling issues * 3368: BAT sortedness info ignored on ORDER BY and TOPN * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3382: Response to PREPARE emtpy if query contains a LIMIT * 3385: Simple query fails with 'identifier not found' * 3387: mclient does not properly double quote schema names when using autofill tab * 3388: case statement in "order by" clause doesn't work when used together with "group by" * 3389: median function with "group by" - SIGSEGV * 3392: ODBC datatype conversion for INTEGER not working properly * 3394: "Cannot find column type" error in temporary tables in functions * 3395: error occurred during a query: "'CASE WHEN" sentence * 3396: Improper UDF expansion * 3397: Error in ODBC-Driver when using Prepared Statements * 3398: Cannot stop monetdbd after erroneously starting an uninitialized dbfarm

1 0

I can integrate monetdb as hadoop?
by Edgar Mejia 02 Dec '13

02 Dec '13

if for example i have 300 millions of rows, i need to use something as hadoop? is posible integrate monetdb as hadoop? if i use only monetdb, and i have 300 millions of rows, how much memory and processor is recommended as minimum? Thanks, EM

2 2