Question

Delete > 500.000 records > out of memory

3

Hi everybody, On a Mendix-hosted environment, we have deployed an ap that uses the logging module of the app-store. We however face the problem that there are a lot of entries in written to the db (over 500.000) and clicking the "delete all" button shows the "in progress" screen but after 30? seconds the screen freezes and the logging shows the exception below. After restarting m2ee and trying again, deleting doesn't work (same problem) or only partitially works. I pasted the stacktrace and the delete-java-action-code below. My question is how I can fix the out of memory. Is just the batchsize too high (10.000) Or would it at all be impossible to delete this amount of records with 1 buttonpress (meaning that the action should be changed, by letting only delete 50.000? records for e.g.)? Stacktrace Sep 12 16:38:22 127.0.0.1 greprod: ERROR - Connector: An error has occurred while handling the request. [User 'gcoroberto@greencloudsonline.com' with roles 'GreenClouds_Operations'] Sep 12 16:38:22 127.0.0.1 greprod: ERROR - Connector: at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:473) Code delete-action final IContext context = this.getContext(); final Map<String, String> sortOrder = new HashMap<String, String>(); final IRemoveBatch batch = Core.removeBatch(context, Message.entityName, 10000, false, false); List<IMendixObject> objectList; boolean done = false; while (!done) { objectList = Core.retrieveXPathQueryEscaped(context, "//%s", 10000, 0, sortOrder, 0, Message.entityName); if (!objectList.isEmpty()) { for (final IMendixObject object : objectList) { batch.removeObject(object); } batch.commit(); objectList.clear(); } else { done = true; } } this.addRefreshClass(Message.entityName); return Boolean.TRUE; I'd like to hear!

asked 2011-09-12

Roberto Postma

4 answers

1

500.000 is really too many records for a logging module. On what setting did you set the log level? From the documentation:

Writing all log messages to the database can have a negative impact on the performance of your application, depending on the number of messages. In a production environment, it is recommended to log only messages of level WARNING and higher.

So that's a point to start with. If you're app is logging too many logging records, you should consider a cleanup scheduled event which removes old (for instance all records older than a week)logging records.

To solve this issue, you really should delete in smaller batches. Deleting 500000 objects with a microflow could cause trouble, but with a java-action it should work. Although it could take some time. Best way is to delete in batches, 50000 is a safe amount of objects. After doing this, you should really log less or cleanup.

answered 2011-09-12

Samet Kaya

0

@Samet Sure I could try. What I wonder about is if anyone can predict if only a smaller batch-size would help, or if I have to limit the amount of records that are deleted (to lets say 50.000)

Regarding the java-code I would not expect a big difference (regarding the (out of) memory) for deleting 10.000 records and 500.000 records, because a list of 10.000 records is retrieved (in objectList) and after committing the batch this is cleared and filled again... Should perhaps the Core.removeBatch also be called everytime inside the loop?

I'd like to hear some advice on

[1] If only limiting the batch-size would help enough
[2] Or if it is necesary to have a limit on a max # records
[3] What would be an advisable batch-size?

answered 2011-09-13

Roberto Postma

0

Update (in a new answer, because I think that is clearer than editing the (large) openingspost):

TOday I tested extensively with the deleteAll java-action from the community commons package.

1: I tried 10.000 as BATCHSIZEREMOVE, 4.000, 1000 but all gave me out of mem (on hosted env). 2: Locally I only tested with 10.000 and gave me out of mem as well 3: BATCHSIZEREMOVE set to 500 worked more or less locally and remote, but with a strange side-effect: it didn't delete all my records, but only about 50% each time.

I started with 285.725 records, and it went from

285.725 to
142.726 to
71.228 to
35.501 to
17.502 to
8.503 to
4005 to
2001 to
1001 to
501 to
2 to
2 to
0

I tried to check and debug the java-code but no luck: I don't understand why it only deletes (about) 50%.

What I do see in the logging every time te java-action finished was a (warning) message like this:

[deleteAll] After delete all there are 35501 objects remaining. This might be a result of the configured security or deletebehavior.

But: the object I try to deleteAll, doesn't have associations (and thus no delete behavior). And the userrole is allowed to delete.

Anyone ideas why this strange "I'll only delete 50% of your records" behavior occurs?

Of course I could switch back to the "old delete-all option" with a low batch-size; for that I didn't have time today (maybe later), but this one seems to be more efficient (as it only retrieves the IDs of the records to delete...

answered 2011-09-19

Roberto Postma

Michel Weststrate · Accepted Answer · 2011-09-13

Hi Roberto,

Note that delete behavior has serious impact on the speed of deletion, so consider removing delete behavior of that entity (if any).

Did you try the deleteAll method of the communitycommons package? I'm pretty curious whether that one is working better on this set of objects. It uses an DB retrieval schema instead of retrieveXPath, so I expect it is a bit more efficient in memory usage.

Its batchsize is 10.000 as well, that worked for me before.