SELECT запрос может быть одной из причин, вызывающих высокую скорость записи на диск под капотом. Да, чтение данных из базы данных может привести к записи на диск. Как ни странно, это происходит потому, что база данных должна вернуть данные в том формате/порядке, в котором мы ее запрашиваем. Этот пост устанавливает небольшой запрос SELECT, который вызовет запись на диск, и объясняет, какая часть запроса вызывает это.

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

После создания таблиц приведенный ниже скрипт генерирует данные для загрузки в таблицы.

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

psql -q -U username -d database_name -h localhost --password < /path/to/data.sql

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

Теперь давайте запустим ANALYZE пару простых запросов и посмотрим, что произойдет. Запустите следующий запрос в терминале базы данных

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book INNER JOIN author ON book.author_id = author.id ORDER BY book.id LIMIT 100;

Это тип запросов, которые запускаются, когда страницы администратора открываются на панели инструментов Django (и select_related заполняется на панели администратора). Приведенный выше запрос приводит к следующему плану запроса

Вот как читается приведенный выше план запроса

Строки 7–8: сканирование таблицы books по первичному ключу
Строки 9–11: поиск автора книги в таблице авторов путем извлечения его на основе идентификатора автора (autho_id книги доступен из строк 7– 8)
Строки 5–6: выполните указанные выше два шага для всех строк в таблице books
Строки 3–4: ограничьте результаты до 100.

В более общем плане план запроса говорит, что нужно найти первые 100 строк таблицы книг, отсортированных по идентификатору, и следовать внешнему ключу, чтобы найти авторов этих книг. Что еще более важно, запрос ничего не записывает обратно в БД и использует память для вычисления всего результата запроса. А сам запрос завершается примерно за 25 мс.

Давайте посмотрим, что произойдет, если запрос немного изменится, чтобы отсортировать его на основе книги title рядом с id. Это может быть запрос, который будет выполняться при щелчке таблиц на панели администратора Django для сортировки по другому столбцу. Выполните следующий ANALYZE запрос

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book INNER JOIN author ON book.author_id = author.id ORDER BY title ASC, book.id ASC LIMIT 100;

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

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

Строки 6–7: Планирование и запуск 2 рабочих потоков
Строки 20–24: Использование рабочих процессов, хеширование таблицы авторов путем последовательного сканирования ее для подготовки к соединению.
Строки 15–19: Использование рабочих процессов , последовательно просмотрите таблицу books и соедините ее с таблицей authors с помощью хэша
Строки 9–14: отсортируйте данные с помощью алгоритма сортировки кучи Top-N
Строки 5–8: соедините данные из рабочие потоки
Строки 3–4: ограничение вывода до 100 строк.

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

Это проблема, с которой мы действительно столкнулись в нашей производственной системе. Мы видели, как число операций ввода-вывода для записи на диск базы данных начало резко увеличиваться, а доступное пространство для хранения данных быстро сокращалось. Нам было очень неинтуитивно определить, что проблема вызвана запросом SELECT. Это имело каскадный эффект на производительность системы в течение достаточно долгого времени. Мы смогли отладить проблему и решить ее благодаря мониторингу на месте. Добавление предупреждений об использовании диска, записи IOPS также поможет вовремя выявить такие проблемы и обеспечить стабильность производственных систем.