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)

Particionado Lógico (Parte I)

Desde la versión 5.1 existe la posibilidad de particionar nuestras tablas de forma horizontal (en líneas), algo que nos puede ayudar en casos puntuales a mejorar el rendimiento de nuestra base de datos. Resumiendo, este sistema nos permite dividir lógicamente una tabla muy grande en otras más pequeñas, dentro de un rango de valores que nosotros indiquemos, de forma que la consulta de datos sea más rápida. Su uso es muy sencillo pero... ¿cuando debemos utilizarlo?

• Cuando la tabla sea tan grande que los índices no entren en RAM.
• Cuando tengamos una tabla realmente grande (no hablo de megas).
• Cuando almacenamos datos históricos.
• Cuando queremos rotar datos.
• Cuando los datos no paran de crecer y crecer...

Hay que tener en cuenta que este particionado es totalmente transparente para el usuario (y por lógica también para nuestra aplicación) por lo que en el caso de decidirnos por esta solución el cambio será poco dramático. Solamente tendremos que tener en cuenta estos detalles:

• La columna que utilicemos para definir el rango de las particiones debe ser un INT, no se acepta cualquier otro valor.
• Si tenemos una clave única o una primary key, esta debe usarse para particionar.
• Como máximo se permiten 1024 particiones.
• No se permiten claves externas.
• No se permiten busquedas FULL TEXT.

Si tenemos una tabla con millones de registros y hacemos una select, MySQL se deberá recorrer toda la tabla (en caso de no usar índices, si estos ocupan más que la RAM) o se tendrá que recorrer todos los índices. Esto, contra más grande es la tabla, mas ineficiente es:

Gracias al particionado es posible hacer una busqueda en una fracción mucho más pequeña de nuestra tabla. Si la dividimos de forma que cada partición incluya 100 filas (particionando por ID), MySQL sabe que el dato se tiene que encontrar en la segunda partición, por lo que se evita tener que buscar en los restantes X millones de registros.

La mejora es clara, pero no siempre particionar nos va a dar mayor rendimiento. Si la tabla no es lo suficientemente grande incluso podemos degradar el rendimiento, ya que añadimos overhead a una base de datos que no necesita grandes recursos para funcionar. Más adelante veremos como implementar el sistema y también como benchmarkear la solución para conocer si nos puede salvar la vida o no.

Ya soy administrador certificado de Mysql5

• MySQL Architecture
• Starting, Stopping, and Configuring MySQL
• Client Programs for DBA Work
• Character Set Support
• Locking
• Storage Engines
• Data (Table) Maintenance
• The INFORMATION_SCHEMA Database
• Data Backup and Recovery Methods
• Stored Routines
• User Management
• Securing the Server
• Upgrade-Related Security Issues
• Optimizing Queries
• Optimizing Schemas
• Optimizing the Server
• Interpreting Error Messages
• Optimizing the Environment
• Scaling MySQL