Re: [MonetDB-users] Delete is slow?

Hi Ed, thanks for you detailed info!

Howdy!

Did the deletes using the "BAT.delete()" function (the one fell swoop method).

right.

- Ran the Mserver with modules: module(mapi,blob,monettime);

- One run of the database was created by:

+---------------------------------- bat(void, str).rename("entity__aggregator__name").persists(TRUE).seqbase(oid(1)); bat(void, str).rename("entity__host__name").persists(TRUE).seqbase(oid(1)); bat(void, str).rename("entity__log__name").persists(TRUE).seqbase(oid(1)); bat(void, str).rename("entity__host_agent__guid").persists(TRUE).seqbase(oid(1)); bat(void, oid).rename("entity__host_agent__host").persists(TRUE).seqbase(oid(1));

bat(lng, oid).rename("eventlog__raw_record__host").persists(TRUE); bat(lng, oid).rename("eventlog__raw_record__log").persists(TRUE); bat(lng, oid).rename("eventlog__raw_record__host_agent").persists(TRUE); bat(lng, oid).rename("eventlog__raw_record__aggregator").persists(TRUE); bat(lng, blob).rename("eventlog__raw_record__record").persists(TRUE); bat(lng, timestamp).rename("eventlog__raw_record__time_logged").persists(TRUE); commit(); +----------------------------------

looks fine to me (using void's like a MonetDB expert! ;-)

- Then I run some scripts to load up the first 5 tables (3-50 rows each).

- Then I run a script to import 250k rows into the last 6 tables. The oid entries point back to the first 5 tables. Each row is inserted using a single "execute()" where the execute consists of 6 MIL statements (these perform "BAT.insert()" as well as lookups to find the OIDs for various pieces of information, such as a host's name -> OID).

As such, using BAT.insert() is fine, at least if you get your input date one-by-one. However, if you're actually bulk-loading the 250k rows into the last 6 BATs, ans you have the respective data in some plain format (e.g., text file with comma separated value), you should better consider using the bulkload features of module ascii_io. Also, the 6x250k lookups in to the first 5 BATs might be solved in a bulk-fashion, at least as far as I can interprete your problem right now. Anyway, you could provide us some more background info, e.g., the "ratio" of your inserts, we could provide you with some more help, here.

- Sorry, no raw data, but easily emulated with some random functions.

- The rough cleanup code:

+---------------------------------- bat("eventlog__raw_record__host").delete(); bat("eventlog__raw_record__log").delete(); bat("eventlog__raw_record__host_agent").delete(); bat("eventlog__raw_record__aggregator").delete(); bat("eventlog__raw_record__record").delete(); bat("eventlog__raw_record__time_logged").delete(); commit(); +----------------------------------

- Everything is fast til the commit, then _BAM!_, 100% CPU.

- If I do everything except bat("eventlog__raw_record__record"), and commit, everything is good (instantaneous response).

- If I then do , it's fine until .

- Thought: could the system be compacting the heap? But then, I would think I would be seeing I/O.

Any thoughts?

Yep, well, not me, but Niels Nes: Your info that its actually the commit on the blob that caused the problems, helped Niels to find and fix a bug in commit for complex types. The bug is fixed in the MonetDB_4-6 branch in CVS. We're not completely sure, whether this bug actaully caused your problem, but we do think so. Unfortunately, we haven't had the time/chance to replay your experiment to check on this. Hence, if you could cvs checkout/update and recompile your MonetDB, and re-run your test, you could check it yourself. Would be great. If cvs checkout/update it not a convenient option for you, we could also provide you with the respective patch, only, but also this requires that you use the source distribution of MonetDB. If neither in an option since you're using the binary distribution, you'll have to wait until we release the final MonetDB 4.6 (hopefully on Monday). In that case, we'll have to find some time to replay your experients before Monday... So much for now. I'll come back to your "Side notes:" separately in a minute.. Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |