Posts tagged with “particionado”
Introducción a MySQL Cluster
MySQL Cluster es una base de datos que como su nombre indica funciona en un Cluster de servidores. Mucha gente confunde terminos y define un conjunto de servidores con replicación como un MySQL Cluster, pero hay que tener en cuenta que son dos conceptos totalmente distintos. MySQL Cluster nos ofrece:
- Alta disponibilidad
- Escalabilidad
- Failover automático
- Redundancia
- Alto throughput
La versión actual es la 7.1 y puede descargarse de http://www.mysql.com/products/database/cluster/
Componentes
Un Cluster MySQL está compuesto por los siguientes componentes:
Manager (ndb_mgmd): es un servicio encargado de poner en marcha el cluster, conectar nuevos servidores y ejecutar distintos comandos de administración mediante el CLI ndb_mgm. Una vez que hemos levantado el cluster no es necesario ni un requisito indispensable que esté levantado.
Data Nodes (ndbd): son nodos encargados del almacenamiento de los datos. Se recomiendan al menos dos para disponer de redundancia y alta disponibilidad. Estas serán las máquinas más potentes del cluster, almacenarán los índices en memoria y los datos en memoria o disco. Todos los Data Nodes deben tener el mismo hardware para evitar crear cuellos de botella.
API nodes (mysqld): aunque el más usado sea mysqld, un API node puede ser cualquier aplicación que haciendo uso de la API acceda al cluster. El típico, también conocido como SQL Node, es el demonio mysqld típico (compilado con soporte nbdcluster). De esta forma podremos escribir o leer datos de nuestra BBDD como hemos hecho hasta ahora, mediante comandos SQL.
Se recomienda que cada componente esté instalado en una máquina física distinta.
Funcionamiento interno
Internamente, el funcionamiento del cluster se basa en dos conceptos básicos. Replicación interna síncrona y auto particionado de datos. La primera nos ofrece la redundancia y el segundo nos da la escalabilidad. Importante diferenciarlo de la replicación típica de MySQL (asíncrona). En este caso, hasta que los datos no han sido replicados en los nodos seleccionados no se devuelve el control al usuario, obteniendo de esta forma la consistencia que no tenemos en la replicación asíncrona.
El particionado (PARTITION BY KEY) es también totalmente automático. El cluster se encarga de dividir las tablas en distintas particiones y dividir los datos entre los distintos Data Nodes. Aunque es posible que definamos nuestro propio particionado, no se recomienda. Añades complejidad y posiblemente el rendimiento no sea el esperado.
Replicas
A la hora de configurar nuestro cluster, una de los valores más importantes a tener en cuenta es decidir el número de replicas que tendremos de nuestros datos. No podemos decidir cualquier número, si no que tendremos que seguir unas sencillas reglas. Pongamos por ejemplo que tenemos 4 Data Nodes. En este caso podremos tener 1, 2 y 4 replicas. Esto es, el número de nodos debe poder ser divisible por el numero de replicas. Aún así, no se debería tener una única replica, ya que eso no nos da ningún tipo de alta disponibilidad ya que al caerse un solo nodo perderíamos el acceso a los datos.
Node Groups
MySQL Cluster agrupa automáticamente los Data Nodes en grupos. Esto no está bajo nuestro control ni podemos decidir que nodo está en que grupo, será trabajo del cluster hacer estas agrupaciones. Siguiendo el ejemplo anterior, si tenemos 4 Data Nodes y 2 réplicas, MySQL Cluster nos generará dos Node Groups (4/2=2). Además hay que tener en cuenta que el número de particiones que se harán de nuestros datos siempre será igual al número de Data Nodes.
Por lo tanto, imaginemos que tenemos el N1 y N2 en el grupo 1 (G1) y N3 y N4 en el grupo 2 (G2). A la hora de particionar y repartir los datos es necesario pensar en la alta disponibilidad, por lo que el particionado se hará de la siguiente forma:
G1 N1 = P1 y P2' N2 = P2 y P1'G2 N3 = P3 y P4' N4 = P4 y P3'
Siendo PX el número de la partición y PX' una copia de la partición de Backup.
Sabiendo esto, podemos imaginar cuando dispondremos de alta disponibilidad. Mientras al menos uno de los nodos de un grupo esté levantado, el cluster estará online. Por ejemplo se podrían caer N1 y N4 o N3 y N2 y todo seguiría funcionando. Pero una caida de N1 y N2 dejaría el cluster completamente caido.
A mayor número de replicas menos posibilidades de fallo, más escalabe y más throughput :)
Próximamente extenderé el tema de MySQL Cluster con más entradas según vaya profundizando en el estudio de la certificación. La intención será termianr teniendo un mini manual para andar por casa que nos permita dar los primeros pasos.
Particionado Lógico (Parte III)
Gracias a information_schema es posible saber el tamaño que ocupan las tablas de nuestras bases de datos.
Toda la información que nos ofrece information_schema en relación con las tablas es la siguiente:
mysql> desc tables; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) | YES | | NULL | | | DATA_LENGTH | bigint(21) | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | YES | | NULL | | | DATA_FREE | bigint(21) | YES | | NULL | | | AUTO_INCREMENT | bigint(21) | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
Por ejemplo, si queremos calcular el tamaño de los índices:
mysql> select sum(index_length) from information_schema.tables; +-------------------+ | sum(index_length) | +-------------------+ | 11746971648 | +-------------------+ 1 row in set (3.32 sec)
El valor es en bytes, por lo que el tamaño de los índices es de aproximadamente 11 GB. No está nada mal.
Otra forma de sacar un buen resumen con más datos es la siguiente sentencia SQL que encontramos en mysqlperformanceblog.com:
mysql> SELECT -> count(*) TABLES -> ,concat(round(sum(table_rows)/1000000,2),'K') rows -> ,concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA -> ,concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx -> ,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size -> ,round(sum(index_length)/sum(data_length),2) idxfrac -> FROM -> information_schema.TABLES; +--------+---------+--------+--------+------------+---------+ | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+---------+--------+--------+------------+---------+ | 929 | 301.52K | 15.73G | 10.94G | 26.67G | 0.70 | +--------+---------+--------+--------+------------+---------+ 1 row in set (3.75 sec)
Tenemos el número de tablas, filas, tamaño de los datos, índices, tamaño total y la proporción índices/datos.
Si lo que deseamos es saber los datos de una tabla en particular, solo es necesario hacer una busqueda más concreta. De esta forma sabremos, por ejemplo, la tabla candidata para el particionado:
mysql> SELECT -> count(*) TABLES -> ,concat(round(sum(table_rows)/1000000,2),'K') rows -> ,concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA -> ,concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx -> ,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size -> ,round(sum(index_length)/sum(data_length),2) idxfrac -> FROM -> information_schema.TABLES -> WHERE table_name LIKE '%log%'; +--------+---------+-------+-------+------------+---------+ | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+---------+-------+-------+------------+---------+ | 8 | 192.81K | 7.05G | 8.94G | 15.99G | 1.27 | +--------+---------+-------+-------+------------+---------+ 1 row in set (1.76 sec)
En nuestro caso, prácticamente todos los datos pertenecen a la tabla log. Sus indices son mayores que la memoria RAM del equipo (4 GB) por lo que tendríamos una tabla perfecta para particionar. Cuando INDICES > RAM, particionar se hace más rápido que usar índices :)
Particionado Lógico (Parte II)
Para las prácticas haremos uso de una BBDD de prueba que podemos descargar aquí:
Sample database with test suite
Lo bueno de esta BBDD es que ya viene repletita de datos, por ejemplo la tabla salaries tiene en torno a dos millones de registros. La particionaremos de forma que logremos mejorar el rendimiento. Hay que tener en cuenta que las pruebas se van a hacer sobre un Netbook, por lo que los resultados no son 100% fiables. Nunca pongáis un netbook como servidor de bases de datos en producción u os quedareis ciegos.
El particionado se puede hacer por rangos, listas, hashes y keys:
RANGO
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21), PARTITION p3 VALUES LESS THAN MAXVALUE );
Es el particionado más sencillo. En el ejemplo se divide usando el campo store_id, de forma que los valores menores de 6 van a la partición p0, los menores de 11 a la p1, los menores de 16 a la p2, etc. Es importante tener en cuenta la inclusión del MAXVALUE. Ahí es donde irán a parar aquellos registros que no encajen dentro del resto de tablas. Si no pusiéramos un MAXVALUE, MySQL no sabría donde meterlo y daría error.
LISTAS
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16) );
En este caso, el particionado se realiza en base a una lista de posibles valores. Al contrario que con RANGE, aquí no tenemos MAXVALUE. Por lo que es de suponer, según el diseño de la web, que nunca tendremos valores distintos a los indicados.
HASHES
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY HASH( YEAR(hired) ) PARTITIONS 4;
Como podéis comprobar, estamos haciendo uso de una columna que no es INT, pero mediante la función YEAR la convertiremos y hacemos uso de ella en la partición. Este truco se suele utilizar muy a menudo para evitar la limitación de particiones en INT. A parte de todo eso, el uso de HASH nos permite dividir los datos de forma equitativa entre todas las particiones, cosa que con otros tipos de particiones podría no pasar. De esta forma, si estamos trabajando una tabla enorme y la queremos dividir en 10 particiones, estas tendrán un número de valores muy similar.
KEY
CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;
Con Key no es necesario indicar la columna que deseamos para particionar, en ese caso hará uso de la clave primaria. KEY es muy parecida a HASH, solo que en lugar de indicarle nosotros el HASH mediante una expresión, lo hará el propio MySQL usando MD5.
Dicho esto, comenzamos a particionar nuestra BBDD. La tabla en cuestión será, como ya indique, la de salarios.
mysql [localhost] {msandbox} (employees) > select count() from salaries; +----------+ | count() | +----------+ | 2844047 | +----------+ 1 row in set (1.66 sec)
Casi 3 millones de registros, no está nada mal :) Vamos a hacer una búsqueda para saber cuantos corresponden al año 2000.
mysql [localhost] {msandbox} (employees) > select count() from salaries where from_date between '2000-01-01' and '2000-12-31'; +----------+ | count() | +----------+ | 255785 | +----------+ 1 row in set (2.39 sec)
Fijaros en el tiempo que ha tardado MySQL en realizar la consulta, 2.39 segundos. Bastante tiempo, si en lugar de una sola querie fuesen 100 concurrentes estaríamos ante un verdadero problema de rendimiento.
mysql [localhost] {msandbox} (employees) > explain select count(*) from salaries where from_date between '2000-01-01' and '2000-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries type: index possible_keys: NULL key: emp_no key_len: 4 ref: NULL rows: 2844513 Extra: Using where; Using index 1 row in set (0.00 sec)
La querie se tiene que recorrer los 3 millones de registros usando un índice (lo cual no está mal) y aún así el rendimiento es pobre. Entonces, pidamos ayuda a nuestras amigas las particiones. Vamos a particionar por año :)
ALTER TABLE salaries PARTITION by range (year(from_date)) ( partition p001 VALUES LESS THAN (1986) , partition p002 VALUES LESS THAN (1987) , partition p003 VALUES LESS THAN (1988) , partition p004 VALUES LESS THAN (1989) , partition p005 VALUES LESS THAN (1990) , partition p006 VALUES LESS THAN (1991) , partition p007 VALUES LESS THAN (1992) , partition p008 VALUES LESS THAN (1993) , partition p009 VALUES LESS THAN (1994) , partition p010 VALUES LESS THAN (1995) , partition p011 VALUES LESS THAN (1996) , partition p012 VALUES LESS THAN (1997) , partition p013 VALUES LESS THAN (1998) , partition p014 VALUES LESS THAN (1999) , partition p015 VALUES LESS THAN (2000) , partition p016 VALUES LESS THAN (2001) , partition p017 VALUES LESS THAN (2002) , partition p018 VALUES LESS THAN (2003) );
Si ahora hacemos la misma select de antes:
mysql [localhost] {msandbox} (employees) > select count() from salaries where from_date between '2000-01-01' and '2000-12-31'; +----------+ | count() | +----------+ | 255785 | +----------+ 1 row in set (0.22 sec)
La búsqueda de los salarios del año 2000. Antes 2,39 segundos, ahora 0,22. La mejora es impresionante.
Un explain partitions nos indica que partición ha usado para la select:
mysql [localhost] {msandbox} (employees) > explain partitions select count(*) from salaries where from_date between '2000-01-01' and '2000-12-31'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: p016 type: index possible_keys: NULL key: emp_no key_len: 4 ref: NULL rows: 2830488 Extra: Using where; Using index 1 row in set (0.00 sec)