MySQL 事件调度的性能影响 [英] Performance Implications of MySQL Event Scheduling

问题描述

我有一个用例，用于在 MySQL 数据库上创建临时用户，然后在 24 小时后删除它们.我会做的足够多，我想自动化这个过程并将它与用户创建脚本一起打包，这样我就不必跟踪这个过程.

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.

我在互联网上四处寻找有关 MySQL 事件调度的性能影响的文档、问题和八卦，但我没有发现任何令人沮丧的事情.我已经看到另一个关于成本"MySQL 调度事件的问题，但讨论主要涵盖了使用 cron 和使用 MySQL 事件调度调度 DB 任务之间的比较.

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.

我在 Amazon RDS 上运行，所以 cron 对我不起作用.MySQL 事件调度看起来是正确的解决方案.我正在安排一个非常轻量级的事件——在创建后 24 小时一次删除一个用户——但我必须保持 MySQL 的事件调度程序 24/7 启用.这有任何性能成本吗?如果没有，为什么默认情况下不启用事件调度程序?

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?

推荐答案

与调度程序运行的 SQL 的成本相比，调度程序的成本无关紧要.这本书

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

事件由单独的事件调度程序线程发起，因为它们与连接无关.它们不接受任何输入也不返回任何值——它们之间没有任何联系来获取输入或返回值......适用于存储过程的类似考虑也适用于事件.首先，您要给服务器做额外的工作.事件开销本身很小，但它调用的 SQL 可能会对性能产生潜在的严重影响.

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.

我怀疑默认情况下未启用事件，因为它们具有 对基于语句的复制的影响，高性能 MySQL 也讨论了:

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]事件可能会导致与其他存储代码可能导致的基于语句的复制相同类型的问题.

[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.

这篇关于MySQL 事件调度的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！