Support the ongoing development of Laravel.io →
Requests Database Eloquent
Last updated 2 years ago.
0

The memory leak is probably due to Laravels query log. I'm on a phone at the moment and I would provided you with a link, but try Googling disable laravel query log.

0

mengidd said:

The memory leak is probably due to Laravels query log. I'm on a phone at the moment and I would provided you with a link, but try Googling disable laravel query log.

Tried disabling the query log and I still get the same issue - crashes out on memory i.e. [28216.358587] Killed process 4838 (php) total-vm:1953244kB, anon-rss:1673624kB, file-rss:0kB

0

Sounds like a candidate for a Queue

Last updated 9 years ago.
0

Can you break the data up? Like 1 through 10,000, then do 10,001 through 20,000? That sort of thing. I had to run reports at a trucking co and I dumped database to local computer, connected to msaccess via odbc, and used access report designer. But this was for printing.
But try to only do part at a time, it may work. Plus seehttp://tagide.com/blog/2012/08/how-to-handle-large-data-in-mysql/

Last updated 9 years ago.
0

jimgwhit said:

Can you break the data up? Like 1 through 10,000, then do 10,001 through 20,000? That sort of thing. I had to run reports at a trucking co and I dumped database to local computer, connected to msaccess via odbc, and used access report designer. But this was for printing.
But try to only do part at a time, it may work. Plus seehttp://tagide.com/blog/2012/08/how-to-handle-large-data-in-mysql/

The data is already broken up, even if I change the chunk size to 10 it still crashes out!

0

davestewart said:

Sounds like a candidate for a Queue

This is already a cron, the problem is that the script pulls in too much data!

0

Have you tried chunkifying inner loops? Say, instead of:

foreach ($company->equipment as $equipment) {

do:

$company->equipment()-chunk(10, function($companyEquipment) {
    foreach ($companyEquipment as $equipment) {
        //...
    }
...
});

Also, it might be a good idea to check for memory leaks. Log memory usage before processing each company, and see if it grows or stays at about the same level.

0

(Sorry, I'm not at my dev computer at the moment, so the content of this post might not work, but still might give a useful hint)

Couldn't you limit the amount that needs to be saved in RAM by changing things like below?

From:

foreach ($company->agreements as $agreement) {
  if (in_array($agreement->status, ['Unsigned', 'Active', 'Cancel on Expiry'])) {

To:

foreach ($company->agreements->whereIn('status', array('Unsigned', 'Active', 'Cancel on Expiry')) as $agreement) {

Or similar to prevent loading too much unneeded data?

Last updated 9 years ago.
0

tkprocat said:

(Sorry, I'm not at my dev computer at the moment, so the content of this post might not work, but still might give a useful hint)

Couldn't you limit the amount that needs to be saved in RAM by changing things like below?

From:

foreach ($company->agreements as $agreement) {
 if (in_array($agreement->status, ['Unsigned', 'Active', 'Cancel on Expiry'])) {

To:

foreach ($company->agreements->whereIn('status', array('Unsigned', 'Active', 'Cancel on Expiry')) as $agreement) {

Or similar to prevent loading too much unneeded data?

You cannot use whereIn like that on a collection

0

But you can use whereIn like this:

foreach ($company->agreements()->whereIn('status', array('Unsigned', 'Active', 'Cancel on Expiry'))->get() as $agreement) {

Notice the parentheses after ->agreements.

0

Xum said:

Have you tried chunkifying inner loops? Say, instead of:

foreach ($company->equipment as $equipment) {

do:

$company->equipment()-chunk(10, function($companyEquipment) {
   foreach ($companyEquipment as $equipment) {
       //...
   }
...
});

Also, it might be a good idea to check for memory leaks. Log memory usage before processing each company, and see if it grows or stays at about the same level.

Checked for memory leaks and it stays constant througout each chunk

0

Here is the full solution that solved the memory problem (4gb+ to 100mb or so)

DB::connection()->disableQueryLog();
$start = microtime(true);
Company::chunk(50, function($companies) {
	foreach ($companies as $company) {
		$maintainedCount = 0;
		$equipmentCount = 0; 
		$leadCount = 0;
		$invoiceCount = 0;
		$projectCount = 0; 
		$orderCount = 0;
		$maintainedCount = reset(DB::select(DB::raw('SELECT COUNT(*) FROM (
									SELECT equipment.id FROM equipment
									INNER JOIN agreements ON agreements.id = equipment.agreement_id 
									WHERE agreements.company_id = :companyId
									AND equipment.status = "Active"
									AND (agreements.status = "Active" OR agreements.status = "Unsigned" OR agreements.status = "Cancel on Expiry")) AS QUERY;'), 
									['companyId' => $company->id])[0]);
		/**			
		* foreach ($company->agreements as $agreement) {
		* 	if (in_array($agreement->status, ['Unsigned', 'Active', 'Cancel on Expiry'])) {
		* 		$maintainedCount += $agreement->equipment->where('status', 'Active')->count();
		* 	}
		* }
		*/
		$equipmentCount = $company->equipment->where('status', 'Active')->count();
		$unmaintainedCount = ($equipmentCount - $maintainedCount);
		$leadCount= $company->leads->where('status', 'Open')->count();
		$invoiceCount = reset(DB::select(DB::raw('SELECT COUNT(*) FROM (
											SELECT invoices.id
											FROM invoices
											WHERE invoices.company_id = :companyId
											AND (invoices.status = "Pending" OR invoices.status = "Complete")) AS QUERY;'), 
											['companyId' => $company->id])[0]);			
		/**
		*foreach ($company->invoices as $invoice) {
		*	if (in_array($invoice->status, ['Pending', 'Complete'])) {
		*		$invoiceCount += 1;
		*	}
		*}
		*/
		$projectCount = reset(DB::select(DB::raw('SELECT COUNT(*) FROM (
												  SELECT projects.id FROM projects
												  INNER JOIN equipment ON equipment.id = projects.equipment_id
												  INNER JOIN locations ON locations.id = equipment.location_id
												  WHERE locations.company_id = :companyId
												  AND (projects.status = "Pending" OR projects.status = "Complete")) AS QUERY;'), 
											      ['companyId' => $company->id])[0]);				
		$orderCount = reset(DB::select(DB::raw('SELECT COUNT(*) FROM (
												SELECT orders.id FROM orders
												INNER JOIN projects ON projects.id = orders.project_id
												INNER JOIN equipment ON equipment.id = projects.equipment_id
												INNER JOIN locations ON locations.id = equipment.location_id
												WHERE locations.company_id = :companyId
												AND (orders.status = "Pending" OR orders.status = "Complete")) AS QUERY;'), 
											    ['companyId' => $company->id])[0]);
		/**
		*foreach ($company->equipment as $equipment) {
		*	foreach ($equipment->projects as $project) {
		*		if (in_array($project->status, ['Pending', 'Complete'])) {
		*			$projectCount += 1;
		*		}
		*		if($project->order <> null) {
		*			if (in_array($project->order->status, ['Pending', 'Complete'])) {
		*				$orderCount += 1;
		*			}
		*		}
		*	}
		*}
		*/
		DB::table('companies')->where('id', $company->id)->update(['unmaintained_count' => $unmaintainedCount, 'lead_count' => $leadCount, 'project_count' => $projectCount, 'order_count' => $orderCount, 'invoice_count' => $invoiceCount]);
	}
});
$duration = number_format((microtime(true) - $start), 2);
$this->info('Company count cache updated! (it took '.$duration.' seconds)');
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.