Friday, April 25, 2014

MySQL Divisórias timestamp - datetime

Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-partitions-timestamp-datetime.html

Então, eu recentemente percebi que eu ainda não falei muito sobre partições MySQL.
Muitos bons posts sobre partições MySQL já existe e eu listei alguns abaixo.
Acontece que se deparar com a seguinte situação e espero que possa ser útil aos outros.

Embora o tipo de dados timestamp é usado frequentemente datetime funciona melhor com partições.
(Timestamp NOT CURRENT_TIMESTAMP PADRÃO NULL ON UPDATE CURRENT_TIMESTAMP)

Assim, o que se lhe segue um exemplo de como a forma de corrigir isso.

Para começar, temos esta tabela simples.

CREATE TABLE `t1` (
`t1_id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(25) DEFAULT NULL,
`field2` int(10) DEFAULT '0',
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`t1_id`),
KEY `tr` (`time_recorded`)
) ENGINE=InnoDB AUTO_INCREMENT=856964


Queremos dividir em campo time_recorded. Para torná-lo dirigir vamos quebrá-lo fora por mês.


ALTER TABLE t1
PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
);
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed


Então agora o que ....

Bem, eu sei que precisa da chave principal atualizado, se queremos que a partição.

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE;
Query OK, 0 rows affected (38.96 sec)
Records: 0 Duplicates: 0 Warnings: 0

Repare que eu tenho BLOQUEIO = NONE isso é porque eu estou usando o MySQL 5.6
A mudança de chave primária não me permite adicionar as partições, porém, eu ainda preciso ajustar o tipo de dados. Eu estou usando o = FECHADO compartilhou esta época. Por favor, reveja os hiperlinks acima para mais informações. Se acontecer de você escolher um tipo que não funciona, geralmente sugere uma solução viável.

ALTER TABLE t1 CHANGE time_recorded time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED;
Query OK, 854312 rows affected (41.89 sec)
Records: 854312 Duplicates: 0 Warnings: 0


Então, agora podemos acrescentar a nossa partição.

ALTER TABLE t1
-> PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
-> PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
-> PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
-> PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
-> PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
-> PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
-> PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
-> PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
-> PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
-> PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
-> PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
-> PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
-> PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
-> PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
-> );
Query OK, 854312 rows affected (50.74 sec)
Records: 854312 Duplicates: 0 Warnings: 0


Podemos selecionar, DELETE, UPDATE, INSERT e etc por partição. Mais sobre isso aqui: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html .

SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 661752 |
+--------------+
1 row in set (0.55 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 64952 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 71336 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 56272 |
+--------------+
1 row in set (0.05 sec)


Enquanto isso funciona e nós agora temos uma partição. Também temos agora de considerar a manutenção de partição. blog de ​​Glynn dá um exemplo muito bom de um Dropping automatizada e adicionando de partições. Concedido, se você não quer deixar cair a partição que você terá que ajustar o exemplo.
Eu ajustei-o para o meu exemplo de tabela. Por favor, revise o blog de ​​Glynn para mais detalhes.


DROP PROCEDURE IF EXISTS Rotate_t1_Partition;
DELIMITER ;;
CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME)
BEGIN
-- Setup
DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
DECLARE partitionToDrop VARCHAR(64);

-- Find and drop the first partition in the table.
SELECT partition_name
INTO partitionToDrop
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema='forums_mysql'
AND table_name='t1'
AND partition_ordinal_position=1;
SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ', partitionToDrop);
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Add a new partition using the input date for a value limit.
SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))');
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Cleanup
SET @stmt = keepStmt;
END;;
DELIMITER ;


Para que eu possa atualizar esta tabela agora com o seguinte facilmente.

CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec)


Você pode também, como o blog de ​​Glynn aponta, você pode usar isso com um NOW () + intervalo de 1 mês ou sempre que tempo que você decidir por suas partições. Grant você tem que ter em mente que ele irá falhar se você passar uma data que já é uma partição.

Para continuar com a automação você pode adicionar este a um evento.

CREATE EVENT Rotate_t1_Partition
-> ON SCHEDULE EVERY 1 MONTH
-> DISABLE ON SLAVE
-> COMMENT 'Remove oldest partition and add a new one '
-> DO
-> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH);
Query OK, 0 rows affected (0.04 sec)


Tenha em mente uma hora válida para o exemplo acima. Eu apenas usei isso como um exemplo.