Slow Query Questions/ru

Руководство по публикации вопросов о медленных запросах

Примерно 50% вопросов по IRC-каналу #postgresql и порядка 75% в рассылке pgsql-performance, задаваемых еженедельно, относятся к проблеме медленных запросов. При этом редко кто из задающих вопрос предоставляет полную информацию по проблемному медленному запросу, что приводит к разочарованию обеих сторон - отвечающих на вопрос и его задающих.

Поэтому перед тем как задать вопрос на тему "Я и мой медленный запрос", пожалуйста, обратитесь к нижеследующим рекомендациям, а так же соберите необходимую информацию. Обратитесь к руководству по сообщению о проблемах и предоставьте сведения согласно ему.

Вопросы, касающиеся проблем производительности, в листе рассылки pgsql-performance задавайте лишь после изучения ресурсов, доступных в разделе категории производительности, предварительно собрав необходимые сведения (см. ниже) и тщательно обдумав проблему. Перед тем как нажать кнопку "Отправить", внимательно прочтите ваше сообщение, представьте себя на месте того человека, которому ничего неизвестно о вашем приложении или конфигурации, еще раз переосмыслите задаваемый вопрос. Это избавит вас от лишних "Вы не объяснили <то>!" и "Что вы имели ввиду, сославшись на <это>?", прежде чем кто-то сможет помочь вам, имея достаточно вводных сведений. Таким образом небольшим приложением усилий вначале пути можно быстрее решить вашу проблему.

Необходимые для предоставления сведения

(замечание: если просите помощи по IRC-каналу, то публикуйте сведения на таких специализированных ресурсах, как, например, pgsql.privatepaste.com, но не напрямую)

• Полная схема таблиц и индексов: публикуйте определения всех таблиц и индексов, на которые имеются ссылки в запросе. Если запрос затрагивает представления или пользовательские функции, то их определения также необходимо предоставить.
• Метаданные таблиц: также к определению таблиц необходимо сообщить примерное количество записей и такие сведения, как, например:
  • содержит ли таблица большие бинарные объекты?
  • есть ли значительный перекос в количестве NULL-значений каких-либо колонок таблицы?
  • есть ли постоянное большое количество операций записи таких, как UPDATE или DELETE к таблице?
  • есть ли быстрый рост данных в таблице?
  • есть ли большое количество индексов таблицы?
  • использует ли таблица триггеры, которые в свою очередь могут вызывать функции базы данных, или имеются ли прямые вызовы функций?

• EXPLAIN (ANALYZE, BUFFERS) вместо EXPLAIN: EXPLAIN (ANALYZE, BUFFERS) сообщает о том, как запрос реально выполнялся, а не то, как он планировался к выполнению. Это важная информация, по которой можно судить о том, где планировщик мог ошибиться. Если невозможно выполнить EXPLAIN (ANALYZE, BUFFERS) в силу каких-либо причин, то это необходимо указать. Пример: EXPLAIN (ANALYZE, BUFFERS) select * from tablename;
  • EXPLAIN (ANALYZE, BUFFERS) был представлен в версии 9.0. Если ваша версия Postgres ниже 9.0, то вместо нее необходимо выполнить EXPLAIN ANALYZE.
  • Для Postgres версии 9.2 и выше включите track_io_timing перед выполнением EXPLAIN. Если подключение установлено от лица суперпользователя, можно включить track_io_timing на уровне сессии, выполнив SET track_io_timing = on;
  • Разместите результат выполнения EXPLAIN (ANALYZE, BUFFERS) или EXPLAIN ANALYZE на explain.depesz.com и предоставьте эти ссылки. Нам это очень нравится, тк. позволяет изучать планы выполнения значительно проще.
• Версия Postgres: необходимо указывать точную полную версию используемого сервера, простой способ - выполнить команду SELECT version(). Поведение планировщика меняется от версии к версии, поэтому эти сведения крайне важны.
• Предыстория: был ли запрос медленным всегда или стал таким с течением времени? Имеются ли копии плана и реального выполнения до того момента, как запрос стал медленным? Что еще изменилось в вашей базе помимо роста данных?
• Железо: необходимо предоставить сведения по аппаратной части сервера. Если используются нестандартные компоненты, пожалуйста, сообщите подробно о них. Обратитесь к руководству по сообщению о проблемах для получения информации о том, какого рода сведения об аппаратной части могут пригодится.
• Обслуживание сервера: применяете ли вы автовакуум и с каким настройками? Если не применяется автовакуум, выполняете ли вручную и как часто команды VACUUM и/или ANALYZE?
• Конфигурация журнала упреждающей записи (WAL): (для запросов записи данных) вынесен ли журнал на другой диск? Изменялись ли параметры конфигурации?
• Настройки GUC: какие настройки базы данных вами проводились? Каковы их значения? Важны такие, как, например, "work_mem", "enable_seq_scan". В конфигурации сервера можно найти полезный запрос, который выведет все измененные параметры, и в более удобочитаемой форме относительно кусков, взятых из вашего конфигурационного файла postgresql.conf.

Что можно попробовать предпринять самостоятельно

Перед тем, как задать вопрос, можно сэкономить кучу своего времени, попробовав следующее:

• Прочитать Использование EXPLAIN, если это еще не сделано.
• Выполнить ANALYZE для базы данных, чтобы обновить статистику запросов.
• Выполнить VACUUM базы данных, что бы избавиться от лишнего мусора, если не выполняется автовакуум на постоянной основе.
• Проверить основные параметры GUC, чтобы удостовериться в обоснованности их значений (также см. Настройка вашего сервера PostgreSQL):
  • shared_buffers должен иметь значение в пределах от 10% до 25% доступной оперативной памяти
  • effective_cache_size должен иметь значение 75% от доступной оперативной памяти
• Поиграйтесь с параметром work_mem: увеличьте его до 8MB, 32MB, 256MB, 1GB. Ощутили эффект?
• Для запросов Insert/Update/Delete можно поэкспериментировать с конфигурацией журнала упреждающей записи (WAL):
  • Вынесите pg_xlog на отдельный диск, если это возможно
  • Увеличьте значение checkpoint_segments до 16 или более, в соответствии со свободным пространством на диске
  • Увеличьте wal_buffers до 16MB
• Проверьте ваш IO: запустите тест dd, bonnie++ или другие тесты дисковой производительности, чтобы убедиться в том, что проблемы не связаны с аппаратной частью. Например, RAID-5 по определению всегда будет медленным для операций insert/update, как бы вы не оптимизировали запросы.