Fork me on GitHub

PostgreSQL - общая информация

Полезные ссылки

Мои записи

Настройка

  • Общий буфер сервера: shared_buffers - На выделенных серверах полезным объемом для shared_buffers будет значение 1/4 памяти в системе. Если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров, то можно подымать это значение и мониторить результат, чтобы не привести к «деградации» (падению) производительности. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности, поскольку PostgreSQL также использует кэш операционной системы (увеличение данного параметра более 40% оперативной памяти может давать «нулевой» прирост производительности).  Проверяйте использование разделяемой памяти при помощи ipcs или других утилит(например, free или vmstat). Рекомендуемое значение параметра будет примерно в 1,2 –2 раза больше, чем максимум использованной памяти

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

  • Максимальное количество клиентов: max_connections - Параметр max_connections устанавливает максимальное количество клиентов, которые могут подключиться к PostgreSQL. Поскольку для каждого клиента требуется выделять память (work_mem), то этот параметр предполагает максимально возможное использование памяти для всех клиентов

  • Память для работы команды VACUUM: maintenance_work_mem - Этот параметр задаёт объём памяти, используемый командами VACUUM, ANALYZE, CREATE INDEX, и добавления внешних ключей. Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей базе данных. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ.

  • temp_buffers — буфер под временные объекты, в основном для временных таблиц. Можно установить порядка 16 МБ;

  • max_prepared_transactions — количество одновременно подготавливаемых транзакций (PREPARE TRANSACTION). Можно оставить по умолчанию — 5;

  • vacuum_cost_delay — если у вас большие таблицы, и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягивая его по времени. Чтобы включить эту функциональность, нужно поднять значение vacuum_cost_delay выше 0. Используйте разумную задержку от 50 до 200 мс. Для более тонкой настройки повышайте vacuum_cost_page_hit и понижайте vacuum_cost_page_limit. Это ослабит влияние VACUUM, увеличив время его выполнения. В тестах с параллельными транзакциями Ян Вик (Jan Wieck) получил, что при значениях delay — 200, page_hit — 6 и limit —100 влияние VACUUM уменьшилось более чем на 80%, но его длительность увеличилась втрое;

  • max_stack_depth — cпециальный стек для сервера, который в идеале должен совпадать с размером стека, выставленном в ядре ОС. Установка большего значения, чем в ядре, может привести к ошибкам. Рекомендуется устанавливать 2–4 MB;
  • max_files_per_process — максимальное количество файлов, открываемых процессом и его подпроцессами в один момент времени. Уменьшите данный параметр, если в процессе работы наблюдается сообщение «Too many open files»;

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

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

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

  • Остановите сервер (!);
  • Перенесите каталоги pg_clog и pg_xlog, находящийся в каталоге с базами данных, на другой диск;
  • Создайте на старом месте символическую ссылку;
  • Запустите сервер;

Индексы

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

  • B-Tree - называют упорядоченное блочное дерево. Узлы в дереве представляют из себя блоки фиксированного размера. У каждого узла фиксированное число детей. В индексном B-Tree значения и RowId размещаются совместно на нижнем слое дерева. Каждый узел дерева представляет из себя одну страницу (page) в некотором формате.
  • R-Tree (Rectangle-Tree) предназначен для хранения пар (X, Y) значений числового типа (например, координат). По способу организации R-Tree очень похоже на B-Tree. Единственное отличие — это информация, записываемая в промежуточные страницы в дереве. Для i-го значения в узле в B-Tree мы пишем максимум из i-го поддерева, а в R-Tree — минимальный прямоугольник, покрывающий все прямоугольники из ребёнка
  • Hash индекс по сути является ассоциативным хеш-контейнером. Хеш-контейнер — это массив из разряженных значений. Адресуются отдельные элементы этого массива некоторой хеш-функцией которая отображает каждое значение в некоторое целое число. Т.е. результат хеш-функции является порядковым номером элемента в массиве. Элементы массива в хеш-конейтнере называются букетами (bucket). Обычно один букет — одна странца. Хеш-функция отображает более мощное множество в менее мощное, возникают так называемые коллизии — ситуация, когда одному значению хеш-функции соответствует несколько разных значений. В букете хранятся значения, образующие коллизию. Разрешение коллизий происходит посредством поиска среди значений, сохранённых в букете.
  • Битовый индекс (bitmap index) — метод битовых индексов заключается в создании отдельных битовых карт (последовательность 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует строка с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства (алгоритм Хаффмана).
  • GiST (Generalized Search Tree) — обобщение B-Tree, R-Tree дерево поиска по произвольному предикату. Структура дерева не меняется, по-прежнему в каждом нелистовом узле хранятся пары (Значения, Номер страницы), а количество детей совпадает с количеством пар в узле. Существенное отличие состоит в организации ключа. B-Tree деревья заточены под поиск диапазонов и хранят максимумы поддерева-ребёнка. R-Tree — региона на координатной плоскости. GiST предлагает в качестве значений в нелистовых узлах хранить ту информацию, которую мы считаем существенной, и которая позволит определить, есть ли интересующие нас значения (удовлетворяющие предикату) в поддереве-ребёнке. Конкретный вид хранимой информации зависит от вида поиска, который мы желаем проводить. Таким образом параметризовав R-Tree и B-Tree дерево предикатами и значениями мы автоматически получаем специализированный под задачу индекс (PostGiST, pg_trgm, hstore, ltree, прочее).
  • GIN (Generalized Inverted Index) — обратный индекс, используемым полнотекстовым поиском PostgreSQL. Это означает, что в структуре индексов с каждой лексемой сопоставляется отсортированный список номеров документов, в которых она встречается. Очевидно, что поиск по такой структуре намного эффективнее, чем при использовании GiST, однако процесс добавления нового документа достаточно длителен.
  • Cluster индекс - Не является индексом, поскольку производит кластеризацию таблицы по заданному индексу. Более подробно можно почитать в разделе «[sec:hard-drive-cluster] »
  • BRIN индекс - в отличие от привычного B-Tree, этот индекс намного эффективнее для очень больших таблиц, и в некоторых ситуациях позволяет заменить собой партицирование (подробно можно почитать в разделе «[sec:partitioning] »). BRIN-индекс имеет смысл применять для таблиц, в которых часть данных уже по своей природе как-то отсортирована. Например, это характерно для логов или для истории заказов магазина, которые пишутся последовательно, а потому уже на физическом уровне упорядочены по дате/номеру, и в то же время таблицы с такими данными обычно разрастаются до гигантских размеров.

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

  • Функциональный индекс (functional index) - можете построить индекс не только по полю/нескольким полям таблицы, но и по выражению, зависящему от полей. Пусть, например, в вашей таблице foo есть поле foo_name, и выборки часто делаются по условию «первая буква из поля foo_name в любом регистре». Вы можете создать индекс

sql CREATE INDEX foo_name_first_idx ON foo ((lower(substr(foo_name, 1, 1))));

и запрос вида

sql SELECT * FROM foo WHERE lower(substr(foo_name, 1, 1)) = 'а';

будет его использовать.

  • Частичный индекс (partial index) - под частичным индексом понимается индекс с предикатом 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, просто не попадут в индекс.

  • Уникальный индекс (unique index) - уникальный индекс гарантирует, что таблица не будет иметь более чем одну строку с тем же значением. Это удобно по двум причинам: целостность данных и производительность. Поиск данных с использованием уникального индекса, как правило, очень быстрый.

  • Индекс нескольких столбцов (multi-column index)

Расширения

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

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

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

  • SQL бэкап;
  • Бэкап уровня файловой системы;
  • Непрерывное резервное копирование;

SQL бэкап

Идея этого подхода в создании текстового файла с командами SQL. Такой файл можно передать обратно на сервер и воссоздать базу данных в том же состоянии, в котором она была во время бэкапа. У PostgreSQL для этого есть специальная утилита — pg_dump. Пример использования pg_dump:

pg_dump dbname > outfile

Для восстановления такого бэкапа достаточно выполнить:

psql dbname < infile

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

psql --set ON_ERROR_STOP=on dbname < infile

Также, можно делать бэкап и сразу восстанавливать его в другую базу:

pg_dump -h host1 dbname | psql -h host2 dbname

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

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

pg_dumpall > outfile

Для восстановления такого бэкапа достаточно выполнить от суперпользователя:

psql -f infile postgres

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

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

Использовать сжатие для бэкапа

Можно использовать программу сжатия данных, например GZIP:

pg_dump dbname | gzip > filename.gz
  • Восстановление:
gunzip -c filename.gz | psql dbname

или

cat filename.gz | gunzip | psql dbname
  • Использовать команду split Команда split позволяет разделить вывод в файлы меньшего размера, которые являются подходящими по размеру для файловой системы. Например, бэкап делится на куски по 1 мегабайту:

pg_dump dbname | split -b 1m - filename

Восстановление:

cat filename* | psql dbname

Использовать пользовательский формат дампа pg_dump PostgreSQL построен на системе с библиотекой сжатия Zlib, поэтому пользовательский формат бэкапа будет в сжатом виде. Это похоже на метод с использованием GZIP, но он имеет дополнительное преимущество — таблицы могут быть восстановлены выборочно. Минус такого бэкапа — восстановить возможно только в такую же версию PostgreSQL (отличаться может только патч релиз, третья цифра после точки в версии):

pg_dump -Fc dbname > filename

Через psql такой бэкап не восстановить, но для этого есть утилита pg_restore:

 pg_restore -d dbname filename

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

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

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

Настройка

  • Первый шаг — активировать архивирование. Эта процедура будет копировать WAL файлы в архивный каталог из стандартного каталога pg_xlog. Это делается в файле postgresql.conf:

archive_mode = on # enable archiving archive_command = 'cp -v %p /data/pgsql/archives/%f' archive_timeout = 300 # timeout to close buffers

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

bash rsync -avz --delete prod1:/data/pgsql/archives/ \ /data/pgsql/archives/ > /dev/null

  • В конце необходимо скопировать файлы в каталог pg_xlog на сервере PostgreSQL (он должен быть в режиме восстановления). Для этого необходимо в каталоге данных PostgreSQL создать файл recovery.conf с заданной командой копирования файлов из архива в нужную директорию:

restore_command = 'cp /data/pgsql/archives/%f "%p"'

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

social