Nota: Traducido de la versión original en Inglés.
ColumnStore de MariaDB es un motor que almacena datos de manera columnar. Aunque se destaca en arquitecturas distribuidas con una cantidad masiva de datos, puedes ver mejoras significativas en el rendimiento en arquitecturas con una única instancia. Este artículo muestra cómo:
- Instalar ColumnStore usando Docker en tu máquina de desarrollo.
- Crear una aplicación web simple en Java para generar datos de prueba realistas.
- Ejecutar consultas SQL de prueba para comparar tiempos de ejecución de los motores ColumnStore vs InnoDB.
Para seguir este artículo, necesitas Java, un IDE de Java y Docker instalado en tu computadora.
¿Qué es ColumnStore?#
ColumnStore es un motor de almacenamiento “enchufable” para MariaDB y MySQL. Puedes instalarlo sobre una instancia de base de datos existente. Es posible tener tablas en la misma base de datos utilizando múltiples motores de almacenamiento. Por ejemplo, puedes crear una tabla llamada libro para OLTP usando el motor InnoDB de propósito general, y una tabla llamada book_analytics para OLAP usando el motor ColumnStore orientado a columnas. Ambas tablas pueden existir en el mismo esquema y puedes ejecutar consultas que mezclen las dos. Revisa los siguientes recursos en línea para aprender más sobre OLTP/OLAP y los diferentes motores de almacenamiento disponibles en MariaDB:
- The place between transactions and analytics – and what it means for you
- Choosing the Right Storage Engine
Instala MariaDB y ColumnStore usando Docker#
Asegúrate de tener Docker instalado y ejecutándose en tu máquina y usa los siguientes comandos para descargar una imagen y crear un contenedor con CentOS, MariaDB y ColumnStore preconfigurados:
docker pull mariadb/columnstore
docker run -d -p 3306:3306 --name mariadb_columnstore mariadb/columnstore
Configura la base de datos#
Si deseas configurar la instancia, puedes conectarte al contenedor usando:
docker exec -it mariadb_columnstore bash
Por ejemplo, puedes editar el archivo /etc/my.cnf para aumentar el tamaño del buffer pool (este es un paso opcional):
[mysqld]
innodb_buffer_pool_size=1G
Recuerda reiniciar el contenedor cuando hagas este tipo de cambios:
docker restart mariadb_columnstore
Conéctate a la instancia de MariaDB#
Usa cualquier cliente SQL que soporte MariaDB para conectarte a la instancia. Por ejemplo, puedes usar la vista de base de datos de IntelliJ IDEA o el comando de línea de comandos, mariadb. Si tienes MariaDB en tu máquina host, ya tienes el comando:
mariadb --protocol tcp -u user -p
Dado que un contenedor Docker no es realmente una máquina virtual, te conectas a la base de datos como si estuviera ejecutándose de forma nativa en tu máquina, es decir, el host es localhost.
Configura el usuario de prueba y las tablas#
Crea un nuevo usuario de base de datos:
docker exec mariadb_columnstore mariadb -e "GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';"
Crea un nuevo esquema en la base de datos y dos tablas, una usando el motor InnoDB y otra usando ColumnStore:
CREATE DATABASE book_demo;
USE DATABASE book_demo;
CREATE TABLE book
(
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) DEFAULT NULL,
author varchar(255) DEFAULT NULL,
publish_date date DEFAULT NULL,
pages int(8) DEFAULT NULL,
image_data longtext DEFAULT NULL,
PRIMARY KEY (id)
) engine = InnoDB;
CREATE TABLE book_analytics
(
id int(11) NOT NULL,
title varchar(255) DEFAULT NULL,
author varchar(255) DEFAULT NULL,
publish_date date DEFAULT NULL,
pages int(8) DEFAULT NULL
) engine = ColumnStore;
Genera datos de prueba usando Java#
Crea un nuevo proyecto usando el Spring Initializr y agrega las dependencias de Spring Data JPA, MariaDB Driver, Lombok y Vaadin. Además, agrega la siguiente dependencia al archivo pom.xml:
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>exampledata</artifactId>
<version>4.0.0</version>
</dependency>
Configura la conexión a la base de datos en el archivo application.properties:
spring.datasource.url=jdbc:mariadb://localhost:3306/book_demo
spring.datasource.username=user
spring.datasource.password=password
Crea una nueva clase Entity de JPA para encapsular los datos de prueba y persistirlos en filas de la tabla:
package com.example;
import lombok.Data;
import lombok.EqualsAndHashCode;
import javax.persistence.*;
import java.time.LocalDate;
@Data
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@Entity
@Table(name = "book")
public class Book {
@EqualsAndHashCode.Include
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String title;
private String author;
private LocalDate publishDate;
private Integer pages;
@Lob
private String imageData;
}
Crea un nuevo repositorio:
package com.example;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface BookRepository extends JpaRepository<Book, Integer> {
}
Crea una clase de servicio con lógica para generar aleatoriamente datos de prueba realistas en lote:
package com.example;
import com.vaadin.exampledata.ChanceIntegerType;
import com.vaadin.exampledata.DataType;
import com.vaadin.exampledata.ExampleDataGenerator;
import lombok.RequiredArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Random;
@Service
@RequiredArgsConstructor
@Log4j2
public class GeneratorService {
private final BookRepository repository;
public void generate(int batchSize, int batches) {
var generator = new ExampleDataGenerator<>(Book.class, LocalDateTime.now());
generator.setData(Book::setTitle, DataType.BOOK_TITLE);
generator.setData(Book::setAuthor, DataType.FULL_NAME);
generator.setData(Book::setPublishDate, DataType.DATE_LAST_10_YEARS);
generator.setData(Book::setPages, new ChanceIntegerType("integer", "{min: 20, max: 1000}"));
generator.setData(Book::setImageData, DataType.BOOK_IMAGE_URL);
for (int batchNumber = 1; batchNumber <= batches; batchNumber++) {
List<Book> books = generator.create(batchSize, new Random().nextInt());
repository.saveAllAndFlush(books);
log.info("Batch " + batchNumber + " completado.");
}
}
}
Crea una interfaz de usuario web:
package com.example;
import com.vaadin.flow.component.button.Button;
import com.vaadin.flow.component.html.H1;
import com.vaadin.flow.component.notification.Notification;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import com.vaadin.flow.component.textfield.IntegerField;
import com.vaadin.flow.router.PageTitle;
import com.vaadin.flow.router.Route;
import lombok.extern.log4j.Log4j2;
@Route("")
@PageTitle("Generador de datos")
@Log4j2
public class GeneratorView extends VerticalLayout {
public GeneratorView(GeneratorService service) {
var batchSize = new IntegerField("Tamaño de lote");
var batches = new IntegerField("Lotes");
Button start = new Button("Iniciar");
start.addClickListener(event -> {
service.generate(batchSize.getValue(), batches.getValue());
Notification.show("Datos generados.");
});
add(new H1("Generador de datos"), batchSize, batches, start);
}
}
Ejecuta la aplicación generadora de datos#
El proyecto incluye una clase Application con un método de punto de entrada estándar de Java. Ejecuta esta aplicación como lo harías con cualquier otra aplicación Java usando tu IDE o la línea de comandos con Maven (asegúrate de usar el nombre del archivo JAR que generó tu proyecto):
mvn package
java -jar target/generador-datos-prueba-0.0.1-SNAPSHOT.jar
El primer build y ejecución de la aplicación toman tiempo, pero las siguientes iteraciones son más rápidas. Puedes invocar la aplicación en el navegador en http://localhost:8080:
Generé 100 lotes de 10000 filas cada uno para completar un millón de filas en la base de datos. Esto lleva tiempo. Revisa el log si quieres ver el progreso.
Desafío: Intenta usar la clase ProgressBar, la anotación @Push y el método UI.access(Command) para mostrar el progreso en el navegador.
Ejecutando un simple proceso ETL para análisis#
Para poblar la tabla book_analytics, podemos ejecutar una simple sentencia SQL INSERT que sirve como un proceso de Extract, Transform, Load (ETL). En concreto, la columna image_data almacena la imagen potencialmente utilizada en una aplicación web que muestra la portada de un libro. No necesitamos estos datos para OLAP, razón por la cual no incluimos la columna image_data en la tabla book_analytics. Puebla los datos con la siguiente sentencia SQL:
INSERT INTO book_analytics(id, author, pages, publish_date, title)
SELECT id, author, pages, publish_date, title FROM book;
El proceso debería tomar sólo unos segundos para un millón de filas.
Compara el rendimiento de consultas analíticas#
A continuación, se presentan algunos ejemplos de consultas que puedes usar para comparar el rendimiento del motor ColumnStore versus InnoDB. Utilicé un enfoque empírico y ejecuté cada consulta varias veces (5 a 10 veces), tomando la ejecución más rápida para cada una. Estos son los resultados:
SELECT COUNT(id) FROM book; -- 251 ms
SELECT COUNT(id) FROM book_analytics; -- 111 ms
SELECT publish_date, SUM(pages) FROM book GROUP BY publish_date; -- 767 ms
SELECT publish_date, SUM(pages) FROM book_analytics GROUP BY publish_date; -- 206 ms
SELECT author, COUNT(id) FROM book GROUP BY author HAVING COUNT(id) > 5; -- 20 s 218 ms
SELECT author, COUNT(id) FROM book_analytics GROUP BY author HAVING COUNT(id) > 5; -- 1 s 93 ms
La mayor diferencia está en la última consulta. Aproximadamente 20 segundos versus 2 segundos. Todas las consultas muestran una ventaja no trivial a favor de ColumnStore.
Ten en cuenta que para otros tipos de operaciones de base de datos, ColumnStore podría ser más lento que InnoDB. Por ejemplo, consultas que leen varias columnas sin funciones de agregación involucradas. Siempre toma decisiones informadas y experimenta con consultas antes de decidirte por InnoDB, ColumnStore u otros motores para tus tablas de base de datos.
Nota: Soy consciente de que este volumen de datos está lejos de un escenario realista para un benchmark. Sin embargo, mi objetivo no es compartir un benchmark, sino un experimento que hace un punto sobre cómo ColumnStore ayuda.