- developers-list - monetdb.org

Bulk Load, Same Table, Sequential COPY INTO, MAPI
by Brandon Jackson 13 Jan '13

13 Jan '13

Dear developers, In the last two weeks we have made a lot of progress on the MonetDB Streaming Bulk Loader Step in the Pentaho Data Integration. Yesterday, I tried to put it into use similar to a larger scale production use and started getting some errors along the lines of: "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" Not sure when these clear up. In this case after previous errors like this, I established a new connection and wanted to simply create a table. sql>CREATE TABLE "testing"."fact_cen_hist" ( "CENSUS_SK" INT , "FACILITY_SK" INT , "RESIDENT_SK" INT , "RESIDENT_LOCATION_SK" INT , "DISCHARGED_TO" CLOB , "PAYOR" VARCHAR(20) , "PAYOR_CATEGORY" VARCHAR(20) , "CENSUS_STATUS" VARCHAR(50) , "CENSUS_EVENT" VARCHAR(50) , "IS_ACTIVE" INT , "IS_MEDICAID_DAY" INT , "IS_MEDICARE_DAY" INT , "IS_HOSPICE_DAY" INT , "IS_COMMERCIAL_INSURANCE_DAY" INT , "IS_PRIVATE_PAY_DAY" INT , "IS_MANAGED_CARE_DAY" INT , "IS_MEDICARE_SECOND_PAYOR" INT , "IS_MEDICAID_PENDING_DAY" INT , "DATE_OF_STAY" INT , "NEXT_EVENT_DATE" INT , "DATE_BEFORE_NEXT_EVENT" INT , "CENSUS_EVENT_DATE" INT , "IS_MEMORY_CARE_DAY" INT , "IS_AL_DAY" INT , "IS_SNF_DAY" INT , "IS_DISCHARGED" INT , "IS_RTA" INT , "ROOM_KEY" VARCHAR(5) , "IS_BED_HOLD" INT , "BILLABLE_DAY" INT , "HEAD_IN_BED_DAY" INT , "FINANCIAL_DAY" INT , "IS_ADMISSION" INT , "IS_READMISSION" INT , "IS_EXPIRED" INT , "PAYOR_SK" INT , "IS_SUBMISSION" INT , "SPLIT_CENSUS" INT ) ; operation successful (2.7s) COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead sql> ---- Generally speaking, our bulk loader reads in a chunk of rows into memory say 10k, 20k or even 100k or 1 Million rows, then it will generate a COPY INTO, know how many rows are outbound; stream the rows through a BufferedWriter with all the new lines, and reading from BufferedReader and waiting for prompts etc. Then PDI will fetch another chunk of rows and repeat the process. I noticed that when using the MAPI API and trying to append a COMMIT in the output brought back an error saying that COMMIT is not allowed in auto commit mode, so it looks like after each big COPY INTO statement sends its complete pack of rows, with the new line stuff, should immediately try to COMMIT. What I want to figure out is why after the first COPY INTO of however many rows, 10k for example, would I get a message about failure to commit because of concurrency, ROLLBACK instead. Once that happens, is MonetDB in a funk? Do I need to shut it down and restart? What can I do to provide more helpful information to understand the nature of the problem? I read on: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/LoadingBulkData That I could supply a guess much larger on the first COPY INTO so MonetDB can prepare itself for a huge influx of rows, despite getting a smaller batch. Example in my mind is know that my table has roughly 6 million rows, so let 6 million be the guess, then have really accurate calls where COPY INTO is told 20k rows are on the way in each subsequent call leading up to the 6 million that I guessed would be necessary. It worries me that this appears to be brittle. We might produce 10 million rows in a source table and want to stream it over. At current, it looks like COMMIT concurrency errors would come up as we sequentially do COPY INTO's building up to finally having the bulk load complete. Any ideas or feedback is very much appreciated. I can carefully provide code, more detail and follow any procedures necessary to help diagnose the issue. Sample data is also available. Thanks ahead of time, Brandon Jackson _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

1 0

Solution: jdbcclient.jar is always prompting even there is password list in .monetdb
by sigit kartiko 10 Jan '13

10 Jan '13

I use jdbcclient.jar to backup several table periodically with my crontab. I already put .monetdb with my password text there. But there is problem, it' s always prompting for password. I check the source and i make it right with this solution: before : if (user != equals(copts.getOption("user").getArgument()) pass = null; after: if (!user.equals(copts.getOption("user").getArgument())) pass = null; regards _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

3 4

MonetDb Clustering
by nguewo dominique 09 Jan '13

09 Jan '13

Hey guys, I'm trying to do a MonetDb Clustering. I am using two laptops both with ubuntu 12.10 installed. In both laptops, MonetDb is installed. I followed the following steps as described in the cluster management documentation: 1. I created a DB farm and set the passphrase in both laptops worker% monetdbd create /fullpath/to/mydbfarm worker% monetdbd set passphrase=mypassphrase /fullpath/to/mydbfarm worker% monetdbd start /fullpath/to/mydbfarm 2. From one system I'm able to discover the other system guarded with monetdbd willem% monetdb discover 3. Using one system I created a database in the other one willem% monetdb -h cruquius -P mypassphrase create pump Since I only have two laptops I created the same database in both the laptops and I'm trying to set the shared property so that i will be able to refer to the same Database from both the laptops. The goal is to have different dataset(tables) of the same database stored in different machines but being able to query the database as a whole from a system. I tried the following but was unsuccesfull laptop1% monetdb -h laptop2-P mypassphrase set shared=pump/1/ pump laptop1% monetdb -h laptop1-P mypassphrase set shared=pump/2/ pump then laptop1% mclient -d '*/pump/2' laptop1% mclient -d '*/pump/1' How should I go about it? Dominique _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

1 0

Re: MonetDB: Feb2013 - Reduce CATNAP in vmtrim
by Sjoerd Mullender 08 Jan '13

08 Jan '13

Please revert. The reason why there is a loop that sleeps 50 ms per iteration is to get a responsive exit. With your change you have to wait up to half a second before the server exits. That is too long. If you want to reduce the frequency with which the vmtrim thread does actual work, you need to change the number of times the loop is run, not the duration of the sleep. On 2013-01-08 01:21, Martin Kersten wrote: > Changeset: 4ca22f5177b6 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4ca22f5177b6 > Modified Files: > gdk/gdk_utils.c > Branch: Feb2013 > Log Message: > > Reduce CATNAP in vmtrim > The overload on memory is already protected in the dataflow scheduler > and optimizer pipeline, which means we probably can relax the time to > issue the BBPtrim(). > > > diffs (22 lines): > > diff --git a/gdk/gdk_utils.c b/gdk/gdk_utils.c > --- a/gdk/gdk_utils.c > +++ b/gdk/gdk_utils.c > @@ -1034,6 +1034,7 @@ GDKmunmap(void *addr, size_t size) > * Their value is turned into a blanc space. > */ > > +#define CATNAP 500 > int GDKrecovery = 0; > > static MT_Id GDKvmtrim_id; > @@ -1053,8 +1054,8 @@ GDKvmtrim(void *limit) > size_t cursize; > > /* sleep using catnaps so we can exit in a timely fashion */ > - for (t = highload ? 500 : 5000; t > 0; t -= 50) { > - MT_sleep_ms(50); > + for (t = highload ? CATNAP : 10 * CATNAP; t > 0; t -= CATNAP) { > + MT_sleep_ms(CATNAP); > if (GDKexiting()) > return; > } > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/checkin-list > -- Sjoerd Mullender _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

3 3

Re: MonetDB: Feb2013 - Limit the number of concurrent queries
by Stefan Manegold 04 Jan '13

04 Jan '13

> Changeset: e79b2edeba71 for MonetDB > URL: > http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e79b2edeba71 > Modified Files: > monetdb5/mal/mal.c > monetdb5/mal/mal.h > monetdb5/mal/mal_interpreter.c > Branch: Feb2013 > Log Message: > > Limit the number of concurrent queries > The total throughput for small (and large) queries is > improved if we limit the number of concurrent queries > being executed. This patch is geared at SQL, wrapping > callMAL with a semaphore to limit the level of parallelism > to number of physical cores. > > > diffs (59 lines): > > diff --git a/monetdb5/mal/mal.c b/monetdb5/mal/mal.c > --- a/monetdb5/mal/mal.c > +++ b/monetdb5/mal/mal.c > @@ -195,6 +195,7 @@ MT_Lock mal_remoteLock; > MT_Lock mal_profileLock ; > MT_Lock mal_copyLock; > MT_Lock mal_delayLock; > +MT_Sema mal_parallelism; > /* > * Initialization of the MAL context > * The compiler directive STRUCT_ALIGNED tells that the > @@ -233,6 +234,7 @@ int mal_init(void){ > MT_lock_init( &mal_profileLock, "mal_profileLock"); > MT_lock_init( &mal_copyLock, "mal_copyLock"); > MT_lock_init( &mal_delayLock, "mal_delayLock"); > + MT_sema_init( &mal_parallelism, (GDKnr_threads ? GDKnr_threads/2 : 4), "mal_parallelism"); ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ With GDKnr_threads == 1 (single threaded execution or single-core system), this results in mal_parallelism == 0, which in turn results in all SQL tests failing (hanging until timeout) with `Mtest.py -n1` (single-threaded). Stefan > > tstAligned(); > MCinit(); > diff --git a/monetdb5/mal/mal.h b/monetdb5/mal/mal.h > --- a/monetdb5/mal/mal.h > +++ b/monetdb5/mal/mal.h > @@ -118,6 +118,7 @@ mal_export MT_Lock mal_remoteLock; > mal_export MT_Lock mal_profileLock ; > mal_export MT_Lock mal_copyLock ; > mal_export MT_Lock mal_delayLock ; > +mal_export MT_Sema mal_parallelism; > > > mal_export int mal_init(void); > diff --git a/monetdb5/mal/mal_interpreter.c > b/monetdb5/mal/mal_interpreter.c > --- a/monetdb5/mal/mal_interpreter.c > +++ b/monetdb5/mal/mal_interpreter.c > @@ -479,7 +479,13 @@ callMAL(Client cntxt, MalBlkPtr mb, MalS > ValPtr lhs; > InstrPtr pci = getInstrPtr(mb, 0); > RuntimeProfileRecord runtimeProfile; > - > + > +/* > + * Control the level of parallelism. The maximum number of > concurrent MAL plans > + * is determined by an environment variable. It is initially set > equal to the > + * number of cores, which may be too coarse. > + */ > + MT_sema_down(&mal_parallelism,"mal_parallelism"); > runtimeProfileInit(mb, &runtimeProfile, cntxt->flags & memoryFlag); > #ifdef DEBUG_CALLMAL > mnstr_printf(cntxt->fdout, "callMAL\n"); > @@ -518,8 +524,10 @@ callMAL(Client cntxt, MalBlkPtr mb, MalS > case PATcall: > case CMDcall: > default: > + MT_sema_up(&mal_parallelism,"mal_parallelism"); > throw(MAL, "mal.interpreter", RUNTIME_UNKNOWN_INSTRUCTION); > } > + MT_sema_up(&mal_parallelism,"mal_parallelism"); > if (cntxt->qtimeout && time(NULL) - stk->clock.tv_usec > > cntxt->qtimeout) > throw(MAL, "mal.interpreter", RUNTIME_QRY_TIMEOUT); > return ret; > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/checkin-list > _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

1 1

Redirect dev.monetdb.org to dev.monetdb.org/hg
by Stefan de Konink 04 Jan '13

04 Jan '13

As $subj, Would probably make it more easy for users to find the hg repositories. Stefan _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

1 0

Table column type meta-data and the MonetDB API (MAPI)
by Brandon Jackson 03 Jan '13

03 Jan '13

Good day, I am working on improvements to the Pentaho MonetDB Bulk Loader plugin and want to get some advice for how to properly use the MonetDB API to get the MonetDB column data type meta-data for a given table. Here is the use case: A row of data in Pentaho Data Integration is composed of fields which have a basic Java data type and other meta data kept in a helper class that keeps track of format strings, digits of precision and so on. At the current state of development a user has to know exactly which data type was used for a particular column and in which order the columns were defined by the original SQL CREATE statement. In PDI, every field (column) starts life with primitive Java data types without necessarily having a specific format defined when the variable is initialized, a user might send a TIMESTAMP (i.e. 2013-01-03 00:00:00) to MonetDB where really a DATE (i.e 2013-01-03) was expected. What I would like to do is before the bulk load actually runs, ask MonetDB through the API what the meta data is for each of the columns and output to the end user a table that shows the columns reported by MonetDB for a given table, the data type specified in the DDL, and show the user a suggested format string, which they could modify if they know what they are doing. Format strings look like "yyyy-MM-dd HH:mm:ss" and "#,###.#" etc. Generally, what API calls or approach is recommended to accomplish what I am trying to do? As a fall back, I considered querying the meta-data out like so: SELECT name AS column_name, type as column_data_type FROM "sys"."columns" WHERE table_id = ( SELECT id AS TABLE_ID FROM "sys"."tables" WHERE name = 'DIM_DATE' and schema_id = (SELECT id AS SCHEMA_ID FROM "sys"."schemas" WHERE name = 'testing')) ORDER BY number; This brings back two columns, name and type where I can match them to the outgoing fields. Then I can process the result set in the step and update the value meta-data with the appropriate format mask. Running such SQL should be unnecessary but at least I have a fall-back solution. Thanks for the advice, Brandon Jackson _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

2 1

Re: MonetDB: Feb2013 - Make joinpath signatures specific
by Stefan Manegold 31 Dec '12

31 Dec '12

Martin, this causes tests monetdb5/optimizer/Tests/tst4300.mal and monetdb5/optimizer/Tests/tst4610.mal to fail with !TypeException:user.main[17]:'algebra.joinPath' undefined in: zz:any := algebra.joinPath(b:bat[:oid,:lng], cr:bat[:lng,:oid], d:bat[:oid,:lng]) and !TypeException:user.main[9]:'algebra.joinPath' undefined in: r:any := algebra.joinPath(a:bat[:oid,:int], br:bat[:int,:oid], c:bat[:oid,:int]) Stefan On Mon, Dec 31, 2012 at 04:55:02AM +0100, Martin Kersten wrote: > Changeset: 9ac4a1b83d56 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9ac4a1b83d56 > Modified Files: > monetdb5/optimizer/optimizer.mal > Branch: Feb2013 > Log Message: > > Make joinpath signatures specific > > > diffs (27 lines): > > diff --git a/monetdb5/optimizer/optimizer.mal b/monetdb5/optimizer/optimizer.mal > --- a/monetdb5/optimizer/optimizer.mal > +++ b/monetdb5/optimizer/optimizer.mal > @@ -280,19 +280,19 @@ pattern optimizer.joinPath(mod:str, fcn: > address OPTwrapper > comment "Join path constructor"; > > -pattern algebra.joinPath(l:bat[:any,:any]...):bat[:any,:any] > +pattern algebra.joinPath(l:bat[:oid,:any]...):bat[:oid,:any] > address ALGjoinPath > comment "Routine to handle join paths. The type analysis is rather tricky."; > > -pattern algebra.leftjoinPath(l:bat[:any,:any]...):bat[:any,:any] > +pattern algebra.leftjoinPath(l:bat[:oid,:any]...):bat[:oid,:any] > address ALGjoinPath > comment "Routine to handle join paths. The type analysis is rather tricky."; > > -pattern algebra.leftfetchjoinPath(l:bat[:any,:any]...):bat[:any,:any] > +pattern algebra.leftfetchjoinPath(l:bat[:oid,:any]...):bat[:oid,:any] > address ALGjoinPath > comment "Routine to handle join paths. The type analysis is rather tricky."; > > -pattern algebra.semijoinPath(l:bat[:any,:any]...):bat[:any,:any] > +pattern algebra.semijoinPath(l:bat[:oid,:any]...):bat[:oid,:any] > address ALGjoinPath > comment "Routine to handle join paths. The type analysis is rather tricky."; > > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/checkin-list -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

1 0

Unique key constraint violation violated
by Tapomay Dey 28 Dec '12

28 Dec '12

Is this a known issue that you are working on? If so please give me the bug id where I would know when its fixed. The case is as follows: I have a table with 32 columns with unique key on 3 of them. I am running live ETLs for putting data into monetdb. These ETLs are generating sql insert scripts.(inserts/updates/ddls) Using JDBC and c3p0 connection pooling. Have developed an external wrapper module to achieve master-master replication. One common consistency check would be a count*. Observation: Queries are run in batches of 100 inserts clubbed into a single insert. I do a select count(*) after each batch of 100. After running 9100 queries the count* on one DB was 9100 but in the other was 9200. The second DB had 100 records that were duplicates of 8901-9000. It means when I inserted 9001 to 9100 the previous batch went into the store again with it and somehow surpassed the unique key constraint check. I have also observed another case where count(*) gave less results than actual no. of rows in the DB. REQUEST: Just want to confirm if such kind of issues are part of active development. Otherwise it would be really difficult for me to push monetdb usage into production. Will try again with MonetDataSource instead of c3p0. But I doubt if the issues are client side or connection-related. Plz note that the keys for which this occurs is random and thus I concluded it's not a data issue. Regards, Tapomay Dey. Software Design Engg. Sokrati Inc. Email: tapomayATsokratiDOTcom _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

2 2

fast bit integration into monetdb
by Stanislav Seltser 27 Dec '12

27 Dec '12

does anybody know if fastbit integration into scidb was ever completed? _______________________________________________ developers-list mailing list developers-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/developers-list

3 4