FreeSource : Статьи/PostgreSQL/Оптимизация

Оптимизация PostgreSQL

Введение

Часто встречаются вопросы по оптимизации баз данных. К сожалению качественный материал на эту тему найти крайне сложно. Эта статья является сочетанием моего опыта с компиляцией писем специалистов в мейллистах и форумах.

VACUUM


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

Я рекомендую ежедневно выполнять команды

Конфигурация

fsync

По-умолчанию Postgre SQL настроен на максимальную надёжность, а не максимальную производительность. Транзакция считается завершённой только после выполнения fsync (сброса кэша файлов на диск).

Если у вас есть UPS, и вы уверены что некорректных перезагрузок не будет, то вы можете в postgresql.conf заменить fsync=yes на fsync=no, тем самым многократно увеличить производительность (ценой, разумеется, некоторого риска).

Размеры буферов

В файле postgresql.conf можно очень гибко настаривать различные буферы, используемые Postgre SQL. Я не буду здесь описывать тонкости их настройки, лучше ставьте тесты на конкретном приложении, для которого вы используете базу.

shared memory

Очень важный для работы Postgre SQL параметр — количество разделяемой памяти. Тут всё просто (в общем-то как и со всеми буферами) — чем больше, тем лучше. В реальной же жизни надо тестировать сколько и подо что отдавать памяти.

enable_seqscan=false

Поставьте enable_seqscan=false в postgresql.conf.

Программирование

COUNT(*)

С точки зрения Postgre SQL COUNT это такая же функция, как и любая другая агрегирующая функция. Точно так же, как и для, например, SUM, все данные из запроса будут переданы этой функции.

При вызове команлы:

SELECT COUNT(*) FROM table;

всё содержимое таблицы будет передано функции COUNT. С диска таблица будет считана целиком. Если таблица большая, то это будет очень длительная операция.

Поэтому, пожалуйста, избегайте использования COUNT(*) на больших запросах, пожалейте ваш сервер!
Обычно в реальной работе нужно считать только небольшого количество элементов, поэтому и не была сделана специфичная оптимизация. Если вам действительно нужно знать количество элементов во всей таблице, то вам придётся написать триггеры на удаление/добавление записей в эту таблицу.

WITHOUT OID

В каждой таблице по-умолчанию создаётся int32 колонка, содержащая уникальный в пределах базы данных номер записи (строки).
Часто это не нужно, и впустую тратит место на диске (и время при чтении таблицы с диска). Параметр WITHOUT OID при создании таблицы отключает эту особенность, экономя 4 байта на каждой строке.

EXPLAIN ANALYZE

Если вам кажется, что какой-то запрос выполняется слишком долго, то вы можете добавить к нему префикс EXPLAIN ANALYZE, что позволит вам увидеть последовательность действий Postgre SQL для выполнения вашего запроса, и понять на каком этапе тратится слишком много времени. Обычно это позволяет увидеть

последовательность действий Postgre SQL для выполнения вашего запроса, и понять на каком этапе тратится слишком много времени. Обычно это позволяет увидеть каким образом можно минимальными изменениями кода многократно увеличить производительность, а также выявить свои ошибки в оптимальности составленых запросов.

Транзакции

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

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

Типы индексов

Postgre SQL поддерживает большое количество разных типов индексов, оптимальных для разных задач.

Например индекс типа hash позволят очень быстро выполнять поиск записи по строковому полю, однако не может быть использован для сортировки или поиска по шаблону.

Изучите внимательно документацию на индексы Postgre SQL, это вложение времени себя однозначно окупит.

Большое количество столбцов

Проверяйте насколько оправдано в ваших приложениях использование таблиц с большим количеством столбцов. По возможности выполняйте нормализацию. Это классика, это надо делать не только с Postgre SQL, но... забывают почему-то :)


(C) Денис Смирнов <mithraen@freesource.info> 28 Nov 2004
Размещение этого документа на других Internet-ресурсах, а также в печатных изданиях не допускается.

Отзывы — здесь вы можете высказать своё мнение по поводу содержимого сайта
Ссылок на эту страницу нет