·

photo credit: John Virgolino via photopin cc

Prevent the generic ‘Killed’ error in your PHP script

Tags: ,

I recently found myself in a task where I had to process 500.000+ rows from a database, each with a while set of columns. The first thing I tried to do was fetch it all in a single query and have PDO sent me a result. My server didn’t like this very much and gave me a subtle Killed -message, halting further execution of my script. Nothing more, nothing less…
Some little research told me that this most likely cause of this error message was the Linux-Out-Of-Memory (OOM) error. Needless to say, the large query was the source of this error. I changed my script a bit to work around this problem, so if you encounter a similar issue, this article might be of some help for you.

Identify the bottleneck

In my case the bottleneck was a SQL query that tried to fetch 500.000+ rows from a database and return it in a set:

What the fetch() -method does is return all the rows in the $rows -array. This would cause PHP to use a lot of memory: each row is stored in the array and when the dataset is big enough this will clog up your memory.

Optimizing it

Now we’ve identified the problem: the $rows -array simply gets too big for PHP. The solution is we have to make it smaller, so it consumes less memory. How can we do that? Well that’s easy: by limiting our SQL-query. But we still want to parse all of our records! So how do we do this?
Well, the answer is easy: instead of creating one big query, we separate our logic in multiple queries that are limited.

Step 1: Count it all

The first thing we need to do is get a count of all the rows we want to parse. This can be simply done with a count-query:

Step 2: Fetch the data in chunks

Now we know the total count of rows in our table, we can fetch our data in smaller chunks:

You might have to fiddle with the $chunkSize -parameter to check what works best for you, but this simple approach will give your PHP script a drastic drop in memory consumption.

Visitors give this article an average rating of 2.7 out of 5.

How would you rate this article?

Leave a Reply