Що потрібно знати при міграції з MySQL на PostgreSQL?

У продовження статті про теорію і практику міграції сховищ даних PostgreSQL, ми поговоримо про проблеми, з якими ви можете зіштовхнутися при переїзді з поширеною СУБД MySQL. Щоб не втомлювати всіх зайвої риторикою, сьогоднішня розповідь буде більш тезовий та проблемно-орієнтований.

Всі нижепредставленное є переліком типових помилок дизайну та експлуатації 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,


Updates FROM 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 доводиться застосовувати неприємний обхідний шлях, щоб отримувати значення epoch з поправкою на часовий пояс, у вигляді SQL-конструкції EXTRACT(the 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, що, наприклад, буде робити Олег Царьов на майбутньому Pgday. У кращому випадку, проблема вирішується використанням механізму hot-standby. У гіршому, доведеться освоювати нетривіальні підтримки та експлуатації інструменти типу Slony!

Я сподіваюся, що ці приклади наочно ілюструють, чому завдання міграції є процесом складним і практично нерозв'язних за допомогою існуючих інструментів. Гарантовано працюють лише вдумливий, ручний підхід і стара військова мудрість «know your data».

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.