=0.1


Васильев А.Ю.



Работа с Postgresql
настройка, масштабирование



справочное пособие

2010
CREATIVE COMMONS ATTRIBUTION-NONCOMMERCIAL 2.5


При написании книги(мануала, или просто шпаргалки) использовались материалы:

ell

Contents

1 Введение


\begin{epigraphs}
\qitem{Послушайте~--- и Вы забудете, по...
... сделайте~--- и Вы поймете.}{Конфуций}
\end{epigraphs}

Данная книга не дает ответы на все вопросы по работе с PostgreSQL. Главное её задание -- показать возможности PostgreSQL, методики настройки и масштабируемости этой СУБД. В любом случае, выбор метода решения поставленной задачи остается за разработчиком или администратором СУБД.

2 Настройка производительности


\begin{epigraphs}
\qitem{Теперь я знаю тысячу способов,...
...пу накаливания.}{Томас Алва Эдисон}
\end{epigraphs}

1 Введение

Скорость работы, вообще говоря, не является основной причиной использования реляционных СУБД. Более того, первые реляционные базы работали медленнее своих предшественников. Выбор этой технологии был вызван скорее

Эти особенности позволяют сильно упростить написание приложений, но требуют для своей реализации дополнительных ресурсов.

Таким образом, прежде, чем искать ответ на вопрос «как заставить РСУБД работать быстрее в моей задаче?» следует ответить на вопрос «нет ли более подходящего средства для решения моей задачи, чем РСУБД?» Иногда использование другого средства потребует меньше усилий, чем настройка производительности.

Данная глава посвящена возможностям повышения производительности PostgreSQL. Глава не претендует на исчерпывающее изложение вопроса, наиболее полным и точным руководством по использованию PostgreSQL является, конечно, официальная документация и официальный FAQ. Также существует англоязычный список рассылки postgresql-performance, посвящённый именно этим вопросам. Глава состоит из двух разделов, первый из которых ориентирован скорее на администратора, второй -- на разработчика приложений. Рекомендуется прочесть оба раздела: отнесение многих вопросов к какому-то одному из них весьма условно.

1 Не используйте настройки по умолчанию

По умолчанию PostgreSQL сконфигурирован таким образом, чтобы он мог быть запущен практически на любом компьютере и не слишком мешал при этом работе других приложений. Это особенно касается используемой памяти. Настройки по умолчанию подходят только для следующего использования: с ними вы сможете проверить, работает ли установка PostgreSQL, создать тестовую базу уровня записной книжки и потренироваться писать к ней запросы. Если вы собираетесь разрабатывать (а тем более запускать в работу) реальные приложения, то настройки придётся радикально изменить. В дистрибутиве PostgreSQL, к сожалению, не поставляется файлов с «рекомендуемыми» настройками. Вообще говоря, такие файлы создать весьма сложно, т.к. оптимальные настройки конкретной установки PostgreSQL будут определяться:

2 Используйте актуальную версию сервера

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

3 Стоит ли доверять тестам производительности

Перед тем, как заниматься настройкой сервера, вполне естественно ознакомиться с опубликованными данными по производительности, в том числе в сравнении с другими СУБД. К сожалению, многие тесты служат не столько для облегчения вашего выбора, сколько для продвижения конкретных продуктов в качестве «самых быстрых». При изучении опубликованных тестов в первую очередь обратите внимание, соответствует ли величина и тип нагрузки, объём данных и сложность запросов в тесте тому, что вы собираетесь делать с базой? Пусть, например, обычное использование вашего приложения подразумевает несколько одновременно работающих запросов на обновление к таблице в миллионы записей. В этом случае СУБД, которая в несколько раз быстрее всех остальных ищет запись в таблице в тысячу записей, может оказаться не лучшим выбором. Ну и наконец, вещи, которые должны сразу насторожить:

2 Настройка сервера

В этом разделе описаны рекомендуемые значения параметров, влияющих на производительность СУБД. Эти параметры обычно устанавливаются в конфигурационном файле postgresql.conf и влияют на все базы в текущей установке.

1 Используемая память

1 Общий буфер сервера: shared_buffers

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

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

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

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

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

На выделенных серверах полезным объемом будет значение от 8 МБ до 2 ГБ. Объем может быть выше, если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров. И, конечно же, не забываем об остальных приложениях. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности. В качестве начальных значений можете попробовать следующие:

Для тонкой настройки параметра установите для него большое значение и потестируйте базу при обычной нагрузке. Проверяйте использование разделяемой памяти при помощи ipcs или других утилит(например, free или vmstat). Рекомендуемое значение параметра будет примерно в 1,2 -2 раза больше, чем максимум использованной памяти. Обратите внимание, что память под буфер выделятся при запуске сервера, и её объём при работе не изменяется. Учтите также, что настройки ядра операционной системы могут не дать вам выделить большой объём памяти. В руководстве администратора PostgreSQL описано, как можно изменить эти настройки:
http://developer.postgresql.org/docs/postgres/kernel-resources.html

Вот несколько примеров, полученных на личном опыте и при тестировании:

2 Память для сортировки результата запроса: work_mem

Ранее известное как sort_mem, было переименовано, так как сейчас определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др. Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.

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

Объём памяти задаётся параметром work_mem в файле postgresql.conf. Единица измерения параметра -- 1 кБ. Значение по умолчанию -- 1024. В качестве начального значения для параметра можете взять 2-4% доступной памяти. Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ. С другой стороны, приложения для поддержки принятия решений с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 МБ. Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии. Например, при памяти 1-4 ГБ рекомендуется устанавливать 32-128 MB.

3 Память для работы команды VACUUM: maintenance_work_mem

Предыдущее название в PostgreSQL 7.x vacuum_mem. Этот параметр задаёт объём памяти, используемый командами VACUUM, ANALYZE, CREATE INDEX, и добавления внешних ключей. Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей базе данных. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа. Например, при памяти 1-4 ГБ рекомендуется устанавливать 128-512 MB.

4 Free Space Map: как избавиться от VACUUM FULL

Особенностями версионных движков БД (к которым относится и используемый в PostgreSQL) является следующее: В каждой СУБД сборка мусора реализована особым образом, в PostgreSQL для этой цели применяется команда VACUUM (описана в пункте 3.1.1).

До версии 7.2 команда VACUUM полностью блокировала таблицу. Начиная с версии 7.2, команда VACUUM накладывает более слабую блокировку, позволяющую параллельно выполнять команды SELECT, INSERT, UPDATE и DELETE над обрабатываемой таблицей. Старый вариант команды называется теперь VACUUM FULL.

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

Если эти параметры установленны верно и информация обо всех изменениях помещается в FSM, то команды VACUUM будет достаточно для сборки мусора, если нет - понадобится VACUUM FULL, во время работы которой нормальное использование БД сильно затруднено.

ВНИМАНИЕ! Начиная с 8.4 версии fsm параметры были убраны, поскольку Free Space Map сохраняется на жесткий диск, а не в память.

5 Прочие настройки

2 Журнал транзакций и контрольные точки

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

В этом случае нет необходимости сбрасывать на диск изменения данных при каждом успешном завершении транзакции: в случае сбоя БД может быть восстановлена по записям в журнале. Таким образом, данные из буферов сбрасываются на диск при проходе контрольной точки: либо при заполнении нескольких (параметр checkpoint_segments, по умолчанию 3) сегментов журнала транзакций, либо через определённый интервал времени (параметр checkpoint_timeout, измеряется в секундах, по умолчанию 300).

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

1 Уменьшение количества контрольных точек: checkpoint_segments

Если в базу заносятся большие объёмы данных, то контрольные точки могут происходить слишком часто2. При этом производительность упадёт из-за постоянного сбрасывания на диск данных из буфера.

Для увеличения интервала между контрольными точками нужно увеличить количество сегментов журнала транзакций (checkpoint_segments). Данный параметр определяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками. Этот параметр не имеет особого значения для базы данных, предназначенной преимущественно для чтения, но для баз данных со множеством транзакций увеличение этого параметра может оказаться жизненно необходимым. В зависимости от объема данных установите этот параметр в диапазоне от 12 до 256 сегментов и, если в логе появляются предупреждения (warning) о том, что контрольные точки происходят слишком часто, постепенно увеличивайте его. Место, требуемое на диске, вычисляется по формуле (checkpoint_segments * 2 + 1) * 16 МБ, так что убедитесь, что у вас достаточно свободного места. Например, если вы выставите значение 32, вам потребуется больше 1 ГБ дискового пространства.

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

2 fsync и стоит ли его трогать

Наиболее радикальное из возможных решений -- выставить значение «off» параметру fsync. При этом записи в журнале транзакций не будут принудительно сбрасываться на диск, что даст большой прирост скорости записи. Учтите: вы жертвуете надёжностью, в случае сбоя целостность базы будет нарушена, и её придётся восстанавливать из резервной копии!

Использовать этот параметр рекомендуется лишь в том случае, если вы всецело доверяете своему «железу» и своему источнику бесперебойного питания. Ну или если данные в базе не представляют для вас особой ценности.

3 Прочие настройки

3 Планировщик запросов

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

4 Сбор статистики

У PostgreSQL также есть специальная подсистема -- сборщик статистики, -- которая в реальном времени собирает данные об активности сервера. Поскольку сбор статистики создает дополнительные накладные расходы на базу данных, то система может быть настроена как на сбор, так и не сбор статистики вообще. Эта система контролируется следующими параметрами, принимающими значения true/false:

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

3 Диски и файловые системы

Очевидно, что от качественной дисковой подсистемы в сервере БД зависит немалая часть производительности. Вопросы выбора и тонкой настройки «железа», впрочем, не являются темой данной статьи, ограничимся уровнем файловой системы.

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

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

1 Перенос журнала транзакций на отдельный диск

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

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

Порядок действий:

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

4 Примеры настроек

1 Среднестатистическая настройка для максимальной производительности

Возможно для конкретного случаю лучше подойдут другие настройки. Внимательно изучите данное руководство и настройте PostgreSQL операясь на эту информацию.

RAM -- размер памяти;

2 Среднестатистическая настройка для оконного приложения (1С), 2 ГБ памяти

3 Среднестатистическая настройка для Web приложения, 2 ГБ памяти

4 Среднестатистическая настройка для Web приложения, 8 ГБ памяти

5 Автоматическое создание оптимальных настроек: pgtune

Для оптимизации настроек для PostgreSQL Gregory Smith создал утилиту pgtune7 в расчете на обеспечение максимальной производительности для заданной аппаратной конфигурации. Утилита проста в использовании и в многих Linux системах может идти в составе пакетов. Если же нет, можно просто скачать архив и распаковать. Для начала:
\begin{lstlisting}[label=lst:p_settings1,caption=Pgtune]
pgtune -i $PGDATA/postgresql.conf \
-o $PGDATA/postgresql.conf.pgtune
\end{lstlisting}
опцией [frame=tblr]-i, -input-config указываем текущий файл postgresql.conf, а [frame=tblr]-o, -output-config указываем имя файла для нового postgresql.conf.

Есть также дополнительные опции для настройки конфига.

Хочется сразу добавить, что pgtune не панацея для оптимизации настройки PostgreSQL. Многие настройки зависят не только от аппаратной конфигурации, но и от размера базы данных, числа соединений и сложность запросов, так что оптимально настроить базу данных возможно учитывая все эти параметры.

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

Для быстрой работы каждого запроса в вашей базе в основном требуется следующее:
  1. Отсутствие в базе мусора, мешающего добраться до актуальных данных. Можно сформулировать две подзадачи:
    1. Грамотное проектирование базы. Освещение этого вопроса выходит далеко за рамки этой статьи.
    2. Сборка мусора, возникающего при работе СУБД.
  2. Наличие быстрых путей доступа к данным -- индексов.
  3. Возможность использования оптимизатором этих быстрых путей.
  4. Обход известных проблем.

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

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

1 Команда ANALYZE

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

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

2 Команда REINDEX

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

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

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

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

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

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

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

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

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

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

SET enable_seqscan=false;

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

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

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

Из этих представлений можно узнать, в частности

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

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

Функциональные индексы Вы можете построить индекс не только по полю/нескольким полям таблицы, но и по выражению, зависящему от полей. Пусть, например, в вашей таблице 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)) = 'ы';
будет его использовать.

Частичные индексы (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 Перенос логики на сторону сервера

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

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

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

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

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

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

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

Проблема Запрос вида
\begin{lstlisting}[language=SQL,label=lst:sql_performance1,caption=SQL]
SELECT count(*) FROM foo;
\end{lstlisting}
осуществляет полный просмотр таблицы foo, что весьма долго для таблиц с большим количеством записей.

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

  1. Если точное число записей не важно, а важен порядок10, то можно использовать информацию о количестве записей в таблице, собранную при выполнении команды ANALYZE:
    \begin{lstlisting}[language=SQL,label=lst:sql_performance2,caption=SQL]
SELECT reltuples FROM pg_class WHERE relname = 'foo';
\end{lstlisting}
  2. Если подобные выборки выполняются часто, а изменения в таблице достаточно редки, то можно завести вспомогательную таблицу, хранящую число записей в основной. На основную же таблицу повесить триггер, который будет уменьшать это число в случае удаления записи и увеличивать в случае вставки. Таким образом, для получения количества записей потребуется лишь выбрать одну запись из вспомогательной таблицы.
  3. Вариант предыдущего подхода, но данные во вспомогательной таблице обновляются через определённые промежутки времени (cron).

2 Медленый DISTINCT

Текущая реализация DISTINCT для больших таблиц очень медленна. Но возможно использовать GROUP BY взамен DISTINCT. GROUP BY может использовать агрегирующий хэш, что значительно быстрее, чем DISTINCT.


\begin{lstlisting}[language=SQL,label=lst:sql_performance3,caption=DISTINCT]
pos...
... from g) a;
count
-------
19125
(1 row)
\par
Time: 36,281 ms
\end{lstlisting}


\begin{lstlisting}[language=SQL,label=lst:sql_performance4,caption=GROUP BY]
pos...
... i) a;
count
-------
19125
(1 row)
\par
Time: 25,270 ms
\par
\end{lstlisting}

5 Оптимизация запросов с помощью pgFouine

pgFouine11 -- это анализатор log-файлов для PostgreSQL, используемый для генерации детальных отчетов из log-файлов PostgreSQL. pgFouine поможет определить, какие запросы следует оптимизировать в первую очередь. pgFouine написан на языке программирования PHP с использованием объектно-ориентированных технологий и легко расширяется для поддержки специализированных отчетов, является свободным программным обеспечением и распространяется на условиях GNU General Public License. Утилита спроектирована таким образом, чтобы обработка очень больших log-файлов не требовала много ресурсов.

Для работы с pgFouine сначала нужно сконфигурировать PostgreSQL для создания нужного формата log-файлов:

Для записи каждого обработанного запроса установите log_min_duration_statement на 0. Чтобы отключить запись запросов, установите этот параметр на -1.

pgFouine -- простой в использовании инструмент командной строки. Следующая команда создаёт HTML-отчёт со стандартными параметрами:
\begin{lstlisting}[label=lst:sql_performance7,caption=pgFouine]
pgfouine.php -file your/log/file.log > your-report.html
\end{lstlisting}

С помощью этой строки можно отобразить текстовый отчёт с 10 запросами на каждый экран на стандартном выводе:
\begin{lstlisting}[label=lst:sql_performance8,caption=pgFouine]
pgfouine.php -file your/log/file.log -top 10 -format text
\end{lstlisting}

Более подробно о возможностях, а также много полезных примеров, можно найти на официальном сайта проекта -- http://pgfouine.projects.postgresql.org.

7 Заключение

К счастью, PostgreSQL не требует особо сложной настройки. В большинстве случаев вполне достаточно будет увеличить объём выделенной памяти, настроить периодическое поддержание базы в порядке и проверить наличие необходимых индексов. Более сложные вопросы можно обсудить в специализированном списке рассылки.

3 Партиционирование


\begin{epigraphs}
\qitem{Решая какую-либо проблему, все...
...самой проблемы.}{Народная мудрость}
\end{epigraphs}

1 Введение

Партиционирование (partitioning, секционирование) -- это разбиение больших структур баз данных (таблицы, индексы) разбить на меньшие кусочки. Звучит сложно, но на практике все просто.

Скорее всего у Вас есть несколько огромных таблиц (обычно всю нагрузку обеспечивают всего несколько таблиц СУБД из всех имеющихся). Причем чтение в большинстве случаев приходится только на самую последнюю их часть (т.е. активно читаются те данные, которые недавно появились). Примером тому может служить блог -- на первую страницу (это последние 5...10 постов) приходится 40...50% всей нагрузки, или новостной портал (суть одна и та же), или системы личных сообщений… впрочем понятно. Партиционирование таблицы позволяет базе данных делать интеллектуальную выборку -- сначала СУБД уточнит, какой партиции соответствует Ваш запрос (если это реально) и только потом сделает этот запрос, применительно к нужной партиции (или нескольким партициям). Таким образом, в рассмотренном случае, Вы распределите нагрузку на таблицу по ее партициям. Следовательно выборка типа «SELECT * FROM articles ORDER BY id DESC LIMIT 10» будет выполняться только над последней партицией, которая значительно меньше всей таблицы.

Итак, партиционирование дает ряд преимуществ:

2 Теория

На текущий момент PostgreSQL поддерживает два критерия для создания партиций:

Чтобы настроить партиционирование таблици, достаточно выполните следующие действия:

3 Практика использования

Теперь начнем с практического примера. Представим, что в нашей системе есть таблица, в которую мы собираем данные о посещаемости нашего ресурса. На любой запрос пользователя наша система логирует действия в эту таблицу. И, например, в начале каждого месяца (неделю) нам нужно создавать отчет за предыдущий месяц (неделю). При этом, логи нужно хранить в течении 3 лет. Данные в такой таблице накапливаются быстро, если система активно используется. И вот, когда таблица уже с милионами, а то, и милиардами записей, создавать отчеты становится все сложнее (да и чистка старых записей становится не легким делом). Работа с такой таблицей создает огромную нагрузку на СУБД. Тут нам на помощь и приходит партиционирование.

1 Настройка

Для примера, мы имеем следующию таблицу:
\begin{lstlisting}[language=SQL,label=lst:partitioning2,caption=<<Мастер>>...
...LL,
logdate TIMESTAMP NOT NULL,
data TEXT,
some_state INT
);
\end{lstlisting}

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

«Мастер» таблица будет «my_logs», структуру которой мы указали выше. Далее создадим «дочерние» таблици (партиции):
\begin{lstlisting}[language=SQL,label=lst:partitioning3,caption=<<Дочерни...
...-01-01' AND logdate < DATE '2010-02-01' )
) INHERITS (my_logs);
\end{lstlisting}

Данными командами мы создаем таблицы «my_logs2010m10», «my_logs2010m11» и т.д., которые копируют структуру с «мастер» таблици (кроме индексов). Также с помощью «CHECK» мы задаем диапазон значений, который будет попадать в эту партицию (хочу опять напомнить, что диапазоны значений партиций не должны пересекатся!). Поскольку партиционирование будет работать по полю «logdate», мы создадим индекс на это поле на всех партициях:
\begin{lstlisting}[language=SQL,label=lst:partitioning4,caption=Создание...
...REATE INDEX my_logs2011m01_logdate ON my_logs2011m01 (logdate);
\end{lstlisting}

Далее для удобства создадим функцию, которая будет перенаправлять новые данные с «мастер» таблици в соответствующую партицию.
\begin{lstlisting}[language=SQL,label=lst:partitioning5,caption=Функция ...
...
END IF;
RETURN NULL;
END;
\end{displaymath}LANGUAGE plpgsql;
\end{lstlisting}

В функции ничего особенного нет: идет проверка поля «logdate», по которой направляются данные в нужную партицию. При не нахождении требуемой партиции -- вызываем ошибку. Теперь осталось создать триггер на «мастер» таблицу для автоматического вызова данной функции:
\begin{lstlisting}[language=SQL,label=lst:partitioning6,caption=Триггер]
...
..._logs
FOR EACH ROW EXECUTE PROCEDURE my_logs_insert_trigger();
\end{lstlisting}

Партиционирование настроено и теперь мы готовы приступить к тестированию.

2 Тестирование

Для начала добавим данные в нашу таблицу «my_logs»:
\begin{lstlisting}[language=SQL,label=lst:partitioning7,caption=Данные]
IN...
...ta, some_state) VALUES(1, '2010-12-15', '15.12.2010 data3', 1);
\end{lstlisting}

Теперь проверим где они хранятся:
\begin{lstlisting}[language=SQL,label=lst:partitioning8,caption=<<Мастер>>...
...me_state
----+---------+---------+------+------------
(0 rows)
\end{lstlisting}
Как видим в «мастер» таблицу данные не попали -- она чиста. Теперь проверим а есть ли вообще данные:
\begin{lstlisting}[language=SQL,label=lst:partitioning9,caption=Проверка...
...ert 2010-12-15 00:00:00 \vert 15.12.2010 data3 \vert 1
(3 rows)
\end{lstlisting}

Данные при этом выводятся без проблем. Проверим партиции, правильно ли хранятся данные:
\begin{lstlisting}[language=SQL,label=lst:partitioning10,caption=Проверк...
...vert 2010-11-10 00:00:00 \vert 10.11.2010 data2 \vert 1
(1 row)
\end{lstlisting}

Отлично! Данные хранятся на требуемых нам партициях. При этом запросы к таблице «my_logs» менять не нужно:
\begin{lstlisting}[language=SQL,label=lst:partitioning11,caption=Проверк...
...ert 2010-11-10 00:00:00 \vert 10.11.2010 data2 \vert 1
(2 rows)
\end{lstlisting}

3 Управление партициями

Обычно при работе с партиционированием старые партиции перестают получать данные и остаются неизменными. Это дает огоромное приемущество над работай с данными через партиции. Например, нам нужно удалить старые логи за 2008 год, 10 месяц. Нам достаточно выполить:
\begin{lstlisting}[language=SQL,label=lst:partitioning12,caption=Чистка логов]
DROP TABLE my_logs2008m10;
\end{lstlisting}
поскольку «DROP TABLE» работает гораздо быстрее, чем удаление милионов записей индивидуально через «DELETE». Другой вариант, который более предпочтителен, просто удалить партицию из партиционирования, тем самым оставив данные в СУБД, но уже не доступные через «мастер» таблицу:
\begin{lstlisting}[language=SQL,label=lst:partitioning13,caption=Удаляем ...
...рования]
ALTER TABLE my_logs2008m10 NO INHERIT my_logs;
\end{lstlisting}
Это удобно, если мы хотим эти данные потом перенести в другое хранилище или просто сохранить.

4 Важность «constraint_exclusion» для партиционирования

Параметр «constraint_exclusion» отвечает за оптимизацию запросов, что повышает производительность для партиционированых таблиц. Например, выпоним простой запрос:
\begin{lstlisting}[language=SQL,label=lst:partitioning14,caption=<<constraint\_e...
...> '2010-12-01 00:00:00'::timestamp without time zone)
(22 rows)
\end{lstlisting}

Как видно через команду «EXPLAIN», данный запрос сканирует все партиции на наличие данных в них, что не логично, поскольку данное условие «logdate > 2010-12-01» говорит о том, что данные должны братся только с партицый, где подходит такое условие. А теперь включим «constraint_exclusion»:
\begin{lstlisting}[language=SQL,label=lst:partitioning15,caption=<<constraint\_e...
...> '2010-12-01 00:00:00'::timestamp without time zone)
(10 rows)
\end{lstlisting}

Как мы видим, теперь запрос работает правильно, и сканирует только партиции, что подходят под условие запроса. Но включать «constraint_exclusion» не желательно для баз, где нет партиционирования, поскольку команда «CHECK» будет проверятся на всех запросах, даже простых, а значит производительность сильно упадет. Начиная с 8.4 версии PostgreSQL «constraint_exclusion» может быть «on», «off» и «partition». По умолчанию (и рекомендуется) ставить «constraint_exclusion» не «on», и не «off», а «partition», который будет проверять «CHECK» только на партиционированых таблицах.

4 Заключение

Партиционирование -- одна из самых простых и менее безболезненных методов уменьшения нагрузки на СУБД. Именно на этот вариант стоит посмотреть сперва, и если он не подходит по каким либо причинам -- переходить к более сложным. Но если в системе есть таблица, у которой актуальны только новые данные, но огромное количество старых (не актуальных) данных дает 50% или более нагрузки на СУБД -- Вам стоит внедрить партиционированию.

4 Репликация


\begin{epigraphs}
\qitem{Когда решаете проблему, ни о ч...
...спокоиться.}{Ричард Филлипс Фейман}
\end{epigraphs}

1 Введение

Репликация (англ. replication) -- механизм синхронизации содержимого нескольких копий объекта (например, содержимого базы данных). Репликация -- это процесс, под которым понимается копирование данных из одного источника на множество других и наоборот. При репликации изменения, сделанные в одной копии объекта, могут быть распространены в другие копии. Репликация может быть синхронной или асинхронной.

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

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

Рассмотрим кратко проблему согласованности (или, скорее, несогласованности). Дело в том, что реплики могут становиться несовместимыми в результате ситуаций, которые трудно (или даже невозможно) избежать и последствия которых трудно исправить. В частности, конфликты могут возникать по поводу того, в каком порядке должны применяться обновления. Например, предположим, что в результате выполнения транзакции А происходит вставка строки в реплику X, после чего транзакция B удаляет эту строку, а также допустим, что Y -- реплика X. Если обновления распространяются на Y, но вводятся в реплику Y в обратном порядке (например, из-за разных задержек при передаче), то транзакция B не находит в Y строку, подлежащую удалению, и не выполняет своё действие, после чего транзакция А вставляет эту строку. Суммарный эффект состоит в том, что реплика Y содержит указанную строку, а реплика X -- нет.

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

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

Для репликации PostgreSQL существует несколько решений, как закрытых, так и свободных. Закрытые системы репликации не будут рассматриваться в этой книге (ну, сами понимаете). Вот список свободных решений:

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

2 Bucardo

1 Введение

Bucardo -- асинхронная master-master или master-slave репликация PostgreSQL, которая написана на Perl. Система очень гибкая, поддерживает несколько видов синхронизации и обработки конфликтов.

2 Установка

Установку будем проводить на Ubuntu Server. Сначала нам нужно установить DBIx::Safe Perl модуль.
\begin{lstlisting}[label=lst:bucardo1,caption=Установка]
sudo aptitude install libdbix-safe-perl
\end{lstlisting}

Для других систем можно поставить из исходников21:
\begin{lstlisting}[label=lst:bucardo2,caption=Установка]
tar xvfz DBIx-...
...e-1.2.5
perl Makefile.PL
make && make test && sudo make install
\end{lstlisting}

Теперь ставим сам Bucardo. Скачиваем22 его и инсталируем:
\begin{lstlisting}[label=lst:bucardo3,caption=Установка]
tar xvfz Bucar...
...tar.gz
cd Bucardo-4.4.0
perl Makefile.PL
make
sudo make install
\end{lstlisting}

Для работы Bucardo потребуется установить поддержку pl/perlu языка PostgreSQL.
\begin{lstlisting}[label=lst:bucardo4,caption=Установка]
sudo aptitude install postgresql-plperl-8.4
\end{lstlisting}

Можем приступать к настройке.

3 Настройка

1 Инициализация Bucardo

Запускаем установку командой:
\begin{lstlisting}[label=lst:bucardo5,caption=Инициализация Bucardo]
bucardo_ctl install
\end{lstlisting}

Bucardo покажет настройки подключения к PostgreSQL, которые можно будет изменить:
\begin{lstlisting}[label=lst:bucardo6,caption=Инициализация Bucardo...
...stgres
4. Database: postgres
5. PID directory: /var/run/bucardo
\end{lstlisting}

Когда вы измените требуемые настройки и подтвердите установку, Bucardo создаст пользователя bucardo и базу данных bucardo. Данный пользователь должен иметь право логинится через Unix socket, поэтому лучше заранее дать ему такие права в pg_hda.conf.

2 Настройка баз данных

Теперь нам нужно настроить базы данных, с которыми будет работать Bucardo. Пусть у нас будет master_db и slave_db. Сначала настроим мастер:
\begin{lstlisting}[label=lst:bucardo7,caption=Настройка баз данн...
...s herd=all_tables
bucardo_ctl add all sequences herd=all_tables
\end{lstlisting}

Первой командой мы указали базу данных и дали ей имя master (для того, что в реальной жизни master_db и slave_db имеют одинаковое название и их нужно Bucardo отличать). Второй и третей командой мы указали реплицыровать все таблицы и последовательности, обьеденив их в групу all_tables.

Дальше добавляем slave_db:
\begin{lstlisting}[label=lst:bucardo8,caption=Настройка баз данн...
...ardo_ctl add db slave_db name=replica port=6543 host=slave_host
\end{lstlisting}

Мы назвали replica базу данных в Bucardo.

3 Настройка синхронизации

Теперь нам нужно настроить синхронизацию между этими базами данных. Делается это командой (master-slave):
\begin{lstlisting}[label=lst:bucardo9,caption=Настройка синхрон...
...dd sync delta type=pushdelta source=all_tables targetdb=replica
\end{lstlisting}

Данной командой мы установим Bucardo тригеры в PostgreSQL. А теперь по параметрам:

Для master-master:
\begin{lstlisting}[label=lst:bucardo10,caption=Настройка синхрон...
...ctl add sync delta type=swap source=all_tables targetdb=replica
\end{lstlisting}

4 Запуск/Остановка репликации

Запуск репликации:
\begin{lstlisting}[label=lst:bucardo11,caption=Запуск репликации]
bucardo_ctl start
\end{lstlisting}

Остановка репликации:
\begin{lstlisting}[label=lst:bucardo12,caption=Остановка репликации]
bucardo_ctl stop
\end{lstlisting}

4 Общие задачи

1 Просмотр значений конфигурации

Просто используя эту команду:
\begin{lstlisting}[label=lst:bucardo13,caption=Просмотр значений конфигурации]
bucardo_ctl show all
\end{lstlisting}

2 Изменения значений конфигурации


\begin{lstlisting}[label=lst:bucardo14,caption=Изменения значений конфигурациии]
bucardo_ctl set name=value
\end{lstlisting}

Например:
\begin{lstlisting}[label=lst:bucardo15,caption=Изменения значени...
...фигурации]
bucardo_ctl set syslog_facility=LOG_LOCAL3
\end{lstlisting}

3 Перегрузка конфигурации


\begin{lstlisting}[label=lst:bucardo16,caption=Перегрузка конфигурации]
bucardo_ctl reload_config
\end{lstlisting}

Более полный список команд -- http://bucardo.org/wiki/Bucardo_ctl

3 Заключение

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

В главе было рассмотрено несколько видов репликации PostgreSQL. Нельзя четко сказать какая лучше всех. Потоковая репликация -- одна из самых лучших вариантов для поддержки идентичных кластеров баз данных, но доступна только с 9.0 версии PostgreSQL. Slony-I -- громоздкая и сложная в настройке система, но имеющая в своем арсенале множество функций, таких как поддержка каскадной репликации, отказоустойчивости (failover) и переключение между серверами (switchover). В тоже время Londiste не обладает подобным функционалом, но компактный и прост в установке. Bucardo -- система которая может быть или master-master, или master-slave репликацией, но не может обработать огромные обьекты, нет отказоустойчивости(failover) и переключение между серверами (switchover). RubyRep, как для master-master репликации, очень просто в установке и настройке, но за это ему приходится расплачиватся скоростью работы -- самый медленный из всех (синхронизация больших обьемов данных между таблицами).

5 Шардинг


\begin{epigraphs}
\qitem{Если ешь слона, не пытайся зап...
...о в рот целиком.}{Народная мудрость}
\end{epigraphs}

1 Введение

Шардинг -- разделение данных на уровне ресурсов. Концепция шардинга заключается в логическом разделении данных по различным ресурсам исходя из требований к нагрузке.

Рассмотрим пример. Пусть у нас есть приложение с регистрацией пользователей, которое позволяет писать друг другу личные сообщения. Допустим оно очень популярно и много людей им пользуются ежедневно. Естественно, что таблица с личными сообщениями будет намного больше всех остальных таблиц в базе (скажем, будет занимать 90% всех ресурсов). Зная это, мы можем подготовить для этой (только одной!) таблицы выделенный сервер помощнее, а остальные оставить на другом (послабее). Теперь мы можем идеально подстроить сервер для работы с одной специфической таблицей, постараться уместить ее в память, возможно, дополнительно партиционировать ее и т.д. Такое распределение называется вертикальным шардингом.

Что делать, если наша таблица с сообщениями стала настолько большой, что даже выделенный сервер под нее одну уже не спасает. Необходимо делать горизонтальный шардинг -- т.е. разделение одной таблицы по разным ресурсам. Как это выглядит на практике? Все просто. На разных серверах у нас будет таблица с одинаковой структурой, но разными данными. Для нашего случая с сообщениями, мы можем хранить первые 10 миллионов сообщений на одном сервере, вторые 10 - на втором и т.д. Т.е. необходимо иметь критерий шардинга -- какой-то параметр, который позволит определять, на каком именно сервере лежат те или иные данные.

Обычно, в качестве параметра шардинга выбирают ID пользователя (user_id) -- это позволяет делить данные по серверам равномерно и просто. Т.о. при получении личных сообщений пользователей алгоритм работы будет такой:

Задачу определения конкретного сервера можно решать двумя путями:

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

Естественно, делая горизонтальный шардинг, Вы ограничиваете себя в возможности выборок, которые требуют пересмотра всей таблицы (например, последние посты в блогах людей будет достать невозможно, если таблица постов шардится). Такие задачи придется решать другими подходами. Например, для описанного примера, можно при появлении нового поста, заносить его ID в общий стек, размером в 100 элементом.

Горизонтальный шардинг имеет одно явное преимущество -- он бесконечно масштабируем. Для создания шардинга PostgreSQL существует несколько решений:

2 Заключение

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

6 Мультиплексоры соединений


\begin{epigraphs}
\qitem{Если сразу успеха не добились,...
...о упорствовать?}{Уильям Клод Филдс}
\end{epigraphs}

1 Введение

Мультиплексоры соединений(программы для создания пула коннектов) позволяют уменьшить накладные расходы на базу данных, когда огромное количество физических соединений тянет производительность PostgreSQL вниз. Это особенно важно на Windows, когда система ограничивает большое количество соединений. Это также важно для веб-приложений, где количество соединений может быть очень большим.

Программы, которые создают пулы соединений:

Также некоторые администраторы PostgreSQL с успехом используют Memcached для уменьшения работы БД за счет кэширования данных.

2 PgBouncer

Это мультиплексор соединений для PostgreSQL от компании Skype. Существуют три режима управления.

К достоинствам PgBouncer относится:

Базовая утилита запускается так:
\begin{lstlisting}[label=lst:pgbouncer1,caption=PgBouncer]
pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
\end{lstlisting}

Простой пример для конфига:
\begin{lstlisting}[label=lst:pgbouncer2,caption=PgBouncer]
[databases]
template1...
... = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
\end{lstlisting}

Нужно создать файл пользователей userlist.txt примерного содержания:''someuser'' ''same_password_as_in_server''

Админский доступ из консоли к базе данных pgbouncer:
\begin{lstlisting}[label=lst:pgbouncer3,caption=PgBouncer]
psql -h 127.0.0.1 -p 6543 pgbouncer
\end{lstlisting}

Здесь можно получить различную статистическую информацию с помощью команды SHOW.

3 PgPool-II vs PgBouncer

Все очень просто. PgBouncer намного лучше работает с пулами соединений, чем PgPool-II. Если вам не нужны остальные фичи, которыми владеет PgPool-II (ведь пулы коннектов это мелочи к его функционалу), то конечно лучше использовать PgBouncer.

Хотя некоторые используют PgBouncer и PgPool-II совместно.

7 Бэкап и восстановление PostgreSQL


\begin{epigraphs}
\qitem{Есть два типа администраторов...
...изойти, она случается.}{Закон Мэрфи}
\end{epigraphs}

1 Введение

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

Тоже самое касается и PostgreSQL баз данных. Бекапы должны быть! Посыпавшийся винчестер на сервере, ошибка в фаловой системе, ошибка в другой программе, которая перетерла весь каталог PostgreSQL и многое другое приведет только к плачевному результату. И даже если у Вас репликация с множеством слейвов, это не означает, что система в безопасности -- неверный запрос на мастер (DELETE, DROP), и у слейвов такая же порция данных (точнее их отсутствие).

Существуют три принципиально различных подхода к резервному копированию данных PostgreSQL:

Каждый из этих подходов имеет свои сильные и слабые стороны.

2 SQL бэкап

Идея этого подхода в создании текстового файла с командами SQL. Такой файл можно передать обратно на сервер и воссоздать базу данных в том же состоянии, в котором она была во время бэкапа. У PostgreSQL для этого есть специальная утилита -- pg_dump. Пример использования pg_dump:
\begin{lstlisting}[label=lst:backups1,caption=Создаем бэкап с помощью pg\_dump]
pg_dump dbname > outfile
\end{lstlisting}

Для восстановления такого бэкапа достаточно выполнить:
\begin{lstlisting}[label=lst:backups2,caption=Восстанавливаем бэкап]
psql dbname < infile
\end{lstlisting}

При этом базу данных «dbname» потребуется создать перед восстановлением. Также потребуется создать пользователей, которые имеют доступ к данным, которые восстанавливаются (это можно и не делать, но тогда просто в выводе восстановления будут ошибки). Если нам требуется, чтобы восстановление прекратилось при возникновении ошибки, тогда потребуется восстанавливать бэкап таким способом:
\begin{lstlisting}[label=lst:backups3,caption=Восстанавливаем бэкап]
psql --set ON_ERROR_STOP=on dbname < infile
\end{lstlisting}

Также, можно делать бэкап и сразу восстанавливать его на другую базу:
\begin{lstlisting}[label=lst:backups4,caption=Бекап в другую БД]
pg_dump -h host1 dbname \vert psql -h host2 dbname
\end{lstlisting}

После восстановления бэкапа желательно запустить «ANALYZE», чтобы оптимизатор запросов обновил статистику.

А что, если нужно сделать бэкап не одной базы данных, а всех, да и еще получить в бэкапе информацию про роли и таблицы? В таком случае у PostgreSQL есть утилита pg_dumpall. pg_dumpall используется для создания бэкапа данных всего кластера PostgreSQL:
\begin{lstlisting}[label=lst:backups5,caption=Бекап кластера PostgreSQL]
pg_dumpall > outfile
\end{lstlisting}

Для восстановления такого бэкапа достаточно выполнить от суперпользователя:
\begin{lstlisting}[label=lst:backups6,caption=Восстановления бэкапа PostgreSQL]
psql -f infile postgres
\end{lstlisting}

1 SQL бэкап больших баз данных

Некоторые операционные системы имеют ограничения на максимальный размер файла, что может вызывають проблемы при создании больших бэкапов через pg_dump. К счастью, pg_dump можете бэкапить в стандартный вывод. Так что можно использовать стандартные инструменты Unix, чтобы обойти эту проблему. Есть несколько возможных способов:

При слишком большой базе данных, вариант с командой split нужно комбинировать с сжатием данных.

3 Бекап уровня файловой системы

Альтернативный метод резервного копирования заключается в непосредственном копировании файлов, которые PostgreSQL использует для хранения данных в базе данных. Например:
\begin{lstlisting}[label=lst:backups14,caption=Бэкап PostgreSQL файлов]
tar -cf backup.tar /usr/local/pgsql/data
\end{lstlisting}

Но есть два ограничения, которые делает этот метод нецелесообразным, или, по крайней мере, уступающим SQL бэкапу:

Как альтернатива, можно делать снимки (snapshot) файлов системы (папки с файлами PostgreSQL). В таком случае останавливать PostgreSQL не требуется. Однако, резервная копия, созданная таким образом, сохраняет файлы базы данных в состоянии, как если бы сервер базы данных был неправильно остановлен. Поэтому при запуске PostgreSQL из резервной копии, он будет думать, что предыдущий экземпляр сервера вышел из строя и повторит журнала WAL. Это не проблема, просто надо знать про это (и не забыть включить WAL файлы в резервную копию). Также, если файловая система PostgreSQL распределена по разным файловым система, то такой метод бэкапа будет очень не надежным -- снимки файлов системы должны быть сделаны одновременно(!!!). Почитайте документацию файловой системы очень внимательно, прежде чем доверять снимкам файлов системы в таких ситуациях.

Также возможен вариант с использованием rsync. Первым запуском rsync мы копируем основные файлы с директории PostgreSQL (PostgreSQL при этом продолжает работу). После этого мы останавливаем PostgreSQL и запускаем повторно rsync. Второй запуск rsync пройдет гораздо быстрее, чем первый, потому что будет передавать относительно небольшой размер данных, и конечный результат будет соответствовать остановленной СУБД. Этот метод позволяет делать бекап уровня файловой системы с минимальным временем простоя.

4 Непрерывное резервное копирование

PostgreSQL поддерживает упреждаюшию запись логов (Write Ahead Log, WAL) в pg_xlog директорию, которая находится в директории данных СУБД. В логи пишутся все изменения сделаные с данными в СУБД. Этот журнал существует прежде всего для безопасности во время краха PostgreSQL: если происходят сбои в системе, базы данных могут быть восстановлены с помощью «перезапуска» этого журнала. Тем не менее, существование журнала делает возможным использование третью стратегии для резервного копирования баз данных: мы можем объединить бекап уровня файловой системы с резервной копией WAL файлов. Если требуется восстановить такой бэкап, то мы восстановливаем файлы резервной копии файловой системы, а затем «перезапускаем» с резервной копии файлов WAL для приведения системы к актуальному состоянию. Этот подход является более сложным для администрирования, чем любой из предыдущих подходов, но он имеет некоторые преимущества:

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

1 Настройка

Первый шаг -- активировать архивирование. Эта процедура будет копировать WAL файлы в архивный каталог из стандартного каталога pg_xlog. Это делается в файле postgresql.conf:
\begin{lstlisting}[label=lst:backups15,caption=Настройка архивир...
...l/archives/%f'
archive_timeout = 300  ...

После этого необходимо перенести файлы (в порядке их появления) в архивный каталог. Для этого можно использовать функцию rsync. Можно поставить функцию в список задач крона и, таким образом, файлы могут автоматически перемещаться между хостми каждые несколько минут.
\begin{lstlisting}[label=lst:backups16,caption=Копирование WAL фай...
...prod1:/data/pgsql/archives/ \
/data/pgsql/archives/ > /dev/null
\end{lstlisting}

В конце, необходимо скопировать файлы в каталог pg_xlog на сервере PostgreSQL (он должен быть в режиме восстановления). Для этого создается в каталоге данных PostgreSQL создать файл recovery.conf с заданной командой копирования файлов из архива в нужную директорию:
\begin{lstlisting}[label=lst:backups17,caption=recovery.conf]
restore_command = 'cp /data/pgsql/archives/%f ''%p'''
\end{lstlisting}

Документация PostgreSQL предлагает хорошее описание настройки непрерывного копирования, поэтому я не углублялся в детали (например, как перенести директорию СУБД с одного сервера на другой, какие могут быть проблемы). Более подробно вы можете почитать по этой ссылке http://www.postgresql.org/docs/9.0/static/continuous-archiving.html.

5 Заключение

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



Footnotes

... мусор1
под которым понимаются старые версии изменённых/удалённых записей
... часто2
«слишком часто» можно определить как «чаще раза в минуту». Вы также можете задать параметр checkpoint_warning (в секундах): в журнал сервера будут писаться предупреждения, если контрольные точки происходят чаще заданного.
... логов3
буфер находится в разделяемой памяти и является общим для всех процессов
... запроса4
Указывает планировщику на размер самого большого объекта в базе данных, который теоретически может быть закеширован
... noatime5
при этом не будет отслеживаться время последнего доступа к файлу
... дисков6
несколько логических разделов на одном диске здесь, очевидно, не помогут: головка всё равно будет одна
... pgtune7
http://pgtune.projects.postgresql.org/
... запрос8
и поэтому EXPLAIN ANALYZE DELETE ... -- не слишком хорошая идея
... правила9
RULE -- реализованное в PostgreSQL расширение стандарта SQL, позволяющее, в частности, создавать обновляемые представления
... порядок10
«на нашем форуме более 10000 зарегистрированных пользователей, оставивших более 50000 сообщений!»
... pgFouine11
http://pgfouine.projects.postgresql.org/
...Slony-I12
http://www.slony.info/
...PGCluster13
http://pgfoundry.org/projects/pgcluster/
...pgpool-I/II14
http://pgpool.projects.postgresql.org/
...Bucardo15
http://bucardo.org/
...Londiste16
http://skytools.projects.postgresql.org/doc/londiste.ref.html
... Skytools17
http://pgfoundry.org/projects/skytools/
... Replicator18
http://www.commandprompt.com/products/mammothreplicator/
...Postgres-R19
http://www.postgres-r.org/
...RubyRep20
http://www.rubyrep.org/
... исходников21
http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/
... Скачиваем22
http://bucardo.org/wiki/Bucardo#Obtaining_Bucardo
... Database23
http://www.greenplum.com/index.php?page=greenplum-database
... Server24
http://www.enterprisedb.com/products/gridsql.do
...Sequoia25
http://www.continuent.com/community/lab-projects/sequoia
...PL/Proxy26
http://plproxy.projects.postgresql.org/doc/tutorial.html
...HadoopDB27
http://db.cs.yale.edu/hadoopdb/hadoopdb.html