Публикация была переведена автоматически. Исходный язык: Русский
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Методология исследования
Тестовая среда и инструменты:
- СУБД: PostgreSQL 17
- Инструмент нагрузочного тестирования: pg_expecto
- Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
- Условия тестирования: параллельная нагрузка, ресурсоемкий запрос
Нагрузка на СУБД
Эксперимент-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 показывает лучшую производительность благодаря:
- Разделению ответственности: Фильтрация данных выполняется один раз и сохраняется
- Эффективному использованию индексов: Индексы на временной таблице ускоряют JOIN операции
- Уменьшению сложности запроса: Основной запрос становится проще для оптимизатора
- Снижению стоимости случайного выбора: ORDER BY RANDOM() выполняется один раз при создании временной таблицы
ℹ️Подход TEMP TABLE более масштабируем и предсказуем по производительности, особенно при работе с большими объемами данных.
Сравнительный анализ производительности и ожиданий СУБД в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"
Операционная скорость
Рис.1 График изменения операционной скорости в ходе нагрузочного тестирования при использовании тестового запроса "JOIN" и "TEMP TABLE"
Рис.2 График изменения относительной разницы операционной скорости для запроса "TEMP TABLE" по сравнению с тестовым запросом "JOIN"
Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%
Ожидания СУБД
Рис.3 График изменения ожиданий СУБД в ходе нагрузочного тестирования при использовании тестового запроса "JOIN" и "TEMP TABLE"
Сравнительный анализ показаний wait_event_type
Ключевые наблюдения:
- Ожидания ввода-вывода (IO):В обоих типах запросов наблюдаются высокие и растущие значения IO.Для TEMP TABLE максимальное значение IO достигает 22882, для LEFT JOIN — 15183.Это указывает на значительную нагрузку на систему ввода-вывода в обоих случаях.
- Межпроцессное взаимодействие (IPC):В LEFT JOIN значения IPC значительно выше (до 24107) и демонстрируют устойчивый рост.В TEMP TABLE IPC ниже (до 5870) и растет медленнее.Это говорит о том, что LEFT JOIN сильнее нагружает механизмы IPC.
- Легковесные блокировки (LWLOCK):В TEMP TABLE значения LWLOCK значительно выше (до 311) и растут быстрее.В LEFT JOIN LWLOCK ниже (до 106) и увеличиваются умеренно.Использование временных таблиц приводит к более активному использованию легковесных блокировок.
- Обычные блокировки (LOCK):Присутствуют только в TEMP TABLE, но после определенного момента становятся нулевыми.Это может означать, что начальные этапы работы с временными таблицами требуют блокировок, которые затем отпускаются.
- Таймауты (TIMEOUT):В LEFT JOIN таймауты выше (до 19) и растут постепенно.В TEMP TABLE таймауты ниже (до 8) и остаются стабильными.Это может указывать на более предсказуемое выполнение временных таблиц.
Выводы:
- Запросы LEFT JOIN создают высокую нагрузку на IPC и IO, с умеренным ростом легковесных блокировок и таймаутов.
- Запросы TEMP TABLE вызывают значительный рост легковесных блокировок (LWLOCK), но при этом IPC и таймауты ниже. Наличие LOCK только на начальном этапе может свидетельствовать об особенностях аллокации временных объектов.
Рекомендации:
- При оптимизации запросов с LEFT JOIN стоит обратить внимание на уменьшение межпроцессного взаимодействия и оптимизацию ввода-вывода.
- При использовании временных таблиц важно контролировать легковесные блокировки, возможно, за счет сокращения времени жизни временных объектов или оптимизации их структуры.
Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"
Ключевые выводы по сравнению:
Детальный анализ:
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 лучше подходит для современных систем с многоядерными процессорами, обеспечивая более эффективное распределение нагрузки и предсказуемую производительность при работе с большими объемами данных.
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Методология исследования
Тестовая среда и инструменты:
- СУБД: PostgreSQL 17
- Инструмент нагрузочного тестирования: pg_expecto
- Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
- Условия тестирования: параллельная нагрузка, ресурсоемкий запрос
Нагрузка на СУБД
Эксперимент-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 показывает лучшую производительность благодаря:
- Разделению ответственности: Фильтрация данных выполняется один раз и сохраняется
- Эффективному использованию индексов: Индексы на временной таблице ускоряют JOIN операции
- Уменьшению сложности запроса: Основной запрос становится проще для оптимизатора
- Снижению стоимости случайного выбора: ORDER BY RANDOM() выполняется один раз при создании временной таблицы
ℹ️Подход TEMP TABLE более масштабируем и предсказуем по производительности, особенно при работе с большими объемами данных.
Сравнительный анализ производительности и ожиданий СУБД в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"
Операционная скорость
Рис.1 График изменения операционной скорости в ходе нагрузочного тестирования при использовании тестового запроса "JOIN" и "TEMP TABLE"
Рис.2 График изменения относительной разницы операционной скорости для запроса "TEMP TABLE" по сравнению с тестовым запросом "JOIN"
Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%
Ожидания СУБД
Рис.3 График изменения ожиданий СУБД в ходе нагрузочного тестирования при использовании тестового запроса "JOIN" и "TEMP TABLE"
Сравнительный анализ показаний wait_event_type
Ключевые наблюдения:
- Ожидания ввода-вывода (IO):В обоих типах запросов наблюдаются высокие и растущие значения IO.Для TEMP TABLE максимальное значение IO достигает 22882, для LEFT JOIN — 15183.Это указывает на значительную нагрузку на систему ввода-вывода в обоих случаях.
- Межпроцессное взаимодействие (IPC):В LEFT JOIN значения IPC значительно выше (до 24107) и демонстрируют устойчивый рост.В TEMP TABLE IPC ниже (до 5870) и растет медленнее.Это говорит о том, что LEFT JOIN сильнее нагружает механизмы IPC.
- Легковесные блокировки (LWLOCK):В TEMP TABLE значения LWLOCK значительно выше (до 311) и растут быстрее.В LEFT JOIN LWLOCK ниже (до 106) и увеличиваются умеренно.Использование временных таблиц приводит к более активному использованию легковесных блокировок.
- Обычные блокировки (LOCK):Присутствуют только в TEMP TABLE, но после определенного момента становятся нулевыми.Это может означать, что начальные этапы работы с временными таблицами требуют блокировок, которые затем отпускаются.
- Таймауты (TIMEOUT):В LEFT JOIN таймауты выше (до 19) и растут постепенно.В TEMP TABLE таймауты ниже (до 8) и остаются стабильными.Это может указывать на более предсказуемое выполнение временных таблиц.
Выводы:
- Запросы LEFT JOIN создают высокую нагрузку на IPC и IO, с умеренным ростом легковесных блокировок и таймаутов.
- Запросы TEMP TABLE вызывают значительный рост легковесных блокировок (LWLOCK), но при этом IPC и таймауты ниже. Наличие LOCK только на начальном этапе может свидетельствовать об особенностях аллокации временных объектов.
Рекомендации:
- При оптимизации запросов с LEFT JOIN стоит обратить внимание на уменьшение межпроцессного взаимодействия и оптимизацию ввода-вывода.
- При использовании временных таблиц важно контролировать легковесные блокировки, возможно, за счет сокращения времени жизни временных объектов или оптимизации их структуры.
Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "TEMP TABLE"
Ключевые выводы по сравнению:
Детальный анализ:
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 лучше подходит для современных систем с многоядерными процессорами, обеспечивая более эффективное распределение нагрузки и предсказуемую производительность при работе с большими объемами данных.
Поделиться
