Блог Андрея Смирнова

Иногда необходимо забирать данные из БД MySQL в режиме реального времени во внешнюю систему, которая никак не связана с MySQL. Существует множество возможных решений, например, можно реализовать «слейва» MySQL, который бы хранил полученные данные во внешней системе.

Одно из возможных решений — сделать «выгрузку» данных из MySQL с помощью UDF (User Defined Functions) и триггеров. Для этого необходимо поставить слейв MySQL, на котором уже повесить на интересующие таблицы триггеры, которые с помощью UDF будут выгружать поток изменений таблиц во внешнюю систему. Слейв необходим, т.к. если триггеры поставить на мастере, то в случае отката транзакции действия, уже сделанные триггерами, откатить не получится, а на слейв попадают только зафиксированные транзакции. Второе,чтобы триггеры работали на слейве, тип репликации должен быть выставлен на STATEMENT-based.

Порывшись в одном интересном архиве UDF для MySQL я нашел несколько функций, которые мне подошли:

• преобразование строки MySQL в json;
• интерфейс с memcached.

В результате получился следующий план действий: данные модифицируются на мастере, реплицируются на слейв с помощью STATEMENT-репликации. В процессе репликации на слейве запускаются триггеры, формируют с помощью UDF пакет обновлений в JSON, и передают его во внешнюю очередь (memcacheq) по memcached-протоколу. Конечно, это не единственный возможный способ, но все UDF уже были почти готовы. После доделывания напильником UDF получился вполне стабильно работающий вариант.

Триггеры выглядят примерно следующим образом:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE FUNCTION kick_photos (row_id INT) RETURNS INT 
BEGIN 
SELECT memc_set('queue_db', (json_object('insert' AS action, 'photos' AS table_name, photos.id AS id, json_members('data', json_object(photos.user_id AS `user_id`,photos.width AS `width`,photos.created_at AS `created_at`,photos.filename AS `filename`,photos.parent_id AS `parent_id`,photos.content_type AS `content_type`,photos.height AS `height`,photos.thumbnail AS `thumbnail`,photos.size AS `size`))))) INTO @dummy FROM photos WHERE id = row_id; 
RETURN @dummy; 
END
 
CREATE TRIGGER photos_INSERT AFTER INSERT ON photos FOR EACH ROW 
SET @dummy = memc_set('queue_db', (json_object('insert' AS action, 'photos' AS table_name, NEW.id AS id, json_members('data', json_object(NEW.user_id AS `user_id`,NEW.parent_id AS `parent_id`,NEW.created_at AS `created_at`,NEW.filename AS `filename`,NEW.width AS `width`,NEW.content_type AS `content_type`,NEW.height AS `height`,NEW.thumbnail AS `thumbnail`,NEW.size AS `size`)))));
 
CREATE TRIGGER photos_DELETE BEFORE DELETE ON photos FOR EACH ROW 
SET @dummy = memc_set('queue_db', (json_object('delete' AS action, 'photos' AS table_name, OLD.id AS id, json_members('data', json_object(OLD.user_id AS `user_id`,OLD.parent_id AS `parent_id`,OLD.created_at AS `created_at`,OLD.filename AS `filename`,OLD.width AS `width`,OLD.content_type AS `content_type`,OLD.height AS `height`,OLD.thumbnail AS `thumbnail`,OLD.size AS `size`)))));
 
CREATE TRIGGER photos_UPDATE AFTER UPDATE ON photos FOR EACH ROW 
BEGIN 
IF json_object(OLD.user_id AS `user_id`,OLD.parent_id AS `parent_id`,OLD.created_at AS `created_at`,OLD.filename AS `filename`,OLD.width AS `width`,OLD.content_type AS `content_type`,OLD.height AS `height`,OLD.thumbnail AS `thumbnail`,OLD.size AS `size`) <> json_object(NEW.user_id AS `user_id`,NEW.parent_id AS `parent_id`,NEW.created_at AS `created_at`,NEW.filename AS `filename`,NEW.width AS `width`,NEW.content_type AS `content_type`,NEW.height AS `height`,NEW.thumbnail AS `thumbnail`,NEW.size AS `size`) THEN 
  SET @dummy = memc_set('queue_db', (json_object('update' AS action, 'photos' AS table_name, OLD.id AS id, json_members('new', json_object(NEW.user_id AS `user_id`,NEW.parent_id AS `parent_id`,NEW.created_at AS `created_at`,NEW.filename AS `filename`,NEW.width AS `width`,NEW.content_type AS `content_type`,NEW.height AS `height`,NEW.thumbnail AS `thumbnail`,NEW.size AS `size`)), json_members('old', json_object(OLD.user_id AS `user_id`,OLD.parent_id AS `parent_id`,OLD.created_at AS `created_at`,OLD.filename AS `filename`,OLD.width AS `width`,OLD.content_type AS `content_type`,OLD.height AS `height`,OLD.thumbnail AS `thumbnail`,OLD.size AS `size`))))); 
END IF; 
END;

Комментарии:

• функция kick_photos позволяет скопировать строчку таблицы в очередь как пакет обновления типа «вставка», может использоваться для начального наполнения внешней системы;
• триггеры на удаление и вставку просто формируют соответствующие пакеты;
• триггер на обновление проверяет, действительно ли в пакете произошли изменения (например, мы можем использовать не все поля в пакете);
• необходимо учесть, что работе FOREIGN KEY CONSTRAINT триггеры не вызываются (очередной прикол MySQL), т.е., например, при если при выполнении запроса на удаление из таблицы A будут по FOREIGN KEY удалятся записи из таблицы B, то в триггере на удаление из A необходимо отработать этот случай, т.к. триггеры на таблице B не будут вызваны.

Код UDF доступен на github, это — «подпиленный» код из репозитория UDF или собственные разработки:

• превращение строки в json — были грязно исправлены проблемы с buffer overrun;
• запись в memcacheq — при запуске сервера будет настроена на запись в localhost:22201, плюс исправления для многопоточного режима и работы из нити workerа репликации;
• timestamp с миллисекундной точностью — полезно для проставления временных меток и анализа производительности репликации.

Описанная особенность MySQL попалась мне на глаза слишком поздно, пишу, чтобы кто-то не напоролся на те же грабли. Начнем с начала. Итак, необходимо было отслеживать изменения MySQL-базы данных и складывать эти изменения в очередь (не в БД) для дальнейшей обработки внешней системой. Для отслеживания изменений подходят триггеры, но они активируются в процессе выполнения запросов транзакции и в случае последующего «rollback» не будут откатываться (что совершенно нормально для триггеров, влияющих только на состояние БД, т.к. состояние БД будет корректно откатываться). Поэтому необходимо выполнять триггеры только для успешных транзакций: проще всего это достигнуть с помощью репликации — на слейв передаются только запросы зафиксированных транзакций. Таким образом, мастер-БД не содержит триггеров, после репликации данные попадают на слейв, таблицы на котором обвешаны триггерами, те активируются и данные попадают в очередь. Казалось бы, все замечательно?

Continue reading…

Тема этого поста была навеяна небольшим спором, разразившимся в ЖЖ. Эта битва будет вечной, как и война добра со злом, светлого и темного, FreeBSD и Linux, и т.п. Можно ломать копья, кричать «кто круче», но спор ни к чему не приведет. Можно делать синтетические тесты, пытаясь определить, кто же быстрее, но один тест скажет, что MySQL быстрее на вставках, а другой — что PostgreSQL лучше масштабируется на многоядерных архитектурах. Найдутся рассказы о том, как всё стало классно, когда мы с MySQL перешли на PostgreSQL, найдутся и обратные истории.

Возвращаясь к теме «спора» с Горным (спора в кавычках, т.к. как такого спора не хотелось, да и не получилось): я не хочу никому сказать, что PostgreSQL — круче или что MySQL — полный отстой. Согласно опыту gornal, проект на PostgreSQL сделать нельзя и поэтому он ужасен, а аргументов вобщем-то других нет, я привел некоторые аргументы в чем PostgreSQL может быть лучше. Но не в этом дело. Совсем не в этом.

Господа, на PostgreSQL можно делать успешные проекты. Это классная, современная СУБД, она не «тормозит», не бойтесь её. Она действительно работает, легко администрируется, устанавливается и т.п. К ней есть куча интереснейших расширений. Если Вы изучали SQL по мануалу MySQL, она Вам ничего не даст, но если Вы читали, например, Дейта или слушали курс по реляционной алгебре и о реляционных СУБД — в PostgreSQL Вы сможете воплотить БД своей мечты и оптимизировать её столько, сколько Вам захочется. Не бойтесь! Попробуйте, может, Вам понравится. Не понравится — есть MySQL, Informix, Oracle, DB2, Firebird и т.п. Но бояться не надо, она не тормозит, честное слово

Всегда будут люди, которым нравятся какие-то продукты, например, ораклоид с пеной у рта будет доказывать, что кроме Oracle нет нормальных СУБД. Gornal скажет, что только на MySQL можно сделать успешный проект. Я люблю PostgreSQL и буду пользоваться им, покуда он будет подходить для тех задач, которые я решаю.

Не верьте тем, кто говорит, что PostgreSQL — отстой, попробуйте сами. Найдите для себя идеальную СУБД, попробуйте её возможности. Я использую PostgreSQL уже около четырех лет, но до сих пор открываю для себя что-то новое, есть специалисты по PostgreSQL, они знают огромное количество еще более интересного.

Напоследок: есть успешные проекты на PostgreSQL в мире веба и около него. Это Skype, Мой Круг и другие. И в сердце Smotri.Com как пламенный мотор трудится PostgreSQL, вся БД обслуживается по сути пятью серверами, один мастер и четыре слейва (Slony). Это самая простая на свете конструкция, её практически не оптимизировали с точки зрения специфики PostgreSQL. Я оцениваю возможности оптимизации в 2-3 раза без изменения логики приложения на сегодня. Дальнейшее возможно с изменением логики (например, шардинг или отгрузка части данных в MemcacheDB или что-то подобное).

Напоследок, российская специфика PostgreSQL:

• PostgresMen
• Фёдор Сигаев и Олег Бартунов — активные разработчики PostgreSQL
• Николай Самохвалов — один из тех, кто сделал Мой Круг, а сегодня активно продвигает PostgreSQL в России
• список можно было бы продолжить…