I have a MySQL table with 300,000 rows. Since I am using a shared hosting server, I am only given 94MB of RAM to work with on Dreamhost, and running the feed file in one While loop will cause the memory to run out. So I tried to break up the loop into batches of 2500 items to prevent the memory running out:
$query = "SELECT COUNT(*) as num FROM products";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$itemcount = $row['num']; // Roughly 300,000 total items
$batches = $itemcount / 2500; // Number of while-loop calls - around 120.
for ($i = 0; $i <= $batches; $i++) {
$offset = $i * 2500; // MySQL Limit offset number
$query = "SELECT title,description,price FROM products LIMIT 2500,$offset";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
}
echo "Run Number: ".$i."<br />";
}
Even when there is nothing in the while loop during testing, I get “500 Internal Server Error” after only 40 seconds or so. Although I am not getting the “Memory Exhausted” error. I’m not sure why the internal server error happens. If I set the item count to around 50000, the full script completes, but it seems 300,000 is too much.
Does anyone know what is happening and if there is a solution, or better way of dealing with this type of script?
<?php
// Firstly connect to the MySQL server using PDO (Replace the dbname, $user and $pass with the appropriate values
try {
$db = $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
//$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::NULL_EMPTY_STRING);
}
catch (Exception $e) {
echo 'Caught exception: ', $e->getMessage(), "\
";
}
try {
$sql="
SELECT
title
, description
, price
FROM
products
";
$stmt = $db->prepare($sql);
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($products);
}
catch (PDOException $e) {
echo"Whoops! Something went wrong!");
echo" Query with error: ".$sql);
echo" Reason given:".$e->getMessage()."\
");
return false;
}
?>
Do you get any errors when using that?
Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.
Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
Thanks for the response. I tried the code that you provided, but I am still getting the 500 Internal Server Error, except that it gets displayed after just 10 seconds. In addition, I am not getting the “Memory Exhausted” error either.
Last I knew, DreamHost kept track of “Conuries” - Connection to Query ratio.
It seems if they were limiting your script to save resource use they would let you know.
In any case, IMHO running queries inside loops isn’t a great thing to do. Can you change things so this doesn’t need to run that often?
eg. caching? only gettiing data that has changed?
Unfortunately, I’m not exactly sure how to view MySQL and Apache error logs. Normally, MySQL errors appear on the page using the following when a problem happen:
mysql_query($query) or die(mysql_error());
The 500 Internal Server Error prevents the script from finishing, so I can’t see any MySQL errors. If I run only 50,000 items, the script completes with no problems, so I assume the MySQL is good.
This message says “your script broke and it didn’t send any HTTP headers before sending the error messages”. Some suggestions for further investigation:
Even batching up your code you are still trying to process 300k records in a single php script execution so batching doesn’t give you any advantage here. It’s most likely a timeout issue on the server, your script is taking to long to execute so the server is dropping your connection.
To find the best solution we really need to know what you are trying to achieve, then a solution might be found without needing to select 300k records in a single php script execution.
I’m on shared hosting on Dreamhost and supposedly the timeout (max_execution_time) is set at 300 according to phpinfo(), but the Internal Server Error happens between 30-60 seconds during the script.
My ultimate goal is to produce a formatted XML product feed of all 300,000 items. I can already produce an XML sitemap index of 300,000+ items using JUST ONE QUERY, but I am selecting fewer columns.
Right now, I am simply running batches of “MySQL Select” queries with more selected columns as a test and running them through a blank while() loop, that’s without even doing any XML file building.
It’s most likely the web server timing out and not php. Web server timeouts are normally set to 30 seconds and that would explain your error message with the headers. Your script didn’t break, the web server just cut it off before it finished.
Look to see if blue host allows you to run php as a cronjob. This is a way of running your php script without going through the we server. Otherwise you could try running the script multiple times with smaller sets of data and have the script append a file with the new generated XML each time it’s run.
I tried it again it again today, but I am getting weird results. The second “try” always starts, but it will stop after 5-10 seconds. Sometimes I get a blank page and sometimes I get a 500 Internal Server Error. I tried echoing a “Done” indicator at the end of the var_dump line but it never shows up.
I don’t understand PDO as it’s new to me, but from the script that SpacePhoenix provided, it looks like this is just one large query, it’s not broken up into batches. I am seeing a “Memory Exhausted” PHP error sometimes, otherwise it’s a 500 error.
His example doesn’t have a LIMIT in the query.
You could try adding a small LIMIT to it for starters and if it works increase it gradually until it breaks again.
I modified the script to add the LIMIT batches. It seems that the script fails with a “Memory Exhausted” error after 4 runs (20,000 items), which is like 5 seconds. It seems like the memory isn’t being purged after each batch and it just keeps adding up.
During each 5000 item batch, I call:
$offset = ",".($i * 5000); // $i is run batch number .. 1, 2, 3 etc in the foreach() loop.
if ($i == 0) {
$offset = "";
}
$sql="SELECT id,price,title,description FROM items LIMIT 5000 $offset";
$stmt = $db->prepare($sql);
$stmt->execute();
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
unset($stmt); unset($products);
I thought that each time the mysql query is executed, the memory from the previous batch would clear. So I added: unset($stmt); unset($products); but this still doesn’t work.
When I use: memory_get_usage() . I am seeing that the memory keeps accumulating after each 5000 item batch. Is there some way to clear the memory after each batch?
I believe I figured it out. After going through the scripts and realizing that I was getting bad advice elsewhere from multiples sources on the internet regarding LIMIT. The LIMIT statement was backwards in my code. The offset should be the first number, not the second one. After I switched them around, everything worked fine. Otherwise it was starting off at the same point (5000) and the LIMIT increased with each batch, with each batch getting larger and larger until the memory maxed.
Thanks for the help and introducing me to PDO, I will look into this more.