PostgreSQL для администраторов SQL Server: первые четыре настройки для проверки
Пересказ статьи Ryan Booz. PostgreSQL for the SQL Server DBA: The First Four Settings to Check
Пять лет назад я начал возвращаться к использованию PostgreSQL, и это не было гладким путешествием. Одной из главных причин, по которой мне тяжело давался прогресс в изучении PostgreSQL на раннем этапе, было то, что я просто не знал, что мне следует искать. Фактически я просто постоянно беседовал в течение месяца о различиях между SQL Server и PostgreSQL с людьми, идущими по тому же пути.
Да, теперь я стал умнее. 
Меня не особо интересует, как вы пришли к PostgreSQL. Может быть вы месяцами тщательно планировали миграцию, или использовали один из (все более) распространенных инструментов «миграции в один клик» от крупных поставщиков, или начинаете новый проект с нуля, вы узнали многие нюансы того, как выполнить установку и написать запросы, которые SQL Server не может непосредственно транслировать в PostgreSQL.
Итак, что находится наверху списка... о чем я обычно спрашиваю вновь переходящих на PostgreSQL?
Настройки памяти. Увлекательно, правда?
Говорят, что память в SQL Server конфигурируется по максимуму для данного сервера, т.е. SQL Server заберет столько, сколько сможет. Однако по большей части SQL Server берет на себя ответственность по управлению памятью. В то время как существуют продвинутые уровни для резервирования памяти для конкретных пользователей, главное внимание сосредоточено на основном объеме памяти, доступном для экземпляра SQL Server, чтобы сервер мог управлять тем, что происходит, динамически в фоновом режиме.
В некотором роде поведение PostgreSQL не отличается. Запросы также обрабатывают 8Кб страницы данных, и планам запросов требуется память для их выполнения. Однако способ, которым конфигурируется и используется память, требует больше внимания. Понимание и начальные установки, которые я обсуждаю с этой статье, а затем проверка их время от времени, существенно для получения производительного сервера и приложений. Если вы используете размещенные решения, подобные Amazon RDS или Azure Database для PostgreSQL – Flexible Server, большинство упомянутых здесь установок имеют разумные начальные значения по умолчанию. Однако ваши данные, приложение и шаблоны запросов могут не соответствовать этим значениям по умолчанию, и вам потребуется сконфигурировать их подходящим образом.
Например, в PostgreSQL требуется, в частности, сконфигурировать количество памяти, которая будет использоваться общими буферами (т.е. "буферный пул" в терминологии SQL Server). Однако это лишь одна настройка памяти, которую мы можем эффективно жестко закодировать во время исполнения. Нет настройки, которая ограничит максимальное количество памяти, которое PostgreSQL может потенциально использовать для других процессов. Следовательно, то, как работает каждая из этих настроек памяти в тандеме с текущим сервером и обработкой запросов, является ключевым моментом.
Давайте погружаться.
Перечисленные здесь настройки, и сотни других, могут быть установлены на уровне сервера в файле postgresql.conf. Если вы используете размещенные решения типа AWS или Azure, то эти значения изменяются через CLI или формы и параметры, которые предоставляются ими с помощью консоли.
В некоторых случаях эти настройки могут быть изменены для конкретной сессии, что может быть полезно, если некоторому запросу требуется больше памяти для выполнения. Если параметр можно установить во время сеанса, я упомяну об этом ниже.
Первой настройкой, которая требует вашего внимания, является shared_buffers. Эта настройка используется для определения количества памяти, выделенной для буферов общей памяти. В отличие от SQL Server, мы должны сказать PostgreSQL сколько памяти зарезервировать для данных, к которым часто обращаются, чтобы уменьшить необходимость в дисковых операциях ввода/вывода.
Если ваш сервер имеет много памяти и вы можете выделить достаточно памяти в кэше для всей базы данных (или баз данных), замечательно! PostgreSQL будет стараться использовать эту память. Однако если у вас ограниченный объем памяти сервера и ваша база данных во много раз превышает shared_buffers, PostgreSQL придется больше обмениваться данными с диском при выполнении запросов.
Имеется множество (как правило, старых) публикаций и формул, говорящих о том, как установить это значение. Начиная с 25% доступной памяти сервера, что является общим и разумным советом. Но это только начальная точка.
Лучший способ настройки shared_buffers - отслеживать коэффициент попадания в кэш. Это не является особенно отслеживаемой статистикой, и вы можете ее найти с помощью простого запроса, приведенного ниже. В отличие от SQL Server (где продолжительность жизни страницы - PLE - более предпочтительна по сравнению с коэффициентом попадания в кэш), отслеживание этого значения предоставляет вам индикатор, который покажет, достаточно ли памяти выделено для буфера.
Если этот коэффициент постоянно слишком низкий (или падает всякий раз при выполнении определенных заданий), это говорит о том, что множество данных перемещается между памятью и диском для получения результатов запроса. В идеале желательно иметь коэффициент попадания в кэш как можно ближе к 100% при обычном функционировании. Все, что постоянно ниже 80-85%, говорит о том, что ваше приложение не так производительно, как могло бы быть, и следует рассмотреть возможность увеличения shared_buffers.
Однако если увеличение значения этой настройки означает более 35%-40% общей доступной для PostgreSQL памяти, то это говорит о том, что ресурсы сервера ограничены, и вам, вероятно, следует инвестировать в более мощный экземпляр, если изменения приложения и данных не оказывают значимого влияния.
Запрос для получения коэффициента попадания в кэш для PostgreSQL
Главный вывод: мы должны сообщить PostgreSQL, как много памяти использовать для кэширования страниц данных. Эта настройка, в сочетании с доступными ресурсами памяти и другими настройками памяти, обсуждаемыми далее, непосредственно влияет на то, насколько эффективен будет PostgreSQL при работе с вашими данными.
work_mem - это следующая наиболее важная настройка для изучения в PostgreSQL, поскольку она непосредственно влияет на планирование запроса и производительность. Она также работает совсем по-разному в SQL Server и отличается от того, что вы знаете об использовании памяти при планировании запроса.
Когда PostgreSQL планирует запрос, он не выделяет предварительно память, как это происходит в SQL Server. Большую часть времени SQL Server выполняет разумную работу, запрашивая конкретные гранты памяти, поэтому данные и операции запроса могут все находиться в памяти для ускорения работы. Если SQL Server запрашивает слишком много памяти, запрос может быть отложен в ожидании освобождения памяти. Если запрашивается слишком мало памяти, то мы получаем страшный значок предупреждения (
) в плане запроса и данные сбрасываются на диск.
В PostgreSQL узлы плана запроса (то, что PostgreSQL назвал бы операторами) могут использовать вплоть до work_mem количества памяти для их конкретной операции. Если сложный план запроса имеет много узлов сортировки или хэширования, каждый узел для каждого исполняющегося запроса может использовать память вплоть до этого количества.
По умолчанию work_mem установлено в 4Мб на операцию узла. Если вы выполняете запрос, который содержит два узла сортировки в плане, то этот план запроса может потенциально использовать вплоть до 2 х work_mem памяти для обработки данных (т.е. 8Мб при значении по умолчанию). Если какой-либо узел требует более work_mem, то операция будет сбрасывать данные на диск, и ваш запрос будет выполняться медленнее.
Самый легкий способ отследить заниженную настройку work_mem - посмотреть вывод плана EXPLAIN ANALYZE для больших сложных запросов. Всякий раз, когда план содержит Sort Method: external merge Disk: xxxxkB, запрос сбрасывал данные на диск , поскольку он не имеет достаточно памяти для выполнения операции. Помните, что PostgreSQL не сделал ничего плохого, он просто ограничен настройками.
В этом случае запрос не может выполняться быстрее, пока он не получит, по меньшей мере, work_mem, которое определяется общим размером данных, которые требуется сортировать на диске. Я рекомендую найти решение для мониторинга, которое будет отслеживать медленные запросы и захватывать детали плана выполнения. Просмотрите репрезентативные временные рамки, насколько высокими (и постоянными) были требования к внешнему диску. Это хорошая стартовая точка для настройки значения work_mem.
Вы можете модифицировать это значение одним из двух способов; индивидуально для сессий/запросов, которые вызывают проблемы, или вы можете изменить его на уровне сервера, что будет влиять на все запросы.
Потенциальная проблема при увеличении настройки work_mem на уровне сервера состоит в том, что PostgreSQL не может проверить, достаточно ли памяти осталось для выполнения запроса. Если вы устанавливаете work_mem в 256Мб, а ваш сервер имеет только 4Гб общей RAM, вы можете легко исчерпать память, если лишь несколько соединений одновременно выполняют запросы.
Если вы можете менять код приложения, ценным вариантом является изменение этой настройки на лету для запросов, которым необходимо больше памяти для быстрого и эффективного выполнения. Запросам, генерирующим отчеты, например, часто требуется больше памяти, но они запускаются лишь несколько раз в день. Поэтому в рамках сессии запроса (или параметров, которые предоставляют некоторые фреймворки), измените значение work_mem с помощью команды SET:
И снова предупрежу быть осторожными с этой настройкой на уровне сервера, поскольку слишком высокое значение будет, вероятно, поглощать много доступной памяти и быстро приведет к ошибкам “out of memory”. На современном железе увеличение этого значения до 16Мб в большинстве случаев безопасно и может служить стартовым значением. Другая быстрая проверка - убедиться, что (connection_limit * work_mem) + shared_buffers меньше, чем общая доступная для PostgreSQL память.
Главный вывод: work_mem является одной из наиболее часто игнорируемых пользователями, пришедшими из SQL Server, настроек. В PostgreSQL мы можем покрутить еще несколько ручек, чтобы повлиять на работу планирования и выполнения запросов. Вам нужно следить за активностью сортировки на внешнем диске и соответственно настраивать это значение, но в пределах наличия памяти вашего сервера и общего числа подключений, которые могут выполняться одновременно.
Третья рассматриваемая настройка - это количество подключений, которое допускает ваш сервер PostgreSQL. По умолчанию PostgreSQL ограничивает число доступных подключений значением 100. Активное приложение без балансировки нагрузки может быстро превзойти этот предел.
Однако, поскольку каждое подключение использует процесс, простое увеличение этого значения ни в коей мере не является магическим решением. Вспомните, что каждое подключение может использовать по меньшей мере объем памяти work_mem, плюс еще для каждого узла сортировки/хэширования. Если вы не имеете достаточно памяти на сервере или вы увеличили серверную настройку work_mem, увеличение лимита подключений может быстро привести к исчерпанию памяти.
Тут на самом деле нужно посмотреть на пул соединений типа pgBouncer или pgPool-II. Некоторые фреймворки популярных языков типа Python могут также применять свои собственные возможности управления пулом.
Главный вывод: простое увеличение лимита числа подключений не является быстрым решением. Если вы обнаружили, что ваше приложение постоянно отключается, или вы должны настроить более высокое значение work_mem на уровне сервера, помните об ограничении числа подключений. Убедитесь, что ваша программа мониторинга отслеживает число подключений и просматривайте результаты.
Подобно другим базам данных, PostgreSQL выполняет фоновую работу для обслуживания данных, схем, статистики, создания резервных копий и восстановления данных и т.д. Одна из основных причин, по которой важно настраивать фоновые рабочие процессы, связана с так называемыми мертвыми кортежами.
PostgreSQL использует процесс под названием Многоверсионный Контроль Параллелизма (MVCC) для поддержания изоляции транзакций и параллельного доступа к данным. Одним из преимуществ такого подхода является то, что читатели (почти) никогда не блокируют писателей при обычном выполнении запросов. Это также допускает такую крутую вещь как транзакционные операторы DDL.
Оборотной стороной медали является то, что каждый UPDATE является удалением и вставкой, что создает мертвые кортежи (строки). Чтобы вернуть занимаемое ими пространство (и сделать некоторые другие вещи в фоновом режиме), PostgreSQL должен выполнять на постоянной основе процесс VACUUM для содержания данных и базы в идеальном состоянии.
MVCC и VACUUM действительно важные понятия в PostgreSQL и заслуживают по меньшей мере отдельной статьи в блоге. Имеются тысячи публикаций в блогах и сообщений на конференциях по этой теме, и важно хорошо понимать эти концепции. Я здесь не буду вдаваться в детали (статья уже и так длинная!), но просто знание о существовании этих процессов полезно, так что мы можем вкратце поговорить о maintenance_work_mem.
Фоновой работе, которая держит вашу базу данных "в форме", требуется достаточно памяти для освобождения пространства от мертвых кортежей, обновления статистики таблиц, модификации карты видимости (тоже в будущем посте!) и т.д. По моему опыту, если установки памяти подходящи для вашей рабочей нагрузки, вы, вероятно, не будете думать о VACUUM для 95% ваших таблиц. Оно "просто работает", и вы можете продолжать двигаться вперед.
Но под нагрузкой, или когда база данных выполняет тяжелое обновление таблиц, VACUUM может упасть, и вы получите раздувание таблицы (как минимум), длительное создание или обновление индексов, устаревшую статистику и другие страшные вещи (если все действительно вышло из под контроля).
По умолчанию PostgreSQL устанавливает maintenance_work_mem в значение 64Мб, 16 х 4Мб (значение по умолчанию для настройки work_mem). Фоновые процессы запускаются только по разу на каждый тип процесса, поэтому более высокое значение настройки памяти не будет оказывать такого влияния на нагрузку сервера, как увеличение work_mem (которое умножается на каждое подключение).
Если вы имеете 16Гб RAM или более, рассмотрите увеличение этого параметра по меньшей мере до 256Мб и посмотрите, насколько улучшится поведение. Один авторитетный магазин PostgreSQL предлагает значение (доступная RAM) х 0.05, но за пределами нескольких сотен мегабайт этот расчет в большинстве случаев тоже начинает ломаться. Если у вас имеются проблемы с выполнением заданий, я рекомендую повысить это значение на 32Мб или 64Мб, и посмотреть, не произошло ли улучшение. Если нет, еще раз увеличьте.
Если у вас нет 32Гб или более RAM, я бы предостерег повышать значение выше 256Мб. Это может сработать и решить вашу проблему, но резервирование такого количества памяти окажет влияние на другую память, доступную PostgreSQL.
Главный вывод: PostgreSQL использует фоновые процессы для поддержания базы данных в оптимальном состоянии. Этим процессам требуется достаточно памяти для обслуживания базы данных, и различная рабочая нагрузка часто будет требовать больше памяти, чтобы работать быстро и эффективно, насколько это возможно.
Переход с SQL Server на PostgreSQL может оказаться сложным и полезным. Мое разочарование в путешествии всегда достигало апогея, когда я знал, что то, что я пытался сделать, должно сработать, но не работало, поскольку я не мог претворить мой предыдущий опыт в действие.
Здесь я обсудил четыре настройки PostgreSQL, которые будут оказывать непосредственное влияние на эффективность работы вашего экземпляра PostgreSQL. Знание о том, что они существуют и что делают по сравнению с тем, что вы знаете об установке SQL Server, должен приблизить вас на шаг на пути к PostgreSQL.
Но, как говорится в заголовке, это всего лишь верхушка айсберга. Имеются сотни настроек в PostgreSQL, о некоторых из которых мы поговорим в будущих публикациях и о том, как он соотносятся с вашими знаниями SQL Server и ожиданиями.
Меня не особо интересует, как вы пришли к PostgreSQL. Может быть вы месяцами тщательно планировали миграцию, или использовали один из (все более) распространенных инструментов «миграции в один клик» от крупных поставщиков, или начинаете новый проект с нуля, вы узнали многие нюансы того, как выполнить установку и написать запросы, которые SQL Server не может непосредственно транслировать в PostgreSQL.
Итак, что находится наверху списка... о чем я обычно спрашиваю вновь переходящих на PostgreSQL?
Настройки памяти. Увлекательно, правда?
Настройки памяти, и почему он важны
Говорят, что память в SQL Server конфигурируется по максимуму для данного сервера, т.е. SQL Server заберет столько, сколько сможет. Однако по большей части SQL Server берет на себя ответственность по управлению памятью. В то время как существуют продвинутые уровни для резервирования памяти для конкретных пользователей, главное внимание сосредоточено на основном объеме памяти, доступном для экземпляра SQL Server, чтобы сервер мог управлять тем, что происходит, динамически в фоновом режиме.
В некотором роде поведение PostgreSQL не отличается. Запросы также обрабатывают 8Кб страницы данных, и планам запросов требуется память для их выполнения. Однако способ, которым конфигурируется и используется память, требует больше внимания. Понимание и начальные установки, которые я обсуждаю с этой статье, а затем проверка их время от времени, существенно для получения производительного сервера и приложений. Если вы используете размещенные решения, подобные Amazon RDS или Azure Database для PostgreSQL – Flexible Server, большинство упомянутых здесь установок имеют разумные начальные значения по умолчанию. Однако ваши данные, приложение и шаблоны запросов могут не соответствовать этим значениям по умолчанию, и вам потребуется сконфигурировать их подходящим образом.
Например, в PostgreSQL требуется, в частности, сконфигурировать количество памяти, которая будет использоваться общими буферами (т.е. "буферный пул" в терминологии SQL Server). Однако это лишь одна настройка памяти, которую мы можем эффективно жестко закодировать во время исполнения. Нет настройки, которая ограничит максимальное количество памяти, которое PostgreSQL может потенциально использовать для других процессов. Следовательно, то, как работает каждая из этих настроек памяти в тандеме с текущим сервером и обработкой запросов, является ключевым моментом.
Давайте погружаться.
Замечание о "бессерверных" опциях базы данных. Некоторые из предложений тех же поставщиков сделали работу с кодовой базой PostgreSQL более расширенной с возможностью динамически менять большинство этих настроек во времени и изменении рабочих нагрузок. В ряде случаев вы даже не можете изменить эти значения, которые могут не работать в плюс.
И все же, по моему мнению, любому пользователю PostgreSQL важно понимать, как эти настройки влияют на вашу работу с PostgreSQL.
Как изменить конфигурационные настройки в PostgreSQL
Перечисленные здесь настройки, и сотни других, могут быть установлены на уровне сервера в файле postgresql.conf. Если вы используете размещенные решения типа AWS или Azure, то эти значения изменяются через CLI или формы и параметры, которые предоставляются ими с помощью консоли.
В некоторых случаях эти настройки могут быть изменены для конкретной сессии, что может быть полезно, если некоторому запросу требуется больше памяти для выполнения. Если параметр можно установить во время сеанса, я упомяну об этом ниже.
Кэширование страниц данных
Первой настройкой, которая требует вашего внимания, является shared_buffers. Эта настройка используется для определения количества памяти, выделенной для буферов общей памяти. В отличие от SQL Server, мы должны сказать PostgreSQL сколько памяти зарезервировать для данных, к которым часто обращаются, чтобы уменьшить необходимость в дисковых операциях ввода/вывода.
Если ваш сервер имеет много памяти и вы можете выделить достаточно памяти в кэше для всей базы данных (или баз данных), замечательно! PostgreSQL будет стараться использовать эту память. Однако если у вас ограниченный объем памяти сервера и ваша база данных во много раз превышает shared_buffers, PostgreSQL придется больше обмениваться данными с диском при выполнении запросов.
Имеется множество (как правило, старых) публикаций и формул, говорящих о том, как установить это значение. Начиная с 25% доступной памяти сервера, что является общим и разумным советом. Но это только начальная точка.
Лучший способ настройки shared_buffers - отслеживать коэффициент попадания в кэш. Это не является особенно отслеживаемой статистикой, и вы можете ее найти с помощью простого запроса, приведенного ниже. В отличие от SQL Server (где продолжительность жизни страницы - PLE - более предпочтительна по сравнению с коэффициентом попадания в кэш), отслеживание этого значения предоставляет вам индикатор, который покажет, достаточно ли памяти выделено для буфера.
Если этот коэффициент постоянно слишком низкий (или падает всякий раз при выполнении определенных заданий), это говорит о том, что множество данных перемещается между памятью и диском для получения результатов запроса. В идеале желательно иметь коэффициент попадания в кэш как можно ближе к 100% при обычном функционировании. Все, что постоянно ниже 80-85%, говорит о том, что ваше приложение не так производительно, как могло бы быть, и следует рассмотреть возможность увеличения shared_buffers.
Однако если увеличение значения этой настройки означает более 35%-40% общей доступной для PostgreSQL памяти, то это говорит о том, что ресурсы сервера ограничены, и вам, вероятно, следует инвестировать в более мощный экземпляр, если изменения приложения и данных не оказывают значимого влияния.
Запрос для получения коэффициента попадания в кэш для PostgreSQL
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;Главный вывод: мы должны сообщить PostgreSQL, как много памяти использовать для кэширования страниц данных. Эта настройка, в сочетании с доступными ресурсами памяти и другими настройками памяти, обсуждаемыми далее, непосредственно влияет на то, насколько эффективен будет PostgreSQL при работе с вашими данными.
Память запроса
work_mem - это следующая наиболее важная настройка для изучения в PostgreSQL, поскольку она непосредственно влияет на планирование запроса и производительность. Она также работает совсем по-разному в SQL Server и отличается от того, что вы знаете об использовании памяти при планировании запроса.
Когда PostgreSQL планирует запрос, он не выделяет предварительно память, как это происходит в SQL Server. Большую часть времени SQL Server выполняет разумную работу, запрашивая конкретные гранты памяти, поэтому данные и операции запроса могут все находиться в памяти для ускорения работы. Если SQL Server запрашивает слишком много памяти, запрос может быть отложен в ожидании освобождения памяти. Если запрашивается слишком мало памяти, то мы получаем страшный значок предупреждения (
) в плане запроса и данные сбрасываются на диск.В PostgreSQL узлы плана запроса (то, что PostgreSQL назвал бы операторами) могут использовать вплоть до work_mem количества памяти для их конкретной операции. Если сложный план запроса имеет много узлов сортировки или хэширования, каждый узел для каждого исполняющегося запроса может использовать память вплоть до этого количества.
По умолчанию work_mem установлено в 4Мб на операцию узла. Если вы выполняете запрос, который содержит два узла сортировки в плане, то этот план запроса может потенциально использовать вплоть до 2 х work_mem памяти для обработки данных (т.е. 8Мб при значении по умолчанию). Если какой-либо узел требует более work_mem, то операция будет сбрасывать данные на диск, и ваш запрос будет выполняться медленнее.
Самый легкий способ отследить заниженную настройку work_mem - посмотреть вывод плана EXPLAIN ANALYZE для больших сложных запросов. Всякий раз, когда план содержит Sort Method: external merge Disk: xxxxkB, запрос сбрасывал данные на диск , поскольку он не имеет достаточно памяти для выполнения операции. Помните, что PostgreSQL не сделал ничего плохого, он просто ограничен настройками.
В этом случае запрос не может выполняться быстрее, пока он не получит, по меньшей мере, work_mem, которое определяется общим размером данных, которые требуется сортировать на диске. Я рекомендую найти решение для мониторинга, которое будет отслеживать медленные запросы и захватывать детали плана выполнения. Просмотрите репрезентативные временные рамки, насколько высокими (и постоянными) были требования к внешнему диску. Это хорошая стартовая точка для настройки значения work_mem.
Вы можете модифицировать это значение одним из двух способов; индивидуально для сессий/запросов, которые вызывают проблемы, или вы можете изменить его на уровне сервера, что будет влиять на все запросы.
Потенциальная проблема при увеличении настройки work_mem на уровне сервера состоит в том, что PostgreSQL не может проверить, достаточно ли памяти осталось для выполнения запроса. Если вы устанавливаете work_mem в 256Мб, а ваш сервер имеет только 4Гб общей RAM, вы можете легко исчерпать память, если лишь несколько соединений одновременно выполняют запросы.
Если вы можете менять код приложения, ценным вариантом является изменение этой настройки на лету для запросов, которым необходимо больше памяти для быстрого и эффективного выполнения. Запросам, генерирующим отчеты, например, часто требуется больше памяти, но они запускаются лишь несколько раз в день. Поэтому в рамках сессии запроса (или параметров, которые предоставляют некоторые фреймворки), измените значение work_mem с помощью команды SET:
SET work_mem = '32MB' -- значение на сессиюИ снова предупрежу быть осторожными с этой настройкой на уровне сервера, поскольку слишком высокое значение будет, вероятно, поглощать много доступной памяти и быстро приведет к ошибкам “out of memory”. На современном железе увеличение этого значения до 16Мб в большинстве случаев безопасно и может служить стартовым значением. Другая быстрая проверка - убедиться, что (connection_limit * work_mem) + shared_buffers меньше, чем общая доступная для PostgreSQL память.
Главный вывод: work_mem является одной из наиболее часто игнорируемых пользователями, пришедшими из SQL Server, настроек. В PostgreSQL мы можем покрутить еще несколько ручек, чтобы повлиять на работу планирования и выполнения запросов. Вам нужно следить за активностью сортировки на внешнем диске и соответственно настраивать это значение, но в пределах наличия памяти вашего сервера и общего числа подключений, которые могут выполняться одновременно.
Число подключений
Третья рассматриваемая настройка - это количество подключений, которое допускает ваш сервер PostgreSQL. По умолчанию PostgreSQL ограничивает число доступных подключений значением 100. Активное приложение без балансировки нагрузки может быстро превзойти этот предел.
Однако, поскольку каждое подключение использует процесс, простое увеличение этого значения ни в коей мере не является магическим решением. Вспомните, что каждое подключение может использовать по меньшей мере объем памяти work_mem, плюс еще для каждого узла сортировки/хэширования. Если вы не имеете достаточно памяти на сервере или вы увеличили серверную настройку work_mem, увеличение лимита подключений может быстро привести к исчерпанию памяти.
Тут на самом деле нужно посмотреть на пул соединений типа pgBouncer или pgPool-II. Некоторые фреймворки популярных языков типа Python могут также применять свои собственные возможности управления пулом.
Главный вывод: простое увеличение лимита числа подключений не является быстрым решением. Если вы обнаружили, что ваше приложение постоянно отключается, или вы должны настроить более высокое значение work_mem на уровне сервера, помните об ограничении числа подключений. Убедитесь, что ваша программа мониторинга отслеживает число подключений и просматривайте результаты.
Текущее обслуживание и резервное копирование/восстановление
Подобно другим базам данных, PostgreSQL выполняет фоновую работу для обслуживания данных, схем, статистики, создания резервных копий и восстановления данных и т.д. Одна из основных причин, по которой важно настраивать фоновые рабочие процессы, связана с так называемыми мертвыми кортежами.
PostgreSQL использует процесс под названием Многоверсионный Контроль Параллелизма (MVCC) для поддержания изоляции транзакций и параллельного доступа к данным. Одним из преимуществ такого подхода является то, что читатели (почти) никогда не блокируют писателей при обычном выполнении запросов. Это также допускает такую крутую вещь как транзакционные операторы DDL.
Оборотной стороной медали является то, что каждый UPDATE является удалением и вставкой, что создает мертвые кортежи (строки). Чтобы вернуть занимаемое ими пространство (и сделать некоторые другие вещи в фоновом режиме), PostgreSQL должен выполнять на постоянной основе процесс VACUUM для содержания данных и базы в идеальном состоянии.
MVCC и VACUUM действительно важные понятия в PostgreSQL и заслуживают по меньшей мере отдельной статьи в блоге. Имеются тысячи публикаций в блогах и сообщений на конференциях по этой теме, и важно хорошо понимать эти концепции. Я здесь не буду вдаваться в детали (статья уже и так длинная!), но просто знание о существовании этих процессов полезно, так что мы можем вкратце поговорить о maintenance_work_mem.
Фоновой работе, которая держит вашу базу данных "в форме", требуется достаточно памяти для освобождения пространства от мертвых кортежей, обновления статистики таблиц, модификации карты видимости (тоже в будущем посте!) и т.д. По моему опыту, если установки памяти подходящи для вашей рабочей нагрузки, вы, вероятно, не будете думать о VACUUM для 95% ваших таблиц. Оно "просто работает", и вы можете продолжать двигаться вперед.
Но под нагрузкой, или когда база данных выполняет тяжелое обновление таблиц, VACUUM может упасть, и вы получите раздувание таблицы (как минимум), длительное создание или обновление индексов, устаревшую статистику и другие страшные вещи (если все действительно вышло из под контроля).
По умолчанию PostgreSQL устанавливает maintenance_work_mem в значение 64Мб, 16 х 4Мб (значение по умолчанию для настройки work_mem). Фоновые процессы запускаются только по разу на каждый тип процесса, поэтому более высокое значение настройки памяти не будет оказывать такого влияния на нагрузку сервера, как увеличение work_mem (которое умножается на каждое подключение).
Если вы имеете 16Гб RAM или более, рассмотрите увеличение этого параметра по меньшей мере до 256Мб и посмотрите, насколько улучшится поведение. Один авторитетный магазин PostgreSQL предлагает значение (доступная RAM) х 0.05, но за пределами нескольких сотен мегабайт этот расчет в большинстве случаев тоже начинает ломаться. Если у вас имеются проблемы с выполнением заданий, я рекомендую повысить это значение на 32Мб или 64Мб, и посмотреть, не произошло ли улучшение. Если нет, еще раз увеличьте.
Если у вас нет 32Гб или более RAM, я бы предостерег повышать значение выше 256Мб. Это может сработать и решить вашу проблему, но резервирование такого количества памяти окажет влияние на другую память, доступную PostgreSQL.
Главный вывод: PostgreSQL использует фоновые процессы для поддержания базы данных в оптимальном состоянии. Этим процессам требуется достаточно памяти для обслуживания базы данных, и различная рабочая нагрузка часто будет требовать больше памяти, чтобы работать быстро и эффективно, насколько это возможно.
Верхушка айсберга
Переход с SQL Server на PostgreSQL может оказаться сложным и полезным. Мое разочарование в путешествии всегда достигало апогея, когда я знал, что то, что я пытался сделать, должно сработать, но не работало, поскольку я не мог претворить мой предыдущий опыт в действие.
Здесь я обсудил четыре настройки PostgreSQL, которые будут оказывать непосредственное влияние на эффективность работы вашего экземпляра PostgreSQL. Знание о том, что они существуют и что делают по сравнению с тем, что вы знаете об установке SQL Server, должен приблизить вас на шаг на пути к PostgreSQL.
Но, как говорится в заголовке, это всего лишь верхушка айсберга. Имеются сотни настроек в PostgreSQL, о некоторых из которых мы поговорим в будущих публикациях и о том, как он соотносятся с вашими знаниями SQL Server и ожиданиями.
Ссылки по теме
- Анатомия плана запроса в PostgreSQL
- Полезные команды/запросы PostgreSQL
- Query memory grants. Часть 1: куда девается память?
- Введение в управление параллелизмом в PostgreSQL
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой