1. In Laravel 9, a job failed with the error: has been attempted too many times or run too long. The job may have previously timed out. As shown in Figure 1.
2. I found that the table contained a total of 8,761,297 records. Using the chunk method, I retrieved the entire table in chunks of 10 records at a time. I printed the timestamp at the beginning of the closure function for each iteration. The final results are as follows:
[2024-04-09 09:43:57] local.INFO: exportExcelStart {"datetime":"2024-04-09 09:43:57"}
[2024-04-09 09:43:58] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:43:58"}
[2024-04-09 09:44:00] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:00"}
[2024-04-09 09:44:01] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:01"}
[2024-04-09 09:44:03] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:03"}
[2024-04-09 09:44:05] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:05"}
[2024-04-09 09:44:07] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:07"}
[2024-04-09 09:44:09] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:09"}
[2024-04-09 09:44:11] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:11"}
[2024-04-09 09:44:13] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:13"}
[2024-04-09 09:44:14] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:14"}
[2024-04-09 09:44:16] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:16"}
[2024-04-09 09:44:18] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:18"}
[2024-04-09 09:44:20] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:20"}
[2024-04-09 09:44:22] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:22"}
[2024-04-09 09:44:23] local.INFO: exportExcelChunk {"datetime":"2024-04-09 09:44:23"}
3. I concluded that processing 10 records took an average of 1.8 seconds. I decided to increase the chunk size from 10 to 1000. I printed the timestamp at the beginning of the closure function for each iteration. The final results are as follows. Processing 1000 records took an average of 4.8 seconds. This means the processing time per 10 records was reduced to 0.48 seconds.
[2024-04-09 09:53:48] local.INFO: exportExcelStart {"datetime":"2024-04-09 09:53:48"}
[2024-04-09 09:53:53] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:53:53"}
[2024-04-09 09:53:58] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:53:58"}
[2024-04-09 09:54:02] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:02"}
[2024-04-09 09:54:07] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:07"}
[2024-04-09 09:54:11] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:11"}
[2024-04-09 09:54:15] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:15"}
[2024-04-09 09:54:20] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:20"}
[2024-04-09 09:54:24] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:24"}
[2024-04-09 09:54:28] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:28"}
[2024-04-09 09:54:32] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:32"}
[2024-04-09 09:54:37] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:37"}
[2024-04-09 09:54:42] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:42"}
[2024-04-09 09:54:48] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:48"}
[2024-04-09 09:54:57] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:54:57"}
[2024-04-09 09:55:04] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:04"}
[2024-04-09 09:55:09] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:09"}
[2024-04-09 09:55:14] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:14"}
[2024-04-09 09:55:18] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:18"}
[2024-04-09 09:55:22] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:22"}
[2024-04-09 09:55:27] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:27"}
[2024-04-09 09:55:33] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 09:55:33"}
4. I tried processing 10,000 records at a time again. Processing 10,000 records took an average of 41 seconds. This means the processing time per 10 records was reduced to 0.41 seconds. Theoretically, a chunk size of 1000 is appropriate. It uses less memory per iteration, and the total execution time is not significantly longer compared to 10,000.
[2024-04-09 10:07:17] local.INFO: exportExcelStart {"datetime":"2024-04-09 10:07:17"}
[2024-04-09 10:08:04] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 10:08:04"}
[2024-04-09 10:09:00] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 10:09:00"}
[2024-04-09 10:09:55] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 10:09:55"}
[2024-04-09 10:10:49] local.INFO: exportExcelChunk1000 {"datetime":"2024-04-09 10:10:49"}
5. In the performance environment, processing 1000 records took 0.6 seconds, which means the processing time per 10 records was reduced to 0.06 seconds. I finally decided to limit the maximum number of exported rows to 100,000. The code implementation is as follows:
$count = 0;
$orderShippingLogQueryBuilder->chunk(1000, function ($orderShippingLogs) use ($params, $excel, $columns, &$count) {
// 限制最大导出行数为 100000
if ($count >= 100000) {
return false;
}
Log::info(
'exportExcelChunk1000',
[
date('Y-m-d H:i:s', time())
]
);
$data = [];
$count = $count + 1000;
});
6. Checking Laravel Telescope, with the local environment limit set to 3000, I confirmed that 3 SQL queries were executed, which matches expectations. As shown in Figure 2.
7. Finally, in the performance environment, exporting an Excel file with 100,000 rows took a total of 107 seconds. This matches expectations.
exportExcelStart ["2024-04-10 03:56:32"]
exportExcelChunk1000 ["2024-04-10 03:56:32"]
exportExcelChunk1000 ["2024-04-10 03:56:33"]
exportExcelChunk1000 ["2024-04-10 03:56:33"]
exportExcelChunk1000 ["2024-04-10 03:56:34"]
exportExcelChunk1000 ["2024-04-10 03:56:35"]
exportExcelChunk1000 ["2024-04-10 03:56:36"]
exportExcelChunk1000 ["2024-04-10 03:58:13"]
exportExcelEnd ["2024-04-10 03:58:19"]