❌ИТОГ - Оптимизация SQL-запроса с использованием pg_expecto

Материал для ознакомления. Один из экспериментов поставлен некорректно.

Исправленный вариант

—————

Предисловие

Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?

Бизнес задача

Подготовить наиболее эффективный SQL-запрос, позволяющий получить информацию для анализа:

• Неполных бронирований
• Билетов без привязки к рейсам
• Рейсов без процедуры посадки
• Статистики по незавершённым операциям

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

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

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

Протестированные решения

LEFT JOIN

EXISTS

TUNING(оптимизация структуры запроса)

ARRAY(Частичная агрегация)

❌TEMPORARY TABLE (Временная таблица)

WHERE (Изменение условия WHERE)

Эксперименты

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

Производительность СУБД в ходе нагрузочного тестирования

График изменения операционной скорости в ходе нагрузочного тестирования

Характерные особенности производительности СУБД для нагрузки 15 соединений и выше

❓Проанализируй данные производительности(чем выше значение тем выше производительность) в ходе нагрузочного тестирования для разных тестовых запросов . Выдели ключевые особенности после точки измерения 81.

1. Катастрофическое падение производительности тестового запроса "WHERE"

• Резкое снижение: Значения колеблются (12-16), затем происходит обвал до 4, после 18 соединений — до 2, а с нагрузки 22 соединения производительность полностью деградирует.
• Важность: Падение до 0 означает, что эти запросы фактически перестают выполняться.

2. Стабилизация и рост производительности "JOIN"

• Положительный тренд: После 15 соединений "JOIN" показывает стабильные значения 14, а после 18 соединений производительность возрастает до 16.

3. Улучшение "EXISTS"

• Рост производительности: С 18 соединений значение увеличивается с 12 до 14 и остается стабильным.

4. Стабильность "TUNING"

• Консистентность: После 15 соединений производительность стабильно держится на уровне 22.

5. "ARRAY"- максимальная стабильность

• Идеальная стабильность: После 12 соединений производительность достигает своего максимального стабильного значения 32.

6. "TEMP TABLE" высокая производительность

• Высокий уровень: Значения 214-216 (самые высокие абсолютные значения).

Критические выводы:

1. Наиболее уязвимый компонент: "WHERE"- сильная деградация под нагрузкой.
2. Наиболее устойчивые: "ARRAY" и "TUNING" запросы показывают отличную стабильность.
3. Парадокс: В то время как "WHERE" деградируют до 0, другие типы запросов либо стабильны, либо улучшаются. Это может указывать на:
4. Конкуренцию за ресурсы
5. Проблемы специфические для условий "WHERE"

Метрики производительности инфраструктуры для нагрузки 15 соединений и выше

❓Проанализируй показатели метрик vmstat в ходе нагрузочного тестирования для разных тестовых запросов . Выдели ключевые особенности по метрикам wa , bi , us , r , cache , sy , si после точки измерения 81.

1. WA (Ожидание ввода-вывода) - CPU Wait I/O

• JOIN: высокий уровень (11-16%), особенно в конце (до 17%)
• EXISTS: умеренный (12-15%), относительно стабильный
• TUNING: самый высокий (24-28%) - явная проблема с дисковыми операциями
• ARRAY: минимальный (0-3%) - низкая нагрузка на I/O
• TEMPORARY TABLE: минимальный (0-1%)
• WHERE: низкий (1-3%)

2. BI (Blocks In - блоки ввода)

• JOIN: высокий (66K-86K)
• EXISTS: умеренный (68K-86K)
• TUNING: высокий (69K-73K)
• ARRAY: средний (32K-35K)
• TEMPORARY TABLE: средний (62K-63K)
• WHERE: экстремально высокий (200K-250K) - интенсивное чтение с диска

3. US (User CPU - пользовательский процессор)

• JOIN: умеренный (54-58%)
• EXISTS: умеренный (56-64%)
• TUNING: высокий (59-66%)
• ARRAY: очень высокий (94-95%)
• TEMPORARY TABLE: очень высокий (93-95%)
• WHERE: высокий (80%)

4. R (Run Queue - очередь процессов)

• JOIN: 7-9 процессов
• EXISTS: 7-10 процессов
• TUNING: 7-8 процессов
• ARRAY: 11-15 процессов
• TEMPORARY TABLE: 13-15 процессов
• WHERE: 12-15 процессов

5. CACHE (Кэш памяти)

• JOIN: стабильно высокий (6800-7100)
• EXISTS: стабильно высокий (6800-7000)
• TUNING: стабильно высокий (6700-6900)
• ARRAY: снижается (6000-6300)
• TEMPORARY TABLE: умеренный (6400-6600)
• WHERE: умеренный (6100-6300)

6. SY (System CPU - системный процессор)

• JOIN: низкий (5%)
• EXISTS: низкий (5%)
• TUNING: низкий (5%)
• ARRAY: низкий (4%)
• TEMPORARY TABLE: низкий (5%)
• WHERE: высокий (12-14%) - интенсивные системные вызовы

7. SI (Swap In)

Данные по SI отсутствуют в предоставленном файле.

Ключевые выводы:

1. Наиболее проблемный запрос: "TUNING" - имеет максимальное время ожидания I/O (24-28%) при умеренной нагрузке CPU.
2. Наиболее CPU-интенсивные запросы: "ARRAY" и "TEMPORARY TABLE" - достигают 95% использования user CPU.
3. Наиболее I/O-интенсивный запрос: "WHERE" - экстремально высокий BI (до 250K) и повышенное системное время CPU (14%).
4. Наибольшая очередь выполнения: "ARRAY", "TEMPORARY TABLE" и "WHERE" (11-15 процессов).
5. Наименее нагруженные запросы: "JOIN" и "EXISTS" показывают сбалансированные метрики без экстремальных значений.

Итог

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

1. Для данной СУБД и в условиях высокой конкурентной нагрузки, для обеспечения максимальной производительности - рекомендован паттерн "ТEMPORARY TABLE"
2. Наихудший паттерн - "WHERE"

Производительность и стабильность инфраструктуры

1. Для данной СУБД и в условиях высокой конкурентной нагрузки, для обеспечения стабильной работы инфраструктуры, если производительность запроса не является ключевым требованием - рекомендованы паттерны "JOIN" и "EXISTS".
2. Наихудший паттерн - "WHERE"