#postgresql #query-optimization #postgresql-12
#postgresql #оптимизация запросов #postgresql-12
Вопрос:
Привет, Postgresql 12 постепенно разогнался до 100% процессора. Команда разработчиков приложений занималась решением проблемы с 60 минут. 2 разных запроса требовали высокой производительности процессора. Задействованные таблицы были небольшими, поэтому я просто запустил анализ всех 3 задействованных таблиц, чтобы исправить любую основную проблему статистики, не проверяя сначала план запроса. Сразу после запуска analyze проблема была исправлена.
Теперь мы попытались воссоздать проблему из резервной копии на несколько часов старше времени проблемы, но план запроса одинаков до и после анализа. Мы не можем найти RCA.
Сведения о системе: vCPU: 8 Оперативная память: 61 ГБ Режим: подчиненный / считываемая копия
Сведения о таблице resource_type: 174 строки роли: 290026 строк user_role_mapping: 3332118 строк
Запрос с планом из prod copy machine (для целей RCA): ОБЪЯСНИТЕ (ПРОАНАЛИЗИРУЙТЕ TRUE, ПОДРОБНОЕ TRUE, ЗАТРАТЫ TRUE, БУФЕРЫ TRUE, ВРЕМЯ TRUE) ВЫБЕРИТЕ * ИЗ user_role_mapping u, ГДЕ u.user_profile_id = ‘44130444’ И u.service_name = ‘GLOBAL’ И u.status = ‘ACTIVE’ И СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ИЗ ролей r, ГДЕ r.id= u.role_id И r.role_name=’Владелец’) И СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ИЗ resource_type_dummy r, ГДЕ r.id=u.resource_type_id И r.type=’ОТЕЛЬ’);
ОБЪЯСНИТЕ (АНАЛИЗ TRUE, ПОДРОБНОЕ TRUE, ЗАТРАТЫ TRUE, БУФЕРЫ TRUE, ВРЕМЯ TRUE) ВЫБЕРИТЕ * ИЗ user_role_mapping u, ГДЕ u.user_profile_id = ‘x1’ И u.service_name = ‘GLOBAL’ И u.status = ‘ACTIVE’ И СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ИЗ ролей r, ГДЕ r.id=u.role_id И r.role_name=’Владелец’) И СУЩЕСТВУЕТ (ВЫБЕРИТЕ * ИЗ resource_type r, ГДЕ r.id=u.resource_type_id И r.type=’ОТЕЛЬ’);
QUERY PLAN
Вложенный цикл (стоимость = 1,00 ..424,35 строк = 1 ширина = 361) (фактическое время = 368,512 ..369,536 строк = 1 цикл = 1)
Вывод: u.id , u.статус, u.created_by, u.created_at, u.modified_by, u.last_modified_at, u.имя_службы, u.crs_id, u.identity_id, u.role_id, u.resource_id, u.resource_type_id, u.user_profile_id
Внутренняя уникальность: true
Буферы: общий доступ = 3 чтения = 9
Тайминги ввода-вывода: read = 369.368
-> Вложенный цикл (стоимость = 0,57 .. 415,82 строки = 1 ширина = 361) (фактическое время = 281,798 ..282,821 строки = 1 цикл = 1)
Вывод: u.id , u.статус, u.created_by, u.created_at, u.modified_by, u.last_modified_at, u.имя_службы, u.crs_id, u.identity_id, u.role_id, u.resource_id, u.resource_type_id, u.user_profile_id
Фильтр объединения: (u.resource_type_id = r_1.id )
Буферы: общий доступ = 1 чтение = 7
Тайминги ввода-вывода: read = 282.696
-> Сканирование индекса с использованием type_index_resource_type для public.resource_type r_1 (стоимость = 0,14 .. 8,16 строк = 1 ширина = 16) (фактическое время = 0,054 .. 0,055 строк = 1 цикл = 1)
Вывод: r_1.id , r_1.статус, r_1.created_by, r_1.created_at, r_1.modified_by, r_1.last_modified_at, r_1.service_name, r_1.crs_id, r_1.тип, r_1.действия
Индекс Cond: ((r_1.type)::text = ‘ОТЕЛЬ’::текст)
Буферы: общий доступ = 1 чтение = 1
Тайминги ввода-вывода: чтение = 0,016
-> Сканирование индекса с использованием user_profile_id_index_user_role_mapping для public.user_role_mapping u (стоимость = 0,43 ..407,19 строк = 37 ширина = 361) (фактическое время = 281,739 ..282,759 строк = 1 цикл = 1)
Вывод: u.id , u.статус, u.created_by, u.created_at, u.modified_by, u.last_modified_at, u.имя_службы, u.crs_id, u.identity_id, u.role_id, u.resource_id, u.resource_type_id, u.user_profile_id
Индекс Cond: ((u.user_profile_id)::text = ‘x1’::text)
Фильтр: ((u.service_name)::text = ‘GLOBAL’::text) И ((u.status)::text = ‘ACTIVE’::text))
Строки удалены фильтром: 2
Буферы: общее чтение = 6
Тайминги ввода-вывода: read = 282.680
-> Сканирование индекса с использованием pk_roles для public.roles r (стоимость = 0,42 .. 8,44 строки = 1 ширина = 16) (фактическое время = 86,708 .. 86,708 строк = 1 цикл = 1)
Вывод: r.id , r.статус, r.created_by, r.created_at, r.modified_by, r.last_modified_at, r.service_name, r.crs_id, r.role_name, r.team_name, r.access_category, r.crs_team_id, r.действия
Изменение индекса: (r.id = u.role_id)
Фильтр: ((r.role_name)::text = ‘Owner’::text)
Буферы: общий доступ = 2 чтения = 2
Тайминги ввода-вывода: read = 86.672
Время планирования: 0,439 мс
Время выполнения: 369,570 мс
(30 строк)
ОБЪЯСНИТЕ (ANALYZE TRUE, VERBOSE TRUE, ЗАТРАТЫ TRUE, БУФЕРЫ TRUE, ВРЕМЯ TRUE) ВЫБЕРИТЕ ПРИВЕДЕНИЕ (urm.id КАК VARCHAR), urm.status, urm.created_by, urm.created_at, ПРИВЕДЕНИЕ (urm.modified_by КАК VARCHAR), urm.last_modified_at, urm.service_name, urm.crs_id,urm.identity_id, ПРИВЕДЕНИЕ (urm.role_id КАК VARCHAR), urm.resource_id, ПРИВЕДЕНИЕ (urm.resource_type_id КАК VARCHAR), urm.user_profile_id, r.role_name role_name, rt.введите resource_type_name ИЗ user_role_mapping ВНУТРЕННЕГО СОЕДИНЕНИЯ urm (ВЫБЕРИТЕ id, role_name ИЗ ролей r ГДЕ r.имя_роли В (‘xa’, ‘xb’, ‘xc’, ‘xd’))r НА urm.role_id = r.id ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБЕРИТЕ идентификатор, rt.type ИЗ resource_type rt, ГДЕ rt.type = ‘эксперимент’)rt НА urm.resource_type_id = rt.id ГДЕ (urm.user_profile_id В(’11’, ’12’)) И urm.service_name = ‘CRS’ И urm.status = ‘АКТИВНЫЙ’;
Вложенный цикл (стоимость = 1,00 ..830,89 строк = 1 ширина = 271) (фактическое время = 240,293 ..240,294 строк = 0 циклов = 1) Вывод: (urm.id )::изменение характера, urm.status, urm.created_by, urm.created_at, (urm.modified_by)::изменение символа, urm.last_modified_at, urm.service_name, urm.crs_id, urm.identity_id, (urm.role_id):: изменение символа, urm.resource_id, (urm.resource_type_id):: изменение символа, urm.user_profile_id, r.role_name, rt.тип Внутренний уникальный: true Буферы:общий доступ = 8 чтение = 5 таймингов ввода-вывода: чтение = 240.145 -> Вложенный цикл (стоимость = 0.57 ..822.43 строк = 1 ширина = 379) (фактическое время = 240.292 ..240.293 строк = 0 циклов = 1) Вывод: urm.id , urm.status, urm.created_by,urm.created_at, urm.modified_by, urm.last_modified_at, urm.service_name, urm.crs_id, urm.identity_id, urm.role_id, urm.resource_id, urm.resource_type_id, urm.user_profile_id, rt.тип Присоединиться к фильтру: (urm. resource_type_id = rt.id ) Строки, удаленные фильтром объединения: 2 буфера: общий доступ = 8 чтение = 5 таймингов ввода-вывода: чтение = 240.145 -> Сканирование индекса с использованием type_index_resource_type в public.resource_type rt (стоимость = 0,14 ..8,16 строк = 1 ширина = 34) (фактическое время = 0,018 ..0,019 строк = 1 цикл = 1) Вывод: rt.идентификатор, rt.status, rt.created_by, rt.created_at, rt.modified_by, rt.last_modified_at, rt.service_name, rt.crs_id, rt.type, rt.actions Индекс Cond: ((rt.type)::text = ‘эксперимент’::текст) Буферы: общий доступ = 2 -> Сканирование индекса с использованием user_profile_id_index_user_role_mapping в public.user_role_mapping urm (стоимость = 0,43 ..814,21 строк = 4 ширина = 361) (фактическое время = 138,694 ..240,267 строк = 2 цикла = 1) Вывод: urm.id , урм.статус, урм.created_by, урм.created_at, urm.modified_by, urm.last_modified_at, urm.service_name, urm.crs_id, urm.identity_id, urm.role_id, urm.resource_id, urm.resource_type_id, urm.user_profile_id Индекс Cond: ((urm.user_profile_id) ::текст = ЛЮБОЙ (‘{11,12}’::текст[])) Фильтр: (((urm.service_name)::text = ‘CRS’::text) И ((urm.status)::text = ‘АКТИВНЫЙ’::text)) Буферы: общий доступ = 6 чтение = 5 Тайминги ввода-вывода: чтение = 240.145 -> Сканирование индекса с использованием pk_roles для public.roles r (стоимость = 0.42 ..8.45 строк = 1 ширина = 32) (никогда не выполнялось) Вывод: r.id , r.статус, r.created_by, r.created_at, r.modified_by, r.last_modified_at, r.service_name, r.crs_id, r.role_name, r.team_name, r.access_category, r.crs_team_id, r.Индекс действий Cond: (r.id= urm.role_id) Фильтр: ((r.role_name)::text = ANY (‘{xa, xb, xc, xd}’::text[])) Время планирования: 0,860 мс Время выполнения: 240,358 мс (27 строк)
Какие-либо другие направления для поиска?
Комментарии:
1. Если это невозможно воспроизвести, вам не повезло. В следующий раз
EXPLAIN (ANALYZE, BUFFERS)обработайте запрос, прежде чем устранять проблему.2. «Теперь мы попытались воссоздать проблему из резервной копии на несколько часов старше времени выпуска» Это имеет смысл, если проблему легко обнаружить, тогда резервное копирование до того, как оно было обнаружено, вероятно, не отобразило бы проблему. У вас есть архив wal, чтобы вы могли перенести его на более позднее время? Если нет, возможно, настройте его, чтобы в следующий раз произошло что-то подобное.
3. Возможно, автоанализ просто случайно получил плохой образец, что привело к проблеме.. А затем другой анализ исправил это. В вашей тестовой копии системы вы можете просто повторить попытку ANALYZE, а затем выполнить запрос в цикле, чтобы посмотреть, можете ли вы иногда получить плохой случай.
4. @LaurenzAlbe: Пробовал несколько объяснений, но безуспешно. В текущем плане запросов для запросов 1 и query2 вы видите некоторые возможности для улучшения? В запросе 2 есть Filter: Filter: ((r.role_name)::text = ANY (‘{xa, xb, xc, xd}’::text[])).. На случай, если там. есть несколько записей для role_name приблизительно 20000, даже тогда этот фильтр применяется вместо использования индекса…. Я полагаю, что этот эффект фильтра сработал бы во время выпуска, хотя никакого увеличения операций ввода-вывода на диске отмечено не было. Все, что мы можем здесь сделать… Я считаю, что большинство временных проблем плохого плана можно было бы исправить с помощью best query и indexing.
5. @jjanes: пожалуйста, проверьте мой комментарий выше.