0

PHP has memory limits set at a global level in the PHP.ini file. I also know that they can be configured at the local level.

Our memory limit for a query is set to 132MB, but we keeping getting errors like this:

Fatal error: Allowed memory size of 161480704 bytes exhausted (tried to allocate 32 bytes) in #{removed_file_path}/script.php on line #{line}

Currently, we are monitoring our systems through an errror log file, that caches errors while users are browsing a page, as well as using cron; however, these don't get logged anywhere.

The error can be fixed by changing any calls to mysql_query to mysql_unbuffered_query, however I need to know where this needs to be changed for all files on the server. If the script is actually not having memory issues, then I don't want to change the code since I want the server to read the results to RAM wherever possible.

We don't want to increase our memory limit in PHP since our host advises us against that.

What can be done to fix the memory issues? We are running on CentOS 6.7.

2

Your PHP interpreter is set to terminate any script which requires more than 132MB of memory. Your script is trying to allocate more than 161MB of memory. The problem is quite simple, and so are the solutions. You can either:

  1. Get your PHP interpreter to allocate more. The reason why your host is advising against that is that if he didn't, he'd end up allocating 8GB+ of memory to you simply because your database keeps growing.
  2. Forget about PHP and the deprecated mysql_* API, and use something which doesn't enforce such memory restrictions. Bare in mind that even if the interpreter doesn't bother you anymore, the OS probably will at some point.
  3. Improve your script.

Using mysql_unbuffered_query goes with solution #3. Since PHP no longer buffers the MySQL result set, it doesn't take as much memory, and the interpreter stops complaining. You are basically switching from buffered to unbuffered queries (see examples #2 and #3, and consider switching to PDO, this is 2016). If you want to keep working with PHP, and if you think your design is correct, this is your solution.

I don't want to change the code since I want the server to read the results to RAM wherever possible.

The people who work on DBMSes are usually doing a great job. Their engines are optimised, and a query won't take more time than it needs. Considering the fact that you're using PHP and the old mysql_* API, you won't have me believe that speed is really such a concern here... My guess is: you can afford to keep the data in the database, and not in RAM. Use unbuffered queries, fetch/process your rows one at a time, and rely on your DBMS a bit more (besides, it will probably do some buffering for you...).

If you're still not convinced, a possible improvement could be to rethink the queries themselves. Are you sure you are not fetching more data than you need? (I mean, do you really need that SELECT * at the beginning of your query?) Are you sure MySQL couldn't take care of some processing for you? DBMSes are quite powerful tools, put them to work, and don't do everything in your PHP script. A typical example would be computing an average: instead of fetching all the numbers in the column and using a summing loop, ask for AVG(column) directly.

Without your script, it is quite difficult for me to give further advice on that part (and the folks at Stack Overflow would probably do a better job). However, if at that point you think you've brought enough modifications to your script... what about submitting it to Code Review? If you can't see a way to improve it even more, chances are the people over there will.

If at some point, you feel like you've exhausted whatever options PHP and MySQL had to offer, I think you'll have to consider using something else. Depending on the kind of data processing you're doing, you might be able to find other tools more suitable to the task at hand.

  • I'll go ahead and troubleshoot a solution based on your comments, and I'll get back. Great explanations. – naltun Feb 10 '16 at 15:47
  • Hey John. Would you at all advise using MySQLi instead of PDO? If not, why so? – naltun Feb 23 '16 at 15:48
  • MySQLi is not an alternative to PDO. See what PDO is here. – John WH Smith Feb 23 '16 at 17:25
  • Thanks John; I'm still troubleshooting a solution (this is one of several tasks). – naltun Feb 24 '16 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.