Use ElggBatch instead. The documentation is very good at explaining how to do it

Thanks RvR for a quick response! I will try to use ElggBatch as suggested.

It's not recommended to manually remove data from the database unless you're VERY sure you know what you're doing.

The suggestion to use ElggBatch will help with out of memory issues. Also don't forget to set 'batch_inc_offset' to false

Another tip is to make a simple php script and execute it from the commandline (make sure you have some output to make sure the script is still running) this will remove the overhead of a webserver and make the script run faster.

Thanks Jerome!

I tried ElggBatch without the 'batch_inc_offset' and the deletion process is still consuming 200+% CPU usage.  I will try to set the batch_inc_offset to false as you suggested.

I delete entities of this subtype using simple command line below to call my cronjob. 

while true

     do

          curl https://mysite/cron/delete_entities_interval?force=1

     done

and for the deletion part. Below is the code block I've been using to delete one entity at a time:

$options = ['type' => 'object', 'subtype' => 'subtype_needed_to_be_deleted', 'limit' => 1];

$entities = new ElggBatch('elgg_get_entities', $options);

foreach ($entities as $entity) {...deletion process---}

With above approach, it is still slow. I also increased the innodb_buffer_pool_size and innodb_buffer_pool_chunk_size to 4GB. That didn't help neither.

I notice one thing that when deleting entities from the database, it returns many rows like 30+ rows when running 'show processlist;' in the mysql command prompt. This website is on development and nobody has access to it. So I thought the 'show processlist' should return only a couple rows - not 30+ rows.

Thanks again Jerome!

Changing to ElggBatch will not make the process consume less CPU it'll go as fast as it can thus using all CPU.

If you want it to slow down build in a sleep(). Bu then it'll take longer.

Also with ElggBatch make sure 'limit' is set to false.

Also if your calling your cron job with curl you'll get the overhead of the webserver. Just make a script which does this part

// start Elgg (in pseudo code)
- Load the Composer autoloader
- start the Elgg Application

// start removing your entities
$options = [
    'type' => 'object', 
    'subtype' => 'subtype_needed_to_be_deleted', 
    'limit' => false,
    'batch' => true,
    'batch_inc_offset' => false,
];

$entities = elgg_get_entities($options);

foreach ($entities as $entity) {
   echo "Removing GUID: {$entity->guid}" . PHP_EOL;
   ...deletion process---
}

The foreach loop will go on until all entities have been removed

Thanks Jerome! I will try your recommendation and let you know how it goes.

With your approach, the CPU usage is mostly below 50% for MySQL service. Awesome!!

Thanks again Jerome!