Запросы

При поиске проблем в RDBMs разработчик обычно подозревает медленные запросы. А что, если дело не в них? О том, какого типа запросы дают нагрузку на базу данных, не позволяя вашему приложению работать должным образом, рассказал в своем докладе на конференции Saint HighLoad++ Online 2020 администратор баз данных Data Egret Андрей Сальников.

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

До момента всеобщей изоляции на поддержке Data Egret был сервис, на котором мастер-база держала где-то 50 тысяч транзакций в секунду.

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

Что было сделано

• Переезд базы данных и приложений на более мощные сервера, у которых больше процессоров и памяти;

• Увеличение количества приложений, потому что нужно обслуживать более мощную онлайн-нагрузку.

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

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

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

• Среднее время выполнения запросов уменьшилось на 10 мс по сравнению с предыдущим оборудованием и предыдущей нагрузкой;

• Сетевой трафик почти не изменился;

• Количество запросов стало больше. Использование ресурсов трудно оценить, потому что изменилось оборудование, и однозначной оценки, больше или меньше ресурсов используется, в такой ситуации дать сложно;

• Не получилось оценить и изменение параметров сервера.

Вроде бы, все хорошо. Но проблема осталась не решенной.

Так как речь идет о базе данных, нужно было посмотреть, что изменилось с профилем нагрузки на БД, и что случилось с запросами. Мониторинг результатов не дал. Но есть и другие инструменты.

Например, pgstatstatements.

В Data Egret этот инструмент используется постоянно, так как в нем есть три приятных бонуса:

1. Отчеты за прошлые периоды обновляются каждые сутки;

2. pgstatstatements логирует абсолютно все, в том числе, частые запросы. В отличие от pgBadger, который зависит от настроек;

3. Можно оценить влияние запросов на серверные ресурсы и соответственно на базу данных, потому что БД использует серверные ресурсы (дисковая память и процессорное время).

Что же удалось обнаружить в pgstatstatements?

До того, как в компании начала расти нагрузка, стандартный отчет, который формируется на основании pgstatstatements, выглядел так:

Есть общая нагрузка по количеству запросов, которая прилетела в база данных за сутки. Здесь это total queries: 3,555,539,206 (unique: 2,268).

Есть специальная позиция other, в которую группируются запросы, не дающие существенную нагрузку на БД: calls: 1,977,858,485 (43.42%) avg_time: 0.06ms (IO: 33.3%).

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

Все остальные запросы в отчете выглядят примерно так: SELECT field1, field2 FROM table1 WHERE field3 = ? and field4 between ? and ?

Они несложные: какая-то выборка по простым условиям, иногда 1-2 joinа ничего сверхъестественного, что могло бы вредить базе данных и требует нашего разбирательства.

После того, как вырос онлайн, отчет изменился:

Что изменилось после начала самоизоляции

• Время на запросы уменьшилось

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

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

• Количество запросов выросло

Общее количество запросов, которые прилетели в базу данных, стало существенно больше (примерно в 1,5 раза). Это видно на графиках.

• Отсутствие пропорций

Если сравнивать позицию по запросам, которые не оказывают существенного влияния на БД, то тут рост не такой пропорциональный:

Казалось бы, должно быть 3 млрд запросов, но на самом деле рост всего несколько процентов. Значит, что-то изменилось в нагрузке на базу данных, и нужно смотреть отчет дальше.

Смотрим вглубь

Просматривая отчет, мы долистали до 17 позиции и увидели запрос, который вызывается 1,5 млрд раз и выглядит как SELECT 1:

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

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

В итоге SELECT 1, являясь стандартным пингом коннекта, и, в то же время, приводит к негативным результатам. Ведь на время этого пинга мы:

• Теряем возможность использовать это соединение для действительно полезного запроса;

• Даем лишнюю нагрузку на CPU (серверные ресурсы БД);

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

По факту произошло следующее: при переносе приложения потерялся патч на ORM, который немного срезал такие пинги.

После правок

Давайте посмотрим на график по количеству транзакций, после того, как были внесены правки в ORM:

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

Можно отследить результат и по общей нагрузке:

До 4 апреля в приложении был не патченный ORM. После этого пика в 100 тысяч транзакций больше не было. База отдана только под полезные запросы, которые необходимо выполнять.

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

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

SELECT 1 реальный случай. На его месте могут быть более простые запросы. Например, в ситуации, когда для отрисовки веб-формы необходимо вычитывать справочники. Не смотря на то, что они дают полезную информацию, она никогда не меняется. Желательно кэшировать ее на стороне бэкенда, а не ходить за ней в базу данных.

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

Мы поговорили о быстрых запросах. Теперь перейдем к мертвым.

Быстрый и Мертвый

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

План действий тот же самый:

• Изучаем мониторинг;

• Смотрим статистику в pgstatstatements;

• Чиним;

• Говорим об обнаруженной проблеме разработчикам.

Как выглядит отчет теперь?

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

Сразу видим pos:1 total time: 02:05:26 (20.3%, CPU: 38.6%, IO: 2.0%). База данных тратит на этот запрос 38% процессорного времени.

Допустим, мы с определенной частотой ищем в платежной системе последние транзакции, созданные по каждому платежному сервису отдельно:

select distinct on (paymentsystem), * from transactions order by createdat desc;

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

Починка будет выглядеть так:

Из маленького запроса, который работает 1 с, мы получаем большой запрос на 2 мс. Идем в комнату к разработчикам и просим их переписать запрос. Деплоим, и ситуация возвращается в привычное русло.

Отчет pgstatstatements позволяет довольно быстро находить запросы, которые портят нам жизнь.

Инструменты

Давайте рассмотрим плюсы и минусы инструментов, с помощью которых мы можем существенно облегчить себе жизнь.

pgBadger

Плюсы:

• Полная информация обо всех запросах;

• Красивые картинки.

Посмотреть на работу pgBadger можно на тестовой странице.

Минусы:

• Специальный конфиг для логов;

• Дополнительная нагрузка на диски, потому что количество пишущихся логов резко возрастает;

• Анализ запросов постфактум;

• Для pgBadger нужно большое количество места.

pgstatstatements

Плюсы:

• Стандартное расширение PostgreSQL, идет из коробки;

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

• Собирает информацию по всем запросам;

• Группирует одинаковые запросы;

• Можно сбрасывать статистику;

• Можно сделать свой отчет на основе pgstatstatements.

Минусы:

• Оверхед на выполнение запроса 2-5%;

• Запросы обезличенные;

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

• Несущественные запросы вымываются;

• Почти нет мониторингов, которые умеют с ним работать;

• Не видит разницы между SELECT и SELECT FOR UPDATE;

• В сыром виде данные неудобны для восприятия.

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

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

Типичные проблемы

Какие же проблемные запросы можно встретить на практике?

Давайте рассмотрим самые распространенные из них.

• Результат запроса нигде не используется;

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

• Неоптимально работающий запрос;

Например, distinct.

• Постоянное обращение к справочникам;

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

• Нехватка индексов на простеньких запросах;

Что-то задеплоили, но забыли индекс. Это можно увидеть в отчете и исправить.

• Смешивание нагрузки OLTP и OLAP на репликах.

Эта проблема связана с общением разработчиков с бизнес-экспертами. Последним бывает трудно объяснить, что OLAP нагрузка не может выполняться на реплике, которая отстает на 3 секунды.

Если отчет выполняется 2 часа, то информация в нем уже на 2 часа устарела. Соответственно, когда одного сервера не хватает, нагрузку OLTP и OLAP нужно разделять по разным серверам.

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

Когда к нам приходит разработчик и говорит, что у него тормозят запросы, дело далеко не всегда в этом. Часто речь идет о проблеме на стороне приложения: например, оно открывает долгие транзакции в базе данных.

Выглядит это так: существуют длительные транзакции, которые длятся 30-40 минут, и в это же время есть миллисекундные всплески запросов.

Если в это время посмотреть на состояние соединения в базе данных, мы увидим много простаивающих соединений (они отмечены желтым цветом):

idle in transaction это соединение, когда приложение открыло транзакцию в БД, выполнило один запрос и ушло заниматься своими делами, забыв закрыть транзакцию. Именно это, в основном, и является первопричиной многих проблем. Как только она решается, можно заниматься тем, с чего начинается статья: искать запросы, которые мешают вам жить.

Вместо заключения

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

Конференция HighLoad++ 2020 пройдет 20 и 21 мая 2021 года. Приобрести билеты можно уже сейчас.

Хотите бесплатно получить материалы конференции мини-конференции Saint HighLoad++ 2020? Подписывайтесь на нашу рассылку.

А интересующихся миром Java ждем на онлайн-митапе Luxoft TechFest #2: Java with Ontico. На нем поговорим о приватных полях-константах в Jira, неочевидных нюансах Java Reactive Stack и работе с распределенным кешем. А после своих выступлений звезды митапа ответят на самые животрепещущие из ваших вопросов. До встречи 10 февраля в 19:00 мск.

Привет!

Сегодня мы выпустили DataGrip 2021.1: наш самый мощный релиз за последние годы. И это не шутка!

Самое важное:

• Интерфейс для работы с правами доступа

• Контекстные шаблоны Live Templates

• Упрощенная навигация

• Легкое копирование источников данных

• Улучшенная сортировка

• Редактирование данных в MongoDB

• Поддержка Azure MFA

Редактирование прав

В окне редактирования объекта теперь можно изменять права на объект.

Также права можно добавлять и изменять в окне редактирования пользователя или роли. Напомним, что вызываются окна редактирования нажатием Cmd/Ctrl+F6.

Это работает в PostgreSQL, Redshift, Greenplum, MySQL, MariaDB, DB2, SQL Server и Sybase.

Контекстные шаблоны

Нас давно просили сделать как у других: чтобы для таблицы в проводнике можно было быстро сгенерировать простой запрос, например SELECT TOP 100 FROM %tableName%.

Мы всегда отвечали, что как только вы научитесь пользоваться шаблонами кода Live Templates, ваша жизнь изменится навсегда, и не надо будет тыкать мышкой во вложенные меню.

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

В итоге мы сделали новый вид шаблонов кода контекстные шаблоны. Работают они так:

Посмотрим шаблон Select first N rows from a table. Найдите его в настройках:

Он выглядит, как обычный шаблон, и его можно использовать в таком качестве. Но у переменной $table$ есть специальное выражение dbObjectName(). Вы увидите это, нажав на Edit Variables. Так вот, именно это выражение делает шаблон контекстным, то есть значение в эту переменную можно подставить автоматически кликнув на любой объект в проводнике базы данных.

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

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

Редактор данных

Редактирование данных в MongoDB

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

<img src="http://personeltest.ru/aways/habrastorage.org/webt/1t/ux/8y/1tux8yvfyrs2eiha3byoj0ss2tu.png" />

Еще можно менять тип ячейки: из контекстного меню или панели инструментов при просмотре больших значений.

Сортировка

Сортировка стала более удобной:

• Мы добавили поле ORDER BY, а <Filter> переименовали в WHERE. Впишите в ORDER BY условия сортировки, чтобы получился работающий запрос.

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

Если хотите отсортировать данные на стороне DataGrip, отключите Sort via ORDER BY. Конечно, в этом случае, сортируются данные только на текущей странице.

Теперь вы можете по умолчанию открывать таблицы отсортированными по числовому первичному ключу.

Панель инструментов

Мы немного обновили панель инструментов в редакторе данных: добавили кнопки Revert Changes и Find. Кнопки Rollback и Commit в режиме автоматического подтверждения транзакций скрываются.

Транспонирование однострочных результатов

Если в полученном результате одна строка, его удобно сразу смотреть в транспонированном виде. Мы сделали для этого настройку:

Навигация

Предсказуемые действия

Мы удалили эти настройки:

Если вы никогда не меняли их значения по умолчанию, то главное изменение в новой версии для вас такое: действие Go to declaration (Ctrl/Cmd+B) теперь открывает DDL этого объекта. Раньше оно подсвечивало объект в проводнике базы данных.

Для перемещения к объекту в проводнике мы представили новое сочетание клавиш: Alt+Shift+B для Windows/Linux и Opt+Shift+B для macOS.

Теперь нет сложной логики, и каждое нажатие горячих клавиш ведет в туда, куда вы ожидаете попасть:

• Ctrl/Cmd+B открывает DDL.

• F4 открывает данные.

• Alt/Opt+Shift+B подсвечивает объект в проводнике.

Удаление настроек всегда ломает привычки некоторому количеству людей. Мы постарались учесть это. Вот наши советы таким пользователям:

• Все сочетания клавиш можно менять. Если, например, вы не хотите отвыкать от того, что Ctrl/Cmd+B подсвечивает объект в проводнике, назначьте это сочетание клавиш действию Select in database tree.

• В то же время, если вам нравится, что внутри скрипта Ctrl/Cmd+B и Ctrl/Cmd+Click открывает определение CREATE, не убирайте эти сочетания с действия Go to declaration, если вы последовали предыдущему совету.

• Если вам нравилось, что при отключенной настройке Preview data editor over DDL editor двойной клик по таблице открывал DDL, это можно вернуть через ключ в реестре. Он называется database.legacy.navigate.to.code.from.tree. Но мы не советуем менять значения в реестре и надеемся, что те полпроцента людей, у которых эта галочка была снята, быстро привыкнут к новому поведению :)

Если мы про что-то забыли, пишите, пожалуйста, в комментах.

Вкладка Database

Тут мы ничего особо не сделали просто переименовали вкладку Tables в Database. Этим мы напоминаем, что по сочетанию клавиш Cmd+O/Ctrl+N можно искать не только таблицы, но и процедуры, функции, схемы.

Соединение

Поддержка Azure MFA

Мы поддержали интерактивную аутентификацию через Azure Active Directory. Если она включена, при соединении у вас автоматически откроется браузер, где вы сможете завершить аутентификацию.

Версия 2.x драйвера для Redshift

Этот драйвер можно скачать в DataGrip, начиная с версии 2021.1. Главное изменение состоит в том, что теперь запросы можно останавливать.

Полная поддержка Google Big Query

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

Поддержка диалекта CockroachDB

Теперь DataGrip правильно подсвечивает запросы и показывает ошибки в скриптах для CockroachDB. Соответствующую интроспекцию мы сделаем в одном из следующих релизов.

Улучшения в окне соединения

Сделали это окно чуть более дружелюбным:

• Источники данных и драйверы разделены на две вкладки.

• На странице каждого драйвера появилась кнопка Create data source.

• Кнопка Test Connection переехала вниз теперь ее видно из всех вкладок, а не только из General и SSH/SSL.

• Для источников данных на основе файлов (так называемых DDL Data Sources) теперь можно явно задать диалект.

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

Проводник базы данных

Легкое копирование источников данных

Возможность копировать и вставлять источники данных мы сделали давно. Но с этого релиза вы можете использовать самые знаменитые сочетания клавиш в мире Ctrl/Cmd+C/V/X.

• Напоминаем, что когда вы копируете источник данных, в буфер обмена сохраняется XML. Его можно послать коллеге в мессенджере, а он вставит его в свою IDE все сработает.

• Если копирование и вставка происходит в одном проекте, вам не понадобится заново вводить пароль.

• Источник данных можно не только копировать, но и вырезать. Вырезание отменяется при помощи Ctrl/Cmd+Z.

Новый интерфейс

Объекты второстепенной важности (роли, пространства имен, внешние источники и др.) мы поместили в папки Server Objects и Database Objects.

Если хотите чтобы было, как раньше, включите настройку Group Database and Schemas.

[Oracle] Скрытие сгенерированных объектов

Если отключить Show generated objects, то из проводника пропадут:

• Таблицы материализованных представлений

• Логи материализованных представлений

• Вторичные таблицы

[SQLite] Новые типы объектов

В SQLite теперь отображаются функции, модули и виртуальные столбцы.

Улучшения для неподдерживаемых баз

Шаблоны для источников данных

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

Еще раз напомним, что поддержка для таких источников данных базовая. Скрипты подсвечиваются на основе стандарта SQL:2016, а информация об объектах берется из драйвера.

Написание запросов

Инспекция про избыточные имена в CTE

Если запрос не запустится из-за избыточных имен в общем табличном выражении, DataGrip сообщит об этом.

[SQL Server] Системные функции можно использовать без имени схемы

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

Поддержка JSON Lines

Формат JSON Lines используется для хранения данных и логов. И новая версия правильно подсвечивает файлы этого формата.

Толщина шрифта

Теперь вы можете настраивать толщину шрифта.

Импорт / Экспорт

Незагруженные данные

Если бинарные данные не были загружены полностью, вы увидите такое сообщение:

Настройка, которая определяет, какое количество данных DataGrip загружает по умолчанию, находится здесь: Settings/Preferences | Database | Data Views | Maximum number of bytes loaded per value.

Запрос в файле Excel

Когда вы экспортируете данные в Excel, на отдельном листе сохраняется изначальный запрос.

First row is header в контекстном меню импорта

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

Интерфейс

Прикрепление папки при помощи drag-n-drop

Прикрепить папку, то есть открыть ее в панели Files, теперь можно, перетащив её.

Открытие вкладок в режиме разделенного редактора

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

Длинные названия вкладок

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

На этом все!

Скачать триал на месяц

Фидбек принимаем в комментариях к посту и здесь:

Трекер (это если точно нашли проблему)

Телеграм-канал

Твиттер

Почта

Команда DataGrip