Strategy to Update Daily Stats

Thanks to help received on this board over the past few months, I have been able to improve performance on my site. This, however, is causing my nightly cron job which updates daily statistics to fail due to the amount of SELECTs.

Currently I have 3 cron jobs, every 20 minutes, every hour, and once a day. The ones that run every 20 minutes and hourly update rows based on users who have been active the past few days. The daily job updates stats for all users. What is happening is that it is taking so long for the daily job to run that it doesn't finish by the time the 20 minute job begins running, and everything grinds to a halt because the 20 minute job never finishes and the next 20 minute job begins.

In order to update the tables, I am doing an UPDATE LOW_PRIORITY, or INSERT DELAYED. Since the SELECTs keep coming in, the jobs never finish.

Does anyone have suggestions on what can be done? Should I take out the LOW PRIORITY part? Should I stop apache once per day and run the daily job so it finishes quicker and display "This site is undergoing daily maintanence. Please check back in a few minutes."?

I am using MYISAM tables in MySQL 4.0x. Thanks.

 

 

 

 

Top