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.
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
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/
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!
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!
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.
(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?
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
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.
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
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)');
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast