Так получилось, что моя команда унаследовала, ист
Большинство объектов находится в удалённых от «человека разумного» регионах (горы, степь). Некоторые объекты находятся в ~200км от ближайшего населённого пункта. Перебои с электропитанием на этих объектах, дело вполне обычное и регулярное. ИБП очень выручают, но иногда и они не в состоянии спасти от продолжительного блэкаута. А чаще всего от серии блэкаутов. Тоесть ИБП ещё не зарядился, оборудование уже включено и работает, пишет данные в БД, и тут ЭП начинает пропадать и появляться, и снова пропадать. Системы падают.
До апгрейда MySQL до версии 5.6.23, в месяц приходилось восстанавливать вручную две-три БД. Сейчас восстанавливать приходится реже, но всё равно приходится. С августа месяца восстанавливали всего две БД.
После одной из статей kaamos, мы начали тестировать 5.6.26 и тестирование показало, что эта версия ещё более живуча. Однако условия сайтов, полноценно симулировать мы не можем (около 20 типов сайтов). Профиль нагрузки на всех этих типах разный, хотя модель БД одна.
Итак, ключевое условие проблемы:
У нас есть таблицы с внешними ключами и ON DELETE CASCADE ON UPDATE CASCADE, на некоторых таблицах которые референцируются на вышеназванные, установлены триггеры.
Вполне возможно, — это плохой дизайн модели данных для MySQL. Почему? Только сегодня наткнулся на следующее утверждение:
The InnoDB engine is fully ACID compliant, but fails the standard definition of consistency when using a combination of InnoDB, foreign keys with cascading actions and triggers. This is the result of triggers being implemented at MySQL's SQL layer and foreign keys being implemented at the InnoDB Storage Engine level.
Если данное утверждение верно, то буква 'C' из ACID, в нашем случае совершенно не гарантирована.
И тогда не стоит удивляться сообщениям об ошибках вида:
[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!
Также похоже не стоит на 100% надеяться на консистентность при репликации.
Возможно данная проблема решена в 5.7, и как только он появится в пакетной базе нашего дистрибутива, мы начнём обновлять системы и счастливо забудем о проблеме.
Однако, сколько ещё сюрпризов таит в себе многоуровневая архитектура MySQL?
Комментарии (27)
thatsme
05.11.2015 00:18+2Простите, как Ваше замечание относится к заметке? У некторого подмножества пользователей MySQL/percona/MariaDB, есть ощущение, что «что-то с MySQL не так», и иногда совершенно безосновательное, иногда обоснованное(см. список багфиксов: 1, 2). Мы-же столкнулись с вполне конкретной проблемой, которая как выясняется известна, но не всем. И проблема в самой архитектуре MySQL, и относится она к очень важной функциональности БД, а именно обеспечение консистентности данных. Да, за полтора года, из за этой ошибки мы не смогли восстановить всего одну БД после падения с помощью recovery и пришлость восстанавливать её из бэкапа. Было потеряно примерно 20 часов данных. Если сказать при этом, что «менеджмент был в ярости», то это будет большое преуменьшение, которое цензурным языком не описать.
Grox
05.11.2015 04:37+4Немного офтопика.
Я понимаю, что это не ваша разработка, но хочу заметить, или спросить, как по вашему:
А удалять или обновлять данные точно нужно в тех же таблицах, в которые вы их пишите? Если это критичная информация, может быть стоило бы хранить её в виде логов (как минимум), а обработку вести в отдельных таблицах?thatsme
05.11.2015 13:50-1Разработчики ПО, не были дураками. Модель БД хорошая, и отвечает всем требованиям. Если-бы не было вышеуказанной проблемы, о которой разработчики видимо были не в курсе, также как и многие другие, то этой заметки-бы и не было. «Если-бы, да кабы, да во рту росли грибы...»
Самое забавное, что в этой теме массивно плюсуют, оффтопики :) Первый коментарий забавляет особо. Я видимо кому-то на холиварную мозоль наступил.
galaxy
05.11.2015 04:00+2This is the result of triggers being implemented at MySQL's SQL layer and foreign keys being implemented at the InnoDB Storage Engine level.
Не совсем сначала понял, чем это может грозить, но ниже в статье написано понятнее:
Also, the support of ForeignKeys in MySQL is not yet complete, as cascade actions are not considered as SQL statements and thus do not fire triggers etc
thatsme
05.11.2015 13:51Если, вы прочтёте внимательно, то эти вещи никак не связаны и относятся к совершенно разным проблемам. Тот отрывок который Вы, бессовестно вырвали из контекста относится к поддержке триггеров в движках отличных от InnoDB и NDB.
galaxy
05.11.2015 16:09эти вещи никак не связаны и относятся к совершенно разным проблемам
не согласен.
Но вы бы уж тогда пояснили, как связана эта проблема и ваша ошибка:
И тогда не стоит удивляться сообщениям об ошибках вида:
[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!
ЗЫ: по какому принципу вы запятые расставляете?
erlyvideo
05.11.2015 08:30-3Самое главное: Mysql как не умел, так и не умеет использовать индексы в средней сложности запросах при сортировке по индексированному так, что бы читать с диска не всю таблицу, а только хвост индекса.
В итоге запросы типа select * from articles where rating > 50 and is_published=1 order by published_at desc limit 40; сортируются на диске.Ivan22
05.11.2015 09:03+2Не понял. Если мускл тут будет индекс по рейтингу и is_published использовать — сортировать по-любому. Или вы хотите индекс по published_at использовать?
erlyvideo
05.11.2015 17:26конечно индекс по published_at. Постгрес в два дискрида всё вынимает, а мускль сортирует.
Ivan22
06.11.2015 09:18может у мускуля статистики нет? Если например в вашей таблице меньше 40 записей с rating > 50 and is_published=1 — то как раз поиск по индексу по (is_published, rating ) будет куда оптимальнее, ибо по published_at придется ВСЕ записи просканить.
erlyvideo
06.11.2015 11:38просто мускль не умеет принимать такое решение и идти по индексу по created_at. И да, в мускле всё плохо со статистикой.
kashey
05.11.2015 09:56+1В последних версиях с этим стало лучше, но иногда приходится использовать вложенные запросы или join таблицы с собой же.
На каждую хитрую гайку можно найти винт… И, к сожалению, нужно.
norguhtar
05.11.2015 09:11Если данное утверждение верно, то буква 'C' из ACID, в нашем случае совершенно не гарантирована.
Верно. Так-как триггеры должны работать с любыми движками в MySQL. Проблема еще в том что какого-то стандарта на что умеет движок нет. Если ориентироваться на MyISAM то все весело и отлично. Он же ничо не умеет :)
kashey
05.11.2015 09:53+1Лет 10 назад, когда я только начинал пиликать сайты я попробовал MySql. На работе в то время использовали Sybase — в принципе нормальная, честная, «полная» субд. Кто-то в те же времена пробовал PostgreSQL.
В тоже время по технической необходимости мы слезали с С++ на чистый C.
И, совершенно случайно, выяснилось — без половины «свистелок и перделок» жить можно — чуть более продуманный подход к архитектуре, чуть меньше веры и доверия магиям сторонних компонент, ешелонированный бэкап, надежность топора…
В том числе полностью забили на тригеры — база должна выполнять «сервисные» функции не тогда когда она хочет, а когда мы решили что можно.thatsme
05.11.2015 13:54+1И опять, комментарий не по теме, и опять заплюсован [здесь смайлик]. Это вирус «четверга» [и здесь].
kashey
05.11.2015 14:35Почему же не по теме? Разница между С/С++ примерно такая же как между MySQL/Oracle. Но разве С хуже С++? Разве jQuery сильно лучше ванильного JS?
У каждого инструмента есть свои плюсы, свои минусы, свои особенности работы. Не надо просто пытаться сделать из мухи слона, а из мускуля базу данных под ентерпрайз. Смирись %username%, и все получится.
Реально можно подстроится под сложные условия работы, например реализовать двойную буферизацию на уровне таблиц. Логирование, журналирование…
Да — вы это ждете от самой системы, быть может не надо ей так доверять?galaxy
05.11.2015 16:34+1Знаете, используя, например, автомобиль как инструмент, я ожидаю, что у него будут фары и колеса, а не мне самому придется оные приобретать и прикручивать.
Если РСУБД не соответствует требованиям ACID, это не РСУБД.
un1t
05.11.2015 14:03+2Очень странная постановка вопроса. У продукта X есть недостаток, поэтому его нельзя использовать. Дак а в остальных как? Может быть там еще хуже. Я вот лично не знаю как с этм делом в Postgres или где-то еще. Что там будет происходить при постаояннх отключениях питания? И похоже что у вас все-таки сильно частная задача, большинство разработчиков базу в таких условиях не экслуатируют.
thatsme
05.11.2015 14:15Я наверное, очень плохо донёс свою мысль в этом топике.
Есть конкретная ситуация, в которой нарушается один из принципов ACID. Для любой РСУБД, подобная ситуация, тем более возникшая из за архитектурных особенностей, это КО.
Есть workaround, по крайней мере для нас: изменить модель данных. Это просто расходы на переработку софта разработчиком. Вопрос чисто финансовый: «Что дороже: целостность данных, или разработка ?»
Однако если Вы поищете, в гугле вот такую строчку:
[ERROR] Table has no primary key in InnoDB data dictionary, but has one in MySQL!
То найдёте ещё 10-к ссылок, на баг этого-же класса, на разных версиях MySQL или percona. (Сразу исключите все ссылки на стандартную ситуацию возникновения этой ошибки: удаление файла из ФС).un1t
05.11.2015 17:01Без рассмотрения альтернатив это все равно плохой аргумент. «Фатальный недостаток автомобильей марки X — они бьются при врезании в стену на скорости 100 км/ч» ну бьются, ну и что. Остальные чтоли не бьются? Может быть альтерантивы еще в более тепличных условиях ломаются. В данной статье это не рассматривается.
И как я понял, эта штука происходит только при использовании каскадов одновременно с тригерами. Я вот например тригеры не использую, поулчается что у меня эта проблема не проявится, так что для меня это не является существенным аругментом.thatsme
06.11.2015 05:40Я разве где-то, что-то с, чем-то сравниваю? У меня альтернативы нет. По крайней мере для этого проекта. На объектах от 6 до 11 миллионов записей, только в исторические таблицы попадает. Терять эти данные никто не позволит.
Да для Вас, в данный момент, это вообще не проблема. А может быть и нет. Всё дело в том, что какие-то вещи в MySQL, обрабатываются на SQL уровне, а какие-то на уровне InnoDB. И что-то, где-то не стыкуется.
Перенос системных таблиц в InnoDB в версии 5.7 это отличный шаг, который кучу проблем с консистентностью репликаций снимет.
Постепенно исправят и эту ошибку. Но на данный момент, в новые проекты MySQL у нас не пойдёт.
Nastradamus
06.11.2015 11:17Вставлю свои 5 копеек: в большинстве серьёзных баз, что я видел, одновременно используют и триггеры и каскады. Постгрес, конечно же.
Ivan22
06.11.2015 12:00И я скромное имхо добавлю. Много разных баз видел. На разных субд. От богом забытого Informix-a до mssql и oraсle, от firebird до netezza. И в общем случае хочу заметить, что никаких общих случаев нет. Всегда все по разному. Где-то надо одно, где-то другое. В каком-то случае конкретный баг — настолько критичен, что хоть базу меняй. А в другом месте, с ним не столкнутся никогда за все время использования. Но там будут свои грабли.
Конечно это я тут капитанствую, но вывод простой — даже самый маленьки баг может быть кому очень критичен. И наоборот — другой баг казалось бы жесть, как работать вообще можно — а у кого-то даже кейса такого никогда не возникнет за 10 лет использования.
ef_end_y
Мне кажется, критиковать mysql должен человек, который: a) попробовал mysql б) попробовал что-то иное, причем на схожих задачах. А так минусы имеет абсолютно все продукты. Насколько я знаю, репликация — это слабая сторона очень многих популярных СУБД и mysql далеко не на последнем месте