Particiones MySQL timestamp - fecha y hora

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

Así que hace poco me di cuenta de que aún no he hablado mucho sobre las particiones de MySQL.
Muchos mensajes de blog en buenas particiones MySQL ya existe y yo hemos enumerado algunos abajo.

• http://glynndba.blogspot.com/2009/10/rolling-time-based-partitions.html
• http://www.mysqlperformanceblog.com/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/
• http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html
• http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Sucede que venir a través del siguiente situación y espero que sea útil a los demás.

Si bien a menudo se utiliza el tipo de datos de marca de tiempo de fecha y hora funciona mejor con particiones.
(Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

Así que el siguiente es un ejemplo de cómo la forma de solucionar este problema.

Para empezar tenemos esta sencilla tabla.


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 crear particiones en el campo time_recorded. Para que sea de particular vamos a romperlo a cabo al mes.


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

¿Y ahora qué ....

Bueno, yo sé que tenemos la clave primaria actualizada si queremos que la partición.


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

Tenga en cuenta que tengo LOCK = NONE esto se debe a que estoy usando MySQL 5.6

• http://dev.mysql.com/doc/ refman/5.6/en/innodb-online- ddl.html
• http://dev.mysql.com/doc/ refman/5.6/en/innodb-create- index-examples.html
• https://blogs.oracle.com/ mysqlinnodb / entrada / Online_ alter_table_in_mysql

El cambio de clave principal no permitirá que añada las particiones sin embargo, todavía tengo que ajustar el tipo de datos. Estoy utilizando los = BLOQUEADO COMPARTIDOS este momento. Por favor revise los hipervínculos arriba para obtener más información. Si quieres pasar a recoger un tipo que no funciona, por lo general sugiere una solución viable.


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

Así que ahora podemos añadir nuestra partición.


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 SELECT, DELETE, UPDATE, INSERT y etc por partición. Más sobre esto aquí: 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)

Aunque esto funciona, y nosotros ahora tenemos una partición. Ahora también tenemos que considerar el mantenimiento de la partición. blog de ​​Glynn da un muy buen ejemplo de un goteo automatizado y adición de particiones. Por supuesto, si usted no quiere eliminar la partición que tendrá que ajustar el ejemplo.
He ajustado para mi ejemplo de la tabla. Por favor revise el blog de ​​Glynn para más detalles.


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 ;

Así que puedo actualizar esta tabla ahora con la siguiente sencilla.


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

Puede también, como el blog de ​​Glynn señala, puede utilizar esto con un NOW () + intervalo de 1 mes o cada vez que marco de tiempo que usted decida para sus particiones. Subvención que tiene que tener en cuenta que se producirá un error si se pasa una fecha que ya es una partición.

Para continuar con la automatización se puede añadir esto a un 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)

Tenga en cuenta un tiempo válido para el ejemplo anterior. Acabo de utilizar esto como un ejemplo.

Planificar el tiempo de inactividad de MySQL y de otras variables de estado para la crítica

Original post: http://anothermysqldba.blogspot.com/2014/03/planning-mysql-downtime-and-other.html

Todos sabemos que, si bien MySQL es estable y puede funcionar durante mucho tiempo sin que se reinicie. De vez en cuando tenemos que reiniciar para que ajustar algunas variables o para actualizar sin embargo. Algunos podrían suponer que durante la noche es el mejor momento para hacer esto debido a que sus usuarios están dormidos. Sin embargo, a menudo la noche a la mañana también incluye copias de seguridad y otras secuencias de comandos crontab relacionados que podrían estar haciendo diferentes minería de datos y etc

Entonces, ¿cómo se puede obtener una guía sobre cuando se tiene la menor actividad en el servidor? Ahora cada aplicación y la base de datos son diferentes, por lo que deben tener en cuenta lo que la actividad relacionada se está ejecutando, en los distintos marcos temporales. Si usted comienza a registrar la variable de estado "Threads_connected" cada minuto de 5 a 10 minutos, de nuevo esto depende de la carga del sistema es una vez por hora podría estar bien, no tardará en reunir la cantidad de diferentes conexiones de MySQL a través de su tiempo conectado marco.

Esto se puede hacer una numerosa maneras. Ya se proporciona en muchas herramientas a nivel de empresa y se puede escribir scripts y etc, pero sólo para mantenerlo en MySQL. Voy a utilizar un evento y de rutina para este sencillo ejemplo.

Crear una tabla simple:

CREATE TABLE `threads_count` (
`threads_count_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`threads_count_id`)
) ENGINE=InnoDB;

Crear una rutina simple:

delimiter //
CREATE PROCEDURE threads_tally()
BEGIN
INSERT INTO threads_count SELECT NULL , VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('Threads_connected');
END//
delimiter ;

Crear un evento simple:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `threads_tally`
ON SCHEDULE EVERY 5 MINUTE
DISABLE ON SLAVE
COMMENT 'keeps a count of Threads_connected'
DO
CALL threads_tally();
DELIMITER ;

Ahora, pero la ejecución del ejemplo anterior, usted tendrá una tabla en la que se pueden seleccionar los valores de (probablemente poner en un gráfico o simplemente para su revisión) para que pueda ver cuando el menor número de conexiones existente en el servidor. Dependiendo de la aplicación y el tráfico que pueda mantener esta funcionando el tiempo suficiente para obtener una perspectiva válida y luego desactivar el evento.

select variable_value , time_recorded FROM threads_count;

Ahora usted puede tomar este curso un paso más allá, si usted no tiene las herramientas de la empresa, y mantener un registro de todas sus variables de estado para la revisión:

Tabla de valores:

CREATE TABLE `status_review` (
`status_review_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_name` varchar(255) DEFAULT NULL,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`status_review_id`)
) ENGINE=InnoDB;

Rutina para recopilar valores:

delimiter //
CREATE PROCEDURE status_reviews()
BEGIN
INSERT INTO status_review SELECT NULL , VARIABLE_NAME, VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS;
END//
delimiter ;

Evento para ejecutar la rutina:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `status_reviews`
ON SCHEDULE EVERY 1 HOUR
DISABLE ON SLAVE
COMMENT 'keeps a eye on status values'
DO
CALL status_reviews();
DELIMITER ;

Es posible que sólo quieren ciertos nombres de variables y valores para su revisión y se puede ajustar la consulta en la rutina en consecuencia. El tiempo de ejecución depende de sus necesidades, he usado una hora en este ejemplo.

Esto, por supuesto, va a generar una gran cantidad de información en el tiempo por lo que si podría ser necesaria una limpieza relevante de estas tablas con el tiempo. Todo depende de la frecuencia con que se conecte y revise la información proporcionada.

Mientras que usted verá un aumento constante de muchos de los valores debido a su crecimiento de los datos y el sistema de usar que también será capaz de identificar los picos de datos en los datos mientras que y correlacionar que a la aplicación y uso del sistema.

La empresa MySQL por ejemplo ya se graficará todos este tipo de información para usted, pero esta es una manera también puede hacerlo usted mismo con un poco de tiempo y la revisión de los datos.