Недавняя серия статей («Памятка евангелиста PostgreSQL: критикуем MySQL грамотно» 1,2,3) зацепила за живое.

Так получилось, что моя команда унаследовала, истеорически сложившуюся систему, с 300+ объектами, где одним из ключевых компонентов системы выступает именно MySQL. На некоторых объектах также используется репликация. ПО использующее MySQL от стороннего разработчика.

Большинство объектов находится в удалённых от «человека разумного» регионах (горы, степь). Некоторые объекты находятся в ~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)


  1. ef_end_y
    04.11.2015 23:47
    +16

    Мне кажется, критиковать mysql должен человек, который: a) попробовал mysql б) попробовал что-то иное, причем на схожих задачах. А так минусы имеет абсолютно все продукты. Насколько я знаю, репликация — это слабая сторона очень многих популярных СУБД и mysql далеко не на последнем месте


  1. thatsme
    05.11.2015 00:18
    +2

    Простите, как Ваше замечание относится к заметке? У некторого подмножества пользователей MySQL/percona/MariaDB, есть ощущение, что «что-то с MySQL не так», и иногда совершенно безосновательное, иногда обоснованное(см. список багфиксов: 1, 2). Мы-же столкнулись с вполне конкретной проблемой, которая как выясняется известна, но не всем. И проблема в самой архитектуре MySQL, и относится она к очень важной функциональности БД, а именно обеспечение консистентности данных. Да, за полтора года, из за этой ошибки мы не смогли восстановить всего одну БД после падения с помощью recovery и пришлость восстанавливать её из бэкапа. Было потеряно примерно 20 часов данных. Если сказать при этом, что «менеджмент был в ярости», то это будет большое преуменьшение, которое цензурным языком не описать.


    1. Grox
      05.11.2015 04:37
      +4

      Немного офтопика.
      Я понимаю, что это не ваша разработка, но хочу заметить, или спросить, как по вашему:
      А удалять или обновлять данные точно нужно в тех же таблицах, в которые вы их пишите? Если это критичная информация, может быть стоило бы хранить её в виде логов (как минимум), а обработку вести в отдельных таблицах?


      1. thatsme
        05.11.2015 13:50
        -1

        Разработчики ПО, не были дураками. Модель БД хорошая, и отвечает всем требованиям. Если-бы не было вышеуказанной проблемы, о которой разработчики видимо были не в курсе, также как и многие другие, то этой заметки-бы и не было. «Если-бы, да кабы, да во рту росли грибы...»

        Самое забавное, что в этой теме массивно плюсуют, оффтопики :) Первый коментарий забавляет особо. Я видимо кому-то на холиварную мозоль наступил.


  1. galaxy
    05.11.2015 04:00
    +2

    This 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


    1. thatsme
      05.11.2015 13:51

      Если, вы прочтёте внимательно, то эти вещи никак не связаны и относятся к совершенно разным проблемам. Тот отрывок который Вы, бессовестно вырвали из контекста относится к поддержке триггеров в движках отличных от InnoDB и NDB.


      1. galaxy
        05.11.2015 16:09

        эти вещи никак не связаны и относятся к совершенно разным проблемам
        не согласен.
        Но вы бы уж тогда пояснили, как связана эта проблема и ваша ошибка:
        И тогда не стоит удивляться сообщениям об ошибках вида:
        [ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!


        ЗЫ: по какому принципу вы запятые расставляете?


  1. 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; сортируются на диске.


    1. Ivan22
      05.11.2015 09:03
      +2

      Не понял. Если мускл тут будет индекс по рейтингу и is_published использовать — сортировать по-любому. Или вы хотите индекс по published_at использовать?


      1. erlyvideo
        05.11.2015 17:26

        конечно индекс по published_at. Постгрес в два дискрида всё вынимает, а мускль сортирует.


        1. Ivan22
          06.11.2015 09:18

          может у мускуля статистики нет? Если например в вашей таблице меньше 40 записей с rating > 50 and is_published=1 — то как раз поиск по индексу по (is_published, rating ) будет куда оптимальнее, ибо по published_at придется ВСЕ записи просканить.


          1. erlyvideo
            06.11.2015 11:38

            просто мускль не умеет принимать такое решение и идти по индексу по created_at. И да, в мускле всё плохо со статистикой.


    1. kashey
      05.11.2015 09:56
      +1

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


  1. norguhtar
    05.11.2015 09:11

    Если данное утверждение верно, то буква 'C' из ACID, в нашем случае совершенно не гарантирована.

    Верно. Так-как триггеры должны работать с любыми движками в MySQL. Проблема еще в том что какого-то стандарта на что умеет движок нет. Если ориентироваться на MyISAM то все весело и отлично. Он же ничо не умеет :)


  1. kashey
    05.11.2015 09:53
    +1

    Лет 10 назад, когда я только начинал пиликать сайты я попробовал MySql. На работе в то время использовали Sybase — в принципе нормальная, честная, «полная» субд. Кто-то в те же времена пробовал PostgreSQL.
    В тоже время по технической необходимости мы слезали с С++ на чистый C.
    И, совершенно случайно, выяснилось — без половины «свистелок и перделок» жить можно — чуть более продуманный подход к архитектуре, чуть меньше веры и доверия магиям сторонних компонент, ешелонированный бэкап, надежность топора…
    В том числе полностью забили на тригеры — база должна выполнять «сервисные» функции не тогда когда она хочет, а когда мы решили что можно.


    1. thatsme
      05.11.2015 13:54
      +1

      И опять, комментарий не по теме, и опять заплюсован [здесь смайлик]. Это вирус «четверга» [и здесь].


      1. kashey
        05.11.2015 14:35

        Почему же не по теме? Разница между С/С++ примерно такая же как между MySQL/Oracle. Но разве С хуже С++? Разве jQuery сильно лучше ванильного JS?
        У каждого инструмента есть свои плюсы, свои минусы, свои особенности работы. Не надо просто пытаться сделать из мухи слона, а из мускуля базу данных под ентерпрайз. Смирись %username%, и все получится.
        Реально можно подстроится под сложные условия работы, например реализовать двойную буферизацию на уровне таблиц. Логирование, журналирование…
        Да — вы это ждете от самой системы, быть может не надо ей так доверять?


        1. galaxy
          05.11.2015 16:34
          +1

          Знаете, используя, например, автомобиль как инструмент, я ожидаю, что у него будут фары и колеса, а не мне самому придется оные приобретать и прикручивать.
          Если РСУБД не соответствует требованиям ACID, это не РСУБД.


  1. un1t
    05.11.2015 14:03
    +2

    Очень странная постановка вопроса. У продукта X есть недостаток, поэтому его нельзя использовать. Дак а в остальных как? Может быть там еще хуже. Я вот лично не знаю как с этм делом в Postgres или где-то еще. Что там будет происходить при постаояннх отключениях питания? И похоже что у вас все-таки сильно частная задача, большинство разработчиков базу в таких условиях не экслуатируют.


    1. 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. (Сразу исключите все ссылки на стандартную ситуацию возникновения этой ошибки: удаление файла из ФС).


      1. un1t
        05.11.2015 17:01

        Без рассмотрения альтернатив это все равно плохой аргумент. «Фатальный недостаток автомобильей марки X — они бьются при врезании в стену на скорости 100 км/ч» ну бьются, ну и что. Остальные чтоли не бьются? Может быть альтерантивы еще в более тепличных условиях ломаются. В данной статье это не рассматривается.
        И как я понял, эта штука происходит только при использовании каскадов одновременно с тригерами. Я вот например тригеры не использую, поулчается что у меня эта проблема не проявится, так что для меня это не является существенным аругментом.


        1. mickvav
          05.11.2015 21:00

          Хм, это ответ вида — «зачем чинить ручник, я же всегда на скорости оставляю!»


          1. un1t
            05.11.2015 21:46

            Да, что-то такое я сказал, но основная мысль была не в этом.


        1. thatsme
          06.11.2015 05:40

          Я разве где-то, что-то с, чем-то сравниваю? У меня альтернативы нет. По крайней мере для этого проекта. На объектах от 6 до 11 миллионов записей, только в исторические таблицы попадает. Терять эти данные никто не позволит.

          Да для Вас, в данный момент, это вообще не проблема. А может быть и нет. Всё дело в том, что какие-то вещи в MySQL, обрабатываются на SQL уровне, а какие-то на уровне InnoDB. И что-то, где-то не стыкуется.

          Перенос системных таблиц в InnoDB в версии 5.7 это отличный шаг, который кучу проблем с консистентностью репликаций снимет.

          Постепенно исправят и эту ошибку. Но на данный момент, в новые проекты MySQL у нас не пойдёт.


          1. un1t
            06.11.2015 10:43

            Ну хорошо, MySQL для новых проектов вы использовать не будете. А что будете?


  1. Nastradamus
    06.11.2015 11:17

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


    1. Ivan22
      06.11.2015 12:00

      И я скромное имхо добавлю. Много разных баз видел. На разных субд. От богом забытого Informix-a до mssql и oraсle, от firebird до netezza. И в общем случае хочу заметить, что никаких общих случаев нет. Всегда все по разному. Где-то надо одно, где-то другое. В каком-то случае конкретный баг — настолько критичен, что хоть базу меняй. А в другом месте, с ним не столкнутся никогда за все время использования. Но там будут свои грабли.
      Конечно это я тут капитанствую, но вывод простой — даже самый маленьки баг может быть кому очень критичен. И наоборот — другой баг казалось бы жесть, как работать вообще можно — а у кого-то даже кейса такого никогда не возникнет за 10 лет использования.