Yii2 и select_full_join

[pozitronik]

Вчера утром юзеры поднимают панику: отзывчивость сервиса (самописная web-CRM на основе Yii2) упала многократно. Проверяем: CPU load на сервере уткнулся в потолок, что случилось - непонятно.
Серверное окружение, надо сказать, настроено почти никак. Стандартная связка nginx+php-fpm+mysqld, аппаратных ресурсов с запасом, всё работает, а до мало-мальского тюнинга руки просто не доходят.
top показывает, что процессорное время отжирает mysqld, так что лезу в sql processlist. Не видно ничего подозрительного, запросов много, но они отрабатываются. Висяков нет, slow_log пустой. Рестартуем сервисы - сразу после запуска нагрузка опять зашкаливает.
Сразу скажу - что это было, мы так и не уверены. Провели, что называется, комплекс мероприятий: докрутили nginx, включили кеш mysql (то, что отключён был - глупый косяк, зато не мой), вытащили темповые таблицы в memfs, поправили индексы, добавили памяти под всякие буфера, и оставили до завтра, то есть до сегодня. Было подозрение, что утром час наибольшей нагрузки, но ситуация не повторяется, что-то помогло, но неясно, что.
А пока мониторил работу sql, заметил очень странную вещь: счётчик select_full_join увеличивается очень быстро, буквально +1000/сек.
[Ликбез]
Отличие этого счётчика от нуля означает, что выполняется запрос с JOIN по неиндексированным полям. mysql вынужден каждый раз производить сканирование всей таблицы, не имеющей нужного индекса, таким образом нагрузка растёт пропорционально количеству записей в ней.
[/Ликбез]
Вместе с этим счётчиком рос в той же пропорции и счётчик created_tmp_disk_tables, очевидно потому, что временные таблицы создавались как раз из-за этих фулл джойнов. Это тоже совсем нехорошо: mysql должен запихивать временные таблицы в память, если под это выделено место, но не делает этого, если JOIN происходит по текстовому или двоичному полю. Обмануть mysql можно, отдав под tmpdir раздел в памяти, но это костыль.

Я был вполне уверен, что с индексами самых часто используемых таблиц у меня всё в порядке. Но даже один незамеченный отсутствующий индекс, помноженный на сотни тысяч обращений, мог дать описываемую ситуацию. Поэтому я включил general_log на некоторое время, убедился, что select_full_join подрос, а затем сделал

SELECT argument FROM general_log WHERE argument LIKE '%JOIN%'

и стал смотреть эти запросы с EXPLAIN.
Забегая вперёд скажу, что единственным типом запросов, не использующим индексы, стали обращения к information_schema, выглядящие примерно как

SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON [...]

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

С одной стороны: information_schema почти целиком состоит из MEMORY-таблиц, т.е. обращения к ним почти ничего не стоят. Но по этой же причине проиндексировать таблицы невозможно, значит счётчики будут расти. Ну и оверхед какой-то есть; при малом количестве обращений он несущественен, но при тысячах запросов в секунду даёт уже заметную нагрузку.

Решением оказывается включение кеша схемы БД в db.conf:

	'enableSchemaCache' => true,
	'schemaCacheDuration' => 0,
	'schemaCache' => 'cache',

Сразу после донастройки минимум вдвое упало количество запросов на БД, количество full_join и tmp_tables стало нулевым. Снижение нагрузки на cpu объективно оценить нельзя, но субъективно нагрузка упала (логично - убраны тысячи холостых запросов).
Единственный минус - при каждом обновлении структуры БД кеш надо сбрасывать через

php yii cache/flush-schema

но, при желании, это автоматизируется в скрипте деплоя.