Все нижепредставленное является перечнем типовых ошибок дизайна и эксплуатации MySQL, которые могут повлиять на процесс адаптации схемы, переработки кода и переноса данных. Наличие всех этих мелочей в разнообразных коварных сочетаниях является одной из причин, по которой существующие “универсальные” инструменты вряд ли справятся конкретно с вашей базой.
Именно поэтому в предыдущей статье я рекомендовал не тратить время на поиск серебряной пули и написать что-нибудь свое “на коленке”, что действительно работает. Данная статья призвана облегчить написание такого инструмента, указывая на потенциальные изъяны, в наличии которых вы может сравнительно быстро убедиться.
Перейдем к делу.
Значения по умолчанию
MySQL печально известен своим стремлением упрощать и облегчать труд разработчика. Как правило, на старте эксплуатации выключен strict режим, который ответственно сообщает об отсутствии корректных значений по-умолчанию при вставке данных в таблицы.
Иными словами, у вас может быть объявлена колонка в таблице без параметра DEFAULT. При вставке в таблицу, вы забудете указать эту колонку, но MySQL не растеряется и вставит что-нибудь “дефолтное” за вас. Как правило, это 0, пустая строка или аналогичное “нулевое” значение соответствующего типа данных. PostgreSQL всегда выбрасывает constraint violation, поэтому будьте готовы исправлять соответствующие ситуации в коде приложения.
Отдельно хочется отметить любовь MySQL вставлять неадекватные значения по-умолчанию в поля типа DATE / DATETIME / TIMESTAMP. Неаккуратная работа с такими полями может привести к наличию строк типа “0000-00-00” или “00:00:00 0000-00-00” в вашей базе. Понятное дело, PostgreSQL такое не пропустит. Приходится либо “фиксить” данные в исходной БД, либо, при импорте, заменять их принудительно на NULL и соответствующим образом исправлять код приложения и структуру соответствующей таблицы.
Строгость constraints, STRICT MODE
В продолжение темы о значениях по-умолчанию и ограничениях, MySQL также довольно лояльно относится к их нарушению. Например, у вас объявлено поле типа VARCHAR2(255). MySQL имеет нездоровую тенденцию делать автоматический truncate строки, если она не помещается в указанную размерность. PostgreSQL будет ругаться.
В разрезе данной проблемы, стоит также отметить, что некорректные последовательности байт в строках указанной кодировки MySQL будет молча вырезать при выключенном STRICT режиме. PostgreSQL всегда ругается на некорректные byte sequences. Будьте готовы дополнительно санитировать входные данные при подготовке кода приложения к работе с PostgreSQL.
Ну и просто заранее проверьте, сделав backup предварительно, что вся ваша база MySQL консистентно пребывает в одной кодировке, и никто из программистов не создал через phpMyAdmin таблицу с полем VARCHAR2 в какой-нибудь swedish локали или нечто подобное.
Транзакции и autocommit
Программист обычно боится использовать транзакции, либо, в силу своей некомпетентности, считает их “тяжелыми” и “медленными”. По факту, исполнение запросов без транзакции невозможно. Поэтому, для таких специалистов, MySQL услужливо работает в режиме AUTOCOMMIT, который скрывает всю порочную практику транзакционной обработки “под капотом”. В PostgreSQL вам, скорей всего, придется симулировать такой подход к эксплуатации базы данных включением специального флага (SET AUTOCOMMIT).
Отдельный интерес представляет возможность MySQL использовать внутри транзакции движки таблиц, не поддерживающие транзакционную обработку данных. Здесь можно лишь процитировать документацию (“If you were not deliberately mixing transactional and nontransactional tables within the transaction, the most likely cause for this message is that a table you thought was transactional actually is not.”) и молча аплодировать.
Если это возможно, постарайтесь систематизировать движки до миграции. Если нет, переезжайте с использованием autocommit либо старайтесь обеспечить хотя бы номинальную транзакционную обработку для таких ситуаций. Хуже уже точно не будет.
Свой собственный абзац заслуживает движок таблиц под названием Memory. Особенность этого движка заключается в том, что он не персистирует данные на диск, а хранит целиком в памяти. Количество памяти фиксировано и задается настройкой, которую менять можно только путем перезапуска сервера БД. С эксплуатацией этого движка связано два нюанса:
таблица, вышедшая за предел допустимого размера, моментально “свопится” на диск, карета превращается в тыкву, иногда вместе с базой данных (если движок Memory использовался для оптимизации производительности);
как правило, в такие таблицы кладутся данные, считающиеся временными, которые не жалко и потерять. После первой потери выясняется, что данные, оказывается-то, были mission critical, и терять их нельзя. А архитектура БД и приложения уже прочно “подсажена” на эти таблицы, в результате чего все начинают на них молиться, а любая штатная перезагрузка базы превращается в специфический ритуал с конвертированием таблиц в персистируемый на диск формат, преобразованием обратно в Memory и подобными шаманствами.
В общем и целом, PostgreSQL просто сам по себе более адекватен в плане менеджмента памяти и, при условии наличия достаточного ее количества, выделенного под вашу базу, будет прекрасно держать такую таблицу в “оперативке” и отдавать ряды по запросам от приложения. Если же все-таки workload на таблицы очень большой, памяти не хватает или банально нет времени перерабатывать приложение под персистируемые таблицы, можете сэмулировать поведение Memory таблиц, создавая аналогичные в PostgreSQL с ключевым словом UNLOGGED (CREATE UNLOGGED TABLE …). Цитируя документацию:
If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead log (see Chapter 29), which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The contents of an unlogged table are also not replicated to standby servers. Any indexes created on an unlogged table are automatically unlogged as well.
Экономия на спичках
Многие архитекторы MySQL часто забывающие о таких крайне важных вещах, как правильная расстановка primary keys и поддержание корректности реляционных взаимоотношений, тем не менее, считают своим долгом очень точно указать разрядность и знак чисел, складываемых в тот или иной integer. MySQL предлагает очень большое разнообразие типов данных (int, smallint, tinyint и проч.), для каждого из которых можно указать предельное количество знаков и отсутствие/наличие отрицательных значений. Например, TINYINT будет вмещать значения от -128 до 127, а UNSIGNED TINYINT — от 0 до 255.
О чем вышеупомянутый архитектор никогда не подумает, так это о будущем. Рано или поздно в такое поле приедет значение, не попадающее в указанные рамки, и будет урезано MySQL до ближайшего корректного значения, при отключенном по-умолчанию strict режиме. Я неоднократно наблюдал базы, где из-за таких глупостей объективно важные и реальные цифры показателей эффективности “срезались” до предельно допустимых форматом высоких значений, что приводило, в свою очередь, к менее эффективной работе системы в целом.
Знающие люди уже догадались, что подобная ситуация происходит и с типами decimal/numeric, в которых традиционно хранится что-нибудь важное, например, деньги. В таких случаях, речь уже может пойти о потере реальной прибыли. PostgreSQL избавлен от подобного зоопарка возможностей выстрелить себе в ногу (а то, и в голову) и всегда будет ругаться на выход за пределы заданных “констрейнтов”.
INSERT IGNORE + ON DUPLICATE KEY… UPDATE
Одни из самых нежно любимых разработчиками MySQL конструкций не имеют прямых аналогов в PostgreSQL.
Постоянное использование INSERT IGNORE настойчиво намекает о проблемах дизайна схемы вашей БД. Тем не менее, сэмулировать такое поведение можно написанием хранимой процедуры, которая делает INSERT и, в случае возникновения UNIQUE EXCEPTION, перехватывает его и молча “гасит”.
ON DUPLICATE KEY… UPDATE, или так называемый UPSERT (UPdate + inSERT), был все-таки признан нужной “фичей” в сообществе PostgreSQL. Его релиз назначен на 9.5 версию СУБД. До тех пор, вы можете сэмулировать это следующей конструкцией:
-- обновляем все записи, которые уже есть в таблице
UPDATE existing_table ex
SET field1 = ex.field1 + up.field1,
field2 = up.field2,
…
FROM updates up
WHERE ex.pkey_field1 = up.pkey_field1 AND ex.pkey_field2 = up.pkey_field2 …;
-- вставляем несуществующие
INSERT INTO existing_table (columns, …)
SELECT new.field1, new.field2, …
FROM new_data new
WHERE NOT EXISTS (
SELECT * FROM existing_table ex WHERE ex.pkey1 = new.pkey1 and ex.pkey2 = new.pkey2 and …;
);Работа с временем в MySQL
Ранее уже упоминалась проблематика округления значений времени в типах данных, имеющихся в MySQL для этих целей. Отдельном рассмотрим разнообразное количество возможных вариантов хранения такой информации. Само по себе это не является категорической проблемой, все неприятности проистекают от незнания разработчиками разницы в типах и, как следствие, фривольное использование всех сразу без какой-то видимой логики. Ситуация, когда для хранения времени используются DATETIME, TIMESTAMP И BIGINT (для значений unix timestamp), довольно регулярна и наблюдается чуть ли не на каждой второй базе.
DATETIME от TIMESTAMP отличается хранением смещения часового пояса. Исходя из этого, планируйте соответствующие типы данных в PostgreSQL (timestamp with/without time zone) и будьте готовы при импорте на лету осуществлять добавление/вычитание поправки на часовой пояс (если вдруг база данных жестко настроена на конкретную “таймзону”, а типы данных при этом используются без разбора).
Не менее важной является проблема хранения unix timestamp в полях типа bigint. MySQL имеет неприятную практику добавлять количество секунд, соответствующих смещению часового пояса, в это значение, которое, идеологически, должно оставаться в UTC. Такое поведение не соответствует стандарту, поэтому, начиная с версии 9.2, в PostgreSQL приходится применять неприятный workaround, чтобы получать значение epoch с поправкой на часовой пояс, в виде SQL-конструкции EXTRACT(EPOCH FROM TZVALUE::TIMESTAMP WITH TIME ZONE), которая извлекает значение эпохи из указанного timestamp, явно конвертируя его в формат с хранением часового пояса.
Работа с часовыми поясами — одна из тех операций, наличие ошибок в которой простительно, поскольку задача действительно является нетривиальной. Объяснение правильных подходов к работе с часовыми поясами в PostgreSQL можно почитать в замечательной статье, расположенной по ссылке www.depesz.com/2014/04/04/how-to-deal-with-timestamps.
Блокировки
Частенько, программисты любят вручную управлять блокировками на базе данных. Явно выставлять shared и exclusive locks или, что еще хуже, менять уровень изоляции транзакции по-умолчанию. Строго говоря, делать это категорически противопоказано. Тем не менее, наблюдались в практике ситуации, когда архитекторы решали реализовывать целостность операций путем “глухой” блокировки обновляемых таблиц. При этом мало кого беспокоило, что на эти же таблицы идет OLTP нагрузка с пользовательских интерфейсов. Очень часто позывы такие бывают от того, что транзакции MySQL не применимы в силу наличия non-transactional движков таблиц или общего безумия в плане настроек, царящих на базе данных. Например, существует долгий запрос, ради которого “таймауты” откручены до предела и нормальные транзакции не применить.
Лучший вариант решения данной проблемы при миграции — попробовать использовать честные транзакции в PostgreSQL, который с большой долей вероятности лучше справится с ситуацией высокого concurrency. Если все-таки блокировки являются неотъемлемой частью логики, изучите механизм advisory locking. Так называемые рекомендательные блокировки являются виртуальными и не приводят к физическому блокированию данных. Их адекватное использование внутри транзакций несет значительно меньшие риски и является более щадящим для ресурсов и производительности СУБД. Но, безусловно, никто не отменяет потребности думать головой и аккуратно отслеживать все “кейсы” применения блокировок в коде приложения.
Заключение
Мы рассмотрели лишь малую часть удивительных вещей, которые могут происходить на сервере СУБД MySQL. Можно очень долго рассуждать от тонкостях работы репликации в MySQL и способах ее воспроизведения уже средствами PG, что, например, будет делать Олег Царев на предстоящем PG Day'15 Russia. В лучшем случае, проблема решается использованием механизма hot-standby. В худшем, придется осваивать нетривиальные в поддержке и эксплуатации инструменты типа Slony!
Я надеюсь, что эти примеры наглядно иллюстрируют, почему задача миграции является процессом сложным и практически нерешаемым с помощью существующих инструментов. Гарантированно работают только лишь вдумчивый, ручной подход и старая военная мудрость “know your data”.
Комментарии (56)
TimsTims
04.06.2015 13:53-2-MySQL имеет нездоровую тенденцию делать автоматический truncate строки, если она не помещается в указанную размерность.
Не сказал бы, что это минус, но и не плюс. В каких-то ситуациях это является плюсом, в каких-то минусом. В любом случае, ответственность ложится на разработчика, которому не нужно терять данные. Чуть ниже вы предлагаете выбрасывать исключение, чтобы опять-же разработчик сел, нашел ошибку и разобрал её. В любом случае вы предлагаете привлекать разработчика, от БД ничего не зависит — всё зависит от прямоты рук.
-О чем вышеупомянутый архитектор никогда не подумает, так это о будущем. Рано или поздно в такое поле приедет значение, не попадающее в указанные рамки, и будет урезано MySQL
Не соглашусь. Как-раз таки размерность поля расчитывается исходя из всех возможных принимаемых значений. Зато экономия «на спичках» оправдывает себя. Опять-же нужны прямые руки, кривые и так всё сломают.
-Постоянное использование INSERT IGNORE настойчиво намекает о проблемах дизайна схемы вашей БД.
Тоже не соглашусь. Что значит «постоянное использование»? Конечно эти команды используются не в каждом запросе на INSERT, но «иногда». Где грань между «иногда» = проблемы дизайна БД? Бывают ситуации, когда тебе нужно вставить много-много данных из другого источника, которые могут уже повторяться, а могут не повторяться, и в них могут быть важные новые данные. Забивать базу хламом ненужно, а удалять старые, чтобы потом сделать один большой INSERT накладно для БД. Так чем же эта команда плоха? Потому-что её нет в PostgreSQL?
Кажется сейчас начнется холивар, нахватаю минуса, но я выразил своё мнение. Mysql не является ПЛОХОЙ базой данных, жду ваших аргументов.
Blumfontein
04.06.2015 14:22+8— Пользователь вставляет 500 символов в колонку VARCHAR 255.
Ситуация 1: если БД ругается, то пользователь создает баг-репорт, отправляет разработчику, разработчик правит, работаем дальше.
Ситуация 2: БД не ругается, пользователь думает, что все норм. Далее еще полгода работа продолжается в таком же режиме. Затем, когда надо поднять информацию, оказывается, что половины данных нет. Все получают по шапке.
— Размерность поля на производительность, кажется, не оказывает практически никакого влияния.
— Насчет INSERT IGNORE и в правду не очень понятно, почему это плохо.
ploop
04.06.2015 14:33Размерность поля на производительность, кажется, не оказывает практически никакого влияния.
В PostgreSQL наблюдалось снижение производительности при использовании безразмерного numeric по сравнению со строгими integer/bigint/float.
Однако с текстом наоборот. Безразменый text зачастую удобнее использовать для всяких данных неопределённой длины (например комментарии, куда некоторые графоманы «войну и мир» вписывают)
TimsTims
04.06.2015 14:41-1Как я и написал(и автор поста) — проблема размерности решается разработчиком либо после либо до ошибки.
В данном случае разраб должен заранее проверять длину комментария, разница лишь в том — будет показана ли ошибка (в postgre), или нет(mysql). Не во всех случаях это хорошо, поэтому в любом случае разработчик должен всё предусмотреть, дело не в БД, а в компетентности разработчика.
EndUser
04.06.2015 16:58+3Программист должен реализовывать бизнес-процесс, а ACID должен следить за тем, чтобы данные бизнес-процесса были целыми.
Если программист бизнес-процессов будет внимательно думать о целостности данных, то кто будет внимательно заниматься реализацией бизнес-процессов?
rdruzyagin Автор
04.06.2015 14:59+2Приветствую!
Спасибо большое за конструктивный комментарий. Я вам даже «стрелочку вверх» нажал рядом с вашим именем.
MySQL ни в коем случае не является плохой СУБД. Мы ее применяли и даже применяем сейчас местами для наших проектов (см. мой комментарий выше). Просто, к сожалению, в ней много всяких непонятных нюансов, исторически возникших и усложняющих ее нормальную эксплуатацию. Типовые такие нюансы я и постарался представить.
> Не сказал бы, что это минус, но и не плюс. В каких-то ситуациях это является плюсом, в каких-то минусом. В любом случае, ответственность ложится на разработчика, которому не нужно терять данные. Чуть ниже вы предлагаете выбрасывать исключение, чтобы опять-же разработчик сел, нашел ошибку и разобрал её. В любом случае вы предлагаете привлекать разработчика, от БД ничего не зависит — всё зависит от прямоты рук.
В том-то и дело, что разработчики часто об этих вещах не подозревают. Начинают делать проект, думают — что все здорово, а потом выясняется через полгода «продакшена». За что я люблю PG — он сразу показывает программисту, где тот накосячил. Это приучает людей к порядку.
> Не соглашусь. Как-раз таки размерность поля расчитывается исходя из всех возможных принимаемых значений. Зато экономия «на спичках» оправдывает себя. Опять-же нужны прямые руки, кривые и так всё сломают.
Мой опыт мне подсказывает, что всегда что-то бывает неучтенным и рано или поздно «приедет» кривое значение. Касательно экономии, опять же, это лишь мой опыт, но я видел нагруженные базы на MySQL и проблемы там были далеко не из-за разрядности и кол-ва знаков. Чаще это были ручные блокировки, много DDL и подобные «тяжелые» вещи.
> Тоже не соглашусь. Что значит «постоянное использование»? Конечно эти команды используются не в каждом запросе на INSERT, но «иногда». Где грань между «иногда» = проблемы дизайна БД? Бывают ситуации, когда тебе нужно вставить много-много данных из другого источника, которые могут уже повторяться, а могут не повторяться, и в них могут быть важные новые данные. Забивать базу хламом ненужно, а удалять старые, чтобы потом сделать один большой INSERT накладно для БД. Так чем же эта команда плоха? Потому-что её нет в PostgreSQL?
В моем понимании, «долбежка» в таблицу повторяющихся значений с целью «напороться» на констрейнт — это признак непродуманного бизнес-процесса или его реализации. Как правило, сначала проектируют так, чтобы не было дубликатов и констрейнты расставляют более-менее по уму. Потом что-то меняется в условиях бизнеса, и начинают валиться дубли. Ленивые программисты моментально «нагугливают» INSERT IGNORE и радуются, вот же она — серебряная пуля! Вставляют IGNORE и отправляют код в «продакшен», не задумываясь, а что это за дубликаты, зачем они? Не надо ли переосмыслить дизайн схемы? Для меня, IGNORE сродни «собаки» в PHP, которая «подавляет» ошибки, вместо того, чтобы корректно их обрабатывать.
Alexeyco
04.06.2015 15:43+1Нюансы есть у всего, абсолютно всего. Вопрос выбора, что брать за основу и считать правильным, а что считать отступлением от условной правильности. Я, например, тоже узнал много нового, когда начал работать с Oracle — например, ее восприятие NULL как чего-то из ряда вон выходящего. Это всегда было, это есть и это будет, иначе зачем вообще нужны разные СУБД. Это частично нивелируется, если у вас есть какой-то слой абстракции — ORM, например. Когда вы работаете напрямую с СУБД (как я) — все чуть сложнее. Но лично я почти сразу, как начал программировать, решил поэкспериментировать с несколькими СУБД, в результате чего практически сразу понял, что чтобы не было фрустраций и нигде не пригорало, не нужно ожидать ВСЕГО от какой-то вещи. Всегда надо допускать, что где-то что-то пойдет не как было задумано.
С другой стороны, обижаться на желчь автора статьи я бы не стал, как не стал бы реагировать на явный перегиб в этой статье в пользу Postgres. Пройдет время, автор попробует что-то еще помимо Postgres, у него снова где-нибудь пригорит, потом еще и еще… а потом просто научится относиться спокойно ко всему. В конце концов, руки есть, ноги есть, голова есть, а значит все не так плохо )))
Специально написал комментарий в ваш адрес, упомянув вас в третьем лице… чтобы желчь не полилась в мою сторону. ))rdruzyagin Автор
04.06.2015 15:49+2Спасибо за комментарий!
Да бросьте Вы, какая желчь, так, рабочие моменты. Перегиб конечно есть, я PostgreSQL очень люблю. Преимущественно за то, что его внедрение в крупные проекты позволило мне больше спать по ночам и не получать смсок о том, что ваша реплика «отстает» на 52 тысячи секунд.
А вот пользоваться чем-то другим вряд ли пока что буду, я уже старею для всех этих экспериментов, приоритеты меняются в жизни. Хотя, с коммерческими СУБД с удовольствием бы поработал, не доводилось наблюдать в крупном «продакшене», но увы — специфика бизнеса не позволяет. Open source — наше все.
symbix
05.06.2015 00:50В pg 9.5 будет и ignore. :-) INSERT… ON CONFLICT IGNORE
rdruzyagin Автор
05.06.2015 16:30Я искренне разочарован. Уже жалею, что написал эту статью, узнаю много нового и печального :-)
Так действительно скоро дойдем до STRICT MODE = Off.
rdruzyagin Автор
04.06.2015 15:03+1Мой коммент уже «уехал», но я дополню потрясающую мысль. Я иногда в коде приложения с MySQL встречал конструкцию:
INSERT IGNORE INTO ... ON DUPLICATE KEY UPDATE
Гори оно все синим пламенем!Alexeyco
04.06.2015 15:53+1Ну а в Oracle есть конструкция MERGE… WHEN NOT MATCHED THEN… WHEN MATCHED THEN. Ну и что с того? Лично я привык по возможности использовать именно ее для массовой вставки данных. ИМЕННО!!! чтобы иметь возможность контроля над тем, что делать с дублями (пишешь иногда полотнище кода, а его нет-нет, да и выполнят два раза)… Можно ли утверждать, что Oracle — лютая кака, т.к. чтобы реализовать что-то, что в MySQL делается таким малым количеством кода, в Oracle надо описывать очень многословно? Нет! У них разные парадигмы, способы финансирования, модели монетизации, история — да вообще все разное.
Всё есть инструмент и всё есть возможность. Если кто-то использует стоматологический инструмент для удаления, пардон, геморроя, то это не инструмент плохой, это личностью врача стоит поинтересоваться более пристально.
Хотя, я так подумал. Инструмент иной раз тоже может склонять нас к неверным решениям. Истина где-то рядом.rdruzyagin Автор
04.06.2015 16:23Я с упомянутой Вами конструкцией из Oracle не знаком, поэтому прокомментировать не могу. Но вот приведенный мой пример совершенно «разрывает шаблон», поскольку в таком запросе совершенно не ясно, как себя поведет MySQL: сделает IGNORE или все-таки обновит запись согласно ON DUPLICATE KEY UPDATE. Как мне кажется, это вещи взаимоисключающие, и странно их видеть в одном и том же запросе.
Но так-то Вы конечно правы, стрелять по ногам позволяет любая база.
aeryaguzov
04.06.2015 14:47ON DUPLICATE KEY… UPDATE, или так называемый UPSERT (UPdate + inSERT), был все-таки признан нужной “фичей” в сообществе PostgreSQL. Его релиз назначен на 9.5 версию СУБД
Ну растет же постгрес :-)
Раньше мыскль рос до постгреса, а в последнее время наоборот, постгрес берет лучшее из мыскля.
В 9.1 например запилили group by по pk, в 9.5 теперь insert on duplicate, самое главное, strict mode не выключайте к 10-й версии :Drdruzyagin Автор
04.06.2015 15:06Вот с «групбаем» я как-то не припомню проблем в 9.0, но давно это было. Upsert — штука полезная, действительно. И приведенный в «посте» пример работает очень шустро. А вот STRICT MODE я таки сомневаюсь, что выключат, хотя чем черт не шутит. Все хорошее когда-то заканчивается)
FractalizeR
04.06.2015 15:10+3Раньше мыскль рос до постгреса, а в последнее время наоборот, постгрес берет лучшее из мыскля.
Можете пару примеров лучшего привести, что еще взято из MySQL в Postgres?
Мне кажется, что сама идея UPSERT для MySQL не инновационна. Он ведь был и в других базах данных. Скажем, в SQLite есть «ON CONFLICT». Просто MySQL самая популярная СУБД, в которой он есть.
FractalizeR
04.06.2015 15:03В PostgreSQL вам, скорей всего, придется симулировать такой подход к эксплуатации базы данных включением специального флага (SET AUTOCOMMIT).
Autocommit для пользовательских SQL запросов в PostgreSQL по умолчанию включен.
«embedded SQL programs», о которых говорится в мануале, — это С программы, ипользующие ecpg. В Postgres 9.4 выключить autocommit невозможно:
[2015-06-04 16:03:11] [0A000] ERROR: SET AUTOCOMMIT TO OFF is no longer supported
rdruzyagin Автор
04.06.2015 15:05Вы уверены? В документации сказано обратное: www.postgresql.org/docs/9.4/static/ecpg-sql-set-autocommit.html
FractalizeR
04.06.2015 15:13Я только что попробовал сделать SET AUTOCOMMIT = OFF на своем Postgres. Ошибку процитировал.
В документации, ссылку на которую вы привели, говорится об использовании autocommit в ecpg окружении. autocommit выключить нельзя начиная еще с версии 7.4
www.postgresql.org/message-id/20050703233108.GA40409@winnie.fuhr.org
rdruzyagin Автор
04.06.2015 15:15Вы абсолютно правы, извиняюсь за бессмысленный комментарий. Признаться, я крайне опечален. Давно приученная практика использовать транзакции привела к тому, что я это пропустил мимо. Тем не менее, это упрощает процесс миграции, так что хоть какая-то польза в этом все-таки есть. Спасибо за подсказку.
FractalizeR
04.06.2015 15:26Да, согласен. Не за что. Странно, что возможность отключить этот режим убрали. Мне кажется, это было достаточно удобно.
hydrobiont
04.06.2015 15:38Его убрали не просто так, а чтобы не вводить в заблуждение пользователей. Со стороны клиентского драйвера режим автокамита выставляется другим способом и сессионная переменная эффективно игнорируется, что может привести к казусам. Ну и вообще механизм содержит в себе сюрпризы, например при работе через pgbouncer
edwardspec
05.06.2015 01:45> В Postgres 9.4 выключить autocommit невозможно
Если точнее, команда «SET AUTOCOMMIT TO OFF» там прибита (скорее всего, как ненужная).
По-хорошему autocommit надо выключать началом транзакции.rdruzyagin Автор
05.06.2015 16:31Именно так. Мы уже как-то давно привыкли так делать, «обертки» для работы с БД даже ругаются у нас штатно в таких случаях на неоткрытую транзакцию.
kirillzorin
04.06.2015 15:58+2Предлагаю мини-тест (это всё для MySQL):
Какой результат будет после следующих команд?
create table table_1 (val int check (val > 5)); insert into table_1 (val) values (7);
ОтветПроверка молча проигнорирована, запись вставлена.rdruzyagin Автор
04.06.2015 16:24Отличные задачки для собеседований :-)
wAngel
04.06.2015 17:01+1Почему вы считаете детали реализации конкретной СУБД отличными задачками для собеседования?
rdruzyagin Автор
04.06.2015 17:30+2Это была ирония, я даже смайлик специально поставил.
Да и на самом деле, чем плохи эти задачи, если разыскивается кандидат, обладающий знаниями тонкостей реализации и в целом владеющий продуктом на уровне «advanced»? Я вот, например, ответы на первую и вторую сообразил, благодаря наличию некоторого опыта, а с третьей пришлось заглянуть под «спойлер».
В общем, не вижу в этом ничего плохого. Я регулярно выдаю программистам вариацию задачи на сотрудников без отделов как часть тестового задания (хотя, это все-таки банальная проверка на знание SQL). Если кандидат утверждает, что он хорошо знает PostgreSQL, я обязательно спрошу что-нибудь про внутреннее устройство.
marenkov
05.06.2015 21:29+1Это отличные задачки для собеседования, если интервьюер хочет аргументировано понизить уровень зарплаты ;)
coh
04.06.2015 21:46По меньше продуктового евангелизма, побольше конкретики.
Эта новая эпопея PG vs MySql выглядит очень странно, если не вспомнить про бизнес интересы российских софтверных компаний интеграторов/автоматизаторов. Ничего не имею против Олега Царева, Postgres Professional, 404, altLinux и прочего бизнеса, даже очень за! И болеею за вас душой! Приятно осознавать, что отечественные компании научились зарабатывать на свободном ПО. Но весьма странно, когда человек выходит на конференции PG и рассказывает про недостатки MySQL, тут гордость и патреотизм уходят…
Ваши аргументы вида mysql обрезает строки и вообще позволяет творить “мракобесие” (включение strict режима снимает эту напряженность), тоже вызывают недоумение, как и бенчмарки на дефолтных настройках у других авторов. Да создатели задумали “дружелюбное” поведение, кто-то скажет добро, кто-то зло. Однако, это решается настройками.
И статья вроде была про миграции, а опять MySql во всем виноват…
Никто же не говорит, что С/C++ “невообразимое варварство”, которое ведет к утечкам памяти.
MySQL создана для веба. Будь то мелкие сайты, которые используют 10% ее возможностей или большие грамотно спроектированных ресурсы, отмасштабированные горизонтально, которые не редко используют только 10% возможностей SQL и СУБД. Именно там, где рядом стоят сервера очередей и nosql, где каждая система выполняют ту задачу, для которой она разработана.
Зачем поливать грязью конкурента, если можно сделать элегантный финт и сказать, что PG лучше подойдет для монсторуозной монолитной системы которая не была расчитана на большой конкурентный поток данных, для увеличения производительности 1С, для удешевления прикладных систем и банковского ПО, для сложного и объемного документооборота, для хранения большого объема неразделяемых данных, чего угодно, но у Postgre web’е нет совершенно никаких решающих преимуществ, а для среднестатистических сайтов вообще без разницы какая СУБД ( главное чтобы разработчки были с ней знакомы).
Самый простой способ — закидать камнями. Пара success story про особенности автоматизации сложных бизнес процессов даст намного больше чем 10ток статей и выступлений из разряда mysql “злосчастное подобие СУБД”.
Расскажите кому и в каком случае стоит задуматься о миграции. Мне и самому очень интересна pg, российские дороботки в виде разграничания прав доступа на уровне строки, все что угодно, больше конкретики, меньше пузомерок.
Простой пример рассказа “Ваш старенький форум поддержки уже не справляется с наплывом посетителей, данные жалко, MySQL трещит по швам, дорабатывать сайт некому? «Тру» репликация PG вас спасет. Мы расскажем Вам как быстрее мигрировать”
Извините, если слишком резко, накипело.Blumfontein
04.06.2015 22:33+2>> у Postgre web’е нет совершенно никаких решающих преимуществ
Ну у MySQL нет не то что решающих, а вообще никаких преимуществ. Все, что умеет MySQL, умеет и Postgre. Зато MySQL не умеет вагон того, что есть в Postgre. В пользу MySQL можно привести только чуть более мощную инфраструктуру (база знаний, вопросов, БД клиенты такие как Workbench и PHPmyAdmin).
>> Простой пример рассказа “Ваш старенький форум поддержки уже не справляется с наплывом посетителей, данные жалко, MySQL трещит по швам, дорабатывать сайт некому? «Тру» репликация PG вас спасет. Мы расскажем Вам как быстрее мигрировать”
Просто вставьте новую колонку в таблицу с 100кк записей.AlexWinner
04.06.2015 22:57PhpPgAdmin же есть:) и PgAdmin III
Blumfontein
05.06.2015 08:57phppgadmin, к сожалению, даже в подметки myAdmin-у не годится. А PgAdmin III больно древний, у меня какие-то проблемы с dll-ками на win8 есть, да и хочется «IDE», а не «Notepad++».
rdruzyagin Автор
04.06.2015 23:01> Просто вставьте новую колонку в таблицу с 100кк записей.
«Кто в армии служил, тот в цирке не смеется»
Самый верный признак роста базы и проекта, это когда приходит к тебе программист, и спрашивает, как добавить в MySQL таблицу колонку, не уводя сервис в «оффлайн» :-)
coh
04.06.2015 23:02-1Ну вот опять одно и то же, никому ненужные холивары.
Подавляющее большинство сайтов не имеют 100кк записей.
А если имеют вполне доросли до TokuDb storage engine, даже если нет, то
pt-online-schema-change или habrahabr.ru/post/121129 (2011 год!!!)
Я говорю о том, что вроде как в мире OpenSource не принято поливать грязью.Blumfontein
05.06.2015 09:18Да никто не поливает грязью MySQL, это просто констатация факта, что MySQL морально устарел. Ну никаких аргументов за MySQL нет, кроме легаси. Ладно бы тут агитировали переход на MongoDB, это точно отдельный холиварный вопрос. Но PostgerSQL — это более функциональный товарищ MySQL. Нет ничего проще, чем начать новый проект на PostgreSQL. Синтаксис похож на 90%. А если вы используете хороший ORM, то разницы вообще не заметите. Зато получите из коробки кучу плюшек типа hstore, json, более информативные тексты ошибок, ну и вообще более оптимизированный движок, и в будущем гораздо меньше боли по масштабируемости.
sergeyfast
07.06.2015 12:18>у Postgre web’е нет совершенно никаких решающих преимуществ
Я вам расскажу пару преимуществ Postgres в вебе – массивы и hstore c возможностью поиска по индексам. Упрощается структура приложения и базы без потери производительности.
А тут в 9.4 уже jsonb подвезли c CREATE INDEX USING VODKA :)symbix
07.06.2015 14:05Водку не подвезли. Я даже репозитория не видел еще. Похоже, это пока просто концепт
rdruzyagin Автор
04.06.2015 22:59+4Приветствую!
Спасибо за комментарий, очень жаль, что он с отрицательным рейтингом.
Как я уже пояснял коллегам в «треде», это был скорее здоровый сарказм с целью обратить внимание на «грехи» MySQL, а ни в коем случае на поливание «грязью». Я сам спокойно отношусь к MySQL и работаю с ним, когда приходится. Хотя конечно, чего уж там греха таить, у нас в 404 Group, PostgreSQL — основная инженерная специализация, мы «посгрес» очень любим и даже конференцию про него делаем. Так что определенная доля евангелизма, безусловно, имеется.
В целом, все это очень субъективно, и я сужу по своей «песочнице». В компании я многие проекты веду в роли куратора и наставника, а не непосредственного «техлида». В таких случаях, если ребята хотят попробовать применить MongoDB для хранения данных или ZeroMQ для реализации очередей, я ни в коем случае не запрещаю. Я вообще за развитие и всяческую «движуху» :)
И Вы все-таки не правы, позволю себе не согласиться. Статья именно про конкретный «кейс» миграции с MySQL на PostgreSQL, от тематики я не уходил. У меня «за плечами» около 10 крупных миграций с MySQL, в том числе очень нагруженных проектов. Три года назад я мигрировал базу, где полтерабайта транзакционных логов в сутки набегало одним лишь биллингом. Сейчас вот с коллегами в процессе переключения на PG кластера MySQL, который суточно принимает нагрузку около миллиарда записей. Описанные проблемы MySQL — это то, с чем регулярно приходиться бороться при подготовке к миграции практически любой сделанной с его помощью базы данных.
Всех этих проблем можно избежать, если правильно «готовить» MySQL. Я несколько лет назад стартовал крупный проект с командой, и мы еще тогда применяли MySQL и вознамерились не повторить ни единой ошибки, из числа допущенных коллегами. У нас это получалось, но было крайне тяжко и мучительно. Взять ту же репликацию. В MySQL она принципиально так устроена, чтобы создавать проблемы. Нужно весь код внимательно вычитывать на предмет репликационно-небезопасных функций, коих там превеликое множество. Очень нервная работа. В PostgreSQL репликация работает и про нее не помнишь.
Вы абсолютно правы в том, что PostgreSQL не обладает принципиальными преимуществами, это просто хорошая и надежная база. В моей практике так уж сложилось, что PostgreSQL у нас just works, а с MySQL — вечные трудности. И это далеко не от недостатка компетенций. У нас крутые админы и DBA с большим опытом. Поэтому я привожу в пример те проблемы MySQL, которые знаю. На крупных проектах, которыми я обычно «рулю», они особенно болезненны. На мелких — просто неприятны, но не смертельны.
Кто-то сильно не любит PostgreSQL и имеет на это право. Я регулярно провожу PostgreSQL-секцию на Питерском слете IT-сообществ Piter United (кстати, если Вы из Питера, приходите в эту субботу, буду рад пообщаться и всех коллег из «треда» приглашаю). На последнем таком слете коллега рассказывал о том, как им надоел PostgreSQL с безумной логикой, написанной с помощью хранимых процедур, и они «мигрились» на Джаву. С удовольствием прослушал доклад и пообщался со спикером.
Я буду рад написать еще одну или две статьи про конкретные примеры миграции, как только время позволит. Просто сильно опасаюсь, что это будет воспринято как негатив в сторону MySQL, т.к. мой опыт связан, преимущественно, с ним. Но я постараюсь что-нибудь придумать :)
Спасибо за конструктивную критику.
Vidog
05.06.2015 14:46А был ли опыт использования с foreign data wrapper?
rdruzyagin Автор
05.06.2015 16:34Используем PL/Proxy в некоторых проектах, только в таком виде. Какой-то конкретный вопрос интересует или в целом впечатления?
Paulus
06.06.2015 02:55MySQL — это одна немногих СУБД, поддерживающих unsigned int типы. Знаковые и беззнаковые поля будут отсортированы MySQL и в PostgreSQL по-разному. Как рашался это вопрос при миграции? Есть ли хорошее решение для сортировки беззнаковых целых в PostgreSQL и в SQL вообще?
rdruzyagin Автор
06.06.2015 09:27Честно говоря, не совсем понял суть проблемы. Поясните, пожалуйста, на примере. В PostgreSQL нет такого понятия как беззнаковый целый тип, также как и в самом стандарте, если мне не изменяет память. Эмулировать «беззнаковость» можно накладываемым на таблицу ограничением (CHECK). У нас такой потребности не возникало. Значения мигрировались в стандартные целые типы PG безо всяких проблем, диапазонов integer или bigint всегда хватало, чтобы вместить все данные. С сортировками при этом проблем вообще никаких не будет.
with values_ as (select * from generate_series(-10, 10) order by random()) select * from values_ order by 1 asc; generate_series ----------------- -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7 8 9 10
Paulus
07.06.2015 23:50> Честно говоря, не совсем понял суть проблемы. Поясните, пожалуйста, на примере. В PostgreSQL нет такого понятия как беззнаковый целый тип, также как и в самом стандарте, если мне не изменяет память.
Верно, в SQL такого пока нет, в MySQL есть
> Эмулировать «беззнаковость» можно накладываемым на таблицу ограничением (CHECK).
Нельзя, остануться только неотрицательные целые
> У нас такой потребности не возникало. Значения мигрировались в стандартные целые типы PG безо всяких проблем, диапазонов integer или bigint всегда хватало, чтобы вместить все данные.
А если бы использовались все 64 бита?
> С сортировками при этом проблем вообще никаких не будет.
Будут, если представить себе двоичное представление. Некий целый 8-битой тип, назовем его tinyint, со зачениями unsigned tinyint 0..255 (0x00..0xFF) сортирутся как 0..255.
Со знаком же он отсортируется как -128..-1..0..127 (0x80..0xFF, 0x00, 0x7F) Слегка не то
С bigint случится ровно то же, signed (есть в Postgre) и unsigned (есть в mySQL) внезапно отсортируются в 2 разных группы
svetasmirnova
08.06.2015 03:08+1Свой собственный абзац заслуживает движок таблиц под названием Memory. Особенность этого движка заключается в том, что он не персистирует данные на диск, а хранит целиком в памяти. Количество памяти фиксировано и задается настройкой, которую менять можно только путем перезапуска сервера БД. С эксплуатацией этого движка связано два нюанса:
таблица, вышедшая за предел допустимого размера, моментально “свопится” на диск, карета превращается в тыкву, иногда вместе с базой данных (если движок Memory использовался для оптимизации производительности);
С internal memory tables не путаете:
MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal temporary tables are:
If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.4, “How MySQL Uses Internal Temporary Tables”.
User-created MEMORY tables are never converted to disk tables.
(http://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html)
«The table is full» ошибка в пользовательской таблице будет при переполнении.
ulfurinn
23.06.2015 22:55+2ON DUPLICATE KEY… UPDATE, или так называемый UPSERT (UPdate + inSERT), был все-таки признан нужной “фичей” в сообществе PostgreSQL.
Не совсем так. Никто не отрицал полезность апсерта, просто его очень сложно реализовать корректно (ACID и все такое прочее), а иначе база данных – не база данных, аmysqlчерт знает что.
restyler
Вся статья пронизана презрением к примитивной MySQL, причем в самых лучших традициях элитных программистов — если в PosgreSQL фича отсутствует, а в MySQL она есть — то значит, фича — ненужная и вредная по определению, а если наоборот, то это значит, что фича убер-важная, а MySQL не доработан :) поменьше желчи, товарищи, и люди к вам потянутся!
defuz
А мне кажется, автору просто накипело.
EndUser
Я не припомню на Хабре статей по миграции с PostgreSQL на MySQL.
marenkov
Тут дело не только в нецелесообразности такого перехода, а еще и в том, что за такую статью можно и по карме получить.
AxisPod
Ага, положил в MySQL, а потом не понимаешь, а почему ничерта не работает, после нескольких часов поисков выражаешь «большое спасибо» MySQL. Уж лучше получить ошибку, чем неявное поведение.
rdruzyagin Автор
Ни в коем случае не старался вас обидеть. Это не презрение, а здоровый сарказм, призванный обратить внимание на типичные проблемы MySQL в «продакшене». Я их наблюдаю уже 9 год своей профессиональной деятельности. И про PostgreSQL такую же статью могу написать, в ней тоже полно нюансов и странных вещей, возникших по историческим причинам.
Коллега defuz абсолютно прав, накипело. К самому MySQL у меня нет претензий, я умею поддерживать и поддерживаю рабочие проекты на MySQL, если текущие условия бизнеса или самой «технички» не позволяют использовать привычный для нас PostgreSQL.
MySQL — замечательный продукт, на котором мы стартовали огромное количество успешных и прибыльных проектов. Просто, к сожалению, в нем слишком много нюансов даже в достаточно тривиальных вещах, которые на практике выливаются в беды и печали.
FSA
Я в своё время тоже выбрал MySQL. Сейчас стойкое желание перейти на PostgreSQL т.к. она больше подходит для тех типов данных с которыми мне приходится работать. Я как раз столкнулся с теми вещами о которых идёт речь в статье. У меня было уже две попытки перехода, но, пока, ни одна из них не увенчалось успеха. Всё из-за старого «говнокода». Волосы на голове шевелятся от ужаса, чего я такого несколько лет умудрился написать :-D Благо проект создал чисто для своего интереса.