После перехода на innoDB странные глюки

Question

[Степан]

Переключил тип хранилища одной из таблиц с myisam на innodb.
Часа 3 на нагружаемом проекте проработало почти без проблем (слишком долго выполнялись селекты даже по примари ключам).
Сейчас замечаю что список запросов в ожидании начал разрастаться нереальными темпами.

32899786 | user | 127.0.0.1:48798 | table | Query | 1296 | Sending data | select id from site_users

Как такой запрос может выполняться 1296 секунд?
Что я делаю не так?

Причина перехода на innodb банальна, слишком много апдейтов по таблице.

Comments

[demimurych]

Каков размер таблицы site_users
Запрос выбирает все id из этой таблицы?

[Степан]

В таблице всего 300к записей.
Отпустило после того, как выделили под innobd больше оперативки.

Запрос из примера это наследие старых mysql_num_rows в коде который писался еще лет 5 назад.
Запрос конечно же сменил. Пока глюков не наблюдаю, 698.71 запросов в секунду.

[Степан]

Кому то видимо очень не понравился вопрос. Уже запросили ретрив на пароль аськи из анкеты и даже ретрив пароля на хабре. О — в карму вообще молчу :)

[demimurych]

Вероятно стоит всерьез задуматься о рефакторинге, раз присутствуют в коде проекта подобного рода запросы.
Мне трудно представить для каких целей может потребоваться выборка 300K id из одной таблицы.

[Степан]

Запрос из примера это наследие старых mysql_num_rows в коде, который писался еще лет 5 назад. 

Да, к сожалению я не родился зная сразу все. Тогда не знал разницу с count(id) или еще лучше SQL_CALC_FOUND_ROWS. Много где использовал mysql_num_rows. По мере нахождения исправляю старые глупости. Но суть вопроса была в другом…

[demimurych]

Ваш вопрос будет автоматически снят при использовании верхной архитектуры приложения.
Разница в блокировках между myisam и iinodb в том, что в первом случае при апдейте блокируется вся таблица, во втором случае только запись которая апдейтиться.
Как результат, традиционно считается что innodb несколько медленнее myisam на чтение, в силу того что при доступе к каждой отдельной записи появляются издержки на обслуживание блокировок. Сменив тип базы с mysam на innodb и делая выборку всех записей при этом база испытывает большую нагрузку на запись в эту же таблицу, вы фактически замедлили выполнения запроса из вашего примера в разы.
И скорее всего столкнулись с тем что mysql просто перестал справляться с нагрузкой. Логично предположить, что кроме такого запроса есть еще несколько таких же «изящных», которые приводят к оверхеду.

[Степан]

Вы ошибаетесь. Во-первых таких запросов больше нет. При myisam и частых апдейтах у меня много запросов ставало в ожидания завершения апдейта. За сутки наблюдений с innodb все стало в разы лучше. Количество запросов в секунду выросло из 700-720 до 800! Количество 502 ошибок вообще ушло в 0 за сегодня.

[demimurych]

Воспользуйтесь чем нибудь вроде этого
dedicatesupport.com/content/optimizatsiya-servera-mysql-shag-pervyi-utilita-mysqltuner
я думаю подобный инструмент вам очень поможет.

Answer 1

[Пума Тайланд]

select id from site_users
если у вас 10 миллиардов id то почему он должен быстро выполняться то?
К тому же не уверен что мускул умеет брать данные из индекса и сильно не уверен, что взять данные из индекса быстрее чем просто из таблицы.

Comments

[Виталий Желтяков]

Согласен с opium, select по такому количеству записей в innodb будет медленнее, чем в myisam. Организуйте по другому индексы.

От себя: на всякий случай проверьте логи, кэширование запросов и права доступа — возможно где-то ошибка.

Answer 2

[rakot]

Причина скорее всего кроется в дедлоках. Какие то запросы на Update намертво залочили пару записей из site_users, а так как вы тянете их все, то запрос ждёт когда они разблокируются. Судя по гуглу проблема существует, можно погуглить «mysql deadlock sending data».

Могу порекомендовать перейти на Percona, там вроде как больше внимания уделяют этой проблеме.

Comments

[Виталий Желтяков]

Это явно не Deadlock-и, т.к. они не проявлялись на myisam.

[Пума Тайланд]

Тогда бы в myisam все умирало бы намного раньше, так как там блочиться вся таблица, а в innodb только записи. Я бы сказал переход на innodb как раз решает эту проблему.

[rakot]

Вы издеваетесь чтоли, какие дедлоки в MyISAM?
VitaZheltyakov какие ты индексы предлагаешь организовать если идёт выборка всей таблицы, mysql даже не будет смотреть в сторону индексов, если в запросе нет слова limit.

[Melkij]

> mysql даже не будет смотреть в сторону индексов
Будет, если под рукой есть покрывающий индекс — оттуда и ответит без обращения к данным.

[rakot]

Каковы шансы на то что id не является индексом?

[Melkij]

Около 20%, мало ли это правая часть составного индекса или какой-то странный человек делал.

[Виталий Желтяков]

Мы не издеваемся. В MyISAM блокируется вся таблица при изменении, в Innodb только строка. Если в таблице происходят частые изменения, то выбор Innodb — это правильный выбор.

А Вам, rakot, я бы посоветовал бы почитать документацию по движкам СУБД.

[rakot]

Хорошо, а теперь найдите определение дедлоку и вообще почитайте что это такое.

[Виталий Желтяков]

rakot, я знаю что такое Deadlock. А вот Вы, видно, не понимаете, что если бы на Innodb возникали бы deadlock-и, то на MyISAM возникали долговременные Lock-и (блокировки).

Answer 3

[Lev Lybin]

innodb натюнена в конфигах мускула? С настройками по умолчанию на хайлоаде она тупит.
Есть много манов, в том числе и на русском:
goo.gl/VrA1R

Comments

[Lev Lybin]

Выставьте таймауты также, потом увидите, скорее всего, что отваливается и туда копать.

Answer 4

[fred]

Это время показывает сколько _реально_ времени выполнялся запрос, включая ожидание блокировок, а не чистое время на запрос. Если залочить таблицу а в другом коннекте сделать update одной строки, он может и 100 лет выполняться, по этому счетчику