CODEWITHSUNDEEP
phpmysqlperformanceapicron
Rohit Dhiman
Rohit Dhiman

Reputation: 2741

How to optimize cron and PHP script for large data update and multiple API calls

Right now, I've a php script that is selecting ~50000 records from database and calling api for each record and on the bases of api response updating records status in the database.

I had thought about using 10 php file run from 10 crons each and dividing 50000 records by 10 then each script have to deal with 5000 records only. but as the records increase I have to create more crons.

am I doing it right or Is there any other better way of doing this?

Upvotes: 0

Views: 947

Answers (2)

Rick James
Rick James

Reputation: 142433

If you have too many crons, they will stumble over each other. And, why use cron? Will you be repeating this task daily? Instead have one job that divvies up the 50000 and launch 10 subprocesses to do the work.

Start with as many crons as your CPU has cores. If the API is cpu-bound, this may be optimal.

If the API is somewhere else, then it depends on how much of your time is spent waiting for the results to come back.

Batch things, if possible, in whatever is the slowest leg -- the API or the fetch from the database.

Goals:

  • Maximize use of your resources.
  • Don't exceed what your resources can achieve. (If 10 PHPs saturates the CPU, it would be bad to increase to 100.)
  • Beware of rate limitations in the API. (One place limits me to 10/minute, so I had to slow down to avoid errors from the API!)

Upvotes: 1

James Jayson
James Jayson

Reputation: 336

The only issue I am seeing from this is the potential execution time from php, this can be solved with set_time_limit(). As for the amount of memory you could select the count of records and then select the nth index, thus processing one record at a time. As a fail-safe measure, a date (or datetime!) field could be entered into a table to record when each record was last updated. In the case of an error the process would then be able to pick up where it left off.

I hope this helps!

Upvotes: 1

Related Questions