← Back to team overview

drizzle-discuss team mailing list archive

Re: Drizzle Replication Question

 

cc'ing the list since it may be interesting to others...

Padraig O'Sullivan wrote:
Hey Jay,

Here's a quick question on something I want to do that you might be
able to help me with. For fun and because I think its a really
interesting project, I'm writing a memcached applier which simply
pushes update and delete events to a memcached server (for now).
Basically, it enables someone to keep a pro-active cache easily.

So the idea is that a user should be able to specify what the format
of the memcached key to use is and also what data should be stored as
the value. One common method that is used to store a value in
memcached is to store the contents of a whole row as a value. So here
is my question...

Say we have a Command::UPDATE message. I know how to obtain the after
value for each field that is modified in the update statement.
However, my question is this: how do I obtain the value of every field
in the updated row? Basically, if I have an UPDATE statement like
this:

UPDATE x SET y = t;

I want to obtain the following:

SELECT * FROM x WHERE y = t;

You can do this, but not in the Command message, since the Command::UPDATE message is a fixed message containing information about the changed fields in the record only, and not information about the entire record...

Without modifying the TransactionServices component in the kernel, you will want your applier plugin to throw SELECT statements such as the one you construct above into a queue which then gets executed against the server in order to retrieve the record in full. Unfortunately, this will eat up quite a bit of processing power I fear...and might be too costly to run on the master itself...it would be better run on a slave machine but for prototyping you could of course run it on the master.

For instance, assuming a MemcacheApplier plugin, declared something like this:

class MemcacheApplier :public drizzled::plugin::Applier
{
private:
  /** Contains SELECT expressions for updated records... */
  std::queue<std::string> updated_records;
  ...
}

Basically, in the apply() method, you'd do something like this:

(note this signature may be slightly different from the pointer-to-Command in the current version on LP...)

using namespace std;
using namespace drizzled::message;

void MemcacheApplier::apply(const Command &to_apply)
{
  switch (to_apply.type())
  {
    case Command::UPDATE:
    {
      string updated_record_select("SELECT * FROM `");
      updated_record_select.append(to_apply.schema());
      updated_record_select.append("`.`");
      updated_record_select.append(to_apply.table());
      updated_record_select.append("` WHERE ");

      /* Build our WHERE statement with Primary Key fields */
      int32_t num_where_fields= to_apply.where_field_size();
      int32_t x;
      for (x= 0;x < num_where_fields; x++)
      {
        if (x != 0)
          updated_record_select.append(" AND ");
          /* Always AND condition with a multi-column PK */

         const Table::Field f= record.where_field(x);

         /* Always equality conditions on a PK */
         updated_record_select(.push_back('`');
         updated_record_select.append(f.name());
         updated_record_select.append("` = \"");
         updated_record_select.append(to_apply.where_value(x));
         updated_record_select.push_back('"');
      }
      /* Add to our queue... */
      updated_records.push(updated_records_select);
    }

  // other stuff, for instance you could put
  // Command::INSERT into a separate queue...
  }
}

Once you've got the stuff in your queue, you can do whatever you want with it, of course, for instance have a separate thread use the Listen and ListenHandler classes from Eric's new protocol work to call the master server with the pre-built SELECT statements and place the results into memcached.

Is there an easy way to do this? I figure I could modify the message
proto for this and just have the after value of every field in the
affected table stored there but this could make the message very large
and its not an ideal solution.

Again, I don't advise changing the Command or Transaction message proto format unless it's absolutely necessary, and in this case, I don't think it is...

Cheers!

Jay

The applier I'm working on (which is very very very basic at the
moment) is available at:

lp:~posulliv/drizzle/memcached_applier

in case you want to have a look at it. I don't have the ability for a
user to specify a key-value format yet but its something I'm hoping to
add pretty soon.

-Padraig




Follow ups