pozitronik: (Sheridan)
[personal profile] 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

но, при желании, это автоматизируется в скрипте деплоя.
From:
Anonymous( )Anonymous This account has disabled anonymous posting.
OpenID( )OpenID You can comment on this post while signed in with an account from many other sites, once you have confirmed your email address. Sign in using OpenID.
User
Account name:
Password:
If you don't have an account you can create one now.
Subject:
HTML doesn't work in the subject.

Message:

 
Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

December 2016

S M T W T F S
    123
45678910
1112131415 1617
18192021222324
25262728293031

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 24th, 2017 12:48 pm
Powered by Dreamwidth Studios