[RESOLVED] cron job to update 1000s of records at a certain time each day...

[MattG]

...just a quick question about it. I want to set up a cron job to run at midnight every night to update the rankings of around 4000 entries in a mysql table. What kind of server load is this likely to cause each night? Forgive my ignorance on this, it's probably a simple/obvious answer :bemused:

[Bjom]

simply test it. It depends on too many factors, so that there is no simple answer. 4000 "entries" (records) actually does not sound like a lot, but as I said, that is not the only factor....

So: write the routine that does the updating and test it not as a cronjob but by running it manually.

Experiment a little to make it more efficient - or come back here with your code if you feel you need help optimizing it.

[MattG]

What would be the best way to do that? Measuring the script execution time?

[Bjom]

that would be a way. you could have a look, if you can, at processor and memory usage.

How many queries does that involve? Could it be an option to place those in a stored procedure in the database and then simply call that sproc?

have a look at the sproc's executiong time and/or if it is only one query - download 'mysql administrator' run qurey/sproc there and have a look at query execution time (as well as your total script ecex time)...

[MattG]

Okay, I will run it locally and test it etc before uploading it to the server and creating the cron job.

Thanks for your help Bjom!

[MattG]

After some testing the script execution time ranges from 1.9 to 2.7 seconds to update each of the 3,406 records. I'm not sure if that's good or not!?

[Bjom]

Same as above: w/o knowing the specifications of your DB / application etc, no way to answer this. This is 1.9 to 2.7 TOTAL time, not per record, right?

Running a script once a day at midnight, that takes about 2 seconds to run....that sounds perfectly ok to me...even if it is not the most efficient script....I wouldnt bother to make it run faster, better invest your time somewhere else.

[MattG]

okay, thanks for all of your help! 🙂