Почему базы Данных замедляются Проблемы с производительностью возникают, когда база данных перестает справляться с нагрузкой, что замедляет работу базы данных и приложений, которые на нее работают. Время отклика увеличивается с миллисекунд до секунд или минут, а пропускная способность сокращается. Загрузка процессора, памяти и операций ввода-вывода резко возрастает, а время ожидания или ошибки увеличиваются. В крайних случаях база данных становится недоступной, что делает данные недоступными.
Первопричины: Сначала запросы Наиболее распространенными причинами являются сами запросы: их слишком много или они написаны неэффективно. Последовательные проверки там, где следует использовать индексы, отсутствие или несовпадение индексов, а также тяжелые объединения снижают производительность. Неиндексированные ключи объединения и избыток объединений особенно вредны для аналитики. Правильно подобранные индексы для столбцов фильтрации и сортировки могут возвращать результаты намного быстрее.
Планы, статистика и устаревание Такие движки, как PostgreSQL, при выборе планов выполнения полагаются на внутреннюю статистику. Устаревшая статистика вводит оптимизатора в заблуждение, из-за чего хороший запрос выполняется плохо. Иногда проблема заключается не в SQL, а в устаревшей статистике, хранящейся на сервере. Обновление статистики приводит планы в соответствие с фактическим распределением данных.
Ограничения по оборудованию и конфигурации Даже perfect SQL замедляется при нехватке оперативной памяти или слабых процессорах. Настройки СУБД по умолчанию часто не учитывают аппаратное обеспечение сервера, что приводит к плохому функционированию кэша и операций ввода-вывода. Одиночный экземпляр в конечном итоге достигает предела ресурсов процессора и памяти, в то время как кластеры ограничены подключенной сетью. Цель состоит в том, чтобы достичь требуемой производительности с минимальными затратами ресурсов.
Скорость разработки схем Формирует форму Модели данных могут затруднять выполнение запросов или ускорять их выполнение. Высокая степень нормализации увеличивает количество объединений и замедляет аналитическое чтение, в то время как денормализация сокращает количество объединений за счет избыточности. Если целостность имеет первостепенное значение, выигрывает более строгая нормализация; при больших объемах сканирования и агрегации помогает денормализация. Неправильный выбор типа данных, например, хранение чисел в виде текста, также влияет на эффективность налогообложения.
Конфликты, блокировки и взаимоблокировки Высокий уровень параллелизма приводит к блокировке, которая увеличивает задержку. Длинные транзакции блокируют множество строк или целых таблиц, заставляя других пользователей ждать. В худшем случае две транзакции блокируют друг друга и не могут продолжить работу. От этого могут пострадать как отдельные экземпляры, так и реплицированные кластеры.
От мониторинга к действию Начните с мониторинга, чтобы выявить узкие места. Используйте Zabbix или Prometheus для определения основных показателей и пояснений, чтобы увидеть, как на самом деле выполняются запросы. Сравните время отклика с течением времени, чтобы выявить регрессию и самые медленные операторы. Затем оптимизируйте работу с наиболее проблемными.
Когда Настройки запроса Недостаточно Помимо переписывания SQL, просмотрите и перепроектируйте индексы. Если результатов по-прежнему не хватает, исправьте схему, примените секционирование или сегментирование или дублируйте данные в системах с разными уровнями нормализации. Распределите наборы данных по отдельным СУБД, если это упрощает рабочие нагрузки. В качестве заключительного шага выполните масштабирование по вертикали, добавив оперативную память/ процессор, или по горизонтали, добавив узлы.
Сжатие: Экономит место, расходует процессор Сжатие на уровне строк или таблиц сокращает использование диска и может ускорить чтение за счет сокращения операций ввода‑вывода. Компромиссы включают фрагментацию таблиц и неизменяемый минимальный размер базы данных. Сжатие требует больших затрат ресурсов процессора, что увеличивает нагрузку на процессор, но при этом экономит место. Сбалансируйте экономию памяти и скорость чтения с более высоким потреблением ресурсов процессора.
Как данные размещаются на диске Секционирование и сегментирование данных позволяет разделить данные для повышения управляемости и производительности. Диапазоны, списки или хэши определяют расположение строк; разделы находятся на одном сервере, сегменты охватывают множество серверов. Даты являются естественными для разбиения диапазонов по годам, месяцам или дням; небольшие перечисления подходят для разбиения списков. Распределение хэшей — часто по первичным ключам — обеспечивает наиболее равномерный разброс.
Хранилища строк, хранилища столбцов и широкие строки Хранилище, ориентированное на строки, преобладает в классических реляционных системах, в то время как хранилища в столбцах широко распространены в аналитических СУБД. Некоторые движки поддерживают “широкие строки” для объединения множества атрибутов. Физический порядок имеет значение, поскольку размещение связанных данных рядом друг с другом сокращает время поиска. Движки стараются, чтобы связанные строки располагались близко друг к другу на диске или внутри разделов.
Кластеризованные индексы и физический порядок MySQL и SQL Server используют кластеризованные индексы, которые хранят табличные данные в порядке следования индексов, поэтому для каждой таблицы может существовать только один кластеризованный индекс. В PostgreSQL отсутствуют кластеризованные индексы, но поддерживается кластеризация, которая переписывает таблицу в соответствии с порядком следования индексов. Любая ВСТАВКА, ОБНОВЛЕНИЕ или УДАЛЕНИЕ со временем нарушает этот порядок и требует повторного объединения вручную. Обычные индексы поддерживаются автоматически; кластеризацию необходимо явно запускать повторно.
Кучи и стоимость случайного чтения Без кластеризации таблицы представляют собой кучи, в которых строки расположены в произвольном порядке, а новые строки заполняют близлежащее свободное пространство. Выборка целевых строк может привести к необходимости поиска во многих фрагментах диска, что особенно опасно для вращающихся дисков. Чем более разбросаны строки, тем медленнее выполняется сканирование. Кластеризация группирует похожие значения, чтобы уменьшить перемещение диска.
Разделенные продажи для более быстрой аналитики Разделите таблицу продаж по дате продажи, чтобы создать компактные файлы по периодам, таким как месяцы или дни. Запросы, ограниченные датой, считывают только соответствующие разделы, пропуская остальные. Дополнительная кластеризация по идентификаторам клиентов или продуктов еще больше улучшает локализацию. С диска считывается меньше данных, а ответы приходят быстрее.
Выбор стратегии обновления При полной перезагрузке таблица сначала очищается, а затем заполняется заново. Функция TRUNCATE предпочтительнее, чем функция DELETE, поскольку она удаляет файл таблицы и создает его заново без сканирования строк, в то время как функция DELETE помечает строки и удаляет их позже. Полная перестройка подходит для небольших таблиц или измерений, но медленная для больших объемов. Они также создают периоды простоя и создают большую нагрузку на источники и сети.
Постепенные нагрузки направлены на изменение При инкрементном обновлении учитываются только строки, измененные с момента последнего запуска. Для отслеживания изменений могут использоваться временные метки, столбцы версий, журналы транзакций (например, MySQL binlog или PostgreSQL WAL) или логический флаг, в то время как в ClickHouse нет журнала транзакций. В процессе применяются функции ВСТАВКИ, ОБНОВЛЕНИЯ и УДАЛЕНИЯ для отражения новых, измененных и устаревших данных. Этот подход подходит для больших таблиц фактов и компонентов хранилища данных, таких как спутники, концентраторы и ссылки.
Периодические перезагрузки с разделами Некоторые конвейеры перезагружаются по временным интервалам, а не по изменениям на уровне строк. При использовании столбца с временной меткой данные разбиваются на периоды, и обновляется только целевой период. Секционированные таблицы делают это эффективным: удаляйте старый раздел и создавайте его заново с исходными строками за этот период. Это работает, когда данные естественным образом сегментируются по времени или отслеживание изменений недоступно.
Сохраняя правильными только последние 30 дней Когда необходимо выполнить удаление в течение 30 дней, записи помечаются флажком tombstone как удаленные перед физической очисткой. Инкрементальная логика объединяет флажок с отметкой времени создания для выдачи данных об удалении на складе. Ежедневные разделы позволяют сохранять данные, удаляя разделы старше 30 дней. Последние дни остаются полностью обновляемыми, в то время как более старые разделы устаревают.
Модели и материализации DBT Модели DBT выражают таблицы в виде инструкций SELECT, шаблонизированных с помощью Jinja и параметров. Зависимости между промежуточным и нижестоящим уровнями отображаются в виде явного графика. Материализация управляет построением результатов: таблица и представление воссоздают объекты при каждом запуске, в то время как инкрементальный процесс нацелен на загрузку только изменений. Конфигурация задается для каждой модели или в файле проекта.
Поэтапные стратегии DBT на практике Уникальный ключ, такой как request_id, позволяет DBT сопоставлять входящие строки с существующими. Стратегии включают добавление и удаление+вставку, которые выбираются для каждой модели и для каждой базы данных. В инкрементальном режиме SQL условно фильтруется только для измененного подмножества, используя такие поля, как request_id и временные метки изменения. При первом запуске выполняется полное создание и загрузка; последующие запуски обновляют только то, что изменилось.
Материализация вставки DBT по периодам Для загрузки с разбивкой по периодам требуются столбцы с отметками времени, определяющие начальный и конечный периоды. При первоначальном выполнении создается и заполняется таблица. При последующих запусках удаляются строки для целевого периода и вставляются данные этого периода из источника. Airflow может организовывать эти периодические сборки по расписанию.
Ознакомление с планом выполнения В ПОЯСНЕНИИ описан алгоритм, которому будет следовать движок для ответа на запрос. В планах перечислены проверки, объединения, сортировки и агрегации, каждая из которых сопровождается оценкой затрат. Для небольших таблиц может быть предпочтительнее последовательное сканирование, даже если существует индекс. В плане представлены отчеты о запуске и общей стоимости, ожидаемых строках и средних размерах строк для руководства настройкой.
Сканирование умнее, а не сложнее При последовательном сканировании считывается каждая строка, и по мере роста таблиц это становится слишком сложным. При индексном сканировании используются B‑деревья или хэш-индексы, чтобы получить только совпадающие строки. При растровом сканировании результаты из нескольких индексов объединяются перед выборкой строк. Самый быстрый способ ‑ это поиск только по индексу, когда в индексе присутствуют все необходимые столбцы.
Рекомендации по настройке запросов Создавайте правильные индексы для ключей filter, join и sort и избегайте SELECT *. Сократите количество связанных подзапросов и дорогостоящих циклов и разумно используйте временные таблицы. Отдавайте предпочтение EXISTS, а не COUNT, удаляйте ненужные DISTINCT и используйте приблизительные подсчеты, когда точность не требуется. Пишите четкие псевдонимы для удобства чтения и всегда оценивайте планы и затраты, чтобы подтвердить правильность улучшений.
Помимо основ: Рычаги переключения скоростей Кэшируйте результаты с помощью денормализации и кэшируйте статические справочные таблицы для среднечастотных запросов. Используйте векторизованные функции, подсказки и материализованные представления с постепенным обновлением. В PostgreSQL планируйте VACUUM и ANALYZE, чтобы поддерживать работоспособность хранилища и свежесть статистики. Загружайте данные параллельно, используйте встроенную обработку, интегрируйтесь с озером данных и определяйте приоритетность запросов для повышения пропускной способности.
Реальная нагрузка на Postgres Служба разработки B2B–приложений работает на PostgreSQL 9.6 с примерно 300 таблицами и примерно 200 миллионами запросов в день, что составляет в среднем 3-4 тыс. запросов в секунду. Срочные исправления привели к проблемам с производительностью, которые необходимо было устранить. Запросы были сгруппированы в топовые, средние и конечные по частоте. Оптимизация небольшого набора запросов высвободила примерно 5-10% ресурсов.
Нацеливаясь на середину, вы получаете большие выигрыши Помимо основного набора, на среднечастотные запросы приходилось около 80% рабочей нагрузки. После оптимизации их доля выросла с 34% до 57%. Улучшения включали изменение индекса, редизайн системы, корректировку модели данных, переписывание логики для отложенной загрузки и кэширование. Статические справочные таблицы и денормализованные кэши ускорили эти пути.
Эффективный перенос данных из MySQL в ClickHouse Избегайте использования Python в качестве основного уровня передачи данных для больших объемов. Считывайте данные из реплики MySQL и записывайте в буферные таблицы ClickHouse, которые сбрасываются в основную таблицу по объему или времени. Предпочитайте функцию сбора данных об изменениях, которая передает события (например, через Kafka) непосредственно в ClickHouse или пользователю, который их пересылает. Если требуется Python, выполняйте пакетную вставку фрагментами и используйте соответствующие драйверы ClickHouse; SQL и Pandas также могут взаимодействовать с обеими системами.
Ресурсы, события и последующие шаги Подробная информация о курсе представлена на целевой странице Otus: вечерние онлайн-занятия два раза в неделю с интерактивными голосовыми вопросами и ответами. Программа охватывает хранение и преобразование данных, загрузку и согласование данных, большой модуль бизнес-аналитики и расширенные темы производительности, а завершается проектом. В преподавательскую группу входят аналитики, разработчики и администраторы баз данных; ведется запись прошедших мероприятий и запланирован открытый урок по сбору требований. К записи будут прикреплены слайды вебинара, а новости сообщества будут доступны в Telegram-канале Otus.