11.09.2012
На производительность PostgreSQL оказывает существенное влияние производительность дисковой системы. В конфигурационном файле postgresql.conf есть несколько параметров, значения которых могут оказать существенное влияние на производительность
fsync
По умолчанию, параметр fsync включен. Это означает, что при выполнении операции COMMIT данные сразу переписываются из кеша операционной системы на диск, тем самым гарантируется консистентность при возможном аппаратном сбое. Обратной стороной этого является снижение производительности операций записи на диск, поскольку при этом не используются возможности отложенной записи данных операционной системы.
Отрицательное влияние включенного fsync можно уменьшить, отключив его, положившись на надежность вашего оборудования, или правильно подобрав параметр wal_sync_method - метод, который используется для принудительной записи данных на диск.
Возможные значения:
Не все методы доступны на определенных платформах. Выбор метода зависит от операционной системы под управлением, которой работает PostgreSQL.
В состав PostgreSQL входит утилита pg_test_fsync, с помощью которой можно определить оптимальное значение параметра wal_sync_method.
Она выполняет серию дисковых тестов с использованием различных методов синхронизации. В результате этого теста получаются оценки производительности дисковой системы, по которым можно определить оптимальный метод синхронизации для данной опереционной системы
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 133333.333 ops/sec
fdatasync n/a
fsync 24.124 ops/sec
fsync_writethrough 25.109 ops/sec
open_sync n/a
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 64516.129 ops/sec
fdatasync n/a
fsync 25.266 ops/sec
fsync_writethrough 26.440 ops/sec
open_sync n/a
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write open_sync sizes.)
16kB open_sync write n/a
8kB open_sync writes n/a
4kB open_sync writes n/a
2kB open_sync writes n/a
1kB open_sync writes n/a
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different descriptor.)
write, fsync, close 26.643 ops/sec
write, close, fsync 26.484 ops/sec
Non-Sync'ed 8kB writes:
write 305.623 ops/sec
Из результатов теста можно определить, что для Windows оптимальным решением будет использование open_datasync.
Пример результатов теста для Linux
Копировать в буфер обмена2000 operations per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 5617.741 ops/sec
fdatasync 5266.734 ops/sec
fsync 5301.412 ops/sec
fsync_writethrough n/a
open_sync 5983.080 ops/sec
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 2632.597 ops/sec
fdatasync 3674.546 ops/sec
fsync 3767.003 ops/sec
fsync_writethrough n/a
open_sync 2640.051 ops/sec
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write open_sync sizes.)
16kB open_sync write 4400.682 ops/sec
8kB open_sync writes 2349.649 ops/sec
4kB open_sync writes 1466.995 ops/sec
2kB open_sync writes 730.509 ops/sec
1kB open_sync writes 374.540 ops/sec
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different descriptor.)
write, fsync, close 5048.108 ops/sec
write, close, fsync 5198.964 ops/sec
Non-Sync'ed 8kB writes:
write 115154.307 ops/sec
Из результатов теста можно определить, что для Linux все варианты синхронизации примерно равноценны и проигрывают варианту с отключенной синхронизацией.
Следует учитывать, что в данном тесте использовалась дисковая система, состоящая из одного диска. При использовании RAID массива с большим количеством дисков картина может быть другой.
wal_buffers
Количество памяти используемое в SHARED MEMORY для ведения транзакционных логов. При доступной памяти 1-4GB рекомендуется устанавливать 256-1024kb. Этот параметр стоит увеличивать в системах с большим количеством модификаций таблиц базы данных.
checkpoint_segments
Oпределяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками. Для баз данных со множеством модифицирующих данные транзакций рекомендуется увеличение этого параметра. Критерием достаточности количества сегментов является отсутствие в логе предупреждений (warning) о том, что контрольные точки происходят слишком часто.
full_page_writes
Включение этого параметра гарантирует корректное восстановление, ценой увелечения записываемых данных в журнал транзакций. Отключение этого параметра ускоряет работу, но может привести к повреждению базы данных в случае системного сбоя или отключения питания.
synchronous_commit
Включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера. Если не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы, но важно обеспечить большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).
Еще одним способом увеличения производительности работы PostgreSQL является отделение перенос журнала транзакций(pg_xlog) на другой диск. Выделение для журнала транзакций отдельного дискового ресурса позволяет получить получить при этом существенный выигрыш в производительности 10%-12% для нагруженных OLTP систем.
Для Linux это делается с помощью создания символической ссылки на новое положение каталога с журналом транзакций.
Для Windows можно использовать для этих целей утилиту junction.
Для этого надо:
1. Остановить postgresql.
2. Сделать бэкап C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog.
3. Скопировать C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog в D:\pg_xlog и удалить C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog.
4. Распаковать программу junction в C:\Program Files\PostgreSQL\X.X.X\data.
5. Открыть окно cmd, перейти в C:\Program Files\PostgreSQL\X.X.X\data и выполнить junction -s pg_xlog D:\pg_xlog.
6. Установить права на папку D:\pg_xlog пользователю postgres.
7. Запустить postgresql.