Оптимизация производительности - Postgres. Оптимизация производительности postgresql

Путь Шамана. Арфы нет - возьмите бубен :): Оптимизация производительности PostgreSQL

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

Сейчас в системе около 700 пользователей, при этом активных бывает до 100 рыл.

Естественно вопрос производительности сейчас первостепенный (если считать с нуля, то задача номер 0 отказоустойчивость будет решена в ближайшее время). Моя задача как админа выжать все возможное из базы данных. Это PostgreSQL версии 8.2, с которого сейчас осуществляется тестовая миграция на 9.0.

Здесь будет приведен файл параметров postgresql.conf с расширенными комментариями по каждому пункту.

Установка каждого параметра выбиралась исходя из существующего опыта эксплуатации, исследования открытых источников, как минимум 3-х, официальной документации, а также тестов производительности приложения на сервере в максимально идентичными настройками БД 8.2. и 9.0.

########################################################## Файл параметров PostgreSQL postgresql.conf# Попытка заставить базу EnergyNET летать соколом.# Указаные настройки подразумевают, что на сервере, где# крутится PostgreSQL никого кроме него нет, не считая# операционной системы.#########################################################

# shared_buffers## Смысл параметра: PostgreSQL не читает данные напрямую# с диска и не пишет их сразу на диск. Данные загружаются# в общий буфер сервера, находящийся в разделяемой памяти,# серверные процессы читают и пишут блоки в этом буфере,# а затем уже изменения сбрасываются на диск.# Если процессу нужен доступ к таблице, то он сначала# ищет нужные блоки в общем буфере. Если блоки# присутствуют, то он может продолжать работу, если# нет — делается системный вызов для их загрузки.# Загружаться блоки могут как из файлового кэша ОС, так и# с диска, и эта операция может оказаться весьма «дорогой».# Если объём буфера недостаточен для хранения часто# используемых рабочих данных, то они будут постоянно# писаться и читаться из кэша ОС или с диска, что крайне# отрицательно скажется на производительности.# Принцип таков, что значение параметра устанавливаем# в размере 25-40% от оперативной памяти. От версии# PostgreSQL не зависит.#shared_buffers = 500MB # 2GB ОЗУ#shared_buffers = 3000MB # 12GB ОЗУ

# temp_buffers## Смысл параметра: Максимальное количество памяти, выделяемой# каждой сессии для работы с временными таблицами.# Необходимо помнить, что как только сессия заняла размер,# указанный в temp_buffers, эта память не освобождается,# пока сессия не завершится. На системах с большим# количеством одновременно работающих пользователей# некорректное (завышенное) значение этого параметра# запросто может привести к бешеному свопу, и как# следствие, краху операционной системы. А если при# этом еще и fsync = off, то готовьтесь восстанавливать# базу из резервных копий.# У нас временные таблицы не используются, так что# можно оставить значение по умолчанию. То есть# вообще удалить из postgresql.conf#temp_buffers = 8MB

# max_prepared_transactions## Смысл параметра: устанавливает максимальное количество# PREPARED TRANSACTION которые могут устанавливаться в# состояние prepared единовременно.# У нас 98% всех транзакций - PREPARED.# Минимально необходимое значение параметра = max_connections#max_prepared_transactions = 600

# work_mem## Смысл параметра: Задаёт объём памяти, который используют# внутренние операции сортировки и хэш-таблицы перед тем# как перейти на использование временных файлов на диске.# Если объём памяти недостаточен для сортироки некоторого# результата, то серверный процесс будет использовать# временные файлы. Если же объём памяти слишком велик,# то это может привести к своппингу.# В сложном запросе параллельно может быть запущено несколько# операций сортировки и хэширования, и каждая может занять# столько памяти, сколько задано в этом параметре, прежде# чем начнет использовать временные файлы. Кроме того, эти# операции могут одновременно выполняться в нескольких сессиях.# Таким образом, используемая память может оказаться в несколько# раз больше, чем work_mem. Операции сортировки используются# для ORDER BY, DISTINCT и операций соединения методом# слияния (merge joins). Хэш-таблицы используются при# операциях соединения методом хэширования (hash joins),# операциях аггрегирования, основанных на хэшировании и# обработке IN-подзапросов на основе хэширования.# Своими словами: если некая операция из перечисленных выше требует# для выполнения больше памяти, чем указано в параметре work_mem,# для этой операции будет создан временный файл в каталоге pgsql_tmp# соответствующей БД. Так что оценить, какой объем необходимо задать# данному параметру можно просто влянув в этот каталог в пиковые# часы нагрузки. Если суммарный объем созданных там файлов поместится# в оперативную память и останется хотя-бы 30% резерва, с учетом# остальных работающих на сервере задач, то можно задавать значение, равное# размеру примерно одинаковых по объему файлов. Тут конечно тоже без# фанатизма, ибо если временный файл больше 100 метров, то нужно# трижды подумать...#work_mem = 32MB

# autovacuum## Смысл простой - выполнение процессов VACUUM в автоматическом# режиме во время работы сервера.# У нас VACUUM и ANALYZE будут выполняться ночью# через CRON, так что включать автовакуум не будем.# Ну незачем нам еще и в рабочее время систему# нагружать некритичными вещами.#autovacuum = off

# effective_io_concurrency## Смысл параметра: Задаёт число операций чтения/записи на# диск, которые по мнению PostgreSQL могут выполняться# одновременно. Увеличение этого значения может повысить# число операций ввода/вывода, которое любая сессия# PostgreSQL может попытаться выполнить параллельно.# Допустимые значения лежат в диапазоне от 1 до 1000,# либо 0, если вы хотите запретить выполнение асинхронных# операций ввода/вывода.# Для оценки необходимого значения этого параметра можно# начать с числа отдельных устройств, включая RAID 0# или RAID 1, используемых базой данных. Для RAID 5# чётность устройств не учитывается. Однако, если база# данных часто занята множеством запросов, получаемых от# различных одновременных сессий, даже низкие значения# могут держать занятым дисковый массив. Значение выше# необходимого для поддержания занятости дисков приведёт# только к дополнительной загрузке процессора.# По простому, ставим значение параметра в число,# равное количеству шпинделей в дисках, на# которых расположена база данных. Потом пробуем уменьшить# или увеличить, и оцениваем результат при каждом изменении# параметра. Вообще параметр новый, малоопробованый,# рекомендаций мало. Так что будем тестировать.#effective_io_concurrency = 4 # 4 диска в RAID10

# fsync## Смысл параметра: Данный параметр отвечает за сброс данных# из кэша на диск при завершении транзакций. Если# установить его значение fsync = off то данные не будут# записываться на дисковые накопители сразу после завершения# операций. Это может существенно повысить скорость# операций insert и update, но есть риск повредить базу,# если произойдет сбой (неожиданное отключение питания,# сбой ОС, сбой дисковой подсистемы).# Самый сука стремный параметр. Если есть возможность,# ВСЕГДА используйте fsync = on. Если с производительностью# жопа, то при значении off большую часть проблем можно# порешать. Вот такая неоднозначная хрень. На винде# желательно его не выключать в следствии глючности самой# винды.# Если fsync = off, то обязательно установить# full_page_writes = off# Если fsync = on, а с производительность проблемы, то# лучше поиграться с synchronous_commit, но fsync не# трогать.#fsync = on

# full_page_writes## Смысл параметра: Если этот параметр включен, сервер# PostgreSQL пишет весь контент каждой страницы диска в WAL# во время первого изменения этой страницы после контрольной# точки. Это необходимо, поскольку если в процессе записывания# страницы произойдет системный сбой, на диске может оказаться# страница, в которой смешаны старые и новые данные. Изменения# на уровне строк данных, которые обычно хранятся в WAL, может# быть не достаточно для восстановления страницы после# системного сбоя. Хранение образа всей страницы гарантирует,# что страница будет восстановлена правильно, но это будет# стоить увеличения количества данных, которые нужно будет# занести в WAL. (Так как чтение WAL всегда начинается с# контрольной точки, удобно делать это при первом изменении# каждой страницы после контрольной точки. Таким образом,# одним из способов уменьшения стоимости записи страниц# является увеличение интервала между контрольными точками.)# Отключение этого параметра ускоряет работу, но может привести# к повреждению базы данных в случае системного сбоя или# отключения питания. Риски схожи с отключением fsync, хотя в# данном случае они меньше.#full_page_writes=on

# synchronous_commit## Смысл параметра: Определяет, должна ли транзакция ждать,# пока записи WAL будут перенесены на диск, прежде чем команда# вернёт клиенту сообщение об успешном выполнении. По# умолчанию и с точки зрения безопасности этот параметр должен# быть включен. Если параметр выключен, возможен промежуток# времени между сообщением об успешном выполнении транзакции# и моментом, когда транзакция на самом деле защищена от сбоя# на сервере. В отличие от fsync, отключение этого параметра# не может привести к риску противоречия в базе данных: сбой# может привести к потере последних прошедших транзакций, но# состояние базы данных при этом будет таким, как будто эти# транзакции просто были прерваны. Таким образом, отключение# synchronous_commit может оказаться полезным, если# производительность важнее, чем точность в проведении# транзакций.#synchronous_commit = off

effective_cache_size: указывает планировщику на размер самого большого объекта в базе данных, который теоретически может быть закеширован. На выделенном сервере имеет смысл выставлять effective_cache_size в 2/3 от всей оперативной памяти; на сервере с другими приложениями сначала нужно вычесть из всего объема RAM размер дискового кэша ОС и память, занятую остальными процессами.

phas13.blogspot.com

Применение машинного обучения для увеличения производительности PostgreSQL / Блог компании Postgres Professional / Хабр

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

Что такое план выполнения SQL-запроса?

Напомним, что SQL — декларативный язык. Это означает, что пользователь указывает только то, какие операции должны быть проделаны с данными. За выбор способа выполнения этих операций отвечает СУБД. Например, запросSELECT name FROM users WHERE age > 25; можно выполнить двумя способами: прочесть все записи из таблицы users и проверить каждую из них на выполнение условия age > 25 или использовать индекс по полю age. Во втором случае мы не просматриваем лишние записи, зато тратим больше времени на обработку одной записи из-за операций с индексами.

Рассмотрим более сложный запрос

SELECT messages.text FROM users, messages WHERE users.id = messages.sender_id; Этот JOIN можно выполнить уже тремя способами:

Вложенный цикл (NestedLoopJoin) просматривает все возможные пары записей из двух таблиц и проверяет для каждой пары выполнение условия.
Слияние (MergeJoin) отсортирует обе таблицы по полям id и sender_id соответственно, а затем использует метод двух указателей для поиска всех пар записей, удовлетворяющих условию. Этот метод аналогичен методу сортировки слиянием (MergeSort).
Хеширование (HashJoin) строит хэш-таблицу по полю наименьшей таблицы (в нашем случае это поле users.id). Хеш-таблица позволяет для каждой записи из messages быстро найти запись, в которой users.id = messages.sender_id.

Если же в запросе требуется выполнить более одной операции Join, то можно также выполнять их в разном порядке, например, в запросе

SELECT u1.name, u2.name, m.text FROM users as u1, messages as m, users as u2 WHERE u1.id = m.sender_id AND u2.id = m.reciever_id;

Дерево выполнения запроса называется планом выполнения запроса.

Посмотреть тот план, который выбирает СУБД для конкретного запроса, можно, используя команду explain:

EXPLAIN SELECT u1.name, u2.name, m.text FROM users as u1, messages as m, users as u2 WHERE u1.id = m.sender_id AND u2.id = m.reciever_id;

Для того, чтобы выполнить запрос и посмотреть выбранный для него план, можно использовать команду explain analyse:

EXPLAIN ANALYSE SELECT u1.name, u2.name, m.text FROM users as u1, messages as m, users as u2 WHERE u1.id = m.sender_id AND u2.id = m.reciever_id;

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

Как СУБД ищет оптимальный план выполнения запроса?

Можно разделить процесс поиска оптимального плана на две части.

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

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

В нашем проекте мы сосредоточились на первой задаче: по данному нам плану надо предсказать его стоимость. Как это можно сделать, не запуская план на выполнение?

На самом деле

В PostgreSQL для плана предсказываются две стоимости: стоимость запуска (start-up cost) и общая стоимость (total cost). Стоимость запуска показывает, сколько ресурсов план потратит до того, как выдаст первую запись, а общая стоимость — сколько всего ресурсов потребуется плану для выполнения. Однако это не принципиально для настоящей статьи. В дальнейшем под стоимостью выполнения будем понимать общую стоимость.

Эта задача также разделяется на две подзадачи. Сначала для каждой вершины плана (plan node) предсказывается, сколько кортежей будет отобрано в ней. Затем на основе этой информации оценивается стоимость выполнения каждой вершины, и, соответственно, всего плана.

Мы провели небольшое исследование, чтобы установить, какая из двух подзадач в PostgreSQL решается хуже. Каждая точка на рисунках ниже соответствует одной вершине плана. Для каждой вершины были предсказаны количество отобраных в ней кортежей и стоимость ее выполнения, а затем измерены реальное количество отобранных кортежей и время выполнения. На правой картинке отображены только те вершины, для которых количество кортежей предсказано правильно, поэтому по ней можно судить о качестве оценки стоимости.

Зависимость истинного количества кортежей от предсказанного

Зависимость времени работы плана от стоимости, если количество кортежей предсказано правильно

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

Рассмотрим использующееся в PostgreSQL решение первой подзадачи.

Как СУБД оценивает количество кортежей в вершинах?

Для начала попытаемся предсказать количество кортежей, отбираемых простым запросомSELECT name FROM users WHERE age < 25;

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

Используя гистограмму, мы легко можем восстановить долю тех пользователей, которые младше 25 лет. Для каждой вершины плана доля всех отобранных кортежей по отношению ко всем обработанным кортежам называется выборочностью (selectivity). В приведенном примере выборочность SeqScan будет равна примерно 0.3. Для получения количества кортежей, отбираемых вершиной, достаточно будет умножить выборочность вершины на количество обрабатываемых кортежей (в случае SeqScan'а это будет количество записей в таблице).

Рассмотрим более сложный запрос

SELECT name FROM users WHERE age < 25 AND city = 'Moscow';

В этом случае с помощью гистограмм по возрасту и городам мы сможем получить только маргинальные выборочности, то есть долю пользователей младше 25 лет и долю москвичей среди пользователей. В модели PostgreSQL все условия (кроме пар условий вида 5 < a AND a < 7, которые автоматически превращаются в условие 5 < a < 7), считаются независимыми. Математики называют два условия A и B независимыми, если вероятность того, что выполняются оба условия одновременно, равна произведению их вероятностей: P(A и B) = P(A)P(B). Однако в прикладном смысле можно понимать независимость двух величин как то, что от значения одной величины не зависит распределение на другую величину.

В чем же проблема?

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

Первый способ заключается в построении многомерных гистограмм. Проблема этого способа заключается в том, что с увеличением размерности многомерная гистограмма требует экспоненциально растущее количество ресурсов для сохранения той же точности. Поэтому приходится ограничиваться гистограммами маленькой размерности (2-8 измерений). Отсюда следует вторая проблема этого метода: нужно каким-то образом понять, для каких пар (или троек, или четверок...) столбцов имеет смысл строить многомерные гистограммы, а для каких необязательно. Чтобы решить эту проблему, требуется либо хороший администратор, который будет изучать планы ресурсоемких запросов, определять корреляции между столбцами и вручную указывать, какие гистограммы нужно достроить, либо программное средство, которое с помощью статистических тестов попытается найти зависимые друг от друга столбцы. Однако не для всех зависимых столбцов имеет смысл строить гистограммы, поэтому программное средство также должно анализировать совместную встречаемость столбцов в запросах. В настоящий момент существуют патчи, позволяющие использовать в PostgreSQL многомерные гистограммы, но в них администратору требуется вручную задавать, для каких столбцов эти многомерные гистограммы должны быть построены.

Используем машинное обучение для оценки выборочности

Однако эта статья посвящена альтернативному подходу. Альтернативный подход — это применение машинного обучения для нахождения совместной выборочности нескольких условий. Как уже говорилось выше, машинное обучение занимается поиском закономерностей в данных. Данные — это набор объектов. В нашем случае объектом является совокупность условий в одной вершине плана. По этим условиям и их маргинальным выборочностям нам требуется предсказать совместную выборочность.

Наблюдаемыми признаками вершины плана будут являться маргинальные выборочности всех её условий. Будем считать эквивалентными между собой все условия, отличающиеся только в константах. Можно рассматривать данное допущение как типичный прием машинного обучения — hashing trick — примененный для уменьшения размерности пространства. Однако за этим стоит более мощная мотивация: мы предполагаем, что вся необходимая для предсказания информация о константах условия содержится в его маргинальной выборочности. Можно показать это строго для простых условий вида a < const: здесь по выборочности условия мы можем восстановить значение константы, то есть потери информации не происходит.

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

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

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

Линейная регрессия:

В случае, когда все настраиваемые параметры равны 1, получаем стандартную модель выборочности PostgreSQL:

Стандартный метод гребневой регрессии предлагает искать параметры с помощью минимизации следующего функционала:

Для тестирования различных подходов мы использовали бенчмарк TPC-H.

В качестве простых регрессоров были использованы следующие методы:

Гребневая линейная регрессия + стохастический градиентный спуск. Этот метод хорош тем, что позволяет использовать динамическое обучение (online learning), поэтому не требует хранить никаких наблюдаемых объектов.
Множество гребневых линейных регрессий + стохастический градиентный спуск. Здесь предполагается, что для каждого набора условий создается отдельный гребневый линейный регрессор. Этот метод, как и прошлый, хорош тем, что позволяет использовать динамическое обучение, поэтому не требует хранить никаких наблюдаемых объектов, однако работает несколько точнее предыдущего, поскольку содержит существенно больше настраиваемых параметров.
Множество гребневых линейных регрессий + аналитическое решение методом Гаусса. Этот метод требует хранить все наблюдаемые объекты, но при этом, в отличие от двух предыдущих, гораздо быстрее настраивается под данные. Однако в этом же и его минус: он ведет себя достаточно нестабильно.

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

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

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

Также этот метод является более стабильным, чем линейная регрессия: для сходимости на бенчмарке TPC-H требуется всего 2 цикла обучения, приведенных на рисунке выше.

К чему приводит использование машинного обучения

Приведем полученные результаты для алгоритма kNN.


До машинного обучения	После машинного обучения

Можно видеть, что предложенный подход в самом деле ускоряет время работы СУБД. На одном из типов запросов бенчмарка ускорение составляет 30-45%, на другом — в 2-4 раза.

Какие есть пути развития?

Есть ещё много направлений для дальнейшего улучшения имеющегося прототипа.

Проблема поиска планов. Текущий алгоритм гарантирует, что в тех планах, к которым сходится алгоритм, предсказания выборочности будут правильными. Однако это не гарантирует глобальной оптимальности выбранных планов. Поиск глобально оптимальных планов или хотя бы лучшего локального оптимума — это отдельная задача.
Режим прерываний для прекращения исполнения неудачного плана. В стандартной модели PostgreSQL нам не имеет смысл прерывать выполнение плана, поскольку у нас есть всего один наилучший план, и он не меняется. С внедрением машинного обучения мы можем прервать выполнение плана, в котором были допущены серьезные ошибки в предсказании выборочности, учесть полученную информацию и выбрать новый лучший план для выполнения. В большинстве случаев новый план будет существенно отличаться от предыдущего.
Режимы устаревания информации. В процессе работы СУБД меняются данные и типичные запросы. Поэтому данные, которые были получены в прошлом, могут быть уже неактуальны. Сейчас в нашей компании ведется работа над хорошей системой определения актуальности информации, и, соответственно, «забывания» устаревшей информации.

Что это было?

В этой статье мы:

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

Спасибо за внимание!

Литература

Про планировщик PostgreSQL
Про машинное обучение (из курса лекций К. В. Воронцова)

habr.com

Простое ускорение PostgreSQL для 1С

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

Если коротко, то по умолчанию PostgreSQL настроен таким образом, чтобы расходовать минимальное количество ресурсов для работы с небольшими базами до 4 Gb на не очень производительных серверах.

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

Настройки выделения ресурсов оперативной памяти RAM для работы PostgreSQL хранятся в файле postgresql.conf.

Доступен как из папки, куда установлен PostgreSQL, так и из pgAdmin:

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

shared_buffers

Это размер памяти, разделяемой между процессами PostgreSQL, отвечающими за выполнения активных операций. Максимально допустимое значение этого параметра — 25% всего количества RAM.

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 64-128 Mb (8192-16384).

temp_buffers

Это размер буфера под временные объекты (временные таблицы). Среднее значение 2-4% всего количества RAM.

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 32-64 Mb.

work_mem

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

При 1-2 Gb RAM на сервере, рекомендуемое значение 32-64 Mb.

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

Еще два важных параметра это maintenance_work_mem (для операций VACUUM, CREATE INDEX и других) и max_stack_depth

Про настройку и назначение этих и остальных параметров можно почитать здесь.

Впрочем, этих 3-х параметров достаточно для временного ускорения (без чистки мусора и оптимизации запросов) PostgreSQL.

jcover.ru

performance - Оптимизация производительности - Postgres

Мне поручено повысить производительность медленного процесса, который обновляет некоторые данные в базе данных PostGres 8.3 (работает на Solaris, обновления управляются скриптами Perl 5.8 через SOAP). Около 50% потребляемого времени у меня очень мало контроля, поэтому настройка моих 50% очень важна.

В таблице обычно около 4500 000 строк, хотя я видел, как он раздувал около 7 000 000. Идентификатор, к которому относится запрос обновления (не первичный или уникальный), имеет только 9000 различных значений, а разброс вложений сильно взвешен в сторону 1 на идентификатор (медианное значение 20, максимальное значение 7000).

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

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

Даже если кто-то говорит, что вытащить соответствующие данные в отдельную таблицу, это отличная/ужасная идея, которая была бы действительно полезной! Любые другие идеи (или дополнительные вопросы для разъяснения) с благодарностью получили!

Query:

UPDATE tab1 SET client = 'abcd', invoice = 999 WHERE id = 'A1000062' and releasetime < '02-11-09'::DATE AND charge IS NOT NULL AND invoice IS NULL AND client IS NULL;

Я понимаю, что "не ноль" далеко не идеален. Идентификатор индексируется как счет-фактура и клиент (btrees, поэтому я понимаю, что PostGres будет/должен/может использовать индекс там). Это довольно тривиальный запрос...

План запроса (объясните с помощью анализа):

Bitmap Heap Scan on tab1 (cost=17.42..1760.21 rows=133 width=670) (actual time=0.603..0.603 rows=0 loops=1) Recheck Cond: (((id)::text = 'A1000062'::text) AND (invoice IS NULL)) Filter: ((charge IS NOT NULL) AND (client IS NULL) AND (releasetime < '2009-11-02'::date)) -> Bitmap Index Scan on cdr_snapshot_2007_09_12_snbs_invoice (cost=0.00..17.39 rows=450 width=0) (actual time=0.089..0.089 rows=63 loops=1) Index Cond: (((snbs)::text = 'A1000062'::text) AND (invoice IS NULL)) Total runtime: 0.674 ms

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

Мне очень нравится идея увеличения статистической ценности - я сразу же буду играть с ней.

qaru.site