Archivos por Etiqueta: mysql

MySQL: Small Guide for Simple Partitioning

Una mini guia de particionamiento sencillo en MySQL 5.5 basado en fecha:

Nota(1): Hay que incluir en la PK el campo que quieras usar en el particionado, así te ahorraras el error:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE IF NOT EXISTS T1 (
  ID INT NOT NULL AUTO_INCREMENT,
  PART DATE NOT NULL,
  NUM INT NOT NULL,
  PRIMARY KEY (ID,PART))
ENGINE = InnoDB 
  PARTITION BY RANGE COLUMNS(PART) ( 
    PARTITION p20140417 VALUES LESS THAN ('2014-04-18'),  
    PARTITION p20140418 VALUES LESS THAN ('2014-04-19'),  
    PARTITION p20140419 VALUES LESS THAN ('2014-04-20')
);

Nota(2): Ojo con las columnas DATE/DATETIME, se almacena la fecha tal cual (a diferencia de un TIMESTAMP que se almacena en formato UTC).

Si la tabla ya existe sin particionamiento y quiere activarse:

ALTER TABLE T1 
  PARTITION BY RANGE COLUMNS(PART) ( 
    PARTITION p20140417 VALUES LESS THAN ('2014-04-18'),  
    PARTITION p20140418 VALUES LESS THAN ('2014-04-19'),  
    PARTITION p20140419 VALUES LESS THAN ('2014-04-20')
);

Agregar nuevas particiones a un particionamiento existente:

ALTER TABLE T1 
  ADD PARTITION (
    PARTITION p20140420 VALUES LESS THAN ('2014-04-21'),
    PARTITION p20140421 VALUES LESS THAN ('2014-04-22')
);

Para hacer el DROP de particiones y los datos contenidos en ellas:

ALTER TABLE T1 
  DROP PARTITION p20140417, p20140418;

Para quitar el particionamiento (sin perder datos):

ALTER TABLE T1 REMOVE PARTITIONING;

Para ver los particionamientos que hay definidos:

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,
       PARTITION_METHOD,PARTITION_DESCRIPTION,TABLE_ROWS 
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE PARTITION_NAME IS NOT NULL;

O un resumen de uso:

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,TABLE_ROWS 
  FROM INFORMATION_SCHEMA.PARTITIONS 
 WHERE PARTITION_NAME IS NOT NULL;
+--------------+------------+----------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+--------------+------------+----------------+------------+
| S1           | T1         | p20140417      |          3 |
| S1           | T1         | p20140418      |          3 |
| S1           | T1         | p20140419      |          2 |
+--------------+------------+----------------+------------+
3 rows in set (0.01 sec)

Para poder sacar los planes de acceso del particionamiento:

EXPLAIN PARTITIONS 
 SELECT * FROM T1
  WHERE ID=1 
    AND PART = UTC_DATE(); 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | T1    | p20140417  | const | PRIMARY       | PRIMARY | 11      | const,const |    1 |       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)
EXPLAIN PARTITIONS 
 SELECT * FROM T1
  WHERE ID=1;
+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions                    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | T1    | p20140417,p20140418,p20140419 | ref  | PRIMARY       | PRIMARY | 8       | const |    2 |       |
+----+-------------+-------+-------------------------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Y la select de prueba que usa la columna de particionamiento (PART):

-- asumiento que hoy es 2014-04-17 y tenemos 365 particionamientos (1 por dia)
SELECT * FROM T1
 WHERE ID=3 
   AND PART BETWEEN UTC_DATE()-1 AND UTC_DATE()+1;
-- en este caso la busqueda estaria entre: p20140416, p20140417 y p20140418

Nota(3): El numero maximo de particiones por tabla es de 1024.

Referencias:
Restrictions and Limitations on Partitioning
Partitioning Types
RANGE Partitioning
RANGE COLUMNS partitioning
ALTER TABLE
ALTER TABLE Partition Operations
Data Type Storage Requirements
The DATE, DATETIME, and TIMESTAMP Types
Date and Time Functions

Data Types: MySQL / Oracle

La tipica chuleta que siempre hay que tener a mano:

MySQL			Oracle 
-----------------	----------------
BIGINT			NUMBER(19, 0)
BIT			RAW
BLOB			BLOB, RAW
CHAR			CHAR
DATE			DATE
DATETIME		DATE
DECIMAL			FLOAT (24)
DOUBLE			FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM			VARCHAR2
FLOAT			FLOAT
INT			NUMBER(10, 0)
INTEGER			NUMBER(10, 0)
LONGBLOB		BLOB, RAW
LONGTEXT		CLOB, RAW
MEDIUMBLOB		BLOB, RAW
MEDIUMINT		NUMBER(7, 0)
MEDIUMTEXT		CLOB, RAW
NUMERIC			NUMBER
REAL			FLOAT (24)
SET			VARCHAR2
SMALLINT		NUMBER(5, 0)
TEXT			VARCHAR2, CLOB
TIME			DATE
TIMESTAMP		DATE
TINYBLOB		RAW
TINYINT			NUMBER(3, 0)
TINYTEXT		VARCHAR2
VARCHAR			VARCHAR2, CLOB
YEAR			NUMBER

Referencias:
Oracle / MySQL compared
Oracle Data Types
MySQL Data Types

Construyendo un Arbol B+ (B+Tree)

B+ tree data structure diagram. Note that ther...

B+ tree data structure diagram.

Introducción e historia:

Los Arboles B+ son una evolucion de los Arboles B, inventados en 1972 (Rudolf Bayer y Edward McCreight), son un tipo de estructura para mantener datos ordenados de modo eficiente O(log-n), esto significa que a medida que el volumen de datos crece, la eficiencia decrece aunque no de modo lineal. Por comparacion, en una tabla hash -o un array- el tiempo es O(1) -constante- independientemente del numero de registros, aunque su utilidad no es la misma que la de un arbol.

La principal diferencia con un Arbol B standard es que almacenan los valores en los nodos hoja y los nodos intermedios solo almacenan referencias a otros nodos (internos u hojas). Otra caracteristica es que se podría iterar sobre todos los nodos hojas de modo secuencial siguiendo los apuntadores, sin tener que recorrer los nodos internos desde la raiz.

Motivación:

Las implementaciones de Java (TreeMap, HashMap, etc) funcionan muy bien en memoria y valen para casi cualquier cosa (son muy genericas), pero el coste del todo terreno suele ser memoria y CPU; y cuando necesitas trabajar con muchos datos y una gran velocidad… la memoria se acaba y los recursos, por definicion, no son ilimitados.

Mi primer intento consistió en buscar alguna implementacion existente para almacenar en disco algo sencillo, claves/valores (long/long) con una velocidad relativamente buena, encontré una librería con buena pinta (JDBM3 -y sus versiones anteriores-), tambien probé varias bbdd SQL { H2-database, HSQLDB y MySQL }.

Por desgracia todas morian en el intento (y no fue por falta de configuración/tuning):

  • MySQL/InnoDB a penas llegaba a 3,5k inserts/segundo.
  • El rendimiento del H2 (embed) de 85k/s al superar el millon de registros caia en picado a 7k/s y el I/O wait moría casi en los 3millones.
  • JDBM3 iba degradandose poco a poco, 250k a 27k/s, 500k a 20k/s, 1millon a 12k/s.
  • El DB_File de Perl (BerkeleyDB), corria a unos 20k/s de modo bastante constante hasta los 3millones.

Resultó algo bastante frustrante al inicio. Era hora de buscar una solucion y mi objetivo eran los 5 millones a 100k/s.

Leer más de esta entrada

A %d blogueros les gusta esto: