Fragmentación en MySQL

Al utilizar comandos de fragmentado a nivel de tablas, mysqld utiliza una clave de partición y un algoritmo de particionado para determinar la división de los datos entre los fragmentos. Los algoritmos de fragmentación que tenemos son:

RANGE: Si la clave de fragmentación está dentro de un rango de valores.
LIST: El fragmento es seleccionado de acuerdo a una lista de valores enteros.
HASH: El fragmento se elige de acuerdo a una función de hash.
KEY: Un algoritmo interno es utilizado por mysqld para elegir como serán distribuidos los datos entre los fragmentos.
*Particionado compuesto: Las particiones de RANGE y LIST pueden ser subfragmentadas usando el fragmentado HASH, y KEY.

Fragmentado RANGE (Por rango de valores)

Suponiendo que tenemos la siguiente tabla definida:

CREATE TABLE empleados (
Id_empleado INT NOT NULL,
nombre VARCHAR(30) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’,
fecha_termino DATE DEFAULT NULL,
salario DECIMAL (8,2) NOT NULL,
codigo_de_trabajo INT NOT NULL,
id_almacenamiento INT NOT NULL
);

Suponiendo que queremos almacenar los datos de 300 empleados en tres tablas de a 100 cada una, procedemos con la siguiente instrucción:

ALTER TABLE empleados
PARTITION BY RANGE (id_almacenamiento) (
PARTITION p0 VALUES LESS THAN (101),
PARTITION p1 VALUES LESS THAN (201),
PARTITION p2 VALUES LESS THAN (301),
PARTITION pfinal VALUES LESS THAN MAXVALUE
);

Esta instrucción utiliza el atributo id_almacenamiento como clave de fragmentado (partition key), y las particiones son p0, p1, p2, y pfinal, en este caso las tres particiones corresponden a los registros en el rango desde 0 hasta 300, pero la partición final es en el caso de que quisiéramos insertar un registro con un valor mayor a 300, lo cual nos generaría un error como el siguiente:

ERROR 1526 (HY000): Table has no partition for value 301.

Si desde la definición de la tabla queremos especificar un esquema de fragmentado, usaríamos la siguiente instrucción:

CREATE TABLE empleados (
Id_empleado INT NOT NULL,
nombre VARCHAR(30) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’,
fecha_termino DATE DEFAULT NULL,
salario DECIMAL (8,2) NOT NULL,
codigo_de_trabajo INT NOT NULL,
id_almacenamiento INT NOT NULL
)
PARTITION BY RANGE (id_almacenamiento) (
PARTITION p0 VALUES LESS THAN (101),
PARTITION p1 VALUES LESS THAN (201),
PARTITION p2 VALUES LESS THAN (301),
PARTITION pfinal VALUES LESS THAN MAXVALUE
);

Fragmentado LIST (Por lista definida)

Suponiendo que ahora tenemos la misma tabla, pero lo que queremos es distribuir a los empleados de acuerdo a una lista de valores definida, los valores se distribuiran de acuerdo a los valores definidos en cada lista de cada partición.

CREATE TABLE empleados (
Id_empleado INT NOT NULL,
nombre VARCHAR(30) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’,
fecha_termino DATE DEFAULT NULL,
salario DECIMAL (8,2) NOT NULL,
codigo_de_trabajo INT NOT NULL,
id_almacenamiento INT NOT NULL
)
PARTITION BY LIST (id_almacenamiento) (
PARTITION Sureste VALUES IN (1,2,3,4,5,6,7,8,9,10,15,17,18,20,21,24),
PARTITION AtlanticoMedio VALUES IN (11,12,13,14,16,19,22,23,25,26,27,28),
PARTITION Noreste VALUES IN (29,30,33,38,40,41,50,56,64,65,75),
PARTITION EsteMedio VALUES IN (32,34,35,42,43,49,51,61,62,63,71),
PARTITION Noroeste VALUES IN (46,53,58,67,68,69,72,74),
PARTITION Canada VALUES IN (31,47,52,59,73),
PARTITION Inlaterra VALUES IN (39,55)
);

Fragmentado HASH (Por dispersión)

En este caso, la distribución de los datos entre un numero x de fragmentos es con el uso del operador residuo (%)

CREATE TABLE empleados (
Id_empleado INT NOT NULL,
nombre VARCHAR(30) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’,
fecha_termino DATE DEFAULT NULL,
salario DECIMAL (8,2) NOT NULL,
codigo_de_trabajo INT NOT NULL,
id_almacenamiento INT NOT NULL
)
PARTITION BY HASH (id_almacenamiento)
PARTITIONS 16;

Fragmentado KEY (Fragmentado por clave)

El funcionamiento de este fragmentado es similar al de HASH, la gran diferencia es el algoritmo, que es similar al de la funcion PASSWORD(), y tiene el siguiente cambio respecto a la consulta anterior.

CREATE TABLE empleados (
Id_empleado INT NOT NULL,
nombre VARCHAR(30) NOT NULL,
apellidos VARCHAR(30) NOT NULL,
fecha_inicio DATE NOT NULL DEFAULT ’2000-01-01’,
fecha_termino DATE DEFAULT NULL,
salario DECIMAL (8,2) NOT NULL,
codigo_de_trabajo INT NOT NULL,
id_almacenamiento INT NOT NULL
)
PARTITION BY HASH (id_almacenamiento)
PARTITIONS 16;

Los casos anteriores de fragmentación son para la Fragmentación Horizontal,

Ahora la fragmentación vertical o también conocida como División de Filas ( row splitting ), ya que los datos de un registro, se almacenan en dos o mas tablas se realiza no por el RDBMS propiamente sino por el administrador o programador.

La implementación de este tipo de fragmentación es guardando el mismo identificador del registro en las dos tablas y los atributos en su tabla correspondiente, o partiendo de un conjunto de datos estaticos, donde una tabla ya definida y con sus registros se divide mediante algun procedimiento almacenados.
La mejor practica, en mi opinión personal es usar la primera forma, que depende más de un programador.

Librito sagrado:
MySQL Administrator’s Bible
Sheeri Cabral, Keith Murphy
Wiley Publishing, Inc.

Creacion de una Replicacion en MySQL

Creacion de Replicacion

Para la creación de una replicación enMySQL, no requerimos mas que de tres sencillos pasos:

1.-Creacion de las cuentas de replicacion en cada servidor.
2.- Configurar el maestro y el esclavo.
3.- Hacer la conexión del esclavo y que replique del maestro.

Creacion de las cuentas de replicacion en cada servidor.

Primero, para este paso, hay que mencionar que el usuario de la replicación, tiene algunos privilegios especiales ya que el procedimiento se realiza mendiante una conexion TCP/IP, y hace un uso del archivo de log del maestro, por lo que se requiere crear uno con la siguiente instrucción:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO usuario_replicacion@'192.168.0.%' IDENTIFIED BY 'contrasenia';

donde:
usuario_replicacion es el nombre del usuario que tendra nuestro esclavo de la replicación.
contrasenia la palabra de acceso para nuestra cuenta

Esta cuenta se debe crear tanto en el esclavo como en el maestro. Y la cuenta esta restringida a la red local, debido a que supone un riesgo a la seguridad si sale a una red publica.

Configurando el Maestro y el Esclavo

Ahora, lo que necesitamos hacer es habilitar el Registro Binario ( log ), en el servidor maestro, esto lo haremos editando el archivo my.ini en windows o my.cnf en unix, agregando las siguientes lineas despues de mysqld en dicho archivo.

log_bin = mysql-bin
server_id = 10

una vez hecho esto necesitamos reiniciar el RDBMS y comprobar que el registro se ha habilitado mediante el comando:

mysql> SHOW MASTER STATUS;
+-------------------+----------+---------------+--------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+---------------+--------------------+
| mysql-bin.000001 | 98 | | |
+-------------------+----------+---------------+--------------------+
1 row in set (0.00 sec)

hecho esto, editamos el archivo my.ini o my.cnf en el esclavo con las siguientes instrucciones:

log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

e igualmente se reinicia el servidor esclavo.

Iniciar el esclavo

El siguiente paso es decirle a esclavo como conectarse al maestro y comenzar la replicacion de sus registros binarios (logs). A partir de este momento ya no es necesario editar el archivo my.ini y usar el comando CHANGE MASTER TO esta sentencia edita dicho archivo. Y la sentencia para iniciar lareplicacion es:

mysql> CHANGE MASTER TO MASTER_HOST='server1',
-> MASTER_USER='usuario_replicacion',
-> MASTER_PASSWORD='contrasenia',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;

los datos igual como antes, son los correspondientes al usaurio de la replicacion en el maestro, y su contraseña, y el archivo de replicacion debe ser el del log que habilitamos o que vimos en el SHOW MASTER STATUS, y el MASTER LOG es para que despues de haber realizado la replicacion podamos visualizar el estado del esclavo.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************

Slave_IO_State:
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
...omitted...
Seconds_Behind_Master: NULL

y finalmente el comando para iniciar la replicacion es (chaca-cha-chan):

mysql> START SLAVE;

y ¡Ya!

Mi gran lectura recomendada para este tema es:

High Performance MySQL, Second Edition
by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny,
Arjen Lentz, and Derek J. Balling
2008 O’Reilly Media, Inc.