The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа
next up previous
Next: 4 Заключение Up: PostgreSQL: настройка производительности Previous: 2 Настройка сервера

Subsections

3 Оптимизация БД и приложения

Для быстрой работы каждого запроса в вашей базе в основном требуется следующее:

  1. Отсутствие в базе мусора, мешающего добраться до актуальных данных. Можно сформулировать две подзадачи:

  2. Наличие быстрых путей доступа к данным -- индексов.
  3. Возможность использования оптимизатором этих быстрых путей.
  4. Обход известных проблем.


3.1 Поддержание базы в порядке

В данном разделе описаны действия, которые должны периодически выполняться для каждой базы. От разработчика требуется только настроить их автоматическое выполнение (при помощи cron) и опытным путём подобрать его оптимальную частоту.


3.1.1 Команда VACUUM

Используется для «сборки мусора» в базе данных. Начиная с версии 7.2, существует в двух вариантах:

При использовании в форме VACUUM [FULL] ANALYZE, после сборки мусора будет обновлена статистика по данной таблице, используемая оптимизатором. В абсолютном большинстве случаев имеет смысл использовать именно эту форму.

Рекомендуется достаточно частое -- в [7] и [8], например, раз в несколько минут (!) -- выполнение VACUUM ANALYZE для часто обновляемых баз (или отдельных таблиц). В обыкновенных случаях достаточно ежедневного 8 выполнения этой команды. При этом обратите внимание: если «бутылочное горлышко» вашего сервера находится в районе дисковой подсистемы, то выполнение VACUUM параллельно с обычной работой может крайне отрицательно сказаться на производительности.

Команду VACUUM FULL стоит запускать достаточно редко, не чаще раза в неделю. Её также имеет смысл запускать вручную для конкретной таблицы после удаления или обновления большой части записей в ней.


3.1.2 Команда ANALYZE

Служит для обновления информации о распределении данных в таблице. Эта информация используется оптимизатором для выбора наиболее быстрого плана выполнения запроса.

Обычно команда используется в связке VACUUM ANALYZE. Если в базе есть таблицы, данные в которых не изменяются и не удаляются, а лишь добавляются, то для таких таблиц можно использовать отдельную команду ANALYZE. Также стоит использовать эту команду для отдельной таблицы после добавления в неё большого количества записей.

3.1.3 pg_autovacuum

Начиная с версии 7.4, в дистрибутиве PostgreSQL поставляется программа pg_autovacuum, которая отслеживает изменения в таблицах и автоматически запускает команды VACUUM и/или ANALYZE для этих таблиц по достижении определённого предела.

Использование этой программы позволяет отказаться от настройки периодического выполнения команд VACUUM и ANALYZE. Более того, в случае использования pg_autovacuum ресурсы не тратятся впустую на обработку таблиц, которые практически не подвергались изменениям.

Для работы pg_autovacuum должен быть включён сборщик статистики (см. пункт 2.4.2) и включён параметр stats_row_level.


3.1.4 Команда REINDEX

Команда REINDEX используется для перестройки существующих индексов. Использовать её имеет смысл в случае

Второй случай требует пояснений. Индекс, как и таблица, содержит блоки со старыми версиями записей. PostgreSQL не всегда может заново использовать эти блоки 9 , и поэтому файл с индексом постепенно увеличивается в размерах. Если данные в таблице часто меняются, то расти он может весьма быстро.

Если вы заметили подобное поведение какого-то индекса, то стоит настроить для него периодическое выполнение команды REINDEX. Учтите: команда REINDEX, как и VACUUM FULL, полностью блокирует таблицу, поэтому выполнять её надо тогда, когда загрузка сервера минимальна.


3.2 Использование индексов

Опыт показывает, что наиболее значительные проблемы с производительностью вызываются отсутствием нужных индексов. Поэтому столкнувшись с медленным запросом, в первую очередь проверьте, существуют ли индексы, которые он может использовать. Если нет -- постройте их.

Излишек индексов, впрочем, тоже чреват проблемами:

Единственное, что можно сказать с большой степенью определённости -- поля, являющиеся внешими ключами, и поля, по которым объединяются таблицы, индексировать надо обязательно.

3.2.1 Команда EXPLAIN [ANALYZE]

Команда EXPLAIN [запрос] показывает, каким образом PostgreSQL собирается выполнять ваш запрос. Команда EXPLAIN ANALYZE [запрос] выполняет запрос 10 и показывает как изначальный план, так и реальный процесс его выполнения.

Чтение вывода этих команд -- искусство, которое приходит с опытом. Для начала обращайте внимание на следующее:

Следует отметить, что полный просмотр таблицы далеко не всегда медленнее просмотра по индексу. Если, например, в таблице-справочнике несколько сотен записей, умещающихся в одном-двух блоках на диске, то использование индекса приведёт лишь к тому, что придётся читать ещё и пару лишних блоков индекса. Если в запросе придётся выбрать 80% записей из большой таблицы, то полный просмотр опять же получится быстрее.

При тестировании запросов с использованием EXPLAIN ANALYZE можно воспользоваться настройками, запрещающими оптимизатору использовать определённые планы выполнения. Например,

SET enable_seqscan=false;
запретит использование полного просмотра таблицы, и вы сможете выяснить, прав ли был оптимизатор, отказываясь от использования индекса. Ни в коем случае не следует прописывать подобные команды в postgresql.conf! Это может ускорить выполнение нескольких запросов, но сильно замедлит все остальные!


3.2.2 Использование собранной статистики

Результаты работы сборщика статистики (см. пункт 2.4.2) доступны через специальные системные представления. Наиболее интересны для наших целей следующие:

pg_stat_user_tables
содержит -- для каждой пользовательской таблицы в текущей базе данных -- общее количество полных просмотров и просмотров с использованием индексов, общие количества записей, которые были возвращены в результате обоих типов просмотра, а также общие количества вставленных, изменённых и удалённых записей.
pg_stat_user_indexes
содержит -- для каждого пользовательского индекса в текущей базе данных -- общее количество просмотров, использовавших этот индекс, количество прочитанных записей, количество успешно прочитанных записей в таблице (может быть меньше предыдущего значения, если в индексе есть записи, указывающие на устаревшие записи в таблице).
pg_statio_user_tables
содержит -- для каждой пользовательской таблицы в текущей базе данных -- общее количество блоков, прочитанных из таблицы, количество блоков, оказавшихся при этом в буфере (см. пункт 2.1.1), а также аналогичную статистику для всех индексов по таблице и, возможно, по связанной с ней таблицей TOAST.
Из этих представлений можно узнать, в частности

Также возможен «дедуктивный» подход, при котором сначала создаётся большое количество индексов, а затем неиспользуемые индексы удаляются.

3.2.3 Возможности индексов в PostgreSQL

3.2.3.1 Функциональные индексы

Вы можете построить индекс не только по полю/нескольким полям таблицы, но и по выражению, зависящему от полей. Пусть, например, в вашей таблице foo есть поле foo_name, и выборки часто делаются по условию «первая буква foo_name = 'буква', в любом регистре». Вы можете создать индекс

CREATE INDEX foo_name_first_idx 

ON foo ((lower(substr(foo_name, 1, 1))));

и запрос вида

SELECT * FROM foo 

WHERE lower(substr(foo_name, 1, 1)) = 'ы';

будет его использовать.

Следует отметить, что возможности задания подобных индексов были значительно расширены в версии 7.4, и приведённый пример может потребовать доработки, чтобы быть запущенным на более старой версии.

3.2.3.2 Частичные индексы (partial indexes)

Под частичным индексом понимается индекс с предикатом WHERE. Пусть, например, у вас есть в базе таблица scheta с параметром uplocheno типа boolean. Записей, где uplocheno = false меньше, чем записей с uplocheno = true, а запросы по ним выполняются значительно чаще. Вы можете создать индекс

CREATE INDEX scheta_neuplocheno ON scheta (id) 

WHERE NOT uplocheno;

который будет использоваться запросом вида

SELECT * FROM scheta WHERE NOT uplocheno AND ...;
Достоинство подхода в том, что записи, не удовлетворяющие условию WHERE, просто не попадут в индекс.

3.2.3.3 Полнотекстовый поиск

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

Наиболее продвинутым из имеющихся средств является tsearch2 http://www.sai.msu.su/ megera/postgres/gist/tsearch/V2/ Он поставляется в дистрибутиве PostgreSQL версии 7.4 в каталоге contrib/tsearch2, вариант для версии 7.3 можно скачать на указанном сайте.

За полным описанием возможностей tsearch2 обратитесь к поставляемой с ним документации.

3.3 Перенос логики на сторону сервера

Этот пункт очевиден для опытных пользователей PostrgeSQL и предназначен для тех, кто использует или переносит на PostgreSQL приложения, написанные изначально для более примитивных СУБД.

Реализация части логики на стороне сервера через хранимые процедуры, триггеры, правила 11 часто позволяет ускорить работу приложения. Действительно, если несколько запросов объединены в процедуру, то не требуется

Кроме того, хранимые процедуры упрощают процесс разработки и поддержки: изменения надо вносить только на стороне сервера, а не менять запросы во всех приложениях.


3.4 Оптимизация конкретных запросов

В этом разделе описываются запросы, для которых по разным причинам нельзя заставить оптимизатор использовать индексы, и которые будут всегда вызывать полный просмотр таблицы. Таким образом, если вам требуется использовать эти запросы в требовательном к быстродействию приложении, то придётся их изменить.

3.4.1 SELECT max(...)/min(...) FROM <огромная таблица>

Все агрегатные функции в PostgreSQL реализованы одинаково: сначала выбираются все записи, удовлетворяющие условию, а потом к полученному набору записей применяется агрегатная функция. У такого подхода есть достоинства -- вы можете легко написать собственную агрегатную функцию -- но есть и недостаток, который заключается в том, что для работы функций типа min() / max() весь набор записей совершенно не нужен.

Для их работы рациональней было бы воспользоваться индексом по полю, для которого ищется максимум (минимум), но для этого придётся сделать реализацию этих агрегатных функций отличной ото всех остальных.

Проблема

Запрос вида

SELECT max(field) FROM foo;
не будет использовать существущий индекс по полю field, а будет делать полный просмотр таблицы. Если записей в таблице много, то это может занять изрядное время.

Решение

Запрос вида

SELECT field FROM foo ORDER BY field DESC LIMIT 1;
вернёт то же самое значение 12 , но при этом сможет использовать индекс по field, если таковой существует.

3.4.2 SELECT count(*) FROM <огромная таблица>

К функции count() относится всё вышесказанное по поводу реализации агрегатных функций в PostgreSQL. Кроме того, информация о видимости записи для текущей транзакции (а конкурентным транзакциям может быть видимо разное количество записей в таблице!) не хранится в индексе. Таким образом, даже если использовать для выполнения запроса индекс первичного ключа таблицы, всё равно потребуется чтение записей собственно из файла таблицы.

Проблема

Запрос вида

SELECT count(*) FROM foo;
осуществляет полный просмотр таблицы foo, что весьма долго для таблиц с большим количеством записей.

Решение

Простого решения проблемы, к сожалению, нет. Возможны следующие подходы:

  1. Если точное число записей не важно, а важен порядок 13 , то можно использовать информацию о количестве записей в таблице, собранную при выполнении команды ANALYZE:

    SELECT reltuples FROM pg_class WHERE relname = 'foo';
  2. Если подобные выборки выполняются часто, а изменения в таблице достаточно редки, то можно завести вспомогательную таблицу, хранящую число записей в основной. На основную же таблицу повесить триггер, который будет уменьшать это число в случае удаления записи и увеличивать в случае вставки. Таким образом, для получения количества записей потребуется лишь выбрать одну запись из вспомогательной таблицы.
  3. Вариант предыдущего подхода, но данные во вспомогательной таблице обновляются через определённые промежутки времени (cron).

3.4.3 SELECT ... WHERE ... IN (SELECT ...)

Сразу отметим, что в версии 7.4 в обработку подзапросов с IN / NOT IN были внесены изменения, и теперь они работают (как минимум) не медленнее, чем подзапросы с EXISTS / NOT EXISTS. Если вы по каким-то причинам не можете обновить версию сервера до 7.4, то читайте дальше.

Проблема

При использовании подзапроса вида

SELECT ...

FROM foo

WHERE foo_field IN (

    SELECT bar_field

    FROM bar

    ...

);

оптимизатор не может использовать индекс по таблице bar, и поэтому запрос отрабатывает крайне медленно.

Решение

Перепишите подзапрос с использованием конструкции EXISTS:

SELECT ...

FROM foo

WHERE EXISTS (

    SELECT bar_field

    FROM bar

    WHERE bar.bar_field = foo.foo_field

    ...

);

Аналогично можно переписать подзапрос с NOT IN, используя конструкцию NOT EXISTS.



Footnotes

... 8
точнее еженощного, т.к. по ночам нагрузка на базу меньше
... 9
в версии 7.4 были сделаны существенные улучшения
... 10
и поэтому EXPLAIN ANALYZE DELETE ... -- не слишком хорошая идея
... 11
RULE -- реализованное в PostgreSQL расширение стандарта SQL, позволяющее, в частности, создавать обновляемые представления
... 12
на самом деле почти то же самое: отличие будет в случае, если в таблице нет записей
... 13
«на нашем форуме более 10000 зарегистрированных пользователей, оставивших более 50000 сообщений!»

next up previous
Next: 4 Заключение Up: PostgreSQL: настройка производительности Previous: 2 Настройка сервера
2003-12-10



Спонсоры:
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2020 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру