при этом не будет отслеживаться время последнего доступа к файлу
Перенос журнала транзакций на отдельный диск
При доступе к диску изрядное время занимает не только собственно чтение данных, но и перемещение магнитной головки.
Если в вашем сервере есть несколько физических дисков (несколько логических разделов на одном диске здесь, очевидно, не помогут: головка всё равно будет одна), то вы можете разнести файлы базы данных и журнал транзакций по разным дискам. Данные в сегменты журнала пишутся последовательно, более того, записи в журнале транзакций сразу сбрасываются на диск, поэтому в случае нахождения его на отдельном диске магнитная головка не будет лишний раз двигаться, что позволит ускорить запись.
Порядок действий:
Остановите сервер (!);
Перенесите каталоги pg_clog и pg_xlog, находящийся в каталоге с базами данных, на другой диск;
Создайте на старом месте символическую ссылку;
Запустите сервер.
Примерно таким же образом можно перенести и часть файлов, содержащих таблицы и индексы, на другой диск, но здесь потребуется больше кропотливой ручной работы, а при внесении изменений в схему базы процедуру, возможно, придётся повторить.
CLUSTER
CLUSTER table [ USING index ] команда для упорядочивания записей таблицы на диске согласно индексу, что иногда за счет уменьшения доступа к диску ускоряет выполнение запроса. Возможно создать только один физический порядок в таблице, поэтому и таблица может иметь только один кластерный индекс. При таком условии нужно тщательно выбирать, какой индекс будет использоваться для кластерного индекса.
Кластеризация по индексу позволяет сократить время поиска по диску: во время поиска по индексу выборка данных может быть значительно быстрее, так
как последовательность данных в таком же порядке, как и индекс. Из минусов можно отметить то, что команда CLUSTER требует «ACCESS EXCLUSIVE» блокировку, что предотвращает любые другие операции с данными (чтения и записи) пока кластеризация не завершит выполнение. Также кластеризация индекса в PostgreSQL не утверждает четкий порядок следования, поэтому требуется повторно выполнять CLUSTER для поддержания таблицы в порядке.
Утилиты для тюнинга PostgreSQL
Pgtune
Для оптимизации настроек для PostgreSQL Gregory Smith создал утилиту pgtune в расчёте на обеспечение максимальной производительности для заданной аппаратной конфигурации. Утилита проста в использовании и во многих Linux системах может идти в составе пакетов. Если же нет, можно просто скачать архив и распаковать. Для начала:
$ pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune
опцией -i, --input-config указываем текущий файл postgresql.conf, а -o, --output-config указываем имя файла для нового postgresql.conf.
Есть также дополнительные опции для настройки конфига:
-M, --memory используйте этот параметр, чтобы определить общий объем системной памяти. Если не указано, pgtune будет пытаться использовать текущий объем системной памяти;
-T, --type указывает тип базы данных. Опции: DW, OLTP, Web, Mixed, Desktop;
-c, --connections указывает максимальное количество соединений. Если он не указан, то будет браться в зависимости от типа базы данных.
Существует также онлайн версия pgtune.
Хочется сразу добавить, что pgtune не панацея для оптимизации настройки PostgreSQL. Многие настройки зависят не только от аппаратной конфигурации, но и от размера базы данных, числа соединений и сложности запросов, так что оптимально настроить базу данных возможно только учитывая все эти параметры.
pg_buffercache
Pg_buffercache расширение для PostgreSQL, которое позволяет получить представление об использовании общего буфера (shared_buffer) в базе. Расширение позволяет взглянуть какие из данных кэширует база, которые активно используются в запросах. Для начала нужно установить расширение:
# CREATE EXTENSION pg_buffercache;
Теперь доступно pg_buffercache представление, которое содержит:
bufferid ID блока в общем буфере;
relfilenode имя папки, где данные расположены;
reltablespace Oid таблицы;
reldatabase Oid базы данных;
relforknumber номер ответвления;
relblocknumber номер страницы;
isdirty грязная страница?;
usagecount количество LRU страниц.
ID блока в общем буфере (bufferid) соответствует количеству используемого буфера таблицей, индексом, прочим. Общее количество доступных буферов определяется двумя вещами:
Размер буферного блока. Этот размер блока определяется опцией --with-blocksize при конфигурации. Значение по умолчанию 8 КБ, что достаточно в большинстве случаев, но его возможно увеличить или уменьшить в зависимости от ситуации. Для того чтобы изменить это значение, необходимо будет перекомпилировать PostgreSQL;
Размер общего буфера. Определяется опцией shared_buffers в PostgreSQL конфиге.
Например, при использовании shared_buffers в 128 МБ с 8 КБ размера блока получится 16384 буферов. Представление pg_buffercache будет иметь такое же число строк 16384. С shared_buffers в 256 МБ и размером блока в 1 КБ получим 262144 буферов.
Для примера рассмотрим простой запрос показывающий использование буферов объектами (таблицами, индексами, прочим):