SQL *

О способе оценки производительности отдельного SQL запроса .

В дополнении к теме:

Размышления о мониторинге производительности отдельного SQL запроса

Для возможного варианта решения задачи:

https://habr.com/ru/posts/833162/

если производительность отдельного SQL запроса в настоящее время не мониторится.

Предположение.

Для того, чтобы оценить производительность отдельного SQL запроса необходимо и достаточно получить отношение стоимости запроса (EXPLAIN ANALYSE) к актуальному времени выполнения запроса .

Важное следствие и ограничение:

Данную оценку производительности можно получить только на данный конкретный момент времени при данном конкретном состоянии СУБД в целом.

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

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

Друзья! У Петра вышло продолжение серии обзорных статей про ClickHouse — open-source OLAP базу данных, ориентированную на колонки. В новой статье наш DevOps-инженер рассказывает про особенности репликации в этой СУБД.

Из этой части вы узнаете:

• как работают сервисы координации Zookeeper и ClickHouse Keeper;

• по какой причине может произойти падение репликации;

• почему не следует очищать Keeper вручную.

Чтобы вспомнить, о чём Пётр рассказывал в первой части — нажмите сюда.

А чтобы ознакомиться с новой частью — сюда.

В продолжении темы - "время отклика СУБД" https://habr.com/ru/posts/827054/

Метрику не имеет смысла использовать для алерта о деградации производительности СУБД .

Допустим имеется OLTP нагрузка на СУБД - большое количество коротких запросов. В результате, имеем некоторое значение метрики "Среднее время отклика" = sum(total_exec_time) / sum(calls).

И вот , ситуация изменилась - запросов OLTP стало меньше , но появились аналитические/долгие запросы .

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

Является ли данная ситуация алертом для создания инцидента о деградации производительности СУБД ?

Конечно же - нет.

Более того - в этом случае резко увеличится количество обрабатываемых блоков разделяемой области.

Что также не является показателем деградации производительности СУБД , даже совсем наоборот .

P.S. Использование данной метрики оправдано только в одном сценарии - нагрузочное тестирование инфраструктуры при обязательном условии постоянства нагрузки на СУБД.

На днях опубликовал на GitHub свой скрипт 2013 года, который наполнял с ИБП Ippon Smart Winner 750 базу данных IBM DB2 данными по напряжению сети (за каждую секунду в течение года, по результатам наблюдений скорректировал уставки на реле напряжения, их безопасность для техники подтвердилась при отгорании нуля), обновлял статус моего DynDNS клиента по данным с роутера, запускал и останавливал виртуальную машину VMware Player (там у меня крутилась openSUSE с сайтом на Apache/Django) по расписанию и когда в планировщике BeholdTV была запланирована запись кабельного телевидения (это было необходимо, поскольку видео захватывалось в .asf/x264 - crf18/AC3 без использования графического ускорения и на всё ресурсов не хватало), следил за качеством ADSL линии. А сегодня дополнил ещё рядом скриптов: в 2014 перенёс сайт на Raspberri Pi (Arch Linux ARM) и там стал захватывать IPTV видео. Про захват у меня на Дзене можно почитать, а скрипы создания оглавления и некоторого контроля версии файлов (WSH/JS) опубликовал на GitHub здесь же. Также скрипты по установке времени и некоторой оптимизации скорости отклика сайта, мониторинга доступности посредством Online Domain Tools. Ещё дополнил своими Windows скриптами по работе с СУБД IBM DB2 Express-C и её оптимизации по книге "Best practices Tuning and monitoring database system performance" (она тоже выложена в соответствии с лицензией). Изначально не включил библиотеку RGraph для построения графиков, теперь выложил под лицензией MIT 2013 года.

На днях вышел из строя ИБП. Переключился на стабилизатор. Было уже. До техники APC стоял ИБП Ippon Smart Winner 750. Мониторил напряжение. Сохранял в базе данных SQL сервера IBM DB2 Express-C 10.5 данные за каждую секунду и хранил в течение года. Это помогло выставить уставки на реле напряжения и нормально пережить несколько отгораний нуля без потерь для техники. Скрипты соответствующие опубликовал сегодня на GitHub у себя. При работе от стабилизатора приходилось потом чинить неоднократно SQL базу данных, благо у меня было настроено журналирование в "Архивном" режиме, а сама база данных периодически тестировалась на наличие повреждений. Сложнее с остальной частью данных на компьютере: в NTFS принято частичное журналирование и внезапное отключение ведёт к необходимости решать, восстанавливать ли из бэкапа или соглашаться с тем, что возможно какие-то повреждения будут не сразу обнаружены и могут вызвать проблемы с дальнейшим ремонтом. Чуть раньше такой же пост на Дзене у себя опубликовал.

Обновление библиотеки asyncpg-lite до версии 0.3.1.1

Рад сообщить, что библиотека asyncpg-lite обновлена до версии 0.3.1. Все предыдущие версии были удалены и больше не доступны для установки. Для корректной работы, пожалуйста, удалите старые версии с помощью следующей команды:

pip uninstall asyncpg-lite

Чтобы установить последнюю версию, используйте:

pip install --upgrade asyncpg-lite

Актуальная версия: 0.3.1.1

Вы можете найти страницу библиотеки на GitHub по следующему адресу: asyncpg-lite на GitHub.

Что нового в версии 0.3.1.1

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

Причины обновления

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

Подробный разбор новой версии библиотеки я постараюсь опубликовать завтра в формате статьи.

Благодарю за ваше внимание и поддержку!

Все те кто пользовался библиотекой asyncpg-lite. Сообщаю, что завтра вечером будет выпущено обновление библиотеки asyncpg-lite. В новой версии библиотека будет полностью переписана с использованием SQLAlchemy и asyncpg (в старой версии использовался чистый asyncpg), при этом базовый синтаксис останется практически неизменным.

Такое решение принято из-за ограничений чистого asyncpg в предотвращении SQL-инъекций. Вместо разработки собственного решения я решил воспользоваться проверенными инструментами ORM, которые предоставляет SQLAlchemy, но, при этом, я постарюсь максимально сохранить ту простоту в использовании, которая была в старых версиях asyncpg-lite.

Начиная с версии asyncpg-lite 0.3, библиотека будет основываться на SQLAlchemy и драйвере asyncpg для работы с PostgreSQL. Версии ниже 0.3 будут сняты с доступности для скачивания и установки с завтрашнего дня.

С выходом asyncpg-lite 0.3 настоятельно рекомендуем удалить старые версии библиотеки и установить актуальную.

Вместе с новой версией библиотеки будет опубликована сопроводительная статья, подробно описывающая все изменения в синтаксисе.

Благодарю за внимание! 🚀

asyncpg-lite обновлена до версии 0.2.2.1!

В новой версии:

• Убран флаг dsn_flag (теперь достаточно не передавать данные в параметр dns и состояние этого флага будет автоматически сброшено на dsn_flag = False)

• Параметр deletion_password теперь обязательный (это сделано для безопасного выполнения критических операций - используйте надежный пароль)

• Добавлен флаг debug: bool во все методы (по умолчанию его значение False)

• Исрпавлены ошибки и улучшены логи (теперь там больше полезной информации)

Зачем нужен флаг debug?

Установив этот флаг в методе вы сможете вывести в консоль дополнительную информацию, такую как параметры запроса и сам SQL-запрос.

На уровень всего класса DatabaseManager не выводил, чтоб не перегружать консоль информацией.

С библиотекой вы сможете ознакомиться тут: asynpg-lite: Простой асинхронный менеджер для PostgreSQL на Python

Благодарю за обратную связь. Обязательно оставляйте свои замечания и пожелания к новым дополнениям к библиотеке.

SQL исполняется 50 лет. Сегодня вспомним историю его создания

В далёком 1970 году Эдгар «Тед» Кодд, информатик из IBM, опубликовал революционную статью под названием «A Relational Model of Data for Large Shared Data Banks», тем самым заложив основы теории реляционной модели данных.

Поначалу IBM не горела желанием внедрять наработки Кодда. В то время у компании была IMS, довольно успешная иерархическая база данных. Лишь спустя три года, в 1973 году, IBM запустила System R — исследовательский проект по изучению теории Кодда. Факт: сам Эдгар практически не принимал участия в разработке. Зато активно трудились двое других сотрудников IBM — Дон Чемберлин и Рэй Бойс, которые ещё до запуска проекта разработали SQUARE.

В синтаксисе SQUARE было много нижних индексов и некоторые математические обозначения. Печатать команды с клавиатуры было неудобно. Чемберлин и Бойс решили сделать язык более простым, таким, чтобы он напоминал структуру английского предложения. Так и появился SEQUEL, который потом переименовали в Structured Query Language — «язык структурированных запросов».

Первый выпуск SQL состоялся в июне 1974-го.

Как к SQL относился Тед Кодд? Он считал, SQL неправильно реализовывал теорию реляционной модели данных. В 1985-м Эдгар опубликовал «12 правил Кодда», описывающие, что должна содержать НАСТОЯЩАЯ реляционная СУБД. Стоит ли говорить, что SQL сам по себе не соблюдает их все?

📺 Советую посмотреть свежее интервью с Доном Чемберлином, где он рассказывает об истории своего детища.

SQL vs NoSQL: как выбрать архитектуру БД для мобильного приложения

SQL (Structured Query Language) — это язык запросов, которые мы используем для работы с реляционными базами данных. У таких БД жесткая структура в виде таблиц. Вся информация там хранится в столбцах и строках. Структурированность — одно из главных преимуществ SQL баз данных.

NoSQL — нереляционные БД, у них нет жесткой структуры. Скажем, у нас есть класс — пользователи. У каждого из них есть ID, имя, фамилия и проч. Эти данные помещаются в отдельный файл (а не в таблицу), и взаимодействие происходит только с этим файлом. Вы уже не можете забрать какое-то одно поле и работаете только с этим классом.

Чтобы выбрать между SQL и NoSQL, нужно исходить из задач бизнеса.

• Если вы делаете маленькое приложение на узкую аудиторию или MVP, можно смело выбирать NoSQL. Такие базы быстрые, с ними проще работать, они легко масштабируются. А если проект растет как на дрожжах — NoSQL можно быстро переписать на SQL.

• Если вы делаете средний по объему проект, лучше SQL. Хотя, если очень хочется, можно и NoSQL. Но тут есть особенности: выбирайте NoSQL, только если у вас есть налаженные ивенты, налаженная имплементация баз данных и опыт работы с NoSQL.

• Если вы делаете энтерпрайз, лучше выбрать SQL. Представим, что в каком-то крупном маркетплейсе 10 тысяч человек оплатили покупки, но не получили товары, потому что транзакция данных прошла некорректно. Цена ошибки тут слишком высока — поэтому SQL.

Больше подробностей — в нашем блоге.

Google Firebase сдался и добавил в свои сервисы SQL базу данных (облачную PostgreSQL) в форме Firebase Data Connect.

Пока в виде preview сервис можно попробовать бесплатно. Потом собираются брать плату и за саму базу, и за API доступа к ней.

Вряд ли Google с такими политиками сможет конкурировать с Supabase.На данный момент это две основные площадки, с которыми фронтендер или мобильный разработчик может без излишних усилий сделать удобный облачный бэкенд, как без логики (просто CRUD доступ), так и с ней (Functions), и оставаясь в рамках стандартов (не сильно привязываясь к проприетарным решениям сервисов).

Состоялся релиз мажорной версии открытого масштабируемого решения для кластеризации баз данных MySQL — Vitess 19. Исходный код проекта опубликован на GitHub под лицензией Apache License 2.0.

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

Изменения и дополнения в Vitess 19:

• прекращение поддержки MySQL 5.7. Разработчики советуют пользователям выполнить обновление до MySQL 8.0, используя Vitess 18, прежде чем переходить на Vitess 19. Однако Vitess 19 по-прежнему будет поддерживать импорт из MySQL 5.7;

• добавлены новые метрики для консолидации потоков и версия сборки в /debug/vars, чтобы обеспечить более глубокое понимание и отслеживаемость;

• улучшена совместимость запросов, реализована поддержка операций удаления из нескольких таблиц, новый запрос SHOW VSCHEMA KEYSPACES и несколько других улучшений синтаксиса SQL, которые расширяют совместимость Vitess с MySQL;

• поддержка отсрочки попыток переключения в случае блокировки. Поддержка принудительного отключения;

• улучшение процесса инкрементного резервного копирования: поддержка имён резервных копий и пустых резервных копий.

«Следуя тенденции последних трёх лет, новая версия Vitess быстрее предыдущей во всех тестах, которые мы отслеживаем в Arewefastyet. Мы исправили несколько проблем с производительностью, доработали интерфейс и код», — пояснили разработчики, порекомендовав изучить документацию проекта и список исправлений.

«Циан» перенесла за 6 недель более 500 микросервисов, 2 монолитных приложения и 500 ТБ своих данных на платформу Yandex Cloud без перерывов в работе сервиса, включая Terraform и Salt.

Миграция проходила в рамках совместной работы специалистов «Циан» и поддержки Yandex Cloud. На облачную платформу они перенесли системы управления базами данных, очереди сообщений и поисковую систему. Данные поступают в объектное хранилище. Для эффективной работы с микросервисами компания использует Yandex Managed Service for Kubernetes.

Технологический стек компании включает Python, C#, Node.js. Работа с данными велась в PostgreSQL, MSSQL, Cassandra, Elasticsearch; приём и отправка сообщений — в RabbitMQ и Kafka.

Подготовительный этап миграции в Yandex Cloud занял 4 месяца. Из них 2 месяца ушло на проверку осуществимости миграции.

За полтора месяца миграции микросервисы и один монолит перенесли в Yandex Managed Service for Kubernetes, а монолит на Windows IIS — на виртуальные машины Compute Cloud. Для хранения данных используют объектное хранилище Object Storage, для гибкой работы с документоориентированными моделями данных — Managed Service for MongoDB, для эксплуатации и администрирования реляционных баз данных — Managed Service for PostgreSQL и Managed Service for MySQL.

Чтобы избежать даунтаймов, пришлось сделать утилиту для миграции микросервисов, развернуть несколько кластеров на Vanilla Kubernetes, синхронизатор эндпоинтов, перейти на нереплицируемые SSD для работы MSSQL.

В прошлом посте я показывал, один из вариантов бессмысленного усложнения запроса использованием 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-кода - и ваши запросы "будут мягкими и шелковистыми"!

Вышел релиз легковесной СУБД, оформленной в виде подключаемой библиотеки, SQLite 3.45. Код проекта распространяется как общественное достояние (public domain) и может использоваться без ограничений и безвозмездно в любых целях. Финансовую поддержку разработчиков SQLite осуществляет специально созданный консорциум, в который входят такие компании, как Bentley, Bloomberg, Expensify и Navigation Data Standard.

Основные изменения:

• все SQL‑функции для работы с форматом JSON переписаны и переведены на новый внутренний формат дерева разбора JSONB, который сериализируется и может храниться в БД для исключения повторного разбора при использовании значений JSON;

• в виртуальную таблицу FTS5, применяемую для полнотекстового поиска, добавлена опция tokendata, позволяющая использовать собственные токенизаторы;

• по умолчанию включена оптимизация SQLITE_DIRECT_OVERFLOW_READ, при которой overflow‑страницы, размер которых больше стандартного размера страницы b‑tree, читаются из файла напрямую, минуя кэш;

• в планировщике запросов повышена эффективность оптимизации транзитивных ограничений (transitive constraint) и улучшено игнорирование индексов, которые признаны низкокачественными при выполнении операции ANALYZE;

• в интерфейсе командной строки улучшено отображение содержимого в кодировке UTF-8 на платформе Windows. Обеспечено автоматическое определение использования CLI‑интерфейса при воспроизведении скриптов «.dump» и внесение соответствующих изменений в настройки.

Источник: OpenNET.

В начале января 2024 года состоялся релиз реляционной СУБД Firebird 5.0, разработка которой началась в 2016 году. Четвёртая версия проекта вышла в июне 2021 года.

Проект Firebird продолжает развитие исходного кода СУБД InterBase 6.0, открытого в 2000 году компанией Borland.

СУБД Firebird распространяется под свободной лицензией MPL. Проект поддерживает стандарты ANSI SQL, в том числе такие возможности, как триггеры, хранимые процедуры и репликацию. Бинарные сборки СУБД Firebird 5.0 доступны для Windows (x86, x64), Linux (x86, x64, ARM32, ARM64), macOS (x64) и Android (x86, x64, ARM32, ARM64, Embedded).

Ключевые изменения в Firebird 5.0:

• реализована возможность выполнения операций в многопоточном режиме;

• добавлена поддержка частичных индексов;

• добавлена возможность обновления БД до актуальной промежуточной версии хранилища (ODS — On-Disk-Structure) на лету (inline update) без создания и восстановления из резервной копии;

• реализован кэш скомпилированных SQL-выражений, обслуживаемый автоматически (устаревающие записи очищаются по мере необходимости);

• добавлен интерфейс для профилирования SQL и PSQL, позволяющий оценивать время выполнения каждого запроса, накапливать статистику о числе запросов и выявлять проблемы с производительностью;

• проведена оптимизация производительности копирования блобов;

• добавлена полная поддержка синтаксиса определения строк, описанного в стандарте SQL.

Источник: OpenNET.

Портал DB-Engines обновил рейтинг популярности СУБД и присудил звание СУБД 2023 года проекту PostgreSQL, который за год продемонстрировал наибольших рост популярности из 417 отслеживаемых систем. Второе место досталось облачной платформе Databricks (за год поднялась с 19 на 17 место), а третье место занял движок Google BigQuery (поднялся с 21 на 19 место).

Ранее PostgreSQL уже признавался СУБД года в 2020, 2018 и 2017 годах. В 2022 году и 2021 году это звание было закреплено за СУБД Snowflake, а в 2019 его получило MySQL, в 2016 - Microsoft SQL Server, в 2015 - Oracle, в 2013 и 2014 годах - MongoDB.

По методике расчёта рейтинг СУБД напоминает рейтинг языков программирования TIOBE и учитывает популярность запросов в поисковых системах, число результатов в поисковой выдаче, объём обсуждений на популярных дискуссионных площадках и в соцсетях, число вакансий в агентствах по найму персонала и упоминаний в профилях пользователей.

Что касается распределения СУБД в рейтинге, PostgreSQL продолжает занимать 4 место, несмотря на наибольший во всем рейтинге рост популярности - 34.11 балла. Рост популярности также демонстрирует проект Databricks и Snowflake. C 8 на 7 место поднялось решение Elasticsearch, а с 33 на 29 - СУБД Firebird, c 44 на 37 - ClickHouse, с 62 на 50 - Prometheus, с 48 на 42 - OpenSearch, с 85 на 76 - TimescaleDB.

Значительное снижение популярности в 2023 году наблюдается у MySQL, Microsoft SQL Server, MongoDB, Redis и SQLite.

Источник: OpenNET.

Если вдруг вам понадобилось базу 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;

❓100 Вопросов по Машинному обучению (Machine Learning) - Вопрос_10

?Вопрос_10: Что такок Tarantool и как он устроен ? (Часть_3)

1. Транзакции: В более новых версиях Tarantool была добавлена поддержка механизма транзакций. Транзакции позволяют группировать несколько операций в единую атомарную операцию, что обеспечивает целостность данных.

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

t.me/DenoiseLAB (Еесли вы хотите быть в курсе всех последних новостей и знаний в области анализа данных)