Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Mysql InnoDB виснет при активной работе с диском  [new]
andrey10
Member

Откуда:
Сообщений: 177
Месяц исследований и экспериментов. Миллионы убитых нервных клеток. Сотни злых пользователей сайта. Итак.

Дано:
10.0.38-MariaDB-0+deb8u1 (также тестировалось на percona)
Linux server 4.9.0-0.bpo.6-amd64
Пустой mysql конфиг (для чистоты эксперимента)
ДБ размером 40ГБ на InnoDB
2 SSD raid1

Запустим тестовый сервис с максимально ограниченными ресурсами на использование диска.
[Unit]
Description=test
[Service]
CPUShares=10
BlockIOWeight=10
BlockIOAccounting=1
BlockIOWriteBandwidth=/dev/md1 10M
ExecStart=/bin/dd if=/dev/zero of=/tmp/del bs=1024 count=1000000
[Install]
WantedBy=multi-user.target


atop, iotop, iostat показывают минимальную нагрузку на диск (10mb/s). И в где-то в середине выполнения dd, в 9 случаев из 10, ВСЕ(!) таблицы на innodb намертво зависают (селект не выполняется) на пару минут, в то время как ВСЕ таблицы на myisam доступны (селект выполняется).

InnoDB таблицы зависают при любой активной работе с диском, будь то архивирование, копирование файлов, чтение. Чем "тяжелее" задача, тем дольше зависание.

Проблема не в производительности дисков и не в аппаратуре. В ДЦ заменяли диски и другие комплектующие по 2 раза. Проблема таится где-то в самом процессе работы InnoDB с диском. Мне сложно интерпретировать свои мысли на этот счет, так как не знаю всех технических нюансов.

Вы можете сказать - дружище, возьми второй диск, поставь на него отдельно mysql и не парься! На что я вам скажу, что это не выход. Даже при бекапе через xtrabackup всё виснет.

Так что же мне делать? Прошу помощи.
25 янв 20, 05:39    [22066404]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
miksoft
Member

Откуда:
Сообщений: 38000
andrey10
10.0.38-MariaDB-0+deb8u1
А почему такая старая ветка?
Ветка 10.0 стала стабильной в 2014 году.

Какой объем дисков?

Пустой конфиг - тоже не здорово, у MySQL некоторые параметры по дефолту имеют значения не лучшие с точки зрения быстродействия.
innodb_flush_log_at_trx_commit - имеет смысл поставить в 1
innodb_buffer_pool_size - имеет смысл поставить в настолько большое значение, насколько возможно выделить оперативки. В пределе - размер всех таблиц и индексов InnoDB +10%, выше уже смысла нет.
25 янв 20, 14:05    [22066471]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2956
andrey10,

Для чистоты эксперимента возьми за 1тыщ материнку под старый Core2Duo c 775 соккетом и 1066 шиной, проц средненький E6600 и 8Г памяти и простые диски, вот это будет чистый эксперимент. Причем набор такой сейчас стоит копейки. Все отладь, затем уже можно на сервер.

Ты же в дата центре не был, зачем им верить, меняли, не меняли....
если хостинг, VPS за 200-300 рублей в месяц, то никто толком на это не смотрит, они же для небольших сайтов визиток

пробовать
innodb_buffer_pool_size - 50-80% ОЗУ, (если там память "существует") по умолчанию около 128М
еще по умалчанию там ibdata1:10M:"autoextend" то есть постоянное наращивание при заполнении первичного объема, это тоже плохо, так как диск фрагментируется
Вообще такие зависоны могут быть элементарно даже из за перегрева дисков, не 37-38 градусов, а 40 - и все...
25 янв 20, 23:24    [22066592]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34587
andrey10,

MyISAM не имеет кэша, его кэш в ОС. ОС часто (глупо) предкэширует открытые приложениями файлы .
MyISAM-овцы даже гордились, на сколько я помню, тем, что типа мы ещё ничего не читали толком, а оно уже в кэше.

INNO , наоборот, хранит кэш сам, и пока он что-то не прочитает, в кэш это не попадёт.

Ну и ты классно придумал -- врубить все параметры по дефолту, ничего не выделять под кэш, который -- главное достоинство INNO,
да ещё и IO как-то искусственно ограничить...
Хорошо хоть СУБД запустил, а то можно было бы не запускать, и смотреть и ухмыляться, как оно (не) работает.
26 янв 20, 01:04    [22066611]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
andrey10
Member

Откуда:
Сообщений: 177
Спасибо за ответы.

"А почему такая старая ветка?"
- Обновлюсь до последней и перепроверю.

"Какой объем дисков?"
- 2 диска по 240

"Пустой конфиг - тоже не здорово"
- да, надо было сразу рабочий выставить. Вот действующий конфиг, на котором зависает: https://anotepad.com/notes/2xf43nww


innodb_flush_log_at_trx_commit стоит 2. Попробую поставить 1.
Также попытаюсь выключить innodb_use_native_aio

На сервере всего 32гб оперативки и мне удалось поставить только innodb_buffer_pool_size = 25гб для тестов, когда объем иннодб таблиц 29ГБ (не 40, как писал в 1 посте). Результаты были точно такими же. Неужели это значение должно быть обязательно больше объема иннодб таблиц? Я где-то читал, что не критично, но в моем случае, если это является истинной причиной зависания диска, то критично. Наверное, мне стоит запустить мускл с текущим конфигом на проекте с 5-8гб innodb и проверить.

"Для чистоты эксперимента возьми за 1тыщ материнку под старый Core2Duo..."
- У меня дедик в ДЦ.

"так как диск фрагментируется"
- у меня SSD

"Вообще такие зависоны могут быть элементарно даже из за перегрева дисков, не 37-38 градусов, а 40 - и все..."
- Нет

"Ну и ты классно придумал -- врубить все параметры по дефолту, ничего не выделять под кэш, который -- главное достоинство INNO,"
- Я тестил как на рабочем, так и на дефолтном - проблема идентична. Выше уже предоставил свой рабочий.

"да ещё и IO как-то искусственно ограничить..."
- Читайте внимательнее, пожалуйста. Использование диска в примере было ограничено для выполнения определенной команды для демонстрации абсурдности проблемы: диск не загружен практически совсем, а mysql зависает.
26 янв 20, 06:14    [22066624]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2956
твой тест гонянения нуля dd-кой отношения к MySQL не имеет
И описывать надо задачи которые делаешь, апдейтишь - инсертишь, селектишь
допустим у тебя инсерты частные. По дефолту файл ibdata1 10М
Пошли у тебя сплошь Инсерты на 10М, заполнили свободный кусок, опять сработал autoextend на 10М (это тоже время занимает)
Да еще если устроил конкурецию на диске своим тестом, вообще нонсенс
У тебя на этих же дисках еще какие то задачи есть что ли?
автор
"InnoDB таблицы зависают при любой активной работе с диском, будь то архивирование, копирование файлов, чтение. Чем "тяжелее" задача, тем дольше зависание."

Для базы лучше 1 отдельный диск, чем 55 в рейде но с другими задачами, какие копирования файлов там у тебя??
Надо настраивать InnoDB, если считаешь, что система идеальна.
Причем методом научного тыка. Так как ничего не понятно.
Определи в чем у тебя затык происходит, только не таким тестом
Не забывай что индексы, логи еще пишутся. По логам MySQL смотри, что там в момент затыка происходило, а не по вводу выводу системы

Сообщение было отредактировано: 26 янв 20, 08:13
26 янв 20, 08:08    [22066636]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2956
innodb_thread_concurrency=4
эти потоки тоже бурутся у системы, можно поэкпериментировать в 1 или 2
26 янв 20, 08:20    [22066637]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
miksoft
Member

Откуда:
Сообщений: 38000
andrey10
innodb_flush_log_at_trx_commit стоит 2. Попробую поставить 1.
Прошу прощения, наоборот, 1 - значение по умолчанию и оно же худшее с точки зрения быстродействия.
26 янв 20, 13:47    [22066710]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
andrey10
Member

Откуда:
Сообщений: 177
Alex_Ustinov
innodb_thread_concurrency=4
эти потоки тоже бурутся у системы, можно поэкпериментировать в 1 или 2


Спасибо, попробую.

Сообщение было отредактировано: 26 янв 20, 14:13
26 янв 20, 14:12    [22066718]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
andrey10
Member

Откуда:
Сообщений: 177
innodb_thread_concurrency=0 решило все проблемы :)
27 янв 20, 08:46    [22066948]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
miksoft
Member

Откуда:
Сообщений: 38000
andrey10,

А сколько ядер доступно?
27 янв 20, 09:33    [22066956]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2956
andrey10
innodb_thread_concurrency=0 решило все проблемы :)
0 рекомендуется при потоках меньше 64, когда не знаешь точное количество, и когда есть неожиданные всплески. При больших количествах рекомендуют использовать дополнительно innodb_thread_sleep_delay - время сна потока или
innodb_adaptive_max_sleep_delay - адаптивное поведение время сна
Короче у тебя стояло потоков=4, это было очень периодически мало, при всплесках количества пользователей.
27 янв 20, 15:46    [22067251]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
andrey10
Member

Откуда:
Сообщений: 177
miksoft,

4 ядра 8 потоков.

"Короче у тебя стояло потоков=4, это было очень периодически мало, при всплесках количества пользователей."
- Не уверен как оно работает, но мне кажется, что когда я выставил 0 mysql более умным образом использовать потоки при стресс нагрузке, а при 4 просто впадал в лок.
28 янв 20, 07:41    [22067541]     Ответить | Цитировать Сообщить модератору
 Re: Mysql InnoDB виснет при активной работе с диском  [new]
miksoft
Member

Откуда:
Сообщений: 38000
andrey10
8 потоков
Ну тогда не меньше 8 надо ставить.
Ну или 0, т.е. без ограничений.

0 - это тоже не здорово при высокой нагрузке, но если у вас почти хватало 4, то, наверное, будет нормально.

Почитайте про ограничения, возникающие при 0 - https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
28 янв 20, 08:15    [22067553]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить