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:
- 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.
- 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.
- Improve your script.
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
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.