6

I've got a use case for creating temporary users on a MySQL database, and then dropping them after 24 hours. I'll be doing this enough that I'd like to automate the process and package it with the user creation script, so that I don't have to keep track of the process.

I've looked around the internet for docs, questions, gossip about the performance implications of MySQL event scheduling, but I haven't found anything discouraging. I've seen another question about the "cost" MySQL scheduled events, but the discussion mostly covers a comparison between scheduling DB tasks using cron vs. using MySQL event scheduling.

I'm running on Amazon RDS, so cron won't work for me. MySQL event scheduling looks like the right solution. I'm scheduling a pretty lightweight event--dropping one user at a time, 24 hrs after creation--but I'll have to keep MySQL's event scheduler enabled 24/7. Is there any performance cost to this? If not, why isn't the event scheduler enabled by default?

Andrew Breunig
  • 135
  • 3
  • 12
  • Are the temporary users MySQL database level users or application level users that happened to be stored within MySQL? – Shadow Jun 15 '16 at 12:55
  • The event scheduler is not enabled by default to lower surface area and thus performance degradation. An event running once every 12 hours is not rocking the boat (at least from a sleeping thread stand-point). To turn on or off the scheduler or a given event by name, see my answer [here](http://stackoverflow.com/a/37824952). – Drew Jun 15 '16 at 16:29

1 Answers1

6

The cost of the scheduler is irrelevant, compared to the cost of the SQL the scheduler runs. The book High Performance MySQL discusses this:

Events are initiated by a separate event scheduler thread, because they have nothing to do with connections. They accept no inputs and return no values -- there's no connection for them to get inputs from or return values to.... Similar considerations to those that apply to stored procedures apply to events. First, you are giving the server additional work to do. The event overhead itself is minimal, but the SQL it calls can have a potentially serious impact on performance.

I suspect that events are not enabled by default because they have an impact on statement-based replication, which High Performance MySQL also discusses:

[E]vents can cause the same types of problems with statement-based replication that other stored code can cause.

Ultimately, like any database problem, you have to implement the solution in your schema and measure the effects because no two instances are alike and no solution is universal.

bishop
  • 34,858
  • 10
  • 96
  • 130