Рубрики
Без рубрики

Тестирование производительности хранилища столбцов MariaDB

Хранилище столбцов Mariadb – это механизм, который хранит данные в виде столбцов. Хотя он светит изнутри… Помеченный явой, ваадин.

MariaDB ‘ы Хранилище столбцов – это механизм, который хранит данные в виде столбцов. Хотя это выгодно в распределенных архитектурах, где у вас есть огромный объем данных, вы можете увидеть нетривиальные улучшения производительности в архитектурах с одним экземпляром. В этой статье показано, как:

  • установите хранилище столбцов с помощью Docker на вашей машине разработки,
  • создайте простое веб-приложение Java для создания реалистичных тестовых данных,
  • запустите тестовые SQL-запросы, чтобы сравнить время выполнения хранилища столбцов с движками InnoDB.

Чтобы следовать этой статье, вам необходимо, чтобы на вашем компьютере были установлены Java, Java IDE и Docker .

Что такое ColumnStore?

Хранилище столбцов – это подключаемый движок для MariaDB и MySQL. Вы можете установить его поверх существующего экземпляра базы данных. Можно иметь таблицы в одной базе данных, используя несколько механизмов хранения. Например, вы можете создать таблицу с именем book для OLTP, используя механизм InnoDB общего назначения, и таблицу с именем book_analytics для OLTP, используя механизм ColumnStore, ориентированный на столбцы. Обе таблицы могут находиться в одной и той же схеме базы данных, и вы можете выполнять запросы, которые смешивают их. Ознакомьтесь с этими онлайн-ресурсами, чтобы узнать больше об OLTP/OLAP и различных механизмах хранения, доступных в MariaDB:

Установка MariaDB и хранилища столбцов с помощью Docker

Убедитесь, что на вашем компьютере установлен и запущен Docker, и используйте следующие команды для загрузки образа и создания контейнера с предварительно настроенными CentOS , MariaDB и ColumnStore.:

docker pull mariadb/columnstore

docker run -d -p 3306:3306 --name mariadb_columnstore mariadb/columnstore

Настройка базы данных

Если вы хотите настроить экземпляр, вы можете подключиться к контейнеру с помощью:

docker exec -it mariadb_columnstore bash

Например, вы можете отредактировать файл /etc/my.cnf , чтобы увеличить размер пула буферов (это необязательный шаг).:

[mysqld]
innodb_buffer_pool_size=1G

Не забудьте перезапустить контейнер, когда вы вносите такого рода изменения:

docker restart mariadb_columnstore

Подключение к экземпляру MariaDB

Используйте любой SQL-клиент, поддерживающий MariaDB, для подключения к экземпляру. Например, вы можете использовать представление базы данных IntelliJ IDEA или средство командной строки, mariadb . Если у вас есть MariaDB на вашем хост-компьютере, у вас уже есть инструмент:

mariadb --protocol tcp -u user -p

Поскольку контейнер Docker на самом деле не является виртуальной машиной, вы подключаетесь к базе данных так, как если бы она была запущена изначально на вашем компьютере, то есть хостом является localhost.

Настройка тестового пользователя и таблиц

Создайте нового пользователя базы данных:

docker exec mariadb_columnstore mariadb -e "GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';"

Создайте новую схему базы данных и две таблицы, одну с использованием механизма InnoDB, а другую с использованием хранилища столбцов:

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;

Генерация тестовых данных с использованием Java

Создайте новый проект с помощью Spring Initializr и добавьте Spring Data JPA , Драйвер MariaDB , Ломбок и Ваадин зависимости. Кроме того, добавьте следующую зависимость в pom.xml файл:


    com.vaadin
    exampledata
    4.0.0

Настройте подключение к базе данных в файле application.properties :

spring.datasource.url=jdbc:mariadb://localhost:3306/book_demo
spring.datasource.username=user
spring.datasource.password=password

Создайте новый класс сущностей JPA для инкапсуляции тестовых данных и сохранения их в строке таблицы:

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;

}

Создайте новый репозиторий:

package com.example;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface BookRepository extends JpaRepository {
}

Создайте класс сервиса с логикой для генерации реалистичных случайных тестовых данных в пакетах:

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 books = generator.create(batchSize, new Random().nextInt());
            repository.saveAllAndFlush(books);
            log.info("Batch " + batchNumber + " completed.");
        }
    }

}

Создайте веб-интерфейс:

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("Data generator")
@Log4j2
public class GeneratorView extends VerticalLayout {

    public GeneratorView(GeneratorService service) {
        var batchSize = new IntegerField("Batch size");
        var batches = new IntegerField("Batches");

        Button start = new Button("Start");
        start.addClickListener(event -> {
            service.generate(batchSize.getValue(), batches.getValue());
            Notification.show("Data generated.");
        });

        add(new H1("Data generator"), batchSize, batches, start);
    }

}

Запуск веб-приложения генератора

Проект включает в себя класс Application со стандартным методом точки входа Java. Запустите это приложение так же, как и любое другое приложение Java, используя IDE или командную строку с Maven (обязательно используйте имя файла JAR, созданного вашим проектом).:

mvn package

java -jar target/test-data-generator-0.0.1-SNAPSHOT.jar

Первая сборка и запуск приложения требуют времени, но дальнейшие сборки и запуски будут быстрее. Вы можете вызвать приложение в браузере по адресу http://localhost:8080 :

Я сгенерировал 100 пакетов по 10000 строк в каждом для миллиона строк в базе данных. Это займет некоторое время. Проверьте журнал, если хотите увидеть прогресс.

Задача: Попробуйте использовать класс Панель прогресса , аннотацию @Push и UI.access(команда) для отображения прогресса в браузере.

Запуск простого процесса ETL для аналитики

Чтобы заполнить таблицу book_analytics , мы можем выполнить простую инструкцию SQL INSERT, которая служит в качестве процесса извлечения, преобразования, загрузки (ETL). В частности, в столбце image_data хранится изображение, потенциально используемое в веб-приложении, которое показывает обложку книги. Нам не нужны эти данные для OLAP, поэтому мы не включили столбец image_data в таблицу book_analytics . Заполните данные с помощью следующего оператора SQL:

INSERT INTO book_analytics(id, author, pages, publish_date, title)
    SELECT id, author, pages, publish_date, title FROM book;

Этот процесс должен занять всего несколько секунд для миллиона строк.

Сравнение производительности аналитических запросов

Ниже приведены некоторые примеры запросов, которые вы можете использовать для сравнения производительности механизма хранения столбцов с InnoDB. Я использовал эмпирический подход и выполнял каждый запрос несколько раз (от 5 до 10 раз), выполняя самый быстрый запуск для каждого. Вот результаты:

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

Самая большая разница заключается в последнем запросе. примерно 20 секунд против 2 секунд. Все запросы показывают нетривиальное преимущество в пользу ColumnStore.

Примите во внимание, что для других видов операций с базой данных хранилище столбцов может быть медленнее, чем InnoDB. Например, запросы, которые считывают несколько столбцов без участия агрегатных функций. Всегда принимайте обоснованные решения и экспериментируйте с запросами, прежде чем выбирать InnoDB, хранилище столбцов или другие механизмы для таблиц вашей базы данных.

Оригинал: “https://dev.to/alejandro_du/testing-mariadb-columnstore-performance-1c97”