I want to insert around 200k entries (several calculated booking dates for each user) into my MySQL database table with a PHP script. But although my script keeps on running, the database stops adding the entries (usually around 80k-85k entries / ~10MiB). Why? And how can I make it work?
The actual Database operations happen in a booking-management-system class (using mysqli prepared statements), from which I call the main method in a loop for every user. I maxed out the execution time via ini_set('max_execution_time', 0)
and ini_set('mysql.connect_timeout', 0)
as well as the server memory limit ini_set('memory_limit', '812M')
so the script and the server itself are not failing.
If I split up the number of user (which get their entries calculated) the inserts work just fine, so there's no limitation on the database site either.
My best guess is that there is some sort of memory/cache/buffer for insert-operations running full, that I'm unaware of. Does this make sense? If so, how can I deal with this kind of memory management? Or what else could be responsible?
Thank you in advance for your replies.
Edit after comments:
Here is my code. Unfortunately, I can't post every method in my class that performs the database operations, but they all are prepared statements that throw exceptions, when the execute()
of the query fails. I get neither errors nor exceptions. The script just keeps running, but the table rows aren't adding.
// Initialisation
...
ini_set('display_errors', '1');
ini_set('error_reporting', E_ERROR || E_WARNING);
ini_set('memory_limit', '812M');
ini_set('max_execution_time', 60000);
ini_set('mysql.connect_timeout', 0);
try{
$select = $mysqli->prepare("SELECT id FROM customers WHERE dept = ?");
$depts = array(24, 25, 29, 36, 93, 163, 108, 181);
foreach($depts as $id){
$select->bind_param('i', $id);
if($select->execute()){
$result = $select->get_result();
while($row = $result->fetch_object()){
$system->customerBooking($row->id);
}
$result->free();
} else {
echo '<br/>'.$select->error;
}
}
} catch(Exception $e){
var_dump($e);
}