Высокоскоростная загрузка данных в хранилище данных

Проблема

Вы загружаете большое количество данных. Производительность заблокирована на этапе INSERT.

Это будет рассмотрено в контексте хранилища данных, с огромной таблицей `Fact` и таблицами сводок (агрегации).

Обзор решения

• Используйте отдельную таблицу подготовки.
• Вставки происходят в `Staging`.
• Нормализация и сводка читают данные из `Staging`, а не из `Fact`.
• После нормализации данные копируются из `Staging` в `Fact`.

`Staging` — это одна (или несколько) таблиц, в которых данные хранятся только до тех пор, пока они не переданы в таблицы нормализации, сводки и таблицу `Fact`.

Поскольку речь идёт, вероятно, о таблице с миллиардом строк, уменьшение ширины таблицы `Fact` за счет нормализации (как указано здесь). Изменение типа INT на MEDIUMINT позволит сэкономить ГБ. Замена строки идентификатором (нормализация) позволит сэкономить много ГБ. Это улучшает пространство на диске и кэшируемость, следовательно, скорость.

Скорость загрузки

Некоторые вариации:

• Большой сброс данных один раз в час по сравнению с непрерывным потоком записей.
• Поток ввода может быть однопоточным или многопоточным.
• Возможно использование стороннего программного обеспечения, которое ограничивает ваши возможности.

Обычно наибольшую скорость загрузки можно достичь, «подготовив» INSERTы каким-либо образом, а затем обрабатывая подготовленные записи пакетно. В этом блоге обсуждаются различные методы подготовки и пакетной обработки.

Нормализация

Предположим, ваш входной файл имеет столбец VARCHAR `host_name`, но вам нужно преобразовать его в более компактный столбец MEDIUMINT `host_id` в таблице `Fact`. Таблица «Нормализация», как я её называю, выглядит примерно так

CREATE TABLE Hosts (
    host_id  MEDIUMINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    host_name VARCHAR(99) NOT NULL,
    PRIMARY KEY (host_id),      -- for mapping one direction
    INDEX(host_name, host_id)   -- for mapping the other direction
) ENGINE=InnoDB;                -- InnoDB works best for Many:Many mapping table

Вот как вы можете использовать `Staging` в качестве эффективного способа перехода от имени к идентификатору.

`Staging` имеет два поля (для примера нормализации):

    host_name VARCHAR(99) NOT NULL,     -- Comes from the insertion proces
    host_id  MEDIUMINT UNSIGNED  NULL,  -- NULL to start with; see code below

Между тем, таблица `Fact` имеет:

    host_id  MEDIUMINT UNSIGNED NOT NULL,

SQL #1 (из 2):

    # This should not be in the main transaction, and it should be done with autocommit = ON
    # In fact, it could lead to strange errors if this were part
    #    of the main transaction and it ROLLBACKed.
    INSERT IGNORE INTO Hosts (host_name)
        SELECT DISTINCT s.host_name
            FROM Staging AS s
            LEFT JOIN Hosts AS n  ON n.host_name = s.host_name
            WHERE n.host_id IS NULL;

Изолировав это как отдельную транзакцию, мы можем её быстро завершить, тем самым минимизируя блокировки. С помощью IGNORE мы не заботимся о том, вставляют ли другие потоки «одновременно» те же имена хостов.

Существует тонкая причина для LEFT JOIN. Если бы это было INSERT IGNORE..SELECT DISTINCT, то INSERT предварительно выделил бы идентификаторы auto_increment для столько строк, сколько предоставляет SELECT. Это очень вероятно «израсходует» много идентификаторов, что может привести к ненужному переполнению MEDIUMINT. LEFT JOIN позволяет найти только новые необходимые идентификаторы (за исключением редкой возможности «одновременной» вставки другим потоком). Более подробное обоснование: Таблица сопоставления

SQL #2:

    # Also not in the main transaction, and it should be with autocommit = ON
    # This multi-table UPDATE sets the ids in Staging:
    UPDATE   Hosts AS n
        JOIN Staging AS s  ON s.host_name = n host_name
        SET s.host_id = n.host_id

Это получает идентификаторы, независимо от того, существуют ли они уже, установлены другим потоком или установлены SQL #1.

Если размер `Staging` изменяется в зависимости от загруженности в течение дня, у этой пары SQL-заявлений есть ещё одна приятная особенность. Чем больше строк в `Staging`, тем эффективнее работает SQL, что помогает компенсировать «загруженные» моменты.

В сопроводительном статье о хранилищах данных SQL #2 включена в INSERT INTO Fact. Но вам может понадобиться `host_id` для дальнейших шагов нормализации и/или сводки, поэтому явное обновление, показанное здесь, часто лучше.

Переключение таблиц подготовки

Простой способ подготовки — это загрузка в течение некоторого времени, а затем пакетная обработка данных в `Staging`. Но это приводит к накоплению новых записей, ожидающих подготовки. Чтобы избежать этой проблемы, используйте 2 процесса:

• один процесс (или набор процессов) для вставки в `Staging`
• один процесс (или набор процессов) для пакетной обработки (нормализации, сводки) и переноса в таблицу `Fact`. Отдельный процесс выполняет обработку, а затем меняет таблицы:

    DROP   TABLE StageProcess;
    CREATE TABLE StageProcess LIKE Staging;
    RENAME TABLE Staging TO tmp, StageProcess TO Staging, tmp TO StageProcess;

Это может показаться не самым коротким способом, но обладает следующими особенностями:

• Операции DROP + CREATE могут быть быстрее, чем TRUNCATE, что является желаемым результатом.
• Операция RENAME является атомарной, поэтому процесс вставки никогда не обнаруживает, что `Staging` отсутствует.

Вариантом двухтабличного переключения является использование отдельной таблицы `Staging` для каждого процесса вставки. Процесс обработки будет переключаться между каждой `Staging` поочерёдно.

Вариантом этого варианта является наличие отдельного процесса обработки для каждого процесса вставки.

Выбор зависит от того, что быстрее (вставка или обработка). Существуют компромиссы; одиночный поток обработки избегает некоторых блокировок, но лишен некоторой параллельности.

Выбор движка

Таблица `Fact` — InnoDB, если только не по причине того, что при сбое системы не потребуется REPAIR TABLE. (Восстановление таблицы MyISAM с миллиардом строк может занимать часы или дни.)

Таблицы нормализации — InnoDB, в основном потому, что это можно сделать эффективно с помощью 2 индексов, тогда как MyISAM потребовал бы 4 для достижения той же эффективности.

`Staging` — здесь много вариантов.

• Если у вас несколько вставщиков и одна таблица `Staging`, InnoDB желательна из-за блокировки на уровне строк, а не таблицы.
• MEMORY может быть самым быстрым, и она избегает операций ввода-вывода. Это хорошо для одной таблицы подготовки.
• Для нескольких вставщиков желательно иметь отдельную таблицу `Staging` для каждого вставщика.
• Для нескольких вставщиков в одну таблицу `Staging` InnoDB может быть быстрее. (MEMORY использует блокировку на уровне таблицы.)
• При использовании одной не-InnoDB таблицы `Staging` на вставщик, использование явной LOCK TABLE позволяет избежать многократных неявных блокировок при каждой вставке.
• Но если вы используете LOCK TABLE, а поток обработки отделён, то периодически необходимо использовать UNLOCK, чтобы позволить RENAME захватить таблицу.
• «Пакетные INSERTы» (100–1000 строк на SQL-запрос) устраняют многие проблемы в перечисленных пунктах.

Сбились с толку? Запутались? Есть достаточно вариаций в приложениях, которые делают непрактичным предсказание наилучшего решения. Или, просто достаточно хорошего. Скорость вашей загрузки может быть достаточно низкой, чтобы вы не столкнулись с «жесткими» ограничениями, которые я помогаю вам избежать.

Стоит ли создавать «временную таблицу CREATE TEMPORARY TABLE»? Вероятно, нет. Рассматривайте `Staging` как часть потока данных, а не для удаления DROP.

Сводка

Это в основном рассмотрено здесь: Таблицы сводок
Сводка выполняется из таблицы `Staging`, а не из таблицы `Fact`.

Проблемы с репликацией

Репликация на уровне строк (RBR) — вероятно, лучший вариант.

Следующее позволяет вам сохранить больше части процесса загрузки в мастер-сервере, тем самым не затрудняя работу сервера(ов) репликации с записью в таблицу подготовки.

• RBR
• `Staging` находится в отдельной базе данных
• Эта база данных не реплицируется (binlog-ignore-db на мастер-сервере)
• В шагах обработки используйте эту базу данных, обращаясь к основной базе данных через синтаксис типа «MainDb.Hosts». (В противном случае binlog-ignore-db сделает не то, что нужно.)

Таким образом

• Записи в `Staging` не реплицируются.
• Нормализация отправляет только несколько обновлений в таблицы нормализации.
• Сводка отправляет только обновления в таблицы сводок.
• Переключение таблиц не реплицирует операцию DROP, CREATE или RENAME.

Разбиение данных

Вы можете, возможно, распределить данные, которые вы пытаетесь загрузить, по нескольким машинам предсказуемым способом (разбиение данных по хэшу, диапазону и т. д.). Выполнение «отчётов» по разнесённой таблице `Fact` представляет собой отдельную задачу. С другой стороны, таблицы сводок редко становятся слишком большими для управления на одной машине.

На данный момент разбиение данных выходит за рамки этого блога.

Толкай меня vs тяни меня

Я неявно предполагал, что данные отправляются в базу данных. Если вместо этого вы «вытягиваете» данные из каких-либо источников, то есть некоторые другие соображения.

Случай 1: Ежечасная загрузка; выполняется через cron

1. Получить загрузку, разобрать её 2. Поместить её в таблицу Staging 3. Нормализация — каждый SQL в отдельной транзакции (автозапись изменений) 4. BEGIN 5. Сводка 6. Копирование из Staging в Fact. 7. COMMIT

Если вам нужна параллельность в сводке, придётся пожертвовать целостностью транзакций шагов 4–7.

Предупреждение: если эти шаги займут более часа, вы находитесь в глубокой проблеме.

Случай 2: Вы осуществляете опрос данных

Вероятно, разумно иметь несколько процессов, выполняющих это, поэтому будет подробно описано использование блокировок.

0. Создать таблицу `Staging` для этого процесса опроса. Цикл: 1. С помощью некоторого механизма блокировки определить, какой «объект» опросить. 2. Опросить данные, извлечь их, разобрать. (Возможные затраты на опрос и разбор значительны) 3. Поместить их в таблицу Staging специфичную для процесса 4. Нормализация — каждый SQL в отдельной транзакции (автозапись изменений) 5. BEGIN 6. Сводка 7. Копирование из Staging в Fact. 8. COMMIT 9. Объявить, что вы закончили работу с этим «объектом» (см. шаг 1) КонецЦикла.

iblog_file_size должен быть больше, чем изменение в статусе «Innodb_os_log_written» в транзакции BEGIN…COMMIT (для любого из случаев).

См. также

• Сопроводительный блог о хранилищах данных
• Сопроводительный блог о таблицах сводок
• Тема форума, которая подтолкнула меня к написанию этого блога
• Обсуждение на StackExchange

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, пошаговые инструкции, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/staging_table