Мысли вслух
Если параметр для организации «честной/справедливой» очереди легковесных блокировок в Postgres Pro действительно позволяет решить проблему с ростом очереди ожиданий LWLock, почему значение параметра lwlock_shared_limit по умолчанию 0, а не 16?
Какие риски возникают при увеличении значения параметра lwlock_shared_limit?
Хорошо, если возможность проведения нагрузочного тестирования. Но, в продуктиве уже нет такой возможности.
И как оценить риски?
Современный стиль разработки информационных систем это:
Переживать за закрытие этапов договора , а не за качество, производительность и отказоустойчивость продукта .
Современный стиль разработки информационных систем это:
На этапе разработки назначить права суперпользователя ролям БД и потом громко возмущаться, когда права изымаются в процессе передачи СУБД в поддержку.
Современный стиль разработки информационных систем это:
Спроектировать и запустить в продакшн высоконагруженную СУБД, в которой транзакция,изменяющая ключевую таблицу, завершается по сигналу от внешней информационной системы(по протоколу http).
И получить в результате постоянную очередь ожиданий и деградацию производительности в случае проблем с внешней системой и/или проблем с сетью.
Современный стиль разработки информационных систем это:
Воспринимать СУБД как черный ящик для хранения данных
Не привлекать DBA к процессу дизайна и разработки
Использовать ORM для взаимодействия с СУБД
Не анализировать коды ошибок СУБД
Не проводить нагрузочное тестирование
Пытаться решать проблемы деградации производительности информационной системы путем подбора магической комбинации конфигурационных параметров СУБД или увеличения железных ресурсов для БД.
В общем, как в песне поется -"Раз-раз, и в продакшн"
Мониторинг производительности СУБД в промышленной эксплуатации имеет ли смысл?
Информационная система введена в промышленную эксплуатацию.
Какие могут быть причины снижения производительности СУБД:
Проблемы с инфраструктурой.
Криворукие разрабы начали эксперименты в продуктивном контуре.
Администраторы системы не рассчитали систему на повышенную нагрузку со стороны пользователей.
Проще говоря причины снижения производительности СУБД всегда внешние и не могут быть решены никакими волшебными серебряными пулями в настройках СУБД. Все эксперименты по оптимизации надо было делать при нагрузочном и стресс тестировании.
А если так — какой смысл мониторить производительность СУБД?
Кроме чисто академического.
Как выяснилось, в 15-й версии PostgreSQL внесено новое изменение в инструкцию CREATE DATABASE (стратегия), существенно влияющее на поведение СУБД . Новая фича сделана по умолчанию, и для того, чтобы работало как раньше - нужно менять текст инструкции.
В результате, после обновления до 15-й версии , возникли очень существенные проблемы, вплоть до аварии СУБД.
IMHO решение выглядит нарушением принципа совместимости снизу вверх.
В продолжении https://habr.com/ru/posts/787966/
Интересно, а будет ли иметь какой то практический смысл использование скользящих средних для анализа производительности СУБД.
Например классический сигнал на продажу - как сигнал о деградации производительности ?
В прошлом посте я показывал, один из вариантов бессмысленного усложнения запроса использованием CASE, сегодня - продолжение.
CASEТут представлена попытка заNULLить значение, если оно равно чему-то.
Но ведь в PostgreSQL есть функция nullif, которая делает ровно то же самое:
NULLIF(значение1, значение2)Функция
NULLIFвыдаёт значение NULL, еслизначение1равнозначение2; в противном случае она возвращаетзначение1. Это может быть полезно для реализации обратной операции кCOALESCE. В частности, для примера, показанного выше:
SELECT NULLIF(value, '(none)') ...В данном примере если
valueравно(none), выдаётся null, а иначе возвращается значениеvalue.
То есть в примере выше стоит переписать короче и понятнее:
, nullif(sdate, '1900-01-01') sdate
, nullif(mdate, '1900-01-01') mdate
В своей лекции про "сложные" SELECT я уже рассказывал про возможности оператора CASE, а еще раньше - про возможности оптимизации выполнения запросов с его помощью.
Но иногда он вовсе не нужен! Обратите внимание на картинку сверху...
Посмотрим на использованный тут синтаксис CASE:
CASE
WHEN условие THEN результат
[WHEN ...]
[ELSE результат]
ENDИли еще конкретнее:
CASE
WHEN условие THEN TRUE -- [условие IS TRUE]
ELSE FALSE -- [условие IS FALSE, IS NULL]
ENDХм... То есть результат этого CASE эквивалентен значению условия с точностью до NULL!
При обращении условия в NULL такой CASE вернет FALSE, но этого же поведения можно добиться с помощью coalesce:
coalesce(условие, FALSE)Но если мы говорим о конкретном примере с условием EXISTS, то уж оно-то точно никак не может принимать значение NULL! Значит, coalesce-обертка нам тут не требуется и эту часть запроса можно сократить до одного лишь условия, без всяких CASE:
EXISTS(
SELECT
NULL
FROM
_inforg20687 t15
WHERE
t15._fld1329 = 0::numeric AND
t15._fld20688rref = t6._idrref AND
t15._fld20689_type = '\\010'::bytea AND
t15._fld20689_rtref = '\\000\\000\\001\\010'::bytea AND
t15._fld20689_rrref = t4._fld6883rref
)В общем, пишите меньше SQL-кода - и ваши запросы "будут мягкими и шелковистыми"!
Нет ни одного открытия, ни в технологии, ни в методах управления, одно только использование которого обещало бы в течение ближайшего десятилетия на порядок повысить производительность, надежность, простоту разработки программного обеспечения.
Фредерик Брукс — американский учёный в области теории вычислительных систем, автор книги «Мифический человеко-месяц». Управлял разработкой OS/360 в IBM. Награждён Премией Тьюринга в 1999 году.
Как оценить производительность СУБД в целом ? Как сравнить производительность СУБД в течении времени ?
А может быть производительность СУБД это вектор: (N1, N2, N3), где:
N1 - количество активных сессий
N2 - количество транзакций
N3 - количество запросов к СУБД в секунду.
Тогда можно получить модуль вектора , как квадратный корень из N1^2 + N2^2 + N3^2 , и уже сравнивать изменение значения и собирать историю.
Затем , построить метрику , и вообще применить механизм статистического анализа - скользящее среднее и т.п.
Почему они это делают? Или очередной риторической вопрос.
Интересная особенность современного поколения разработчиков - они очень любят эксклюзивные блокировки.
1) select ... for update - "самый распространенный вариант"
2) pg_advisory_lock - "у нас фреймворк такой"
В результате - стандартная ситуация, которой лет 30, если не больше - "на тесте все работает, а в продуктивном контуре, при реальных пользователях нет".
Я в СУБД с 97-го года , пока не могу представить сценарий при котором , в современных СУБД нужна эксклюзивная блокировка строк и таблиц, особенно в высоконагруженной OLTP.
Наверное мои знания и представления о современной разработке для СУБД несколько устарели.
Компания решает выполнить решение государства о переходе на отечественное ПО.
Как обстоят дела сейчас:
Ищется поставщик решения , параллельно пишется техническое решение/пояснительная записка с максимальной размытыми формулировками и цифрами с потолка.
Поставщик решения разрабатывает решение , в компании формируется проектная команда, формируется инфраструктура.
Поставщику решения главное - получить оплату по договору.
Проектной команде - закрыть этапы и KPI.
Нагрузочное тестирование проводится в режиме - для галочки. Можно вообще в продакшн выводить без НТ.
Стресс тестирование проводится - никак.
Результат - ХYZовый код: "у нас проблемы после перехода на отечественное ПО". Потому, что поставщик решения один и качество ему не важно от слова вообще. В случае проблемы, все списывается на "у вас с инфраструктурой проблемы, у нас на тестовом сервере все работает"
Как было бы лучше:
Формируется проектная команда.
Проектная команда определяет доступные ресурсы инфраструктуры.
Проектная команда формирует требования к решению.
Объявляется конкурс.
Поставщики готовят решения.
Проводятся сравнительные испытания.
По итогам испытаний выбирается поставщик решения для компании.
Результат — побеждает сильнейший и качественный продукт.
Стратегия давно реализована и применяется госзаказах и конкурсах между разными КБ. В результате получаются отличные изделия которыми можно гордится.
Почему не реализовано в IT ? Это риторический вопрос.
Если вдруг вам понадобилось базу IP2Location перевести из DECIMAL-представления IP-адресов в "родной" для PostgreSQL тип inet, то для IPv4-адресов все будет тривиально:
'0.0.0.0'::inet + ipnum::bigintА вот для преобразования числа к формату IPv6-адреса придется проявить немного изобретательности:
"математически" разбиваем число на 8 двухбайтовых сегментов по (2 ^ 16) ^ i
каждое значение преобразуем в шестнадцатиричную систему счисления и добиваем лидирующими нулями
склеиваем сегменты через двоеточие и кастуем к inet
array_to_string(ARRAY(
SELECT
lpad(to_hex(trunc(
ipnum % (2::numeric(39,0) ^ ((i + 1) * 16)) / (2::numeric(39,0) ^ (i * 16))
)::integer), 4, '0')
FROM
generate_series(7, 0, -1) i
), ':')::inetВ принципе, после этого мы можем "свернуть" ip_from и ip_to в подсеть, не обращая внимания на исходный формат:
inet_merge(ip_from, ip_to) subnetА если проиндексируем эти подсети с помощью gist...
CREATE INDEX ON country_inet USING gist(subnet inet_ops);... то сможем по индексу быстро определять принадлежность произвольного IPv4/IPv6-адреса подсетям с помощью соответствующих операторов примерно таким запросом:
SELECT
*
FROM
country_inet
WHERE
subnet >> '8.8.8.8' AND
country <> '-'
ORDER BY
masklen(subnet) DESC
LIMIT 1;IS NOT NULL + OR
Иногда внутри SQL-запроса возникает необходимость проверить наличие/отсутствие NULL-значения в некотором наборе полей:
a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULLНо то же самое по смыслу условие можно записать гораздо короче с помощью функции coalesce:
coalesce(a, b, c) IS NOT NULLПодробнее об особенностях работы со сложными выражениями можно прочитать в статье "PostgreSQL Antipatterns: вычисление условий в SQL".
IS NOT NULL + AND
Немного изменим условие - заменим OR на AND:
a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULLТут нам поможет ROW-конструктор:
(a, b, c) IS NOT NULLIS NULL + AND
Теперь заменим IS NOT NULL на IS NULL:
a IS NULL AND b IS NULL AND c IS NULLТут достаточно вспомнить из логики, что (A and B) эквивалентно not(not A or not B), а (A or B) - not(not A and not B), поэтому легко применяем not к варианту IS NOT NULL + OR:
coalesce(a, b, c) IS NULLИли с помощью ROW-конструктора:
(a, b, c) IS NULLРазница будет заключаться в том, что
coalesceвычисляет выражения "лениво" (см. "«Ленивый сахар» PostgreSQL").
IS NULL + OR
Остался последний вариант:
a IS NULL OR b IS NULL OR c IS NULLТут мы можем "обратить" вариант IS NOT NULL + AND:
NOT (a, b, c) IS NOT NULLЗаметьте, что пара NOT тут "не сокращается", иначе получился бы предыдущий вариант.
Периодически в коде запросов и "заточенных" под них индексов наблюдаю примерно подобные куски:
coalesce("Фамилия", '') || ' ' || coalesce("Имя", '') || ' ' || coalesce("Отчество", '')Понятно, что тут хотели обезопасить себя от заполненности любого из полей NULL-значением, чтобы случайно вся строка не заNULL'илась.
Правда, тут возникают некоторые артефакты в виде "висящих пробелов" типа ' Иван Иванович' или 'Иванов Иван '.
Но ведь есть решение изящнее и проще - функция concat_ws:
concat_ws(' ', "Фамилия", "Имя", "Отчество")RTFM!
Хитрый способ в PostgreSQL перебрать всю таблицу медленно и аккуратно, но эффективно, используя указатель ссылающийся на конкретный tuple - ctid.
var endBlock int
row := db.QueryRow(ctx, `select relpages from pg_class where oid = 'table1'::regclass::oid`)
err = row.Scan(&endBlock)
if err != nil {
return
}
startBlock := 0
blocksPerIteration := 50
maxTuplesPerBlock := 150
for {
var rows pgx.Rows
rows, err = db.Query(ctx, `
select id
from table1
where ctid = any (
array(
select format('(%s, %s)', a, b)::tid
from generate_series($1::int, $2::int) a(a)
cross join generate_series(0, $3) b(b)
))
and value = '100000'`,
startBlock,
startBlock+blocksPerIteration,
maxTuplesPerBlock,
)
if err != nil {
return
}
var id int
for rows.Next() {
err = rows.Scan(&id)
if err != nil {
return
}
slog.Info("found row", "id", id)
}
rows.Close()
startBlock += blocksPerIteration
if startBlock > endBlock {
break
}
time.Sleep(100 * time.Millisecond)
}Нюансы:
из-за того что тип tid не оптимизирует операции больше/меньше, приходится использовать ctid = any (...)
для определения maxTuplesPerBlock можно использовать запрос
select 8096 / min(x)
from (
select pg_column_size(table1) x
from table1 tablesample system(1)
) dможно делать не только select , но и update и delete, но помнить что строки могут и перемещаются как внутри блока, так и между ними
Небольшой пример как копировать данные между базами данных используя go, pgx, и copy.
Предположим что у нас есть два коннекта к базе (одной или нескольким, это не важно). Далее используя io.Pipe() создаём Reader и Writer, и используя CopyTo() и CopyFrom() переносим данные.
r, w := io.Pipe()
go func() {
_, err := db1.PgConn().CopyTo(ctx, w, `copy table1 to stdout binary`)
if err != nil {
slog.Error("error", "err", err)
return
}
_ = w.Close()
}()
_, err = db2.PgConn().CopyFrom(ctx, r, `copy table1 from stdin binary`)
_ = r.Close()Вся прелесть тут в том что используем наиболее быстрый способ с точки зрения PostgreSQL.
Используя copy (select * from where ... order by ... limit ...) to stdout можем регулировать нагрузку на чтение, следить за прогрессом и управлять копированием данных.
В качестве Reader может выступать что угодно, хоть файл csv, хоть другая СУБД, но тогда данные придётся дополнительно конвертировать в формат понимаемый PostgreSQL - csv или tsv, и использовать copy ... from stdin (format csv).
Нюанс: copy ... from stdin binary , binary обязывает использовать одинаковые типы данных, нельзя будет integer колонку перенести в колонку smallint, если такое требуется, то параметр binary надо опустить.
Java-разработчики, мы вас ждём на One day offer!
Что такое One day offer?
Мероприятие, на котором можно стать частью нашей команды: без лишних собесов, тестовых заданий и бюрократии
Кто может участвовать?
Мы ищем java-разработчиков middle+ и senior уровня. Неважно, в каком городе ты живешь, главное находиться на территории России
Когда и где?
9 ноября в онлайне
Как попасть?
Оставь заявку на участие
Пройди предварительное онлайн-интервью и получи приглашение на ивент
Подключайся на ивент, чтобы познакомиться с проектами поближе, пройти финальное собеседование и получить свой оффер
Узнать подробности и подать заявку по ссылке
❗️Последний день подачи заявки на участие — 7 ноября