Эффективность функций

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

Одна из вещей, которые я хотел бы сделать, - это переместить некоторые из прикладной логики в хранимые процедуры, поэтому я здесь прошу DOS и DON'Ts (лучшие практики) по использованию функций в PostgreSQL (9.0), в частности в отношении ошибки производительности.

37 голосов | спросил Derek Downey 18 52011vEurope/Moscow11bEurope/MoscowFri, 18 Nov 2011 22:47:52 +0400 2011, 22:47:52

4 ответа


40

Строго говоря, термин «хранимые процедуры» указывает на Процедуры SQL в Postgres, введенные с Postgres 11. Связанные:

Существуют также функции , делая почти, но не совсем то же самое, и они были там с самого начала.

Функции с LANGUAGE sql - это в основном просто пакетные файлы с обычными командами SQL в оболочке функций (и, следовательно, атомарные, всегда выполняются внутри single транзакция). Все утверждения в SQL-функции планируются сразу , что существенно отличается от выполнения одного оператора за другим и может повлиять на порядок блокировки.

Для чего-то большего, самым зрелым языком является PL /pgSQL (LANGUAGE plpgsql). Он работает хорошо и был улучшен с каждым выпуском за последнее десятилетие, но он лучше всего подходит для склеивания команд SQL. Он не предназначен для тяжелых вычислений (кроме команд SQL).

Функции PL /pgSQL выполняют запросы типа подготовленные заявления . Повторное использование кэшированных планов запросов сокращает некоторые накладные расходы на планирование и делает их немного быстрее, чем эквивалентные SQL-операторы, что может быть заметным эффектом в зависимости от обстоятельств. Он может также иметь побочный эффект, как в этом связанном вопросе:

Это несет в себе преимущества и недостатки подготовленных операторов - как обсуждалось в руководство . Для запросов в таблицах с нерегулярным распределением данных и различными параметрами динамический SQL с цитируя руководство :

  

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

Мы получаем лучшее из двух миров большую часть времени (за вычетом некоторых дополнительных накладных расходов) без (ab), используя EXECUTE. Подробности в Что нового в PostgreSQL 9.2 из PostgreSQL Wiki .

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

Избегайте вложенности сложных функций, особенно функций таблицы (RETURNING SETOF record или TABLE (...)). Функции представляют собой черные ящики, представляющие собой оптимизационные барьеры для планировщика запросов. Они оптимизируются отдельно, а не в сочетании с внешним запросом, что упрощает планирование, но может привести к менее совершенным планам. Кроме того, эффективность и размер результатов не могут быть эффективно предсказаны.

Исключением для этого правила являются простые функции SQL (LANGUAGE sql), который может быть " inlined " - если выполнены некоторые предварительные условия . Узнайте больше о том, как работает планировщик запросов в этой презентации Нила Конвей (расширенный материал).

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

Вот почему функции не точно "хранимые процедуры" (хотя этот термин используется иногда, вводящим в заблуждение). Некоторые команды, такие как VACUUM , CREATE INDEX CONCURRENTLY или CREATE DATABASE не может работать внутри блок транзакций, поэтому они не допускаются к функциям. (Ни в процедурах SQL, ни в версии pg 11. Это может быть добавлено позже.)

На протяжении многих лет я написал тысячи функций plpgsql.

ответил Erwin Brandstetter 21 12011vEurope/Moscow11bEurope/MoscowMon, 21 Nov 2011 15:33:11 +0400 2011, 15:33:11
7

Некоторые DO's:

  • Используйте SQL как язык функций, когда это возможно, поскольку PG может встроить операторы
  • Правильно используйте IMMUTABLE /STABLE /VOLATILE, поскольку PG может кэшировать результаты, если они неизменяемы или стабильны.
  • Правильно используйте STRICT, так как PG может просто вернуть значение null, если какой-либо ввод является нулевым, а не работает функция
  • Рассмотрим PL /V8, когда вы не можете использовать SQL в качестве языка функций. Это быстрее, чем PL /pgSQL в некоторых ненаучных тестах, которые я запускал
  • Используйте LISTEN /NOTIFY для более длительных процессов, которые могут произойти вне транзакции.
  • Рассмотрите возможность использования функций для реализации разбивки на страницы, поскольку разбиение на страницы на основе ключей может быть быстрее, чем разбиение на страницы на основе LIMIT
  • Удостоверьтесь, что вы тестируете свои функции.
ответил Neil McGuigan 19 ThuEurope/Moscow2013-12-19T22:44:27+04:00Europe/Moscow12bEurope/MoscowThu, 19 Dec 2013 22:44:27 +0400 2013, 22:44:27
6

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

Я верю (но не уверен на 100%), что функции языка SQL быстрее, чем те, которые используют любые другие языки, потому что они не требуют переключения контекста. Недостатком является то, что процедурная логика не допускается.

PL /pgSQL - это самый зрелый и функциональный набор встроенных языков - но для производительности можно использовать C (хотя это будет полезно только для интенсивных вычислительных функций)

ответил Jack Douglas 18 52011vEurope/Moscow11bEurope/MoscowFri, 18 Nov 2011 23:23:00 +0400 2011, 23:23:00
4

В postgresql вы можете сделать очень интересный материал, используя пользовательские функции (UDF). Например, есть десятки возможных языков, которые вы можете использовать. Встроенные pl /sql и pl /pgsql являются надежными и надежными и используют метод песочницы, чтобы пользователи не делали ничего слишком ужасного. UDF, написанные на C, дают вам максимальную мощность и производительность, поскольку они работают в том же контексте, что и сама база данных. Тем не менее, это похоже на игру с огнем, потому что даже небольшие ошибки могут вызвать огромные проблемы, при сбое бэкэндов или повреждении данных. Языки custome pl, такие как pl /R, pl /ruby, pl /perl и т. Д., Предоставляют вам возможность писать как уровни базы данных, так и приложения на тех же языках. Это может быть удобно, поскольку это означает, что вам не нужно преподавать программист perl java или pl /pgsql и т. Д., Чтобы написать UDF.

Наконец, существует язык pl /proxy . Этот язык UDF позволяет запускать ваше приложение на нескольких или более серверах postgresql на сервере для масштабирования. Он был разработан хорошими людьми в Skype и в основном позволяет использовать горизонтальное масштабирующее решение для бедного человека. На удивление легко писать.

Теперь о производительности. Это серая область. Вы пишете приложение для одного человека? Или за 1000? или за 10 000 000? Способ создания вашего приложения и использования UDF будет зависеть от LOT от того, как вы пытаетесь масштабировать. Если вы пишете тысячи и тысячи пользователей, то главное, что вы хотите сделать, - максимально уменьшить нагрузку на db. UDF, которые уменьшают объем перемещаемых данных и обратно в базу данных, помогут уменьшить нагрузку ввода-вывода. Однако, если они начнут увеличивать нагрузку на ЦП, они могут быть проблемой. Вообще говоря, снижение нагрузки IO является первым приоритетом, и убедитесь, что UDF эффективны, чтобы не перегружать ваши CPU следующим образом.

ответил Scott Marlowe 24 42011vEurope/Moscow11bEurope/MoscowThu, 24 Nov 2011 07:03:53 +0400 2011, 07:03:53

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132