14

In my web app I use two recurring events that "clean up" one of the tables in the database, both executed every 15 minutes or so.

My question is, could this lead to problems in performance in the future? Because I've read somewhere -I don't recall where exactly- that MySQL events are supposed to be scheduled to run once a month or so. Thing is, this same events keep the table in a pretty reduced size (as they delete records older than 15~ minutes), maybe this compensates the frequency of their execution, right?

Also, is it better to have one big MySQL event or many small ones if they are be called in the same frequency?

federico-t
  • 12,014
  • 19
  • 67
  • 111

1 Answers1

4

I don't think there's a performance indication in the monthly base just more of a suggestion of what to do with it. So i think you're ok with doing your cleanup using the events.

In the end the documentation suggets that the events are

Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

And the concept for those is that you can run a task every minute if you wish to do so.

On the second part of that question:

Serialize or spread it up. If you split them up into many events that will run at the same time you will create spikes of possibly very high cpu usage that might slow down the application while processing the events.

So either pack everything into one event so it runs in succession or spread the single events up so they execute on different times during the 15 minutes timeframe. Personally i think the first one is to be preferred, pack them up into a single event as then they are guaranteed to run in succession, even if a single one of them keeps running longer than usual.

The same goes for cronjobs. If you shedule 30 long-running exports at a single time your application is going to fail miserably during that timeslot (learned that the hard way).

bardiir
  • 14,556
  • 9
  • 41
  • 66
  • Thanks for your answer man! very detailed. I think I'll go with the second option you mentioned, having several events but executing them asynchronously to distribute the cpu usage – federico-t Apr 20 '12 at 09:52
  • 1
    @JohnDoe btw, if you're looking into creating a log or something you should take a look at mongo-db for that, there's a feature called capped collections that will limit the size of a table and clean it automatically: http://www.mongodb.org/display/DOCS/Capped+Collections – bardiir Apr 20 '12 at 09:55