4️⃣Оптимизация SQL-запроса с использованием pg_expecto: Часть-4 "Временная таблица"

Не верь на слово, проверь под давлением.

Методология исследования

Тестовая среда и инструменты:

• СУБД: PostgreSQL 17
• Инструмент нагрузочного тестирования: pg_expecto
• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
• Условия тестирования: параллельная нагрузка, ресурсоемкий запрос

Нагрузка на СУБД

1️⃣Эксперимент-1 : Оптимизация с использованием EXISTS

2️⃣Эксперимент-2 : Оптимизация структуры запроса

3️⃣Эксперимент-3 : Частичная агрегация

4️⃣Эксперимент-4 : Временная таблица

Тестовый запрос - TEMP TABLE

План выполнения тестового запроса -TEMP TABLE

Сравнительный анализ планов выполнения запросов

❓Проведи сравнительный анализ и сформируй итог по планам выполнения тестовых запросов

Ключевые различия:

1.Подход к работе с данными:

TEMP TABLE: Использует временную таблицу с предварительной фильтрацией и индексами

2.Использование ресурсов

TEMP TABLE: Меньше дисковых операций сортировки (109,672 kB)

Оба запроса используют external merge сортировку из-за большого объема данных

3.Операции в плане

TEMP TABLE: Разделяет логику - сначала фильтрация во временную таблицу, затем JOIN

4.Чтение данных

TEMP TABLE: Работает с уже отфильтрованными строками во временной таблице

Итоговый вывод:

ℹ️TEMP TABLE показывает лучшую производительность благодаря:

1. Разделению ответственности: Фильтрация данных выполняется один раз и сохраняется
2. Эффективному использованию индексов: Индексы на временной таблице ускоряют JOIN операции
3. Уменьшению сложности запроса: Основной запрос становится проще для оптимизатора
4. Снижению стоимости случайного выбора: ORDER BY RANDOM() выполняется один раз при создании временной таблицы

ℹ️Подход TEMP TABLE более масштабируем и предсказуем по производительности, особенно при работе с большими объемами данных.

Сравнительный анализ производительности и ожиданий СУБД в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"

Операционная скорость

График изменения операционной скорости в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "TEMP TABLE"

График изменения относительной разницы операционной скорости для запроса "TEMP TABLE" по сравнению с тестовым запросом "JOIN"

⬆️Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%

Ожидания СУБД

График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "TEMP TABLE"

❓Проведи сравнительный анализ и сформируй итог по показаниям wait_event_type для тестовых запросов

Сравнительный анализ показаний wait_event_type

Ключевые наблюдения:

1. Ожидания ввода-вывода (IO):
   В обоих типах запросов наблюдаются высокие и растущие значения IO.
   Для TEMP TABLE максимальное значение IO достигает 22882, для LEFT JOIN — 15183.
   Это указывает на значительную нагрузку на систему ввода-вывода в обоих случаях.
2. Межпроцессное взаимодействие (IPC):
   В LEFT JOIN значения IPC значительно выше (до 24107) и демонстрируют устойчивый рост.
   В TEMP TABLE IPC ниже (до 5870) и растет медленнее.
   Это говорит о том, что LEFT JOIN сильнее нагружает механизмы IPC.
3. Легковесные блокировки (LWLOCK):
   В TEMP TABLE значения LWLOCK значительно выше (до 311) и растут быстрее.
   В LEFT JOIN LWLOCK ниже (до 106) и увеличиваются умеренно.
   Использование временных таблиц приводит к более активному использованию легковесных блокировок.
4. Обычные блокировки (LOCK):
   Присутствуют только в TEMP TABLE, но после определенного момента становятся нулевыми.
   Это может означать, что начальные этапы работы с временными таблицами требуют блокировок, которые затем отпускаются.
5. Таймауты (TIMEOUT):
   В LEFT JOIN таймауты выше (до 19) и растут постепенно.
   В TEMP TABLE таймауты ниже (до 8) и остаются стабильными.
   Это может указывать на более предсказуемое выполнение временных таблиц.

Выводы:

• Запросы LEFT JOIN создают высокую нагрузку на IPC и IO, с умеренным ростом легковесных блокировок и таймаутов.
• Запросы TEMP TABLE вызывают значительный рост легковесных блокировок (LWLOCK), но при этом IPC и таймауты ниже. Наличие LOCK только на начальном этапе может свидетельствовать об особенностях аллокации временных объектов.

Рекомендации:

• При оптимизации запросов с LEFT JOIN стоит обратить внимание на уменьшение межпроцессного взаимодействия и оптимизацию ввода-вывода.
• При использовании временных таблиц важно контролировать легковесные блокировки, возможно, за счет сокращения времени жизни временных объектов или оптимизации их структуры.

Сравнительный анализ метрик iostat для дискового устройства используемого файловой системой /data, в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"

❓Проведи сравнительный анализ и сформируй итог по показаниям iostat для тестовых запросов в ходе нагрузочного тестирования

Одинаковая картина для обоих тестов:

1. Отсутствие дисковой нагрузки.
2. Работа в памяти: Все операции выполняются в оперативной памяти.

Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"

❓Проведи сравнительный анализ и сформируй итог по показаниям vmstat для тестовых запросов в ходе нагрузочного тестирования

Ключевые выводы по сравнению:

Детальный анализ:

1. Процессы:

• "LEFT JOIN": Количество работающих процессов (procs_r) растёт до 9, есть процессы в ожидании (procs_b до 2).
• "TEMP TABLE": procs_r доходит до 13, что указывает на более высокую конкуренцию за CPU.

2. Память:

• Оба запроса работают в условиях достаточного объёма свободной памяти.
• "LEFT JOIN" активнее использует кэш (выше memory_cache), что может быть связано с обработкой больших объёмов данных.

3. Ввод-вывод:

• "LEFT JOIN" создаёт значительную нагрузку на диск (высокие io_bi и io_bo), что согласуется с высокой долей cpu_wa.
• "TEMP TABLE" меньше нагружает диск, но при этом сильнее нагружает CPU.

4. Системная активность:

• "LEFT JOIN" вызывает больше прерываний и контекстных переключений, что может быть связано с активной работой с данными и блокировками.
• "TEMP TABLE" демонстрирует более низкую системную активность, но выше нагрузку на CPU в пользовательском режиме.

5. Использование CPU:

• "LEFT JOIN": Высокое ожидание ввода-вывода (cpu_wa), что указывает на узкое место в дисковой подсистеме.
• "TEMP TABLE": Низкий cpu_id (простой CPU), высокий cpu_us — запрос активно использует процессор для вычислений.

Итог:

• "LEFT JOIN" — I/O-зависимый запрос, создаёт высокую нагрузку на диск и вызывает ожидание ввода-вывода. Подходит для систем с быстрыми дисками и достаточным объёмом оперативной памяти для кэширования.
• "TEMP TABLE" — CPU-зависимый запрос, активно использует процессор, но меньше нагружает диск. Может быть эффективнее в системах с быстрыми CPU и достаточным количеством ядер.

Рекомендация:

• Для оптимизации "LEFT JOIN" стоит рассмотреть увеличение кэша БД, использование индексов и оптимизацию запросов для уменьшения объёма читаемых данных.
• Для "TEMP TABLE" можно рассмотреть увеличение числа CPU/ядер, оптимизацию использования временных таблиц и снижение сложности вычислений.

Общий итог : Часть-4 "TEMP TABLE"

ℹ️ Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - использование временной таблицы оказывает существенное влияние на увеличение производительности СУБД .

Производительность СУБД:

⬆️Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%💥

❓Сформируй общий итог о сравнении производительности и показателей инфраструктуры в ходе нагрузочного тестирования.

📊 Ключевые выводы о производительности

1. Операционная скорость

• TEMP TABLE демонстрирует значительное преимущество - среднее увеличение операционной скорости составляет +14.62% по сравнению с LEFT JOIN
• Подход с временными таблицами обеспечивает более предсказуемую и масштабируемую производительность

2. Характер нагрузки на систему

LEFT JOIN (I/O-зависимый запрос):

• Высокая нагрузка на дисковую подсистему (io_bi до 87 тыс., io_bo до 7 тыс.)
• Значительное межпроцессное взаимодействие (IPC до 24 107)
• Высокое время ожидания ввода-вывода (cpu_wa 10-17%)
• Больше прерываний и контекстных переключений

TEMP TABLE (CPU-зависимый запрос):

• Интенсивное использование процессора (cpu_us до 81%)
• Меньше дисковых операций (сортировка 109,672 kB против большего у JOIN)
• Высокие легковесные блокировки (LWLOCK до 311)
• Более стабильные таймауты (до 8)

🔧 Архитектурные преимущества TEMP TABLE

1. Разделение ответственности:

• Фильтрация данных выполняется один раз и сохраняется
• Основной запрос упрощается для оптимизатора СУБД

2. Эффективное использование индексов:

• Индексы на временной таблице ускоряют JOIN операции
• Снижение стоимости случайного выбора (ORDER BY RANDOM())

3. Оптимизация работы с данными:

• Уменьшение сложности запроса
• Предсказуемое использование памяти
• Более эффективное кэширование

📈 Инфраструктурные выводы

1. Для LEFT JOIN требуется:

• Быстрые диски (SSD/NVMe)
• Достаточный объем оперативной памяти для кэширования
• Оптимизация индексов и запросов

2. Для TEMP TABLE требуется:

• Мощные многоядерные процессоры
• Контроль легковесных блокировок
• Оптимизация использования временных объектов

🎯 Рекомендации по выбору подхода

Выбирать TEMP TABLE когда:

• Система имеет мощные процессоры
• Требуется высокая параллельная обработка
• Важна предсказуемость производительности
• Работа с большими объемами данных

Выбирать LEFT JOIN когда:

• Система имеет быстрые диски
• Ограниченные вычислительные ресурсы
• Простые запросы с хорошей индексацией
• Достаточный объем RAM для кэширования

💡 Ключевой вывод

Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы использование временных таблиц (TEMP TABLE) обеспечивает существенное увеличение производительности СУБД (+14.62%) и является более масштабируемым решением.

Подход TEMP TABLE лучше подходит для современных систем с многоядерными процессорами, обеспечивая более эффективное распределение нагрузки и предсказуемую производительность при работе с большими объемами данных.