MySQL是否可以从事件计划程序创建新分区 [英] Can MySQL create new partitions from the event scheduler

问题描述

我有一张看起来像这样的桌子:

I'm having a table looking something like this:

CREATE TABLE `Calls` (
  `calendar_id` int(11) NOT NULL,
  `db_date` timestamp NOT NULL,
  `cgn` varchar(32) DEFAULT NULL,
  `cpn` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`calendar_id`),
  KEY `db_date_idx` (`db_date`)
) 
 PARTITION BY RANGE (calendar_id)(
   PARTITION p20091024 VALUES LESS THAN (20091024) ,
   PARTITION p20091025 VALUES LESS THAN (20091025));

我能以某种方式使用mysql调度程序自动添加一个新分区(提前2天)-我正在寻找一个示例，该示例将每天添加一个新分区-它会运行类似的内容

Can I somehow use the mysql scheduler to automatically add a new partition(2 days in advance) - I'm looking for an example that would, every day add a new partition - it'd run something like

alter table Calls add partition (partition p20091026 values less than(20091026));

运行计划任务时构造p20091026/20091026的位置，该值从现在开始+ 2天得出. (还是我最好通过cron编写脚本?)

Where p20091026/20091026 is constructed when the scheduled task run, deriving the value from now + 2 day. (Or am I better of scripting this through cron ?)

推荐答案

是的，您可以执行此操作.

Yes, you can do this.

请注意，默认情况下，调度程序不处于活动状态(请参见事件计划程序配置)，因此它不是零风险的选项.例如，如果您的运营团队将您的应用程序迁移到新服务器上，但是忘记启用调度程序，则您的应用程序将无法正常运行.还需要特殊的特权，这可能又需要在新服务器上设置.

Note that the scheduler isn't active by default (see Event Scheduler Configuration), so it's not a zero-risk option. For example, if your operations team migrates your app to a new server, but forgets to enable the scheduler, your app will get hosed. There's also special privileges needed, which again may need to be set up on a new server.

我的建议:首先，创建一个存储过程(请参见下面的代码示例)来处理定期的分区维护:如果表太大，则删除旧分区，并添加足够的新分区(例如1周)，以便即使维护proc暂时没有运行，您的应用不会消失.

My advice: first, create a stored procedure (see code sample below) which handles periodic partition maintenance: dropping old partitions if the table gets too big, and adding enough new partitions (e.g. 1 week) so that even if the maintenance proc isn't run for a while, your app won't die.

然后冗余地调度对该存储过程的调用.使用MySQL调度程序，使用cron作业，以及使用您喜欢的任何其他方式.然后，如果一个调度程序不起作用，则另一个调度程序可以解决该问题.如果正确设计了存储过程，则无需执行任何操作就可以便宜地执行无操作.您甚至可能想要从您的应用中调用它，例如作为生成长期报告时的第一条语句，或者作为日常ETL流程的一部分(如果有的话).我的观点是，已调度任务的致命弱点是确保调度程序实际上正在工作-因此，请在此处考虑冗余.

Then redundantly schedule calls to that stored proc. Use the MySQL scheduler, use a cron job, and use any other way you like. Then if one scheduler isn't working, the other can pick up the slack. If you design the sproc correctly, it should be cheap to execute a no-op if it doesn't need to do anything. You might even want to call it from your app, e.g. as the first statement when generating a long-running report, or as part of your daily ETL process (if you have one). My point is that the achilles heel of scheduled tasks is ensuring that the scheduler is actually working-- so think about redundancy here.

请确保不要同时安排所有通话，以免彼此通话！ :-)

Just make sure not to schedule all the calls at the same time so they won't step on each other! :-)

这是维护过程的代码示例-首先修剪旧分区，然后添加新分区.我放弃了错误检查并防止多次同时执行，以此来吸引读者.

Here's a code sample for what your maintenance proc could look like-- first it prunes old partitions, then adds new ones. I left error checking and preventing multiple simultaneous executions as an exerise for the reader.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`UpdatePartitions` $$
CREATE PROCEDURE `test`.`UpdatePartitions` ()
BEGIN

  DECLARE maxpart_date date;
  DECLARE partition_count int;
  DECLARE minpart date;
  DECLARE droppart_sql date;
  DECLARE newpart_date date;
  DECLARE newpart_sql varchar(500);

  SELECT COUNT(*)
    INTO partition_count
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  -- first, deal with pruning old partitions
  -- TODO: set your desired # of partitions below, or make it parameterizable
  WHILE (partition_count > 1000)
  DO

    -- optionally, do something here to deal with the parition you're dropping, e.g.
    -- copy the data into an archive table

     SELECT MIN(PARTITION_DESCRIPTION)
       INTO minpart
       FROM INFORMATION_SCHEMA.PARTITIONS
       WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

     SET @sql := CONCAT('ALTER TABLE Calls DROP PARTITION p'
                        , CAST((minpart+0) as char(8))
                        , ';');

     PREPARE stmt FROM @sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

    SELECT COUNT(*)
      INTO partition_count
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';


  END WHILE;

  SELECT MAX(PARTITION_DESCRIPTION)
    INTO maxpart_date
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  -- create enough partitions for at least the next week
  WHILE (maxpart_date < CURDATE() + INTERVAL 7 DAY)
  DO

    SET newpart_date := maxpart_date + INTERVAL 1 DAY;
    SET @sql := CONCAT('ALTER TABLE Calls ADD PARTITION (PARTITION p'
                        , CAST((newpart_date+0) as char(8))
                        , ' values less than('
                        , CAST((newpart_date+0) as char(8))
                        , '));');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT MAX(PARTITION_DESCRIPTION)
      INTO maxpart_date
      FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME='Calls' AND TABLE_SCHEMA='test';

  END WHILE;

END $$

DELIMITER ;

顺便说一句，恕我直言，分区维护(确保预先创建新分区，修剪旧分区等)对于自动化至关重要.我个人已经看到大型企业数据仓库停工了一天，因为最初要分割一年的分区，但是没人记得第二年要创建更多的分区.因此，您在这里考虑自动化非常好-这对您正在从事的项目而言是个好兆头. :-)

BTW, partition maintenance (ensuring new partitions are created in advance, pruning old partitions, etc.) is, IMHO, critically important to automate. I've personally seen a large enterprise data warehouse go down for a day because a year's worth of partitions was cretaed initially but no one remembered to create more partitions once the next year came around. So it's very good you're thinking about automation here-- it bodes well for the project you're working on. :-)

这篇关于MySQL是否可以从事件计划程序创建新分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！