Как бороться с дедлоками?

Question

[Max Payne]

И так, есть небольшой сервис с одной базой данных, 32 таблицами в ней и примерно 1.2 млн. записей, ~30 запросов в секунду к MariaDB (так говорит PHPMyAdmin, на деле, по идее, должно быть куда меньше) и в последнее время начал замечать что если не делать ночных перезагрузок сервера - днём, а чаще в обед или после него обязательно всё лочится дедлоком и сервис падает, при чём даже завершение всех процессов приложения не помогает, а только перезапуск MariaDB или сразу сервера.

В качестве ОС используется Ubuntu 16.04.

Опишу немного структуру БД, может быть это поможет. При проектировании базы данных я руководствовался простыми правилами:

1) Таблицы к которым идут ТОЛЬКО SELECT (и очень редкие UPDATE/DELETE/INSERT, раз в неделю, а то и месяц) запросы используют MyISAM.
1.0) Сюда я включил: все таблицы со статичными данными, которые не изменяются + одна таблица с редкими INSERT (несколько раз в час) и частыми SELECT.
2) Таблицы к которым идут SELECT + UPDATE + INSERT используют InnoDB (в MariaDB это XtraDB).
2.0) Сюда я включил: таблицу пользователей - очень частые SELECT + UPDATE, и реже INSERT; таблицу чатов - очень частые SELECT, реже UPDATE и INSERT; и другие, с которыми проблемы менее вероятны так как они менее нагружены.
2.1) Высоконагруженная таблица логов (в которой более миллиона записей) и которая использует частые запросы SELECT + INSERT использует также InnoDB (тут сомневаюсь в правильности выбора подсистемы).

Собственно суть вопроса:
Правильно ли я поступил при выборе подсистем для таблиц? Какими способами можно вылечить дедлоки?

Comments

[AlikDex]

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

Answer 1

[Boris Köln]

1. MySQL умеет убивать deadlock, это не приводит к падению сервера.
Укажите все deadlock-запросы. Их надо отрефакторить.

2. Зачем постоянно update юзеров? Для счетчиков или даты последнего посещения лучше выбрать другое хранилище.

3. Зачем постоянно select юзеров? Используйте кэширование

4. Зачем постоянно select логов? Обычно логи только пишут. Да и БД для этого тоже вовсе не обязательно использовать.

5. Зачем постоянно select чатов? Достаточно только для новых участников чата

Comments

[Max Payne]

1. Где их можно посмотреть? Ситуацию следующая - подключаюсь к SSH и вижу что "всё зависло" -> "ребут".

2. Информация о балансе/опыте/дате блокировки и других характеристиках пользователей храняться в этой таблице. То есть нужно прочитать => при необходимости перезаписать.

3. Кэширование на уровне MySQL включено, не знаю есть ли смысл использовать memcached/redis, всё таки не тот уровень.

4. Логи в основном пишутся, но иногда нужно подсчитать количество всех действий связанных с определенным пользователем или количество действий за последние N дней связанных с этим пользователем.

5. Таблица чатов содержит по-большей части настройки для чатов, их в любом случае нужно читать, только одно поле может изменяться довольно часто - раз в несколько минут, остальные либо с неизменными после INSERT'а данными, либо для каждого чата меняются редко.

[Boris Köln]

1. И откуда вывод про дедлоки?

2. баланс/опыт/дата блокировки меняется не так часто

3. хотя бы в сессию.

4-5. подсчитать - это "очень редко", а не "очень часто"

[Max Payne]

Boris Korobkov,
1. Потому что кроме них ничего быть не может :)
2. Довольно редко, но меняются. Каждые несколько секунд могут быть UPDATE и каждые несколько минут INSERT, а всё это время идут SELECT'ы.
3. Если можно поподробней.
4. Ну это не только, ещё в логах селектится дата последнего действия пользователя. При чём при каждом обращении пользователя к сервису.

[AlikDex]

Исходя из советов, какая-то очень нежная бд должна быть :D

Может дело просто в марийке?

[Boris Köln]

Max Payne,
1. мисс Марпл?

3. php.net/manual/ru/book.session.php

4. Что-то не то с бизнес-логикой

[Max Payne]

Boris Korobkov,
1. Эркюль Пуаро

3. Не использую PHP и сессии вообще. У меня серверное приложение, запросы приходят извне, грубо говоря по API.

4. Раньше последнее действие хранилось в таблице юзеров, сейчас используется такой велосипед:

SELECT 1 FROM `log` WHERE `user_id`=111 and `ts` > NOW() - INTERVAL 1 SECOND LIMIT 1;

по сути мне не сам таймстамп нужен, а только узнавать прошло ли достаточное количество времени с последнего запроса.

[AlikDex]

Max Payne, по 4-му пункту уж точно не в мускуле делать такое. либо в памяти того приложения держать, если оно как демон работает. Либо какой-нибудь редис использовать.

[Max Payne]

AlikDex, Хранил в памяти приложения отдельно timestamp метки, но посчитал что раз лог ведется постоянный - почему бы его не юзать.