fab fab - 1 year ago 68
MySQL Question

PHP MySQL times out when updating large db

I have a MySQL database with about 6 million song IDs on an external provider with chrooted access to command line.

I get Bad Gateway error at about record 450, even if it keeps updating till about record 950. It's far from the full db and I don't really know how to handle it. I also tried to limit the query, but as said it will stops and a manual split is impossible (6000 handwritings!!!)

A flow overview is as follows, pseudocode after for deeper understanding:

  1. retrieve song IDs from a table

  2. query for details an external API (REST with json results)

  3. insert the full song details in a different table

Thank you in advance for help.


ini_set("memory_limit", "300M");//MAX SUPPORTED BY HOSTING

$query1 = "SELECT id FROM songs";
$sql = mysqli_query($link, $query1) or die("error");

while ( $row = mysqli_fetch_assoc($sql) ) {
$url = 'https://api.com/tracks?pass='.$pass.'&id='.$row["id"];
$content = file_get_contents($url);
$json = json_decode($content, true);//RETURNS "results" ARRAY

$query2 = "INSERT INTO songs_ok VALUES ";

foreach($json["results"] as $result){
$query2.= "( ";
$query2.= $row["timestamp"].",";
$query2.= $row["artist"].",";
$query2.= " ),";

$query2 = rtrim($query2,", ").";";

$sql2 = mysqli_query($link, $query2) or die("error");



p.s. I also tried to put the INSERT query at bottom, but obviously the result is almost the same. Thx

Answer Source

First: as @cale_b suggested, you should insert in batches which means inserting more records in a single query like INSERT INTO table (col1, cols2) VALUES (record1, record1), (record2, record2), (record3, record3), etc...

Second: you should retrieve your data from the table in batches. Meaning, retrieve for example 100 rows at a time (it's done with LIMIT in sql's SELECT and with OFFSET).

The first thing will keep your database server from overloading, the second thing will keep your memory from overloading.

Also, take into a consideration that some web services, like Cloudflare have maximum execution time set to a specific time that you CANNOT change. You should then run your script as a console application, as running scripts in console have no maximum execution times.

You can call your console script from a web application with a simple command:

$command = 'php /path/to/your_script.php > /dev/null 2>/dev/null &';


The added > /dev/null 2>/dev/null & will start console application in an independant process, so it won't be connected to your web app and won't break if a web app process stops.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download