Техники хранилищ данных

Предисловие

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

• Как загружать большие таблицы.
• Нормализация.
• Разработка «таблиц сводных данных» для повышения эффективности «отчетов».
• Очистка старых данных.

Подробное описание таблиц сводных данных приведено в сопутствующем документе: Таблицы сводных данных.

Терминология

Этот список отражает терминологию «хранилищ данных».

• Таблица фактов — одна большая таблица с «сырыми» данными.
• Таблица сводных данных — дублирующая таблица с обобщенными данными, которая может использоваться для повышения эффективности.
• Измерение — столбцы, определяющие аспекты набора данных (регион, страна, пользователь, SKU, почтовый индекс и т. д.).
• Таблица нормализации (таблица измерений) — соответствие между строками и идентификаторами; используется для экономии места и ускорения.
• Нормализация — процесс построения соответствия («Нью-Йорк» ↔ 123).

Таблица фактов

Методы, которые следует применять к большой таблице фактов.

• id INT/BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
• PRIMARY KEY (id)
• Вероятно, нет других индексов
• Доступ только через id
• Все VARCHAR нормализованы; хранятся идентификаторы
• ENGINE = InnoDB
• Все «отчеты» используют таблицы сводных данных, а не таблицу фактов
• Таблицы сводных данных могут заполняться из диапазонов id (другие методы описаны ниже)

Есть исключения, когда необходимо получить несколько строк из таблицы фактов. Однако следует минимизировать количество индексов в таблице, поскольку они могут негативно сказаться на операциях INSERT.

Зачем хранить таблицу фактов?

После создания таблицы (таблиц) сводных данных необходимость в таблице фактов снижается. Один из вариантов, который следует серьезно рассмотреть, — это вообще не создавать таблицу фактов. Либо по крайней мере можно раньше удалять старые данные из таблицы фактов, чем из таблиц сводных данных. Возможно, даже хранить таблицы сводных данных постоянно.

Случай 1: Вам необходимо найти исходные данные, связанные с каким-либо событием. Но как вы найдете эти строки? В этом случае может потребоваться вторичный индекс.

Если вторичный индекс слишком велик для кэширования в оперативной памяти и если индексируемые столбцы случайны, то каждая вставляемая строка может привести к обращению к диску для обновления индекса. Это снижает скорость вставки до примерно 100 строк в секунду (на обычных дисках). Несколько случайных индексов ещё больше замедляют вставку. RAID-массивы и/или SSD ускоряют вставку. Кэширование записи помогает, но только для кратковременных нагрузок.

Случай 2: Вам нужно некоторое событие, но вы не подготовили оптимальный индекс. Ну, если данные разделяются по дате, то даже если у вас есть предположение о том, когда произошло событие, «обрезка по разделам» предотвратит чрезмерно медленную работу запроса.

Случай 3: Со временем приложению, вероятно, понадобятся новые «отчеты», что может привести к созданию новой таблицы сводных данных. В этот момент будет полезно просмотреть старые данные, чтобы заполнить новую таблицу.

Случай 4: Вы обнаруживаете ошибку в обобщении и нуждаетесь в перестроении существующей таблицы сводных данных.

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

Партирование загрузки таблицы фактов

Когда речь идет о миллиардах строк в таблице фактов, «партирование» вставок является фактически обязательным. Есть два основных способа:

• INSERT INTO Fact (.,.,.) VALUES (.,.,.), (.,.,.), ...; — «партированная вставка»
• LOAD DATA ...;

Третий способ — вставить или загрузить данные в таблицу Staging, а затем

• INSERT INTO Fact SELECT * FROM Staging; Эта операция INSERT..SELECT позволяет выполнять другие действия, такие как нормализация. Подробнее об этом позже.

Операторы партированных вставок

Размер блока обычно должен составлять 100–1000 строк.

• 100–1000 строк при вставке будут выполняться в 10 раз быстрее, чем вставка по одной строке.
• Более 100 строк могут мешать репликации и операциям SELECT.
• Более 1000 строк — это убывающая отдача — практически нет дальнейшего повышения производительности.
• Не превышайте, скажем, 1 МБ для составленного оператора INSERT. Это касается размера пакетов и т. д. (1 МБ маловероятно, для таблицы фактов). Решите, к чему стремиться ваше приложение: к 100 или 1000.

Если данные поступают непрерывно, и вы добавляете слой партирования, давайте проделаем вычисления. Вычислите скорость поступления — R строк в секунду.

• Если R < 10 (1 млн в день = 300 млн в год) — вставка по одной строке, вероятно, будет работать нормально (то есть партирование необязательно)
• Если R < 100 (3 млрд записей в год) — вторичные индексы в таблице фактов могут быть приемлемы
• Если R < 1000 (100 млн записей в день) — избегайте вторичных индексов в таблице фактов
• Если R > 1000 — партирование может не работать. Решите, как долго (S секунд) вы можете приостановить загрузку данных, чтобы собрать пакет строк.
• Если S < 0,1 с — может не хватить производительности

Если партирование кажется целесообразным, разработайте слой партирования так, чтобы он собирал данные в течение S секунд или 100–1000 строк, в зависимости от того, что наступит раньше.

(Примечание: аналогичные вычисления применимы к операциям UPDATE в таблице).

Таблица нормализации (измерений)

Нормализация важна в приложениях хранилищ данных, поскольку существенно уменьшает занимаемое место на диске и повышает производительность. Есть и другие причины для нормализации, но для хранилищ данных важнее экономия места.

Вот типичная структура таблицы измерений:

    CREATE TABLE Emails (
        email_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- don't make bigger than needed
        email VARCHAR(...) NOT NULL,
        PRIMARY KEY (email),  -- for looking up one way
        INDEX(email_id)  -- for looking up the other way (UNIQUE is not needed)
    ) ENGINE = InnoDB;  -- to get clustering

Примечания:

• MEDIUMINT занимает 3 байта с диапазоном UNSIGNED от 0 до 16 млн; выберите SMALLINT, INT и т. д. на основе консервативной оценки того, сколько «foo» будет в конечном итоге.
• размеры типов данных
• В таблице может быть более одного VARCHAR. Например, для городов у вас могут быть столбцы Город и Страна.
• InnoDB лучше, чем MyISAM, из-за структуры двух ключей.
• Вторичный ключ фактически (email_id, email), поэтому «покрывает» определенные запросы.
• Можно не указывать AUTO_INCREMENT для уникальности.

Партированное нормализация

Я выделяю эту тему, потому что есть некоторые тонкие моменты.

Вы можете быть искушены сделать

    INSERT IGNORE INTO Foos
        SELECT DISTINCT foo FROM Staging;  -- not wise

Проблема в том, что операция «сжигает» идентификаторы AUTO_INCREMENT. Это происходит потому, что MariaDB предварительно выделяет идентификаторы, прежде чем дойдет до «IGNORE». В результате значения AUTO_INCREMENT могут быстро превысить ожидаемые.

Лучше так:

    INSERT IGNORE INTO Foos
        SELECT DISTINCT foo
            FROM Staging
            LEFT JOIN Foos ON Foos.foo = Staging.foo
            WHERE Foos.foo_id IS NULL;

Примечания:

• LEFT JOIN .. IS NULL находит `foo`, которые еще не в Foos.
• Эта операция INSERT..SELECT не должна выполняться внутри транзакции с остальными операциями. В противном случае увеличиваются риски тупиковых ситуаций, что приводит к сжиганию идентификаторов.
• IGNORE используется в случае одновременного выполнения INSERT из нескольких процессов.

После выполнения этой операции INSERT вы сможете найти все необходимые foo_id:

    INSERT INTO Fact (..., foo_id, ...)
        SELECT ..., Foos.foo_id, ...
            FROM Staging
            JOIN Foos ON Foos.foo = Staging.foo;

Преимущества «партированного нормализации» заключаются в возможности прямого обобщения из таблицы Staging. Два подхода:

Случай 1: PRIMARY KEY (dy, foo) и обобщение синхронизированы с, скажем, изменениями в `dy`.

• Этот подход может столкнуться с проблемами, если новые данные поступают после обобщения данных дня.

    INSERT INTO Summary (dy, foo, ct, blah_total)
        SELECT  DATE(dt) as dy, foo,
                COUNT(*) as ct, SUM(blah) as blah_total)
            FROM Staging
            GROUP BY 1, 2;

Случай 2: (dy, foo) — не уникальный индекс.

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

Случай 3: PRIMARY KEY (dy, foo) и обобщение может происходить в любое время.

    INSERT INTO Summary (dy, foo, ct, blah_total)
        ON DUPLICATE KEY UPDATE
            ct = ct + VALUE(ct),
            blah_total = blah_total + VALUE(bt)
        SELECT  DATE(dt) as dy, foo,
                COUNT(*) as ct, SUM(blah) as bt)
            FROM Staging
            GROUP BY 1, 2;

Слишком много вариантов?

В этом документе описано несколько способов сделать то или иное. В вашей ситуации один подход может быть более/менее приемлемым. Но если вы думаете: «Просто скажите, что делать!», вот как:

• Загрузить исходные данные в временную таблицу (`Staging`).
• Нормализовать данные из `Staging` — использовать код из случая 3.
• INSERT .. SELECT для перемещения данных из `Staging` в таблицу фактов
• Обобщить данные из `Staging` в таблицы сводных данных с помощью IODKU (Insert ... On Duplicate Key Update).
• Удалить Staging

Эти методы должны обеспечивать хорошую производительность и масштабируемость в большинстве случаев. По мере развития вашей ситуации вы можете узнать, почему я описал альтернативные решения.

Очистка старых данных

Обычно таблица фактов разделена по диапазонам (10–60 диапазонов дней/недель и т. д.) и периодически нуждается в очистке (DROP PARTITION). Этот раздел описывает безопасный и чистый способ проектирования разбиения и выполнения операций DROP: Очистка разделов

Мастер/раб

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

Фрагментация

«Фрагментация» — это разделение данных по нескольким серверам. (В отличие от репликации и Galera, где данные на всех серверах одинаковы, и все данные должны быть записаны на все сервера.)

С помощью описанных здесь методов обработки без фрагментации можно обрабатывать терабайты данных на одном компьютере. Для десятков терабайтов, вероятно, потребуется фрагментация.

Фрагментация выходит за рамки данного документа.

Как быстро? Как много?

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

Серьезным фактором, влияющим на производительность, являются ключи UUID/GUID. Поскольку они очень «случайны», обновления таких ключей (в масштабе) ограничены одной строкой = одним обращением к диску. Обычные диски могут обрабатывать только 100 обращений в секунду. RAID и/или SSD могут увеличить эту цифру до примерно 1000 в секунду. Огромные объемы оперативной памяти (для кэширования случайного индекса) — дорогостоящее решение. Возможна конвертация UUID первого типа в приблизительно хронологические ключи, тем самым смягчая проблемы производительности, если UUID записываются/считываются с некоторой хронологической группировкой. Обсуждение UUID

Аппаратное обеспечение и т. д.:

• Один SATA-накопитель: 100 IOP (операций ввода/вывода в секунду)
• RAID с N физическими накопителями — 100*N IOP (приблизительно)
• SSD — в 5 раз быстрее, чем вращающиеся носители (в данном контексте)
• Партированная вставка — 100–1000 строк в 10 раз быстрее, чем вставка по одной строке (см. выше)
• Очистка «старых» данных — не используйте DELETE или TRUNCATE, разработайте систему, позволяющую использовать DROP PARTITION (см. выше)
• Представьте каждый индекс (кроме PRIMARY KEY в InnoDB) как отдельную таблицу
• Учитывайте шаблоны доступа к каждой таблице/индексу: случайный, последовательный или промежуточный

"Подсчёт обращений к диску" — анализ производительности «на скорую руку»

• Случайные обращения к таблице/индексу — подсчитываются как каждое обращение к диску.
• Обращения в конце (INSERT хронологически или с AUTO_INCREMENT; диапазон SELECT) — считаются нулевыми обращениями к диску.
• Между (горячие/популярные идентификаторы и т. п.) — подсчитываются как что-то среднее
• Для INSERT выполните анализ для каждого индекса; сложите их.
• Для SELECT выполните анализ для используемого индекса, плюс для таблицы. (Использование двух индексов встречается редко.) Стоимость вставки зависит от типа данных первого столбца в индексе:
• AUTO_INCREMENT — фактически 0 IOP
• DATETIME, TIMESTAMP — фактически 0 для «текущих» времен
• UUID/GUID — 1 обращение за вставку (плохо)
• Другие — зависит от их шаблонов Стоимость SELECT становится немного сложной:
• Диапазон по PRIMARY KEY — представьте, что получается 100 строк за обращение к диску.
• IN по PRIMARY KEY — 1 обращение к диску за каждый элемент в IN
• "=" — 1 обращение (для 1 строки)
• Вторичный ключ — Сначала вычислите обращения к индексу, затем…
• Представьте, что каждая строка требует 1 обращения к диску.
• Однако, если строки, скорее всего, будут «рядом» друг с другом (по PRIMARY KEY), то это может быть < 1 обращения к диску/строка.

Подробнее о подсчёте обращений к диску

Как быстро?

Посмотрите на свои данные; вычислите количество строк в секунду (или час, день или год). В году примерно 30 млн секунд; 86 400 секунд в день. Вставка 30 строк в секунду означает миллиард строк в год.

10 строк в секунду — это примерно всё, что можно ожидать от обычного компьютера (после учёта различных накладных расходов). Если у вас меньше, то проблем не будет, но всё же, возможно, стоит создать сводные таблицы. Если больше 10/сек, то кэширование и т. п. становится очень важным. Даже на продвинутом оборудовании 100/сек — это примерно всё, что можно ожидать без применения описанных здесь техник.

Не очень быстро?

Предположим, скорость вставки составляет всего одну десятую от IOP дисковой подсистемы (например, 10 строк/сек против 100 IOP). Также предположим, что данные поступают не «всплесками», а более плавно в течение дня.

Обратите внимание, что 10 строк/сек (300 млн/год) предполагают, возможно, 30 ГБ для данных + индексов + таблиц нормализации + сводных таблиц на 1 год. Я бы назвал это «не так уж много».

Тем не менее, нормализация и обобщение важны. Нормализация предотвращает удвоение размера данных. Обобщение ускоряет отчёты на порядки величины.

Давайте разработаем и проанализируем «простую схему загрузки» для 10 строк в секунду без «кэширования».

    # Normalize:
    $foo_id = SELECT foo_id FROM Foos WHERE foo = $foo;
    if no $foo_id, then
        INSERT IGNORE INTO Foos ...

    # Inserts:
    BEGIN;
        INSERT INTO Fact ...;
        INSERT INTO Summary ... ON DUPLICATE KEY UPDATE ...;
    COMMIT;
    # (plus code to deal with errors on INSERTs or COMMIT)

В зависимости от количества и случайности ваших индексов и т. д., 10 строк фактов могут (или не могут) потребовать меньше 100 IOP.

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

По этим причинам я начал этот разговор с большим запасом (10 строк против 100 IOP).

Ссылки

• sec. 3.3.2: Модель измерения и «Звёздная схема»
• Сводные таблицы

См. также

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

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

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