Facebook: embedding extra information in to the MySQL replication stream

[Paul Lindner]

IIRC you can also use the blackhole storage engine and not worry about
table updates..

So for example you can set up a blackhhole table

CREATE TABLE invalidate(key varchar(100) ) ENGINE = BLACKHOLE;

and replicate it and add triggers in all the right places..

On Wed, Apr 02, 2008 at 03:56:13PM -0400, Patrick Galbraith wrote:
> 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!
>
>
>>>
>>> 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
>>
>>
>>
>>
>>
>
>
>
>

-- 
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner at inuus.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://lists.danga.com/pipermail/memcached/attachments/20080402/fd266471/attachment.pgp