The problem is caused by loading too many data into the limited memory of PHP, once the memory exceeds the limit within this page scope, PHP will throw the error:
To ease the problem, you can set the directive memory_limit to -1 in php.ini to run your programs without limit on memory consumption.
[root@test ~]# vi /etc/php.ini
...
memory_limit = -1
#memory_limit = 128M
...
Or you can do it at run-time by the function:
<?php
ini_set('memory_limit', '-1');
...
?>
It might be able to workaround the error, but this is not a real cure to this problem in the production environment, you will see the error again some other days as data growing. I suggest you to review your codes to find out the root cause and remove the error definitely. Here are the questions you might need to know before doing something:
- What kind of data from database was loaded into memory?
- Which SQL statement was executed?
- Do you really need all of the data in your page?
In my experiences, I take the following steps to tune the statement in order to reduce the size of data that returned.
- Identify the interrupted point of execution.
- Identify the SQL statement that made PHP to fail.
- Execute the statement on the console or tools. (Tools are better to trace)
- Add more filter conditions on the statement.
- Remove unnecessary columns from the statement.
- Use distinct or group functions to return less data.
- Divide the data into pieces.
This is for identifying the suspect SQL statement in 2.
The is the target statement for tuning.
You can see how many data returned and think over the tuning strategy.
If there were unnecessary rows, you can add filters in the where clause to return less rows.
The wild card "*" is overly abused in the select column clause by developers, it may cause unnecessary data loaded into memory. You can use specific column names instead of "*" to return less data.
The keyword "DISTINCT" is extremely useful to remove duplicate rows hided in the dark. Group functions can summarize your data into meaningful figures. These two ways can condense rows into a few.
If you had tried all the options but helpless, this is your last resort: To divide you data into pages on the presentation layer instead of listing all the rows at a time.
For achieving the goal, you can add the limit clause after the statement, such as:
mysql> select name, introduction from table_name where date(c_date) = '2015-01-01' limit 10, 20;
This should be the correct way to do it.