Facebook: embedding extra information in to the MySQL replication stream

[Patrick Galbraith]

Brian Aker wrote:

> Hi!
>
> On Apr 2, 2008, at 12:35 PM, Gabriel Laden wrote:
>
>> Quote:
>> Fortunately MySQL is open source software, meaning we can actually 
>> change the way it works by modifying the code. We did just that— 
>> embedding extra information in to the MySQL replication stream that 
>> allows us to properly update memcached in Virginia. This ensures that 
>> the cache and the database are always in sync.
>>
Hi there!

I have written Memcached Functions for MySQL for things like this. You 
can have a setup where certain data you want to automatically be set in 
memcached can be done with triggers that call these functions, depending 
on what you have to store.

I have been meaning to write a users guide showing how you can leverage 
these functions.

 From my livejournal blog at http://capttofu.livejournal.com:

CREATE TABLE `jsoncache` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`memcache_key` varchar(32) DEFAULT NULL,
`json` mediumtext,
PRIMARY KEY (`id`)
)

mysql> delimiter | mysql> CREATE TRIGGER `jsoncache_trigger` BEFORE 
INSERT ON `jsoncache` FOR EACH ROW begin set @id= concat('jsoncache:', 
NEW.id); set @tt= memc_set(@id, NEW.json); set NEW.memcache_key = @id; end |
Query OK, 0 rows affected (0.05 sec)

mysql>delimiter ;

mysql> insert into jsoncache (json) values ('{{json stuff}}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from jsoncache;

+----+--------------+----------------+
| id | memcache_key | json |
+----+--------------+----------------+
| 1 | jsoncache:1 | {{json stuff}} |
+----+--------------+----------------+
1 row in set (0.00 sec)

mysql> select memc_get('jsoncache:1');

+-------------------------+
| memc_get('jsoncache:1') |
+-------------------------+
| {{json stuff}} |
+-------------------------+
1 row in set (0.00 sec)

Hope this helps!

--Patrick

>>
>> Was this code change released by Facebook ? We have exactly such a 
>> need for this type of setup.
>
>
> No, it was not. You can emulate this behavior by using the MySQL UDF 
> in your queries, which is a better route to go for supportability.
> http://tangent.org/586/Memcached_Functions_for_MySQL.html
>
> Cheers,
> -Brian
>
> -- 
> _______________________________________________________
> Brian "Krow" Aker, brian at tangent.org
> Seattle, Washington
> http://krow.net/ <-- Me
> http://tangent.org/ <-- Software
> _______________________________________________________
> You can't grep a dead tree.
>
>
>


-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad