Может ли MySQL создавать новые разделы из планировщика событий

Ответ 1

Да, вы можете это сделать.

Обратите внимание, что планировщик по умолчанию не активен (см. Конфигурация планировщика событий), поэтому он не является параметром нулевого риска. Например, если ваша операционная группа переносит ваше приложение на новый сервер, но забывает включить планировщик, ваше приложение будет закрыто. Также необходимы специальные привилегии, которые, возможно, также потребуется настроить на новом сервере.

Мой совет: сначала создайте хранимую процедуру (см. пример кода ниже), которая обрабатывает периодическое обслуживание раздела: удаление старых разделов, если таблица становится слишком большой, и добавление достаточно новых разделов (например, 1 неделя), так что даже если обслуживание proc не запускается некоторое время, ваше приложение не умрет.

Затем избыточно планируйте вызовы для этого сохраненного proc. Используйте планировщик MySQL, используйте задание cron и используйте любой другой способ. Затем, если один планировщик не работает, другой может занять слабину. Если вы правильно спроектируете sproc, для выполнения операции no-op должно быть дешево, если ничего не нужно делать. Вы даже можете назвать это из своего приложения, например. как первое утверждение при создании долговременного отчета или как часть вашего ежедневного процесса ETL (если он у вас есть). Я хочу сказать, что ахиллея запланированных задач гарантирует, что планировщик действительно работает, поэтому подумайте о избыточности здесь.

Просто не забудьте запланировать все вызовы одновременно, чтобы они не наступали друг на друга!: -)

Здесь пример кода, для которого может выглядеть ваш процесс обслуживания - сначала он обрезает старые разделы, а затем добавляет новые. Я оставил проверку ошибок и предотвратил несколько одновременных исполнений в качестве exeise для читателя.

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, обслуживание разделов (обеспечение новых разделов создается заранее, обрезка старых разделов и т.д.), IMHO, критически важно автоматизировать. Я лично видел, как крупный корпоративный хранилище данных сокращался на один день, потому что изначально было разбито на год разделение, но никто не помнил, чтобы создавать дополнительные разделы сразу после следующего года. Поэтому очень хорошо, что вы думаете о автоматизации здесь - это хорошо предсказывает проект, над которым вы работаете.: -)

Ответ 2

Отличное решение от Джастина. Я взял его код в качестве отправной точки для моего текущего проекта и хотел бы упомянуть несколько вещей, которые возникли во время его реализации.

• Существующая структура разделов в таблице, в которой вы запускаете это, не должна включать раздел типа MAXVALUE - все разделы должны быть разделены литеральными датами. Это связано с тем, что SELECT MAX (PARTITION_DESCRIPTION) вернет "MAXVALUE", который не будет преобразован в дату на следующем шаге. Если вы получаете нечетное сообщение при вызове процедуры, говорящей что-то вроде: незаконное смешивание сортировок для '<', это может быть проблемой.

• При выборе имен разделов из таблицы INFORMATION_SCHEMA рекомендуется добавить: "AND TABLE_SCHEMA =" dbname ", потому что в то время как более чем один раздел может существовать с тем же именем для одной и той же таблицы (в разных базах данных), все они перечислены в таблице INFORMATION_SCHEMA вместе. Без спецификации TABLE_SCHEMA выберите ваш выбор, например. MAX (PARTITION_DESCRIPTION) даст вам максимальное имя раздела среди всех существующих разделов для таблиц этого имени в каждой базе данных.

• Где-то в пути у меня были проблемы с ALTER TABLE xxx ADD PARTITION, как и в решении Justin, я думаю, что тот же формат для имени раздела (yyyymmdd) использовался как разделитель разделов, который ожидаемый yyyy-mm-dd (v5.6.2).

• По умолчанию используется только добавление разделов в будущем по мере необходимости. Если вы хотите создавать разделы для прошлого, вам нужно будет сначала настроить раздел на более старую дату, чем самый старый раздел. Например. если вы сохраняете данные за последние 30 дней, сначала добавьте раздел, скажем, 35 ​​дней назад, а затем запустите процедуру. Конечно, это возможно только для того, чтобы сделать это на пустой таблице, но я думал, что стоит упомянуть.

• Чтобы создать желаемый промежуток прошлых/будущих разделов, как в 4., вам сначала потребуется выполнить процедуру дважды. Для примера в 4. выше, первый запуск будет создавать разделы для -35 дней до настоящего времени и необходимые будущие разделы. Затем второй прогон будет обрезать разделы между -35 и -30.

Вот что я сейчас использую. Я добавил некоторые параметры, чтобы сделать его более гибким с точки зрения вызывающего. Вы можете указать базу данных, таблицу, текущую дату и количество разделов для хранения как в прошлом, так и в будущем.

Я также изменил наименования разделов, так что раздел с именем p20110527 представляет день, начинающийся с 2011-5-27 00:00 вместо дня, заканчивающегося на тот момент.

По-прежнему отсутствует проверка ошибок или предотвращение одновременного выполнения: -)

DELIMITER $$

DROP PROCEDURE IF EXISTS UpdatePartitions $$

-- Procedure to delete old partitions and create new ones based on a given date.
-- partitions older than (today_date - days_past) will be dropped
-- enough new partitions will be made to cover until (today_date + days_future)
CREATE PROCEDURE UpdatePartitions (dbname TEXT, tblname TEXT, today_date DATE, days_past INT, days_future INT)
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=tblname
AND TABLE_SCHEMA=dbname;

-- SELECT partition_count;

-- first, deal with pruning old partitions
WHILE (partition_count > days_past + days_future)
DO
-- optionally, do something here to deal with the parition you're dropping, e.g.
-- copy the data into an archive table

 SELECT STR_TO_DATE(MIN(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
   INTO minpart
   FROM INFORMATION_SCHEMA.PARTITIONS
   WHERE TABLE_NAME=tblname
   AND TABLE_SCHEMA=dbname;

-- SELECT minpart;

 SET @sql := CONCAT('ALTER TABLE '
                    , tblname
                    , ' DROP PARTITION p'
                    , CAST(((minpart - INTERVAL 1 DAY)+0) as char(8))
                    , ';');

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

SELECT COUNT(*)
  INTO partition_count
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME=tblname
  AND TABLE_SCHEMA=dbname;

-- SELECT partition_count;

END WHILE;

SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
INTO maxpart_date
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=tblname
AND TABLE_SCHEMA=dbname;

-- select maxpart_date;
-- create enough partitions for at least the next days_future days
WHILE (maxpart_date < today_date + INTERVAL days_future DAY)
DO

-- select 'here1';
SET newpart_date := maxpart_date + INTERVAL 1 DAY;
SET @sql := CONCAT('ALTER TABLE '
                    , tblname
                    , ' ADD PARTITION (PARTITION p'
                    , CAST(((newpart_date - INTERVAL 1 DAY)+0) as char(8))
                    , ' VALUES LESS THAN ('''
                    , newpart_date
                    , '''));');

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

SELECT STR_TO_DATE(MAX(PARTITION_DESCRIPTION), '''%Y-%m-%d''')
  INTO maxpart_date
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME=tblname
  AND TABLE_SCHEMA=dbname;

SET maxpart_date := newpart_date;

END WHILE;

END $$

DELIMITER ;