point212
@point212
сисадмин linux, программист php

MySQL грузит все ядра проца. Глюк?

Лучше всего проблему иллюстритует сия картинка
8d61c3b01df3486ebc59c6d2d09e58c4.png

Если описать это словами, то выходит так. Сервер работает как ни в чём ни бывало. Нагружено около половины ядер. И не на 100%, а на 50-70%. Потом внезапно нагрузка улетает в космос. При этом база встаёт раком, ответы происходят очень долго. Всё это длится 10-50 секунд, и потом опять перерыв на минутку.

И я никак не могу понять в чём причина этой беды. Ибо эту картинку я вижу не в первый раз. На нее я натыкался и ранее, еще лет 5 назад. То есть собственно версия ядра, дистрибутива или даже мускуля скорее всего не причем.

Причем по мониторингу (htop) видно что проц то загружен системным вызовом. Т.е. это или огромное количество некоторых вызовов к ядру, или интенсивное выделение-забирание памяти, или ввод-вывод.
Но как промониторить самые топовые вызовы ядра я не знаю. Память судя опять же по мониторингу массово не выделяется и не забирается (по меньшей мере гигабайтами, чтобы это было заметно).
iotop показывает ввод-вывод не сильно отличающийся от такового в нормальном состоянии.

Запросы во время глюка выполняются самые обычные. Не сказать чтобы как-то менялась пропорция выбор/обновление, или запрашивались особые таблицы. Думал может что-то по крону запускается из переодических заданий. Но я пробовал останавливать их все на время. Проблема остается.

К слову о сервере и системе: 2 x Xeon E5-2680v3 @2.5GHz (24 реальных ядра), 64Гб DDR4. SSD энтерпрайз уровня на 960Гб. Быстрые. Ну то есть сервер очень даже ничего. ОС Centos 7 (ядро 3.10), юзаю Percona 5.7. База на отдельном разделе (впрочем рояли это особо не должно играть). Кроме мускуля на сервере не стоит вообще ничего.
Собственно неделя как переехали со старого сервака, который был ровно в 2 раза слабее и перестал тянуть нагрузку.
Так вот на нём по началу я тоже видел такую же картину переодически. Но потом подобрал такие параметры в конфиге мускула, что всё вроде как улеглось. Но все ж сервак перестал тянуть, и мы переехали на новый... а тут опять эта проблема.

И тут время перейти к тому, чтобы рассказать что я УЖЕ делал:
1) Рестарт мускула - спасает ситуацию на минуту
2) Рестарт сервера ни на что не влияет
3) Тюнинг параметров. Пробовал дефолтные. Пробовал со старого сервака. Пробовал поднимать до разумных значений. Пробовал до неразумных. Пробовал тюнить по советам утилиты mysqltuner. Ничего не помогает.

Важное замечание: проблема наблюдается только в час пик. Так что всё это явно коррелирует с нагрузкой на мускул сервер. В остальное время дня всё окей.

Что еще я хочу сказать... я не настоящий сварщик. В смысле не DBA. Просто рядовой Linux-админ. Я плохо понимаю как внутри устроен mysql, innodb и так далее. Поэтому и прошу помощи. Разобраться сам не смог.

Ниже прикреплю на всякий случай шапку от mytop:
MySQL on localhost (5.7.18-16)                                                              up 0+00:38:59 [00:32:19]
 Queries: 26.1M  qps: 11695 Slow:     0.0         Se/In/Up/De(%):    66/09/04/00 
             qps now: 11493 Slow qps: 0.0  Threads:  180 (   5/   8) 66/09/04/00 
 Key Efficiency: 93.0%  Bps in/out: 11.0M/89.7M   Now in/out: 10.7M/90.5M

Сейчас конечно не час-пик уже. Но хоть какая-то инфа.

И заодно конфиг мускула
[mysqld]
bind-address=xxxx
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=1
innodb_buffer_pool_size=16384M #пробовал всякое. от 8 до 32гб. разницы нет
innodb_log_file_size=1024M # тоже всякое. вплоть до комментирования
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
sql-mode=""
query_cache_size = 4096M # тоже менял от 0 до 4гб
join_buffer_size = 64M
thread_cache_size = 8
max_connections=8192
open_files_limit=8192
explicit_defaults_for_timestamp=1

max_allowed_packet=128M

log-error=/var/log/mysqld.log
log_error_verbosity=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=4096
innodb_buffer_pool_populate = yes
flush_caches = yes
numa_interleave = yes


Если интересно, могу опубликовать скрин Mysql Workbench Dashboard во время того когда мускул глючит.
Или Perfomance Statistics. Все равно я в ней ничего особо не понимаю.

Очень хочется разрешить уже этот глюк для себя. И понять почему он возникает.
Пока подозрения на то что у меня мускул сконфигурирован так, что потенциально может запросить больше памяти чем есть. И это как-то сносит крышу ядру.
  • Вопрос задан
  • 5493 просмотра
Решения вопроса 3
@vlarkanov
Погуглите ошибку leap second - одним из ее симптомов может быть аномальная загрузка проца.

Ещё вариант: посмотрите как настроен и как функционирует NUMA. Возможно, стоит включить interleave в настройках mysqld_safe (через numactl).

Что с IO? Какова нагрузка на диск? Посмотрите через iotop например.

По параметрам. Кроме мускула на сервере что-то крутится? Если нет, смело выкручивайте
innodb_buffer_pool_size до примерно 70% объема RAM.
innodb_log_file_size - это размера лога транзакций innodb. Чем он больше - тем реже пересоздается этот файл, и тем меньше нагрузка на диск. Но тем дольше восстановление в случае сбоя. 512mb должно хватить.

innodb_flush_log_at_trx_commit = 0 Вы уверены? Поставьте хотя бы 2 - по производительности тож на тож, но безопаснее. А лучше и вовсе 1. Медленно, зато надежно.

query_cache_size = 4096M Куда столько? Помните, что при каждом INSERT\UPDATE этот кеш переписывается. Поставьте 100mb для начала.
Ответ написан
@asd111
Поскольку у вас 24 ядра и 11.000 запросов в секунду query_cache лучше отключить.
[mysqld]
query_cache_size = 0
query_cache_type = 0   # важно ! отключает mutex, которым оперирует query_cache

Потому что в таких условиях он больше мешает чем помогает. Особенно если у вас мало таблиц и есть изменение таблиц, из которых чаще всего происходит чтение. Всё дело в том что ядра борятся за доступ к кэшу и если есть insert, update, delete в таблицу, для которой есть записи в кэше, то кэш всей этой таблицы обнуляется и снова ядра борятся кто будет обнулять, кто будет читать, писать и вся вот эта многопоточная борьба за мьютекс query_cache грузит CPU. И ещё кучу времени занимает очистка кэша если размер кэша большой(гигабайты).
Если интересно что происходит с кэшем гляньте SHOW STATUS LIKE "qcache%";
Там стоит обратить внимание на Qcache_lowmem_prunes. Чем эта переменная меньше тем лучше (в идеале 0 )- эта переменная показывает сколько раз обнулялся кэш для таблиц.
Ответ написан
@poige
> thread_cache_size = 8

Вот это имеет смысл поднять — можно сразу штук 200 поставить.

Про query_cache тут уже говорили, он, и правда, для детских нагрузок, потом быстро становится узким местом производительности — бесполезным, и даже вредным.

UPD.: thread-pool, похоже, помог

P. S. На самом деле, конечно, нужно знать природу нагрузки — какие это запросы, сколько одновременно прилетает, и так далее.

Отдельно могу заметить на тему htop — программка няшная, но, зачастую, кривая. Вот, судя по скрину, включён показ отдельных thread'ов — откуда тогда у одного треда 4k CPU usage? Или это он не только показывает отдельные threads, но ещё и агрегирует их per process? Обычный Linux'овый top, в этом плане, как-то понятней.
Ответ написан
Пригласить эксперта
Ответы на вопрос 4
point212
@point212 Автор вопроса
сисадмин linux, программист php
Итак, граждане. У меня всё хорошо. Проблема ушла.

Похоже дело было именно в массивных операциях с памятью, которые мускул/ядро вынужден был проводить раз в несколько десятков секунд. А причиной тому были неправильные настройки буфферов и кэшей в конфиге.
И скорее всего самой повлиявшей из них был query_cache_size.

Вторая вещь (а скорее даже первая) которую нужно проверить и убедиться что у вас нет с этим проблем - это leap second bug. Гуглите в инете, смотрите и проверяйте.

Спасибо всем, кто участвовал в обсуждении.

Погорячился я, ребята.
С увеличением нагрузки проблема вернулась :( Увы... так что вопрос всё ещё открытый.
Что крутить дальше - не знаю.
Ответ написан
dummyman
@dummyman
диссидент-схизматик
Похоже, отлаживать ситуацию придется на уровне модификации запросов из приложения. Как уже упоминалось выше, на момент отладки следует включать и анализировать лог медленных запросов. Где-то дописать индексы, где-то заменить чтение из обычной таблицы на чтение из временной таблицы. Тут же, самим SQL регламентирован инструмент всех времен и народов - EXPLAIN.
Отдельным моментом стоит обратить внимание на блокировки. Часто сталкивался с ситуацией, когда множество запросов на чтение не дают выполняться запросам на запись (или наоборот перегруз запросов на запись блокируют чтение), чем под нагрузкой моментально переполняют очередь - и основное процессорное время расходуется управлением этой очередью. Конкретно из своего опыта (я работал чуть больше года техником в хостинг-конторе), чаще всего проблема проявлялась у сайтов на базе Wordpress. Причем, сам движок Wordpress оптимизирован достаточно хорошо. Проблема в бесконечном количестве плагинов от авторов средней руки. Особенно, плагины, связанные со сбором/подсчетом статистики. При генерации одной страницы используется десятки противоречащих друг-другу запросов на чтение и запись. Бывало, из-за блокировок два одновременно работающих (конкурирующих) инстанса php посылают к БД запросы блокирующие работу друг-друга с такой интенсивностью, что адекватная работа возможна только ограничением их количества одним лишь инстансом php. И никакой переезд на более мощный или облачный сервер не поможет. Логические ошибки решать наращиванием мощностей неэффективно и может быть очень дорого. - А потому любое наращивание мощностей железа - есть решение временное, и способно отсрочить работы по отладке максимум на несколько дней. В проектировании приложений (как web/rest, так и standalone) надо понимать что делает каждый action - либо он пишет в БД, либо читает из БД. Если каждый action будет требовать много чтений и записей в одних и тех же таблицах, это обязательно приводит к проблемам блокировок.

Тулзами для анализа событий io для решения проблем mysql пользоваться бесполезно - mysql достаточно хорошо контролирует использование io не доводя ядро до перегрузок. Но, и опять же предоставляет множество настроек для несистемных ограничений, которые могут быть установленны неэффективно и проблему можно решить скорректировав их.

И еще мысль ради пищи для размышлений - во большинстве ситуаций таблицы MyISAM работают в разы быстрее InnoDB.
Ответ написан
evnuh
@evnuh
Поиск Гугл помог мне, впусти и ты его в свой дом
Slow: 0 из 26м запросов? Все запросы такие хорошие или слоу лог отключён?
Ответ написан
@shagguboy
qps now: 11493
вас ДДосят?
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы