Как оптимизировать производительность запросов в PostgreSQL

Как оптимизировать производительность запросов в PostgreSQL

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

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

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

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

Воспользуйтесь инструментом «Проверка ответа сервера» в Яндексе. В форме необходимо ввести адрес страницы (это может быть главная страница сайта или внутренняя), которую вы хотите проверить, и нажать на кнопку «Проверить».

Внизу формы появятся результаты проверки:

Здесь можно посмотреть код ответа сервера (должен быть 200 для существующих страниц), IP сайта, кодировку, размер страницы, а также – время ответа в мс. В нашем случае – это 23 мс.

Если время ответа сервера превышает 50 мс, лучше провести работы по оптимизации показателя. Если время превышает 200 мс, данные работы необходимо провести обязательно.

Создание поля автоинкремента (счетчика) в таблице базы данных MS SQL Server, которая размещается в «*.mdf»-файле

В данной теме показано как создать поле-счетчик (уникальное поле) в таблице базы данных Microsoft SQL Server , которая размещается в файле «MyDataBase.mdf» .

Данная тема базируется на предыдущих темах:

Поиск на других ресурсах:

Содержание

Условие задачи

Заданы файлы «MyDatabase.mdf» и «MyDataBase.ldf» базы данных, которые предназначены для работы в системе управления реляционными базами данных Microsoft SQL Server . Файлы можно скачать в архиве здесь .

База данных содержит одну таблицу с именем Source . Таблица Source содержит следующие поля:

• ID_Source – целого типа ( int );
• Name – типа «строка» из 50 символов;
• Address – типа строка из 100 символов.

В задаче нужно сделать поле ID_Source уникальным счетчиком. При добавлении новой записи в таблицу, значение поля должно увеличиваться на 1 (автоинкрементное поле), то есть быть уникальным.

Выполнение

1. Подключение «*.mdf» -файла базы данных в MS Visual Studio

Запустить на выполнение MS Visual Studio .

С помощью утилиты Server Explorer нужно подключить ранее созданніе файлы «MyDataBase.mdf» и «MyDataBase.ldf» . Архив с файлами можно загрузить здесь .

Рекомендуется, чтобы файлы были размещены в одном каталоге.
Пример добавления готового (ранее созданного) «*.mdf» -файла локальной базы данных к MS Visual Studio подробно описывается в статье:

После подключения базы данных в окне Server Explorer будет отображена база данных «MyDataBase.mdf» (рисунок 1).

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

Рис. 1. База данных MyDataBase.mdf в окне Server Explorer

Рис. 2. Таблица Source

2. Настройка поля ID_Source как счетчика. Свойство «Identity Column»

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

Чтобы установить поле ID_Source уникальным, нужно выполнить следующие действия:

• раскрыть вкладку Tables в базе данных MyDataBase.mdf (рисунок 3);
• в вкладке Tables сделать правый клик «мышкой» и из контекстного меню вызвать команду «Open Table Definition» (рисунок 3). В результате откроется окно определения полей таблицы;
• активировать поле ID_Source и в окне свойств «Properties» установить значение свойства «Identity Column = ID_Source» (рисунок 4);
• сохранить и закрыть таблицу Source .

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление свойства Identity Column в значение ID_Source

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

Теперь эту таблицу можно использовать в своих проектах.

3. Заполнение таблицы данными

После установлки в таблице поля ID_Source как уникального счетчика можно программно или вручную заполнять таблицу данными (записями).

Чтобы заполнить таблицу данными (записями) нужно выполнить следующие действия:

• вызвать контекстное меню с помощью правого клика мышкой на таблице Source и из контекстного меню выбрать команду «Show Table Data» (рисунок 5). В результате откроется окно ввода данных в таблицу (рисунок 6);
• ввести данные в таблицу Source . Поскольку, поле ID_Source есть счетчиком, то вводить данные в это поле не удастся. Можно вводить данные только в полях Name и Address . Значение поля Source будет генерироваться автоматически.

Рис. 5. Команда «Show Table Data»

Рис. 6. Ввод данных в таблицу Source

Как построить отчет по целям

1. Перейти в стандартный отчет «Конверсии».
2. В любом другом отчете в строке над данными можно выбрать фильтр по цели.
   Кнопка «Выберите цель» позволяет отфильтровать данные только по одной из целей, в разделе «Метрики» их можно выбрать несколько — просто введите в поиске «Достижения целей» и отметьте нужные вам для отчета. После этого отчет перестроится.
3. Также с помощью целей можно выделить сегмент пользователей:
   Здесь можно включить или исключить цель. Например, если включить цель «Нажали “заказать”», в отчет попадут только визиты, в которых была достигнута эта цель. А если исключить — попадут все визиты, в которых эта цель достигнута не была.

Полезные ссылки

Алгоритмы кэширования — статья на wiki.
LRU (Least Recently Used) — подробная статья о LRU с примерами реализации на C, C++, Java.
LRU, метод вытеснения из кэша — статья о том, как быстро реализовать алгоритм LRU.
Least Frequently Used (LFU) Cache Implementation — статья о LFU с примером на C++.
LFU cache in O(1) in Java — пример реализации LFU на Java.
Алгоритмы кэширования — что-то вроде презентации некоторых алгоритмов кэширования в формате PDF.

Поиск и исправление медленных запросов к базе данных WordPress

Медленные SQL-запросы могут негативно отразиться на производительности WordPress-сайта. Иногда медленные запросы – это результат плохо сформированного SQL-кода, который должен быть реализован несколько иным путем. И в некоторых ситуациях медленные запросы сначала являлись быстрыми – но с ростом возраста сайта запросы становились все медленнее и медленнее, и уже начинали отставать от расширяющейся базы данных.

Вне зависимости от того, каким образом ваш SQL-код стал медленным, давайте взглянем на некоторые пути поиска и устранения проблемных запросов в WordPress.

Поиск медленных запросов

Поиск источника медленных запросов включает в себя два шага:

1. Определяем, какие запросы на самом деле медленные.
2. Ищем код, который генерирует и исполняет их.

Давайте рассмотрим два плагина и одно SaaS-решение, которые помогут нам найти медленные запросы.

Query Monitor

Query Monitor – плагин, выводящий различную информацию о текущей странице. В дополнение к многочисленным данным о внутренних механизмах WordPress, плагин выводит детальную статистику по следующим пунктам:

• Сколько запросов произошло по этому вызову
• Какие запросы на странице заняли больше всего времени
• Какие функции выполнялись дольше всего в SQL-запросах
• Какие запросы шли от плагинов, тем и ядра WordPress

Query Monitor выделяет медленные запросы с помощью страшного красного текста, который упрощает выявление проблемного SQL:

Debug Bar

Еще один великолепный инструмент для поиска медленного SQL– это старый добрый плагин Debug Bar. Debug Bar выводит информацию о внутренних механизмах WordPress, когда вы загружаете страницу с такими данными, как:

1. Параметры WP_Query
2. Информация вызова (включая соответствие правил перезаписи)
3. SQL-запросы, генерируемые текущей страницей

Чтобы активировать третий пункт в Debug Bar (SQL-отслеживание), убедитесь в том, что SAVEQUERIES включена на вашем сайте – обычно это делается в файле wp-config.php:

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

Поиск медленного SQL в Debug Bar не так прост. К примеру, плагин не имеет сортируемых таблиц и не подсвечивает медленные запросы. Debug Bar обеспечивает трассировку функций, которая позволяет выявить источник запроса.

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

NewRelic

NewRelic – это сервис, который измеряет и отслеживает произвольность вашего веб-приложения, включая WordPress. Сервис предлагает массу разной информации о производительности вашего сайта. Очень легко потеряться в данных, которые предлагает NewRelic, начиная с детального выполнения кода и заканчивая пошаговыми отчетами по SQL-запросам.

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

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

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

Обнаружение медленных запросов с помощью EXPLAIN

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

В этом нам поможет MySQL ключ EXPLAIN. Он позволит понять, что произошло. Добавление EXPLAIN в начало запроса покажет, как MySQL выполняет запрос. В случае с более сложными запросами EXPLAIN поможет выявить медленные участки в ваших SQL – к примеру, медленные подзапросы или неэффективные процессы.

К примеру, если у вас есть запрос следующего вида:

Вы можете добавить к нему EXPLAIN, просто выполнив следующее:

Вот как выглядит вывод EXPLAIN в phpMyAdmin:

Я не совсем понимаю, как работают внутренние механизмы MySQL, но при этом запуск EXPLAIN для запросов дает мне понимание того, как MySQL выполняет мой SQL-запрос. Использует ли запрос индекс? Сканирует ли он всю таблицу? Даже для простых запросов EXPLAIN обеспечивает некоторую информацию, позволяющую понять, как все работает.

Вы можете запустить EXPLAIN либо из командной строки MySQL, либо через ваш предпочтительный MySQL-инструмент.

Исправление медленных запросов

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

Вариант 1. Изменение запроса.

На сайте CSS-Tricks у нас есть запрос, который выполняется очень медленно – этот запрос является частью мета-панели Custom Fields. Вот сам SQL:

Данный фрагмент кода содержит SQL, который позволяет получить список meta_keys из таблицы wp_postmeta. Интересующие нас meta_keys не должны начинаться с подчеркивания «_». Оператор GROUP BY означает, что каждый результат является уникальным.

Выполнив этот запрос 5 раз, мы получим следующие данные:

Могли бы мы написать другой запрос, чтобы получить тот же самый результат? Нам нужно выбрать уникальные meta_keys. Синоним к слову «unique» (уникальный) – distinct, и, как оказалось, в SQL есть такой оператор!

Используя оператор DISTINCT, мы можем сделать следующее:

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

Данное сравнение демонстрирует существенное улучшение!

Вариант 2. Добавление индекса.

Когда вы выполняете запрос SQL к стандартной таблице MySQL, MySQL сканирует всю таблицу, чтобы определить, какие строки соответствуют данному запросу. Если ваша таблица очень большая, то в таком случае сканирование займет довольно много времени.

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

Индексы могут быть добавлены к отдельным столбцам или к группе столбцов. Синтаксис выглядит следующим образом:

С индексом для meta_key исходный SQL-запрос будет выполняться следующим образом:

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

Вариант 3. Кэширование результатов запроса

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

Чтобы кэшировать запрос, нам понадобится WordPress Transients API. Transients используются для хранения результатов сложных операций, таких как:

• Запросы к внешним сайтам (к примеру, получение последних записей Facebook)
• Медленные блоки обработки (к примеру, поиск длинных строк с помощью регулярных выражений)
• Медленные запросы к базе данных

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

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

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

Выбор подхода

Мы рассмотрели три варианта, и кроме них есть еще примерно 17 способов решения проблемы с медленными запросами. Какой подход лучше всего выбрать?

Когда я работаю с чужим кодом, я предпочитаю ориентироваться на принцип программистов: «Выбирай самое простое решение, которое может работать».

Первый вариант (переписывание запроса) позволил получить превосходные результаты, однако как быть, если переписанный запрос не всегда выдает те же самые результаты? Мы можем неосознанно нарушить код путем замены запросов.

Второй вариант (добавление индекса) не всегда возможен, что зависит от таблицы и столбцов, используемых запросом. В случае с базовыми таблицами WordPress вам нужно будет подумать о возможных побочных эффектах индексации:

• Поддерживает ли обновление ядра дополнительные индексы?
• Не замедлит ли добавление индекса другие запросы, такие как INSERT и UPDATE?

Третий вариант (кэширование результатов через transients) оказывает минимальное воздействие – мы не меняем исходный запрос и нам не нужно изменять структуру базы данных.

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

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

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

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

Пример 3.1

Что мы можем легко сделать, так это разделить количество вызовов в самом начале. Давайте установим порог не более пяти запросов, выполняющихся параллельно в любой момент времени. Таким образом, распределим список операций на пакеты размерностью в пять запросов. Затем мы вызываем fn для каждого аргумента, чтобы получить список промисов. И, наконец, мы ждем завершения этого списка, прежде чем переходить к следующему.

Что ж, не могу не согласиться, мы сделали то, что нужно. Количество Promises, выполняемых параллельно, не должно превышать 5 в любой момент времени. Мы обрабатываем данные пакетно и получаем результат. Общее выполнение заняло 40 секунд, что хуже, чем 9, но намного лучше, чем 142. Однако давайте взглянем на диаграмму.

Исходя из результатов, мы можем увидеть пики количества выполненных промисов. Это означает, что нагрузка не распределяется и, вероятно, часть ресурсов находится в режиме ожидания. Проблема состоит в том, что для выполнения одного пакета операций требуется столько времени, сколько требуется для выполнения самой медленной. Пакет может содержать 5 операций, 4 из которых занимают 1 секунду, а последняя — 10 секунд. Поскольку мы используем Promise.all, для завершения всего пакета также потребуется 10 секунд.

Пример 3.2

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

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

Теперь изменим логику. Создадим пакет зарезолвленных промисов и выполним следующие действия к каждому из промисов в пакете:

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

Таким образом, как только один из выполняемых в данный момент промисов будет выполнен, мы возьмем следующий аргумент и вызовем fn . И поскольку размер начального пакета равен нашему пределу количества параллельных запросов, у нас не будет более 5 выполняемых промисов за один момент.

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

Общее время выполнения заняло 33 секунды. Мы выиграли 7 секунд и намного лучше распределили нагрузку.

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

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

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