I got a PHP script which browses files containing infos about 1M or 2M users Some users need to be updated due to changes provided.
I must be very naive, but thought my sequential code would send request after request, and couldn't overload the servers. The fact is : I put the 4 cpus of a production server to 95% use at my first run, and had a cron crash tonight, on another server.
So I guess PDO requests are sent to the server, and asynchronously executed. Nice, but... how can I avoid to overload it then ?
I couldn't find anything on the topic, so I guess I'm not looking for the right keywords.
My code logic is pretty simple :
For every user, I send first a SELECT request to check if the UPDATE is necessary, and if it is, I actually execute the UPDATE request.
I wonder why, but checking with the first SELECT request proved more efficient than just "updating 0 lines" with the same WHERE restrictions
The code can be roughly summed-up as this :
foreach ($parsed_user as $user => $infos) {
//PDO value bindings
if ($stmt_check_user_need_update->execute()){
continue;
if ($stmt_update->execute()) {
//log success
}
else {
//log error
}
As long as it doesn't crash, this works pretty well. The thing is... I'd like it to be reliable, and avoid impacting production environments.
I run updates on both SQL-Server and MySQL.