By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

Cutting down memory usage

jlm699
100+
P: 314
Greetings all,
I'm using a PHP-based web page to access an SQL database containing logs which has grown quite large over time. A few weeks ago I recieved the following error:
"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 11 bytes) in /location_of_script/database.php on line 75"

Now I know basically what's causing this problem I'm just not sure exactly how I should go about fixing it... See after I got this error I went into php.ini and changed the memory_limit from 128M to 256M (the max amt of memory a script may consume). I did this so that I could still access the database via the webpage, but I know that as the database continues to grow this temporary fix will eventually break too.

My database.php script is as follows...

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. class Database
  3. {
  4. var $database_name;
  5. var $database_user;
  6. var $database_pass;
  7. var $database_host;
  8. var $database_link;
  9. // This associative array will be used for Table headings in display()
  10. var $key_lookup = array("rn"=>"Road Number", "date_time"=>"Date & Time", "date"=>"Date","time"=>"Time", "usr"=>"User", "sw_ver"=>"Software Version");
  11.  
  12. function Database($name)
  13. {
  14.     if (strlen($name)) $this->database_name = $name;
  15.     else $this->database_name = "Testing";
  16.     $this->database_user = "user";
  17.     $this->database_pass = "passwd";
  18.     $this->database_host = "localhost";
  19. }
  20.  
  21. function changeName($name) { $tihs->database_name = $name; }
  22. function changeUser($user) { $this->database_user = $user; }
  23. function changePass($pass) { $this->database_pass = $pass; }
  24. function changeHost($host) { $this->database_host = $host; }
  25.  
  26. function changeOpts($user, $pass, $host, $name)
  27. {
  28.     $this->database_name = $user;
  29.     $this->database_user = $user;
  30.     $this->database_pass = $pass;
  31.     $this->database_host = $host;
  32. }
  33.  
  34. function connect()
  35. {
  36.     $connStr = "host=" . $this->database_host .
  37.         " dbname=" . $this->database_name .
  38.         " user=" . $this->database_user    .
  39.         " password=" . $this->database_pass;
  40.     $this->database_link = pg_connect($connStr) 
  41.         or die('Could not connect: ' . pg_last_error());
  42. }
  43.  
  44. function disconnect()
  45. {
  46.     if (isset($this->database_link)) pg_close($this->database_link);
  47.     else pg_close();
  48. }
  49.  
  50. /*    This function will allow us to run queries that produce no output
  51.       such as INSERT, DROP, UPDATE, etc.
  52.     We first need to make sure there is a valid connection however.    */
  53. function iquery($qry)
  54. {
  55.     if(!isset($this->database_link)) $this->connect();
  56.     $temp = pg_query($this->database_link, $qry)
  57.         or die("Error: " . pg_last_error());
  58. }
  59.  
  60. /*    This function will allow us to run queries that do produce output
  61.       (ie, SELECT)
  62.     We first must verify that we are connected to a database.    */
  63. function query($qry)
  64. {
  65.     if(!isset($this->database_link)) $this->connect();
  66.     $result = pg_query($this->database_link, $qry)
  67.         or die("Error: " . pg_last_error());
  68.     $returnArray = array();
  69.     $i=0;
  70.     while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
  71.         if ($row)
  72.             $returnArray[$i++]=$row;
  73.     pg_free_result($result);
  74.  
  75.     return $returnArray;
  76. }
  77.  
  78. /*    This function allows us to display any and all types of queries,
  79.       including cross-queries.  The only complication arises when
  80.       querying the snapshot table, which stores the snapshot as a
  81.       large chunk of raw text.  Special processing is required    */
  82. function display($results)
  83. {
  84.     $keys = array_filter(array_keys($results[0]), is_string);
  85.     echo '<table border=1 cellpadding=2>';
  86.     echo '<tr>';
  87.     foreach($keys as $key) {
  88.         echo '<th>';
  89.         if (array_key_exists($key, $this->key_lookup))
  90.             echo $this->key_lookup[$key];
  91.         else
  92.             echo $key;
  93.         echo '</th>';
  94.     }
  95.     echo '</tr>';
  96.     foreach($results as $result)
  97.     {
  98.         echo '<tr>';
  99.         foreach($keys as $key) {
  100.             if (!strcmp($key, "rn") or !strcmp($key, "name") or !strcmp($key, "usr") or
  101.                 !strcmp($key, "log_msg") or !strcmp($key, "type"))
  102.                 echo '<td nowrap>';
  103.             else
  104.                 echo '<td nowrap align=right>';
  105.             /* In the case of snapshot log we must process for readability */
  106.             if (!strcmp($key, "text")) {
  107.                 echo '<table border=1 bordercolor=gray>';
  108.                 echo '<tr>';
  109.                 $text=explode("\n", $result[$key]);
  110.                 for ($cnt = 0; $cnt < count($text); $cnt += 1) {
  111.                     $text[$cnt] = explode(" ", $text[$cnt]);
  112.                 }
  113.                 foreach($text as $line) {
  114.                     foreach($line as $word) {
  115.                         if (strcmp(substr($word,-1), ',')) {
  116.                             echo '<td nowrap align=right>'.$word;
  117.                             echo '</td></tr><tr>';
  118.                         }
  119.                         else {
  120.                             echo '<td nowrap align=right>';
  121.                             echo substr($word,0,-1).'</td>';
  122.                         }
  123.                     }
  124.                 }
  125.                 echo '</table>';
  126.             }
  127.             else
  128.                 echo $result[$key];
  129.             echo '</td>';
  130.         }
  131.         echo '</tr>';
  132.     }
  133.     echo '</table>';
  134. }
  135. }
  136. ?>
  137.  
So I assume that in order to cut down on the memory size that each query() call consumes I need to generate the html table as I'm recieving the data... does anybody have an idea of how I would go about this?

Any help would be appreciated
Dec 17 '07 #1
Share this Question
Share on Google+
2 Replies


code green
Expert 100+
P: 1,726
This is not unusual. You may have to reconsider how you want to handle the data.
This line is probably the most memory intensive, (but it may not fail here)
[PHP]while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
if ($row)
$returnArray[$i++]=$row;[/PHP]By the way, the if() condition is simply duplicating the while() condition here.
You are collecting the results in one big array. Nothing wrong with that, I have a similar class.
When my scripts cause memory problems I submit multiple queries, process the data then concatenate the output into one big HTML string.
At the moment you have 2-3 arrays handling a lot of data.
Suprisingly, using the big string idea consumes less memory.
Dec 17 '07 #2

jlm699
100+
P: 314
This is not unusual. You may have to reconsider how you want to handle the data.
This line is probably the most memory intensive, (but it may not fail here)
[PHP]while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
if ($row)
$returnArray[$i++]=$row;[/PHP]By the way, the if() condition is simply duplicating the while() condition here.
You are collecting the results in one big array. Nothing wrong with that, I have a similar class.
When my scripts cause memory problems I submit multiple queries, process the data then concatenate the output into one big HTML string.
At the moment you have 2-3 arrays handling a lot of data.
Suprisingly, using the big string idea consumes less memory.
You're right... I went ahead and merged the above function with the display() function. In this solution, instead of adding onto the returnArray I simply pump the data to output. This has greatly cut down on the memory usage of this script.

Thanks!
Jan 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.