Mysql trigger/events vs Cronjob

Question

Hello,

I have an auction website which let my users place an unlimited amount of autobiddings.

To monitor these autobiddings something has to check the database every second.

My question is if it is better to use mysql trigger events or to user a cronjob every minute that executes a 60 sec looping php script.

If i use the mysql trigger events there will be hundreds of events stacks on eachother, and fired on different times. Is this even possible?? ANd isn't the server load goning to be enourmous. I heard somewhere that the database will be locked while there is a schedueled event. I am using innoDB tables btw.

I hope some one can shed some light on this toppic.

Regards!

Answer 1

You'd best run a seperate script that runs eternally and whatches your database. That way you won't need cron. Nor a massive amount of triggers.

But you might want to reconsider your entire question. It's not necessary to actually update the bids every second. You only need to fill in the past x minutes/hours when someone actually points his browser to an auction or makes a manual bid. If it's all autobids you can calculate forwards an backwards with ease.

Answer 2

The database handles scheduled requests no different from other request. But as many scheduled requests contain database and table maintenance operations that do lock the database it is not uncommon for them to do so.

Having said that: as your systems has to react to actions by a user the technical preferable way of doing this is using triggers. In practice this might lead to performance problems when your site does have high loads - though using a scheduled event might cause the same trouble.

My advise is to put your logic in stored procedures and call these stored procedures from the triggers. When you find that the triggers don't keep up you can always remove the triggers and call the stored procedures from a cron job.

Answer 3

I would probably model the solution to your auto-bidding problem a bit differently:

How about an event-based approach? You store the auto-bid requests of your users and if someone actually bids on an object you process the previously queued auto-bids.

This has the following benefits:

• load on the database is distributed organically
• you only do lookups that are actually needed at the time.
• it is real-time and not tick-based
• it is easier to reason about the business/application logic because it is local instead of global