Best practices

[Jamie McCarthy]

jthijssen at noxlogic.nl (Joshua Thijssen) writes:

> Let's say we want to restore products from a previous backup.
> This means new articles would be removed since they do not exist
> in the backup.

> If we do a very quick deletion (say the sql-query "DELETE FROM
> ARTICLES WHERE category_id=5")

memcached isn't designed to work well with updates or deletes that
are be applied using database logic, i.e. that are applied to the
underlying data store according to some criteria besides the primary
key.

There are ways around it but none of them are very satisfying.  For
these data objects a better solution might be database replication.

For the rare/exceptional events like restoring (part of) a database
from backup, the solution I'd suggest is restarting all your
memcached servers!

For what sounds like a more common event like a DELETE based on a
non-primary key, there isn't really a good solution.  What I'd
probably do is, wrapped in a transaction, pull the list of primary
keys from the DB just before I do the DELETE and use that list to
delete the memcached entries.

        START TRANSACTION;
        SELECT primary_id FROM articles WHERE category_id=5;
    for $id (@primary_ids) { $memcached->delete($id, 3) } or whatever
        DELETE primary_id FROM articles WHERE category_id=5;
        COMMIT;

Your housekeeping-table sounds like it's duplicating information,
and I'm not sure for what purpose.  In the cases like the above,
you're going to be updating the database anyway, so doing the SELECT
is not going to add significantly to the work the DB has to do.

> when deleting articles from the database, we don't need to go
> over them 1 by 1, deleting the key from the cache etc etc.

I have trouble imagining a data access pattern that would make the
solution you describe more efficient than just clearing the cache
once the data is no longer valid.  I'm not saying it's not possible,
but it'd have to be pretty unusual.

And again, if you frequently update/delete data based on something
other than the primary key, maybe memcached is not the right
solution for improving that aspect of your performance.
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg