Описание:

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

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

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

Но это все плохо. Чтобы решить проблему «тормозов», нужно действовать так, как это делают (или должны делать) врачи в медицине.

Пациент: — Доктор, у меня все болит. И тут болит (тыкает пальцем в грудь), и тут (тыкает пальцем в живот), и даже тут (тыкает пальцем в колено)! Доктор: — Дайте посмотреть. (смотрит...) — Голубчик, так у вас палец сломан!

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

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

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

Почему? В современной разработке важно сделать работающий продукт быстро. А не идеально вылизанный и оптимизированный, но через год. Поэтому используются фреймворки, различные ORM и другие средства и инструменты для ускорения разработки продукта. А вот когда проект уже «взлетел» и приносит деньги — его нужно оптимизировать, причесывать и наращивать мощность. Обычно все начинается снаружи. Ну, кому понравится, допустим, интернет-магазин, в котором поиск товара занимает 2 секунды — это очень много. И оптимизацию нужно начинать с конфигурации базы: правильной настройки памяти, диска, автовакуума и пр.

И только, когда это сделано, переходить к оптимизации «медленных запросов». Алгоритм этой оптимизации довольно прост:

1. отобрать запросы для оптимизации;
2. оптимизировать запросы;
3. повторить.

Что стоит оптимизировать? Ответ на этот вопрос многогранен, если в двух словах — оптимизировать нужно только то, что имеет смысл оптимизировать. Нужно взять топ «медленных» запросов и посмотреть на них.

Можно выделить три основных сценария:

1. Оптимизация этого запроса принесет пользу проекту (увеличит конверсию денег, привлечет новых клиентов, да просто будет быстрее и комфортнее для пользователей).
2. Это тяжелый аналитический запрос, и его можно просто перенести на другое время или скорость его исполнения вообще не критична.
3. Ну и последний случай. Если прикольная фича занимает кучу ресурсов, но пользы проекту не приносит, нужно задуматься — может, не стоит это все внедрять, может, стоит пересмотреть саму идею?

— Привет. — Привет. — Как там ваши дела с Кристиной? Еще не поженились? — Нет, мы расстались. — А что случилось? — Мне надоело, у нее были слишком большие запросы. — Например, какие? — Ну например, update instance inner join (select group.id as group_id, (select message.id from message inner join thread on thread.id = message.thread_id where location_id = @location_id and language_id = @language_id and concat(group_key, '.') like concat(group.key, '.%') order by message.created desc limit 1) as last_message_id, (select count(*) from thread where location_id = @location_id and language_id = @language_id and concat(group_key, '.') like concat(group.key, '.%')) as thread_count, (select if(sum(thread.message_count) is null, 0, sum(thread.message_count)) from thread where location_id = @location_id and language_id = @language_id and concat(group_key, '.') like concat(group.key, '.%')) as message_count from group where @channel_key like concat(key, '.%')) as statistics on statistics.group_id = instance.group_id set instance.message_id = statistics.last_message_id, instance.thread_count = statistics.thread_count, instance.message_count = statistics.message_count where instance.location_id = @location_id and instance.language_id = @language_id;

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

Как?

EXPLAIN:

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

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

EXPLAIN ANALYZE:

Не только строит план выполнения запроса, но еще выполняет его и показывает как изначальный план, так и реальный процесс его выполнения. Размерность, в которой команды оценивают полезность — COST (это время, затраченное на извлечение одного блока размером 8Kb при последовательном seq scan).

Обращайте внимание на следующее:

• использование полного просмотра таблицы (seq scan);
• использование наиболее примитивного способа объединения таблиц (nested loop);
• для EXPLAIN ANALYZE: нет ли больших отличий в предполагаемом количестве записей и реально выбранном? Если оптимизатор использует устаревшую статистику, то он может выбирать не самый быстрый план выполнения запроса.

Следует отметить, что полный просмотр таблицы далеко не всегда медленнее просмотра по индексу. Если, например, в таблице несколько сотен записей, умещающихся в одном-двух блоках на диске, то использование индекса приведет лишь к тому, что придется читать еще и пару лишних блоков индекса. Если в запросе придется выбрать 80% записей из большой таблицы, то полный просмотр опять же получится быстрее.

При тестировании запросов с использованием EXPLAIN ANALYZE можно воспользоваться настройками, запрещающими оптимизатору использовать определенные планы выполнения. Например,

SET enable_seqscan=false;

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

Ни в коем случае не следует прописывать подобные команды в postgresql.conf! Это может ускорить выполнение нескольких запросов, но сильно замедлит все остальные!

Использование индексов

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

Излишек индексов, впрочем, тоже чреват проблемами:

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

Единственное, что можно сказать с большой степенью определенности — поля, являющиеся внешними ключами, и поля, по которым объединяются таблицы, индексировать надо обязательно.

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

• Оптимизация производительности
• Reading EXPLAIN ANALYZE
• PostgreSQL EXPLAIN

Правила выполнения задания:

1. Время создания окружения занимает до 5 минут.
2. После нажатия кнопки «Начать выполнение» для вас будет создано окружение для выполнения задания, состоящее из одной или более виртуальнах машин с Ubuntu Linux. К ним будут выданы IP, логин и пароль для доступа по SSH, а также при необходимости — дополнительных ресурсов.
3. Также, если необходимо, вам могут быть выданы переменные, которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания (например, ${base_domain}).
4. После выполнения всех пунктов задания нажмите кнопку «Проверить выполнение», и в течение 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
5. В случае, если вы что-то забыли, можно исправить ошибку и отправить на проверку повторно (нажав кнопку «Проверить выполнение»).
6. После получения удовлетворительной оценки нажмите кнопку «Завершить задание», чтобы созданное окружение уничтожилось и вы могли приступить к следующему заданию.
7. Если у вас закончилось время (истек таймер) — окружение будет автоматически уничтожено и вам придется начать выполнение заново.
8. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки (используя кнопку «Задать вопрос куратору») или в чате практикума в любое удобное для вас время. Обращаем внимание — кураторы проверяют вопросы в течение 24 часов.

Задание:

1. Установить postgresql 13 через пакетный менеджер.
2. Добавьте в PostgreSQL пользователя root c правами SUPERUSER.
3. Подключитесь к postgresql и создайте базу данных с именем task12.
4. Создайте простую таблицу pgsql с полями ( id INT PRIMARY KEY, name TEXT NOT NULL ) в бд task12.
5. С помощью функции generate_series заполните таблицу pgsql значениями от 1 до 100000, в котором поле name будет содержать рандомный текст в md5.

INSERT INTO pgsql SELECT n , md5 (random()::text) FROM generate_series (1, 100000) AS foo(n);

6. Получите план выполнения запроса, который̆ выбирает все записи из таблицы pgsql, с помощью команды EXPLAIN. EXPLAIN SELECT * FROM pgsql;

7. В выводе EXPLAIN найдите и сохраните в файл /opt/cost_preview.txt стоимость выборки первой строки, стоимость выборки всех строк, количество рядов (rows), а также среднею ширину кортежа в байтах. В формате: cost=*.**..****.** rows=****** width=**

8. Выполните ANALYSE таблицы pgsql. Затем повторите EXPLAIN запрос из предыдущего задания.

9. Вы должны увидеть, что результат вывода изменился, найдите и сохраните в файл /opt/cost.txt стоимость выборки первой строки, стоимость выборки всех строк, количество рядов (rows), а также среднюю ширину кортежа в байтах.

10. Выполните запрос стоимости, который возвращают все строки, идентификатор которых принадлежит диапазону от 10 до 20 с помощью команды: EXPLAIN ANALYZE SELECT * FROM pgsql WHERE id >= 10 and id < 20;