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++) { Listbooks = 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”