MySQL & PostgreSQL: Часть 3. Конфигурация PostgreSQL

Конфигурация, идущая по умолчанию, в большинстве случаев не рассчитана на максимальное использование ресурсов – это справедливо как для PostgreSQL, так и для MySQL. Пути по умолчанию для каталогов можно изменить непосредственно при сборке из исходников. Некоторые параметры постгресу можно передавать из командной строки. Так же можно определить переменную окружения PGOPTIONS, а значения некоторых переменных можно изменить прямо в процессе выполнения запросов.

В предыдущей части цикла речь шла о конфигурировании MySQL – сегодня мы будем говорить о настройке PostgreSQL.

Сергей Яковлев, Консультант, независимый специалист

Яковлев Сергей — независимый разработчик с многолетним опытом прикладного и системного программирования; вносит вклад в развитие open-source на своем персональном сайте www.iakovlev.org. Консультант.



16.01.2012

1. Как конфигурировать PostgreSQL

Сконфигурировать постгрес можно либо из командной строки, либо с помощью конфиг-файла. Из командной строки можно запустить команду postgres:

  postgres -c log_connections=yes -c log_destination='syslog'

Можно изменить переменную окружения:

  env PGOPTIONS='-c geqo=off' psql

Базовый конфигурационный файл для постгрес называется postgresql.conf, его настройка влияет на все базы. В нем находятся 10 основных разделов.

1-й раздел. FILE LOCATIONS – определяются каталоги, в которых лежат данные и конфиги.

  1. data_directory = 'ConfigDir' – каталог для хранения данных. Для изменения расположения еще двух конфигов – pg_hba.conf и pg_ident.conf – можно использовать команду postgres.

2-й раздел. CONNECTIONS AND AUTHENTICATION – задаются настройки для числа коннектов, SSL, TCP.

3-й раздел. RESOURCE USAGE – настройки ресурсов.

  1. shared_buffers – общий буфер в мегабайтах, откуда берутся считанные с диска данные, куда пишутся изменения, которые потом будут сброшены на диск. Минимум – 128 КБ, максимум = max_connections*16 КБ. По умолчанию стоит 32 MБ. Чрезмерное увеличение этого параметра приведет к тому, что ядро перестанет отдавать память.
  2. temp_buffers = 8 MБ (min 800 КБ) – буфер для каждой сессии.
  3. max_prepared_transactions = 5 – максимальное число одновременных транзакций. Его можно сделать равным max_connections. Если вы не используете транзакции, его можно обнулить.
  4. work_mem = 1 MБ (min 64 КБ) – количество памяти для встроенной сортировки и хэширования таблиц (ORDER BY, DISTINCT, JOIN). Каждая сессия, каждая сортировка съедает эту порцию памяти. Предел равен свободной памяти, разделенной на максимальное число одновременных коннектов.
  5. maintenance_work_mem = 16 MБ (min 1 MБ) – максимум памяти, используемый для таких операций, как VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Увеличение параметра может улучшить работу команды VACUUM, а также ускорить восстановление дампа. Этот параметр может быть сопоставим с размером самого большого индекса.
  6. max_files_per_process = 1000 (min 25) – максимальное число открытых файлов для одной сессии.
  7. vacuum_cost_delay = 0 (0–1000 milliseconds) – влияет на частоту выполнения команды VACUUM. Если у вас большие таблицы и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягивая частоту ее выполнения по времени.
  8. bgwriter_delay = 200 ms (10–10000 ms) – в постгресе есть фоновый процесс, который периодически сбрасывает буфер на диск. Этот параметр определяет интервал, на который засыпает этот процесс.
  9. max_fsm_relations = 1000 (min 100) – максимально возможное количество таблиц, для которых будет отслеживаться свободное место, должно быть не меньше общего количества таблиц во всех базах.

4-й раздел. WRITE AHEAD LOG – внутренние настройки синхронизации, коммитов, чекпойнтов, архивация.

  1. checkpoint_segments = 3 – указывает количество транзакций, после которых происходит физический сброс данных на диск, при интенсивной записи параметр можно увеличить.
  2. checkpoint_timeout = 5min – интервал, через который происходит сброс транзакций на диск.
  3. fsync = on – этот параметр можно выключить (off), это разгрузит I/O, при этом транзакции перестанут периодически сбрасываться на диск, что, конечно, рискованно.
  4. synchronous_commit = on – включение/отключение асинхронного механизма транзакций. Рекомендуется отключать этот параметр вместо fsync(), потому что это надежнее. Аналогичный эффект дает команда SET LOCAL synchronous_commit TO OFF внутри самой транзакции, что еще более гибко.
  5. full_page_writes = on – перед тем как записать страницу данных на диск, строится образ записываемой страницы на диске, потом страница сбрасывается на диск. В случае краха потерянная страница будет восстановлена с образа. Это увеличивает нагрузку на I/O, но гарантирует надежность.
  6. wal_buffers = 64 КБ (min 32 КБ) – буфер журнала одной транзакции, по умолчанию 64 КБ.
  7. commit_delay, commit_siblings – параметры временной задержки между попаданием транзакции в буфер и сбросом ее на диск.
  8. archive_mode = off – архивация логов
  9. archive_command = '' – задать внешнюю команду для логирования, например:
        archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'

5-й раздел. QUERY TUNING – конфигурация планировщика запросов.

  1. enable_hashjoin = on ; enable_indexscan = on ;enable_mergejoin = on – включает план запроса для соответствующих типов.
  2. effective_cache_size = 128 MБ – параметр используется для построения плана запроса. Чем больше будет этот параметр, тем меньше вероятность, что оптимизатор при построении плана обратится к диску. Обычно отводят до 50% доступной памяти.
  3. default_statistics_target = 10 – задает объем статистики, собираемой командой ANALYZE. Увеличение параметра может повысить качество планировщика, увеличивая при этом время.

6-й раздел. ERROR REPORTING AND LOGGING – ротация постгрессовских логов, детализация логов.

7-й раздел. RUNTIME STATISTICS – настройка статистики по запросам, индексам.

8-й раздел. AUTOVACUUM PARAMETERS – в постгресе ''мусор'' из таблиц (например, удаленные записи) убирается с помощью команды VACUUM в параллельном процессе.

  1. autovacuum = on – включение.
  2. autovacuum_max_workers = 3 – число процессов.
  3. autovacuum_naptime = 1min – интервал между командами vacuum.

9-й раздел. CLIENT CONNECTION DEFAULTS – настройки по умолчанию для клиентов.

  1. search_path = '"$user",public' – задаёт порядок, в котором производится поиск по схемам, если на объект (таблица, тип данных, функция и т.д.) ссылаются только по имени, без указания компонентов схемы. Таким образом поддерживается разделяемое использование базы данных (когда у пользователей нет индивидуальных схем, все используют разделяемые общие), индивидуальные схемы для пользователей и их сочетания.
  2. default_transaction_isolation = 'read committed' – уровень изоляции по умолчанию. Возможны также 'read uncommitted', 'repeatable read', 'serializable'.
  3. datestyle = 'iso, dmy' – формат вывода даты и времени. Возможны варианты: ISO, Postgres, SQL, German. Для времени – DMY, MDY, YMD.
  4. max_connections = 100 – число одновременных коннектов, слишком большое увеличение может привести к проблемам.

10-й раздел. LOCK MANAGEMENT – настройка блокировок.

  1. deadlock_timeout = 1 s – продолжительность времени в секундах между проверками на дедлок. Для загруженных серверов имеет смысл это значение увеличить.
  2. max_locks_per_transaction = 64 (min 10) – максимум для числа одновременных блокировок.

2. Настройка аутентификации

По умолчанию PostgreSQL настроен так, что каждый локальный пользователь может подсоединиться к базе, совпадающей по названию с регистрационным именем клиента при условии, что такая база данных уже создана. Для настройки доступа к серверу существует конфиг-файл pg_hba.conf . Он состоит из записей, каждая из которых регулирует правила получения доступа для конкретной машины или для целой группы IP. Следующая запись разрешает доступ всем пользователям к базам, совпадающим по названию:

  local all all ident sameuser

Следующая запись разрешает пользователю serg с определенного ip-шника работать с базой test:

  host    test   serg     123.321.123.321 trust

Здесь:

host – возможно сетевое соединение, local – только локальное, trust – полностью доверяем, ident – доступ по записи, md5 – авторизация по зашифрованному паролю.


3. Настройка дисковой системы

Дисковая подсистема является для PostgreSQL определяющей. Поэтому, если есть возможность, то лучше применить именно её.

Для большей производительности имеет смысл разнести файлы базы данных и журнал транзакций по разным дискам. В журнал данные пишутся последовательно, т.е. пишущая головка диска не будет при этом ''скакать''. Перенести журнал на новый раздел можно без перенастройки конфига – нужно остановить сервер, перенести базу, а на старом месте оставить символическую ссылку на новый каталог.

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

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


4. Настройка параллелизма (concurrency)

Ситуация concurrency возникает в том случае, когда две и более сессии пытаются получить доступ к одним и тем же данным. Ключом к управлению этой ситуацией в постгресе является контроль транзакций – Multiversion Concurrency Control (MVCC). Это делается на основе мгновенных снимков состояния базы,так называемых snapshot. Каждая транзакция изолирована внутри своей сессии. При этом достигается разрешение конфликтов блокировок между читающими и пишущими запросами. Хотя блокировки на уровне таблиц и строк никто не отменял, тем не менее использование MVCC дает лучшую производительность.

В стандартном SQL есть четыре уровня изоляции транзакций, которые по-разному обеспечивают следующее поведение.

  1. Транзакция не должна видеть незакомиченных данных от другой транзакции (dirty read).
  2. Одни и те же данные, прочитанные 2 раза подряд внутри одной транзакции, не должны отличаться (nonrepeatable read).
  3. Один и тот же запрос внутри транзакции должен возвращать один и тот же результат (phantom read).

В постгресе фактически реализованы два уровня изоляции – Read Committed и Serializable, остальные два уровня похожи на них. Read Committed – уровень изоляции по умолчанию в постгресе. Это настраивается с помощью конфигурационного параметра default_transaction_isolation = 'read committed'.


5. Мониторинг

В постгресе есть несколько системных таблиц, которые дают полезную информацию о состоянии базы данных: pg_stat_activity, pg_stat_database, pg_stat_user_tables, pg_stat_user_indexes, pg_locks:

select datname,now()-query_start as duration,current_query from
  pg_stat_activity;
select datname, case when blks_read = 0 then 0 else blks_hit /
  blks_read end as ratio from pg_stat_database;
select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100
  else seq_scan / idx_scan end as ratio from pg_stat_user_tables
  order by ratio desc;
select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
  order by n_tup_upd desc;
select indexrelname,idx_tup_read,idx_tup_fetch,case when
  idx_tup_fetch = 0 then 100 else idx_tup_read / idx_tup_fetch end as
  ratio from pg_stat_user_indexes order by ratio desc;
select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables
  order by n_tup_upd desc;
select l.mode,d.datname,c.relname,l.granted,l.transactionid from
  pg_locks as l left join pg_database as d on l.database= d.oid left
  join pg_class as c on l.relation = c.oid;

Заключение

Подведем итоги: конфигурация PostgreSQL отличается от конфигурации MySQL в сторону большей гибкости и соответствия стандартам. Она позволяет настраивать: аутентификацию; использование ресурсов, буферизацию на уровне сервера, сессии, запроса; дефрагментацию базы данных; параметры транзакции – частоту, асинхронность, дублирование; параметры плана запроса – размер буфера, анализатор запроса; клиентские настройки – пространство имен, уровни изоляции, форматы данных, число коннектов и многое другое. Все эти инструменты дают возможность сконфигурировать надежный промышленный сервер баз данных, отвечающий современным стандартам SQL.

В следующей части цикла мы вернемся к MySQL и будем говорить о масштабировании.

Cтатья ссылается на версию PostgreSQL 8.3.

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, Linux
ArticleID=787920
ArticleTitle=MySQL & PostgreSQL: Часть 3. Конфигурация PostgreSQL
publish-date=01162012