英文:
Symfony\Component\ErrorHandler\Error\FatalError Allowed memory size of 536870912 bytes exhausted (tried to allocate 282828416 bytes)
问题
It appears that you're facing a memory exhaustion issue when generating a CSV file in your Laravel application, particularly when you loop through a large number of records. The error message you provided suggests that your application is trying to allocate more memory than is allowed.
You mentioned that your PHP memory limit is set to -1, which theoretically means there should be no memory limit. However, in practice, the server and other factors can still impose memory limits.
Here are some steps you can take to address this issue:
-
Check Server Memory: Ensure that your server has sufficient memory available for your application. The memory limit set in php.ini is only one aspect; your server itself may have its own limitations.
-
Optimize Code: Review the code in your
updateCsvWithFinancialSummariesForContractDetailsfunction. Given that you're processing a large number of records, it's important to optimize the code to minimize memory usage. Consider if there are areas where you can free up memory during processing. -
Batch Processing: Instead of processing all 6,000 records in a single loop, consider breaking them into smaller batches and processing them one batch at a time. This can help reduce memory usage.
-
Streaming: If possible, consider using streaming to generate the CSV file. Streaming allows you to write data directly to the file as you process it, rather than storing it in memory first. Laravel's
response()function with a closure can be useful for this purpose. -
Database Optimization: Ensure that your database queries are as efficient as possible. Make use of indexing, only fetch the columns you need, and avoid loading unnecessary data.
-
Memory Profiling: Use tools like Xdebug or Laravel Debugbar to profile memory usage and identify specific areas where memory is being consumed excessively.
-
Incremental Writing: Instead of building the entire CSV content in memory and then writing it to a file, try to write it incrementally. This means writing each record to the file as you process it, rather than storing all records in memory and writing them at once.
-
Check for Circular References: Be cautious of circular references in your code, as they can lead to unexpected memory consumption. Ensure objects are being properly garbage collected.
By taking these steps and optimizing your code, you should be able to reduce memory usage and resolve the memory exhaustion issue you're encountering.
英文:
I am trying to query a bunch of records from my database in a laravel api call and then concatenate parsed model properties to a string. That string will be used in a csv file.
However after refactoring to use multiple methods for more readability, I am not getting the following error:
Symfony\Component\ErrorHandler\Error\FatalError Allowed memory size of 536870912 bytes exhausted (tried to allocate 282828416 bytes)
In the php.ini we have the memory limit set to -1
memory_limit = -1
Here is the file I'm using for the api call
<?php
namespace App\Utils\Blueprints;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use App\Enums\FinancialExportStatusEnum;
use App\Enums\FinancialExportTypeEnum;
use Illuminate\Support\Collection;
use App\Strategy;
use Exception;
/**
* Class used to create financial exports for blueprint data
*/
class BlueprintFinancialExportUtil
{
/**
* Constants
*/
public const FINANCIAL_EXPORTS_FOLDER_S3_PATH = "vena/financial_exports/";
public const VENA_BUCKET_NAME = "s3-vena-solutions";
/**
* Query all won, approved strategies updated between dates provided and return the collection of strategies
* For commission version 2 and up, use the reviewer_status for approval
* For commission version 1, use the status only for approval
*
* @param Carbon $start_date
* @param Carbon $end_date
* @return Collection of Strategy
*/
public function getBlueprintDataBetweenDates(Carbon $start_date, Carbon $end_date): Collection
{
/**
* Use updated_at date in order to pick up blueprints that are marked as won/approved,
* so we don't ignore older blueprints that were newly marked as won/approved.
*/
$strategies = Strategy::where('updated_at', '>=', $start_date)
->where('updated_at', '<=', $end_date)
->where('status', 'won')
->orderBy('name', 'ASC')
->get();
/**
* Filter to keep only strategies where reviewer_status is approved for commission_version >= 2
*/
return $strategies->filter(function ($strategy) {
if ($strategy->commission_version >= 2) {
return $strategy->reviewer_status == 'approved';
}
return true; // keep it if the commission version is <= 2 since we already filtered using status
});
}
/**
* Query the financial_exports table for most recent successful upload
* and return it, given the export type.
* If none is found, use the default earliest date defined
*
* @param FinancialExportTypeEnum $export_type
*
* @return Carbon
*/
public function getDateOfLastSuccessfulUpload(FinancialExportTypeEnum $export_type): Carbon
{
$successful_upload_date_query_result = DB::table('financial_exports')
->select('created_at')
->where('export_type', $export_type)
->where('status', FinancialExportStatusEnum::Succeeded)
->get();
// default to 1970-01-01 00:00:00
$date_of_last_successful_upload = new Carbon('first day of January 1970');
if (!($successful_upload_date_query_result->isEmpty())) {
$index_of_last_date = count($successful_upload_date_query_result) - 1;
$date_of_last_successful_upload = new Carbon($successful_upload_date_query_result[$index_of_last_date]->created_at);
}
return $date_of_last_successful_upload;
}
/**
* Sorting function for a combination of fields in this order
*
* 1. Type: revenue or expense
* 2. Service name
* 3. Department name
*
* Only use the subsequent sorting field if the first one has both objects matching
* on the value.
*
*
* @param array $finance_summary_array
*
* @return array
*/
public function sortFinancialSummariesArray(array $finance_summary_array): array
{
usort($finance_summary_array, function ($finance_summary_a, $finance_summary_b) {
$same = 0;
$type_sort_result = strcmp($finance_summary_a->type, $finance_summary_b->type);
if ($type_sort_result === $same) {
$service_name_comp = strcmp($finance_summary_a->service_name, $finance_summary_b->service_name);
if ($service_name_comp === $same) {
return strcmp($finance_summary_a->department_name, $finance_summary_b->department_name);
}
return $service_name_comp;
}
return $type_sort_result;
});
return $finance_summary_array;
}
/**
* Using blueprint data between dates as well as the provided filename and headers,
* write contract details csv to storage.
*
* @param string $filename
* @param Collection $blueprints_between_dates
*
*/
public function writeBlueprintDataToContractDetails(string $filename, Collection $blueprints_between_dates): void
{
$headers_row_fields = [
'BlueprintID',
'Client Tier',
'Description',
'Type',
'Service',
'Department',
'Year',
'Month',
'Amount',
'Type of deal',
'Business model',
'Industry'
];
$csv_file_contents = implode(",", $headers_row_fields) . "\n";
foreach ($blueprints_between_dates as $blueprint) {
$finance_summary_array = $blueprint->getFinanceSummary();
$finance_summary_array = $this->sortFinancialSummariesArray($finance_summary_array);
$business_type = $blueprint->business_type()->first();
$client = $blueprint->client()->first();
$industry = isset($client) ? $client->industry()->first() : null;
$industry_name = isset($industry) ? $industry->name : "";
$csv_file_contents .= $this->updateCsvWithFinancialSummariesForContractDetails($finance_summary_array, $blueprint, $csv_file_contents, $industry_name, $business_type);
}
$this->saveAndUploadCSVExport(FinancialExportTypeEnum::Details, $blueprints_between_dates, $filename, $csv_file_contents);
}
/**
* For each string value processed, we have to replace commas with
* another character that is not a delimiter. If we don't,
* when we write the csv, for each comma it will think
* there is another column and the columns won't match
* the headers (i.e. each field might have more than 1 column),
* which we don't want.
*/
public function updateCsvWithFinancialSummariesForContractDetails(array $finance_summary_array, Strategy $blueprint, string $csv_file_contents, string $industry_name, object $business_type): string
{
foreach ($finance_summary_array as $finance_summary) {
if (isset($finance_summary->months)) {
foreach ($finance_summary->months as $finance_summary_month) {
$client_tier_name = isset($blueprint->client) && isset($blueprint->client->tier) ? $blueprint->client->tier->name : null;
$row_fields = [];
$row_fields[] = $blueprint->id;
$row_fields[] = str_replace(",", " ", $client_tier_name);
$row_fields[] = str_replace(",", " ", $blueprint->name); // name acts as description for blueprint
$row_fields[] = str_replace(",", " ", $finance_summary->type);
$row_fields[] = str_replace(",", " ", $finance_summary->service_name);
$row_fields[] = str_replace(",", " ", $finance_summary->department_name);
if (array_key_exists("date", $finance_summary_month)) {
$finance_summary_date = Carbon::createFromFormat('Y-m-d', $finance_summary_month['date']);
$row_fields[] = str_replace(",", " ", $finance_summary_date->year);
$row_fields[] = str_replace(",", " ", $finance_summary_date->month);
}
$finance_summary_month_cost = 0;
if (array_key_exists("value", $finance_summary_month)) {
$finance_summary_month_cost = $finance_summary_month["value"];
}
$row_fields[] = "$" . str_replace(",", " ", $finance_summary_month_cost);
$row_fields[] = str_replace(",", " ", $blueprint->type);
$row_fields[] = isset($business_type) ? str_replace(",", " ", $business_type->name) : ""; // same as business model
$row_fields[] = str_replace(",", " ", $industry_name);
$csv_file_contents .= implode(",", $row_fields) . "\n";
}
}
}
return $csv_file_contents;
}
/**
* Calculate and return the monthly internal sales commission
*
* @param Strategy $blueprint
*
* @return float
*/
public function getMonthlyInternalSalesCommission(Strategy $blueprint): float
{
$monthly_internal_sales_commission = 0;
if ($blueprint->guaranteed_term > 0) {
$monthly_internal_sales_commission = $blueprint->total_commission / $blueprint->guaranteed_term;
}
return $monthly_internal_sales_commission;
}
/**
* Calculate and return the referral partner costs sales commission
*
* @param Strategy $blueprint
*
* @return float
*/
public function getMonthlyReferralPartnerCosts(Strategy $blueprint): float
{
$monthly_referral_partner_costs = 0;
if ($blueprint->guaranteed_term > 0) {
$monthly_referral_partner_costs = $blueprint->total_partner / $blueprint->guaranteed_term;
}
return $monthly_referral_partner_costs;
}
/**
* Using blueprint data between dates as well as the provided filename and headers,
* write contract overview csv to storage.
*
* @param string $filename
* @param Collection $blueprints_between_dates
*
*/
public function writeBlueprintDataToContractOverview(string $filename, Collection $blueprints_between_dates): void
{
$headers_row_fields = [
'BlueprintID',
'Description',
'Length Of Contract',
'Guaranteed Term',
'Deal Value',
'Outsource Costs',
'Monthly Internal Commission',
'Monthly Referral Partner Costs',
'Start date',
'End date (Guaranteed term)',
'Pillar name',
'Type of deal',
'Business model',
'Industry',
'Client Tier'
];
$csv_file_contents = implode(",", $headers_row_fields) . "\n";
foreach ($blueprints_between_dates as $blueprint) {
$csv_file_contents .= $this->updateCsvContentsWithFinancialSummariesForContractOverview($blueprint, $csv_file_contents);
}
$this->saveAndUploadCSVExport(FinancialExportTypeEnum::Overview, $blueprints_between_dates, $filename, $csv_file_contents);
}
public function updateCsvContentsWithFinancialSummariesForContractOverview(Strategy $blueprint, string $csv_file_contents): string
{
$blueprint_outsource_costs = $blueprint->costs()->get();
$blueprint_outsource_total_cost = 0;
/**
* If there are outsource costs for this blueprint
* iterate over them and add them up to the total
*/
if (!$blueprint_outsource_costs->isEmpty()) {
foreach ($blueprint_outsource_costs as $blueprint_outsource_cost) {
$blueprint_outsource_total_cost += $blueprint_outsource_cost->costs;
}
}
/**
* For each string value processed, we have to replace commas with
* another character that is not a delimiter. If we don't,
* when we write the csv, for each comma it will think
* there is another column and the columns won't match
* the headers (i.e. each field might have more than 1 column),
* which we don't want.
*/
$monthly_internal_sales_commission = $this->getMonthlyInternalSalesCommission($blueprint);
$monthly_referral_partner_costs = $this->getMonthlyReferralPartnerCosts($blueprint);
$pillar = $blueprint->pillar()->first();
$business_type = $blueprint->business_type()->first();
$months_to_add_for_end_date = empty($blueprint->guaranteed_term) ? 0 : $blueprint->guaranteed_term;
$end_date = Carbon::parse($blueprint->date)->addMonths($months_to_add_for_end_date);
$client = $blueprint->client()->first();
$industry = isset($client) ? $client->industry()->first() : null;
$industry_name = isset($industry) ? $industry->name : "";
$client_tier = isset($blueprint->client) && isset($blueprint->client->tier) ? $blueprint->client->tier->name : null;
$row_fields = [];
$row_fields[] = $blueprint->id;
$row_fields[] = str_replace(",", " ", $blueprint->name); // name acts as description for blueprint
$row_fields[] = str_replace(",", " ", $blueprint->length); // length of contract
$row_fields[] = str_replace(",", " ", $blueprint->guaranteed_term);
$row_fields[] = "$" . $blueprint->total_retainer; // same as deal value
$row_fields[] = "$" . $blueprint_outsource_total_cost;
$row_fields[] = "$" . $monthly_internal_sales_commission; // same as internal sales commission / guaranteed_term
$row_fields[] = "$" . $monthly_referral_partner_costs; // same as referral partner costs / guaranteed_term
$row_fields[] = $blueprint->date;
$row_fields[] = $end_date->toDateString();
$row_fields[] = isset($pillar) ? $pillar->name : "";
$row_fields[] = $blueprint->type;
$row_fields[] = isset($business_type) ? $business_type->name : ""; // same as business model
$row_fields[] = $industry_name;
$row_fields[] = $client_tier ? $client_tier : "";
$csv_file_contents .= implode(",", $row_fields) . "\n";
return $csv_file_contents;
}
/**
* Saves status of csv export and uploads to S3
*
* @param FinancialExportTypeEnum $financialExportTypeEnum
* @param Collection $blueprints_between_dates
* @param string $filename
* @param string $csv_file_contents
* @return void
*/
public function saveAndUploadCSVExport(FinancialExportTypeEnum $financialExportTypeEnum, Collection $blueprints_between_dates, string $filename, string $csv_file_contents): void
{
Log::debug("BlueprintFinancialExportUtil: " . count($blueprints_between_dates) . " blueprints processed for $filename");
$this->saveExportStatusToDB($filename, $financialExportTypeEnum, FinancialExportStatusEnum::InProgress);
/**
* Try to upload the file and if it fails, throw an exception and log it
*/
try {
Storage::disk($this->VENA_BUCKET_NAME)->put($this->FINANCIAL_EXPORTS_FOLDER_S3_PATH . $filename, $csv_file_contents);
if (Storage::disk($this->VENA_BUCKET_NAME)->exists($this->FINANCIAL_EXPORTS_FOLDER_S3_PATH . $filename)) {
$this->saveExportStatusToDB($filename, $financialExportTypeEnum, FinancialExportStatusEnum::Succeeded);
} else {
$this->saveExportStatusToDB($filename, $financialExportTypeEnum, FinancialExportStatusEnum::Failed);
}
} catch (Exception $uploadException) {
Log::debug("BlueprintFinancialExportUtil: vena csv export failed");
Log::debug("BlueprintFinancialExportUtil: " . $uploadException->getMessage());
$this->saveExportStatusToDB($filename, $financialExportTypeEnum, FinancialExportStatusEnum::Failed);
throw $uploadException;
}
}
/**
* Create contract overview and contract details csv exports for blueprint data between specified dates
*/
public function createCSVExports(): void
{
$date_of_last_successful_upload_for_overview = $this->getDateOfLastSuccessfulUpload(FinancialExportTypeEnum::Overview);
$date_of_last_successful_upload_str_for_overview = $date_of_last_successful_upload_for_overview->toDateString();
$date_of_last_successful_upload_for_details = $this->getDateOfLastSuccessfulUpload(FinancialExportTypeEnum::Details);
$date_of_last_successful_upload_str_for_details = $date_of_last_successful_upload_for_details->toDateString();
$yesterday = Carbon::yesterday();
$yesterday_str = $yesterday->toDateString();
$contract_overview_filename = "Contract_Overview_" . $date_of_last_successful_upload_str_for_overview . "_" . $yesterday_str . ".csv";
$contract_details_filename = "Contract_Details_" . $date_of_last_successful_upload_str_for_details . "_" . $yesterday_str . ".csv";
$blueprints_between_dates_for_overview = $this->getBlueprintDataBetweenDates($date_of_last_successful_upload_for_overview, $yesterday);
$blueprints_between_dates_for_details = $this->getBlueprintDataBetweenDates($date_of_last_successful_upload_for_details, $yesterday);
$this->writeBlueprintData($blueprints_between_dates_for_overview, $blueprints_between_dates_for_details, $contract_overview_filename, $contract_details_filename);
}
/**
* Reusable method to write blueprint data for contract overview and contract details
*
* @param Collection[Strategy] $blueprints_between_dates_for_overview
* @param Collection[Strategy] $blueprints_between_dates_for_details
* @param string $contract_overview_filename
* @param string $contract_details_filename
*
* */
public function writeBlueprintData($blueprints_between_dates_for_overview, $blueprints_between_dates_for_details, $contract_overview_filename, $contract_details_filename): void
{
if (count($blueprints_between_dates_for_details) > 0) {
$this->writeBlueprintDataToContractDetails($contract_details_filename, $blueprints_between_dates_for_details);
Log::debug("Completed writing csv exports for contract details");
} else {
Log::debug("No blueprints found to export for contract details");
}
if (count($blueprints_between_dates_for_overview) > 0) {
$this->writeBlueprintDataToContractOverview($contract_overview_filename, $blueprints_between_dates_for_overview);
Log::debug("Completed writing csv exports for contract overview");
} else {
Log::debug("No blueprints found to export for contract overview");
}
}
/**
* Create contract overview and contract details csv exports for blueprint data between manual date inputs
*
* @param Carbon $start_date
* @param Carbon $end_date
*
* @return array of contract csv filenames
*/
public function createCSVExportsFromManualDateInputs(Carbon $start_date, Carbon $end_date): array
{
$contract_overview_filename = "Contract_Overview_" . $start_date . "_" . $end_date . ".csv";
$contract_details_filename = "Contract_Details_" . $start_date . "_" . $end_date . ".csv";
$blueprints_between_dates_for_overview = $this->getBlueprintDataBetweenDates($start_date, $end_date);
$blueprints_between_dates_for_details = $this->getBlueprintDataBetweenDates($start_date, $end_date);
$this->writeBlueprintData($blueprints_between_dates_for_overview, $blueprints_between_dates_for_details, $contract_overview_filename, $contract_details_filename);
return [
'overview' => $contract_overview_filename,
'details' => $contract_details_filename
];
}
/**
* Save financial export status to db
*
* @param string $filename
* @param string $export_type
* @param FinancialExportStatusEnum $export_status
*
*/
public function saveExportStatusToDB(string $filename, FinancialExportTypeEnum $export_type, FinancialExportStatusEnum $export_status): void
{
DB::table('financial_exports')
->updateOrInsert(
['filename' => $filename, 'export_type' => $export_type],
['status' => $export_status, 'updated_at' => now(), 'created_at' => now()]
);
}
}
The stacktrace is showing me it's an issue with this function call and operation
$csv_file_contents .= $this->updateCsvWithFinancialSummariesForContractDetails($finance_summary_array, $blueprint, $csv_file_contents, $industry_name, $business_type);
We are looping through 6000 records for this call, parsing the results and then concatenating to a string for the csv file to be written.
Before breaking it up into methods it was working, but now it's not.
Also since I have more memory than it's showing I am trying to allocate, I'm confused on the error.
After logging the memory usage, right when the application starts the query in the resolve method before any db operations the memory seems high. Here is a log of the bytes as it progresses:
VenaExports query: start of resolve 52172024
VenaExports query: right before createCSVExportsFromManualDateInputs 52287568
createCSVExportsFromManualDateInputs: before any operations 52287568
writeBlueprintData: blueprint id: before writing contract details: 62600016
writeBlueprintDataToContractDetails: blueprint id: 6423
writeBlueprintDataToContractDetails: before update mem 63504864
writeBlueprintDataToContractDetails: after update mem 63545408
writeBlueprintDataToContractDetails: blueprint id: 6948
writeBlueprintDataToContractDetails: before update mem 63569176
writeBlueprintDataToContractDetails: after update mem 63582904
writeBlueprintDataToContractDetails: blueprint id: 6949
writeBlueprintDataToContractDetails: before update mem 63631280
writeBlueprintDataToContractDetails: after update mem 63667984
writeBlueprintDataToContractDetails: blueprint id: 6956
writeBlueprintDataToContractDetails: before update mem 63666144
writeBlueprintDataToContractDetails: after update mem 63674336
writeBlueprintDataToContractDetails: blueprint id: 6958
writeBlueprintDataToContractDetails: before update mem 63703032
writeBlueprintDataToContractDetails: after update mem 63730600
writeBlueprintDataToContractDetails: blueprint id: 1374
writeBlueprintDataToContractDetails: before update mem 63746272
writeBlueprintDataToContractDetails: after update mem 63780800
writeBlueprintDataToContractDetails: blueprint id: 2698
writeBlueprintDataToContractDetails: before update mem 64004440
writeBlueprintDataToContractDetails: after update mem 64086728
writeBlueprintDataToContractDetails: blueprint id: 1872
writeBlueprintDataToContractDetails: before update mem 64061984
writeBlueprintDataToContractDetails: after update mem 64201280
writeBlueprintDataToContractDetails: blueprint id: 1062
writeBlueprintDataToContractDetails: before update mem 64206536
writeBlueprintDataToContractDetails: after update mem 64481000
writeBlueprintDataToContractDetails: blueprint id: 1064
writeBlueprintDataToContractDetails: before update mem 64492208
writeBlueprintDataToContractDetails: after update mem 65045200
writeBlueprintDataToContractDetails: blueprint id: 6076
writeBlueprintDataToContractDetails: before update mem 65059504
writeBlueprintDataToContractDetails: after update mem 66161384
writeBlueprintDataToContractDetails: blueprint id: 5807
writeBlueprintDataToContractDetails: before update mem 66168368
writeBlueprintDataToContractDetails: after update mem 68380240
writeBlueprintDataToContractDetails: blueprint id: 4488
writeBlueprintDataToContractDetails: before update mem 68398720
writeBlueprintDataToContractDetails: after update mem 72814240
writeBlueprintDataToContractDetails: blueprint id: 2810
writeBlueprintDataToContractDetails: before update mem 72839312
writeBlueprintDataToContractDetails: after update mem 81678512
writeBlueprintDataToContractDetails: blueprint id: 1633
writeBlueprintDataToContractDetails: before update mem 81702656
writeBlueprintDataToContractDetails: after update mem 99376928
writeBlueprintDataToContractDetails: blueprint id: 3406
writeBlueprintDataToContractDetails: before update mem 99463760
writeBlueprintDataToContractDetails: after update mem 134824560
writeBlueprintDataToContractDetails: blueprint id: 1997
writeBlueprintDataToContractDetails: before update mem 134785128
writeBlueprintDataToContractDetails: after update mem 205490312
writeBlueprintDataToContractDetails: blueprint id: 5829
writeBlueprintDataToContractDetails: before update mem 205522920
答案1
得分: 0
问题出在字符串连接上。
我研究了另一个相关的问题，解决方法是使用 implode 函数将数组转换为字符串。
在 PHP 中，以数组条目存储似乎比字符串连接在大规模情况下更高效。
不确定为什么字符串连接较慢。
英文:
The issue was with the string concatenation.
I researched another related issue and the fix was using the implode function to create a string from an array.
Storing array entries appears to be more efficient than string concatenation in php at scale.
Not sure why string concatenation is slow.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论