Проблема с производительностью PostgreSQL
В журналах postgresql я вижу, что выполнение некоторых простых запросов (без объединений и с использованием только условий соответствия, использующих индексы) занимает от 1 до 3 секунд. Я регистрирую запросы, выполнение которых занимает больше секунды, поэтому есть похожие запросы, которые выполняются менее чем за секунду, о которых не сообщается.
Когда я пытаюсь выполнить тот же запрос с помощью EXPLAIN ANALYZE, это занимает несколько миллисекунд.
Таблица содержит около 8 миллионов записей, и в нее выполняется обширная запись и запросы. Я включил автоматический вакуум и даже недавно (несколько часов назад) провел ВАКУУМНЫЙ АНАЛИЗ на этой таблице.
Пример записи в журнале запросов: 30 декабря 10:14:57 db01 postgres [7400]: [20-1] LOG: продолжительность: 3857,322 мс инструкция: SELECT * FROM «ответы» ГДЕ («ответы» .contest_id = 17469) И (user_id не 30 декабря 10:14:57 db01 postgres [7400]: [20-2] null) ORDER BY updated_on desc LIMIT 5
Competition_id и user_id проиндексированы. updated_on не индексируется. Если я его проиндексирую, планировщик запросов игнорирует индекс contest_id и вместо этого использует updated_on, что еще больше замедляет запрос. Максимальное количество записей в приведенном выше запросе без LIMIT не должно превышать 1000.
Любая помощь приветствуется.
Ответы 5
Здесь могут быть полезны еще несколько деталей, в зависимости от того, можете ли вы их предоставить. Наиболее полезным будет фактический результат EXPLAIN ANALYZE, чтобы мы могли видеть, что он делает при выполнении запроса. Определение запрашиваемой таблицы также может оказаться полезным вместе с индексами. Чем больше информации, тем веселее. Я могу только догадываться прямо сейчас о том, что происходит, вот несколько слепых ударов:
- Одновременно с этой базой данных происходит множество других операций SELECT, и периодически данные и / или результат истекают из какого-то кеша.
- Есть еще что-то, что периодически блокирует эту таблицу на срок более 3-4 секунд, прежде чем снова освободить ее, в течение этого времени этот запрос зависает.
- Эта таблица сильно записана в так, так что в конечном итоге статистика таблицы почти никогда не отражает реальность, и поэтому анализатор запросов ошибается при принятии решения о том, использовать ли индекс (а) для выполнения запроса.
У других людей могут быть другие идеи, но да. Дополнительная информация о том, что происходит, может оказаться полезной.
@Nikhil: Мало запустить, надо еще и прочитать. Если вы хотите, чтобы другие помогли вам разобраться в ваших проблемах с производительностью, нам тоже придется это прочитать.
Похоже, это происходит из-за подкачки.
Купите оперативную память или уменьшите количество процессов на машине, кроме БД.
pgsql-performance - отличный список рассылки, где можно задавать подобные вопросы.
Похоже, у вас здесь две проблемы:
1) Вы хотите иметь возможность индексировать updated_on, но если вы это сделаете, PostgreSQL выберет неправильный план.
Моим первым безумным предположением было бы то, что PostgreSQL переоценивает количество кортежей, соответствующих предикату «(responses.contest_id = 17469) AND (user_id is not null)». Если postgres сначала использует этот предикат, он должен позже отсортировать значения для реализации ORDER BY. Вы говорите, что он соответствует 1000 кортежей; если postgresql считает, что он соответствует 100000, возможно, он думает, что сканирование с использованием индекса updated_on будет дешевле. Другим фактором может быть ваша конфигурация: если для work_mem установлено низкое значение, он может подумать, что сортировка дороже, чем она есть на самом деле.
Вам действительно нужно показать вывод EXPLAIN ANALYZE медленного запроса, чтобы мы могли понять, почему он может выбирать сканирование индекса на updated_on.
2) Даже если он не проиндексирован, иногда для его выполнения требуется время, но вы не знаете, почему, потому что если вы запустите его вручную, он будет работать нормально.
Используйте модуль Contrib auto_explain, новый в версии 8.4. Это позволяет вам регистрировать вывод EXPLAIN ANALYZE запросов, которые занимают слишком много времени. Простая регистрация запроса ставит вас перед проблемой, с которой вы столкнулись сейчас: каждый раз, когда вы запускаете запрос, он выполняется быстро.
если точно такой же запрос занимает миллисекунды в объяснении анализа и 3 секунды в журналах (т.е. я предполагаю, что это занимает 3 секунды, а не каждый его вызов занимает столько времени) - то это определенно означает, что это проблема с блокировкой.
- запирание
- обмен
- КЛАСТЕР / ВАКУУМ ЗАПОЛНЕН в задании cron
- насыщенная сеть
- насыщенный ввод-вывод
проверьте iostat, vmstat, iptraf ...
Если проблема №1 (много других SELECT ...), что я могу сделать, чтобы улучшить производительность? Я только что провел анализ объяснения, и это заняло около 4 секунд. Когда я снова запустил его с другими идентификаторами, это заняло несколько миллисекунд. Следовательно, я подозреваю, что это связано с выгрузкой данных из кеша.