troubleshooting Question

PHP + Wordpress + Large Database = Hell

Avatar of mikelima
mikelima asked on
PHPMySQL ServerWordPress
3 Comments1 Solution553 ViewsLast Modified:
Hi,
I have a large wordpress blog with 120,000 posts. All posts are text. The posts table has 400 Mb.

I am trying to do a very single task: retrieve all permalinks and modified dates of all posts and save them to a text file.

The problem is that PHP is crap when dealing with with large datasets... not counting the one trillion bugs it has.

The problem with the code I have developed (see below) is that it works almost perfectly, until it stops somewhere after retrieving 100,000 posts. The problem? simple: memory exhausted, probably due to some PHP memory leak.

I am using PHP 5.2.0 and MySQL 5 and please do not suggest me to upgrade PHP or MySql, as I cannot do that. I need to come with a code that can do the task without exhausting memory.

Please check out the code and see if you have any idea!


<?php
//this is two lines are calling wordpress config and all the required files it will need to run
$wp_did_header = true;
require_once( '/home/user/mysite/wp-config.php');

//I have increased the script's memory up to this point with no success
ini_set ( "memory_limit", "700M");


$query = "select count(id) as number FROM wp_posts";
$result = mysql_query($query) or die(mysql_error());
$line = mysql_fetch_array($result);
$totalPosts = $line[0];
$counter = 0;
$output = "output.txt";
$fk = fopen($output, 'w') or die("can't open file");

while ($counter <= $totalPosts) {

      $start = $counter;
      
      //I have tried different values for delta, in order to retrieve posts in chunks... small success doing this, but not enough
      $delta = 5000;
      if ($start + $delta > $totalPosts)
            $delta = $totalPosts - $start;

      $query = "SELECT id,post_modified FROM wp_posts LIMIT $start,$delta";
      
      $result = mysql_query($query) or die(mysql_error());

      while ($line = mysql_fetch_row($result) ) {

            fwrite($fk, get_permalink($line[0]) . chr(10));
            fwrite($fk, mysql2date('Y-m-d\TH:i:s\Z', $line[1], false) . chr(10));
            $counter = $counter + 1;
      }
      Unset($line);
      Unset($query);
      Unset($result);
      //$counter = $counter + 10;
}
fclose($fk);
?>


 
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros