InnoDB buffer pool и JSON-тела на трёх языках

Сетка заметок начала ждать MySQL, когда тела стали настоящими статьями. LaraBoom хранит title, excerpt и body как JSON-объекты с полями en, ru и de. Для админки и для Vue форма правильная. Для SELECT * на списке нет.

Сетке нужны id, категория, заголовок, анонс, картинка, размер, даты. Три HTML-статьи ей не нужны. На свежем сиде это несколько килобайт, и всем всё равно. Когда тела выросли, список из пятидесяти строк затягивал в InnoDB buffer pool около 1.2 МБ JSON. JSON API большую часть этого выбрасывал до того, как ответ уходил из PHP. Vue SSR платил тем же чтением на каждый публичный рендер сетки.

Пользователи чувствовали медленный список. MySQL чувствовал buffer pool, похожий на кэш HTML статей. Индексы стека и новостей теряли страницы телам заметок, которые плитка никогда не печатала.

Что делал buffer pool

InnoDB читает страницы, не колонки. Страница 16 КБ, в которой лежит толстый body, тащит этот body в RAM, даже если запросу нужен только title. Пока страница в пуле, она живёт там, пока место не понадобится другому. Трафик списка частый. Трафик деталки нет. Пул начал выглядеть как кэш HTML статей, который эндпоинт списка перечитывает снова и снова.

innodb_buffer_pool_size на этом Docker-хосте 512 МБ. Звучит щедро, пока на той же машине сидят Redis, Memcached, php-fpm и Node. Я смотрел Innodb_buffer_pool_reads против Innodb_buffer_pool_read_requests, пока листал заметки в трёх локалях. Hit ratio был нормальный. Зря занятая RAM нет. Страницы, которые должны были держать индексы стека и новостей, держали тела заметок.

EXPLAIN выглядел спокойно. Фильтр is_published, сортировка sort_order, покрывающий индекс на бумаге. Индекс не покрывает запрос, который всё равно берёт колонку body. Списковые эндпоинты LaraBoom собраны под админские таблицы. Публичная сетка их переиспользовала.

Убрать толстую колонку

Вторую базу я не хочу. Хочу короткую строку списка. Body уходит в note_bodies с ключом note_id, одна строка, тот же JSON. Публичный запрос списка на этом останавливается:

SELECT id, category_id, title, excerpt, image,
       published_at, size, sort_order, is_published
FROM notes
WHERE is_published = 1
ORDER BY sort_order ASC

Детальный эндпоинт джойнит или делает второй запрос по id. Один лишний запрос на странице статьи дешевле, чем пятьдесят тел на каждый рендер сетки, включая проход Vue SSR.

Админка по-прежнему грузит body, редактору он нужен. Этот путь редкий и с авторизацией. Я оставил его одним чтением побочной таблицы. Выносить excerpt в отдельные колонки по локалям не стал. JSON анонса маленький. Проблема в JSON тела.

JSON_EXTRACT это не выход

JSON_EXTRACT(body, '$.ru') выглядит как сжатие результата. Страницу с диска он не сжимает. InnoDB всё равно читает всю clustered-запись. Вы платите за HTML в RAM, потом выбрасываете два языка в SQL, потом, возможно, выбрасываете третий в PHP, если списку он и не нужен.

Generated column плюс вторичный индекс помогают фильтрам. Списку, который body видеть не должен, они не помогают. Generated column ещё и копирует данные. Три stored generated column на три локали утроят худшую часть строки.

• Колонки списка остаются в notes.
• HTML живёт в note_bodies.
• Список кэшируется в Redis после короткого запроса, не до него.

После разнесения те же пятьдесят строк из MySQL весили около 40 КБ. Страницы buffer pool по заметкам снова были похожи на индексные. SSR сетки перестал ждать блоб, который никогда не печатал. Детальная страница не замедлилась так, чтобы это было видно. Если замедлится, это единственный запрос, который я готов кэшировать, по id, с TTL, который умирает при сохранении в админке.

Какой опыт из этого

Меряю размер payload списка и какие страницы сидят в buffer pool, не только hit ratio. Нормальный hit ratio при 1.2 МБ ненужного HTML это всё ещё плохой запрос списка.

JSON_EXTRACT и покрывающему индексу на бумаге я не верю, пока SELECT всё ещё называет body. Админский список это не публичная сетка.

Правило, которое оставляю: если колонки нет на плитке, её нет в запросе списка. JSON для i18n нормален. JSON для i18n, в котором лежит статья, это ресурс деталки. Кэш в Redis после короткого запроса.