Bill Garrison Bill Garrison - 2 years ago 105
SQL Question

LAMP / Laravel - Report generation maxing out single CPU

So I have developed a report generation system in Laravel. We are using php 7 (opcache enabled) / apache / mysql / on a centos 7 box. With one report, grabbing all the information ends up taking about 15 seconds but then I have to loop through and do a bunch of filtering on Collections etc etc. I have optimized this from top to bottom for about a week and have got the entire report generation to take about 45 seconds (dealing with multiple tables with greater than 1 million entries). This maxes out my CPU until its done of course.

My issue is when we pushed it live to the client their CPU is not up to the task. They have 4 cpu's @ 8 cores each @ 2.2ghz. However, since php is a single process it only runs on one cpu and maxes it out and since its so slow it takes closer to 10 minutes to run the report.

Is there any way to get apache / php / linux use all 4 cpu's for a single php process? The only other option is to tell the client they need a better server....not an option. Please help.

Answer Source

So I stopped trying to find a way to have the server handle my code better and found a few ways to optimize my code.

First off, I used the collection groupBy() method to group my collection so that i had a bunch of sub-arrays with the id as key. When I looped through these I just grabbed that sub-array instead of using the collection's filter() method which is REALLY slow when dealing with this many items. That saved me a LOT of processing power.

Secondly, every time I used a sub-array I removed it from the main array. So the array became smaller and smaller every time it went through the foreach.

These optimizations ended up saving me a LOT of processing power and now my reports run fine. After days of searching for a way to allow php to handle parallel processing etc I have come to the conclusion that its simply not possible.

Hope this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download