List:Internals« Previous MessageNext Message »
From:MARK CALLAGHAN Date:September 25 2010 8:03pm
Subject:Re: Per-db binlogging
View as plain text  
I was skeptical of triggers, but we were able to use them for online
schema change -- see
http://www.facebook.com/note.php?note_id=430801045932

It isn't easy to change replication code. I think you might get your
work done faster with triggers.

On Sat, Sep 25, 2010 at 12:58 PM, Weldon Whipple <weldon@stripped> wrote:
> Mark,
>
> Thanks for the suggestion on triggers. (Can you point me to specific
> URLs/books/articles with example code?)
>
>
> I've actually been making fairly good progress with the original plan
> for per-db binlogging, with each database having a separate output
> file. Here is what I've done so far. Feel free to point out flaws in
> my plan:
>
> After learning about 2PC (Two-phase commit protocol), I realized that
> I couldn't write to a general binlog and a db-per-file binlog
> "simultaneously" (i.e. to both kinds of logs for a given
> modification). I've been working on a solution that will do one or the
> other type of binlog, but not both.
>
> Step 1:
> This week I started working on version 5.5 by changing the declaration
>
> MYSQL_BIN_LOG mysql_bin_log
>
> to
>
> MYSQL_BIN_LOG *mysql_bin_log
>
> At the place in mysqld.cc where the first call to mysql_bin_log was
> made (on startup), I first create ("new") a MYSQL_BIN_LOG from the
> heap.
>
> I went through all the code and changed all occurrences of:
>
> mysql_bin_log.{something}
>
> to
>
> mysql_bin_log->[something]
>
> (I.e., I changed dots to arrows). In a few places where the &
> (address-of) operator was used in function/method calls, I removed the
> &.
>
> Then I rebuilt the code and ran all the test cases. All the test cases
> passed (!!!!)
>
> Step 2:
> Using gdb, I stepped through the code and identified all the calls
> that initialize MYSQL_BIN_LOG on startup.
>
> Step 3:
> I'm now implementing a function in sql_parse.cc that responds to the
> START DATABASE BINLOG FOR <db> command by creating an instance of a
> MYSQL_BIN_LOG-derived class, setting the binlog file and index file to
> one based on the database name, then place a pointer to that
> MYSQL_BIN_LOG-derived class in a HASH managed by a singleton class
> DB_BINLOG_MGR.
>
> So far things are going pretty well.
>
> I'm still doing exploratory development, but my plan this coming week is to:
>
> Step 4:
> Add to the THD class a (private) pointer to MYSQL_BIN_LOG (which can
> point to either a MYSQL_BIN_LOG or its derived DB_BINLOG instance), as
> well as an accessor and a mutator method. (The DB_BINLOG instance adds
> almost nothing to the MYSQL_BIN_LOG class that it extends. I might
> eventually be able to abandon DB_BINLOG and just use MYSQL_BIN_LOG for
> all cases?)
>
> Step 5:
> On initialization (in mysqld.cc), if "regular" binlogging is in effect
> (based on options in my.cnf or given on the command-line), I'll have
> THD reference the global MYSQL_BIN_LOG instance.
>
> Step 6:
> When client requests are received (at the time when a THD is assigned
> to that request), if per-file-db binlogging is in effect, code will
> ask the DB_BINLOG_MGR for an instance of DB_BINLOG for the DB that is
> being used. THD will be made to point to that instance.
>
> Step 7:
> I'll have to change all functions/methods that reference
> MYSQL_BIN_LOG, so that they get the MYSQL_BIN_LOG pointer from the THD
> instance that is passed as the first argument. Then the MYSQL_BIN_LOG
> instance (either the singleton MYSQL_BIN_LOG or the derived DB_BINLOG
> instance) can "do its thing."
>
> (I forgot to mention that in Step 1 above, I changed the test
>
> if (mysql_bin_log.is_open() ...)
>
> TO
>
> if (mysql_bin_log && mysql_bin_log->is_open() ...)
>
> throughout the source tree--in order to avoid signal 11 NULL pointers ...)
>
> CONCERNS:
>
> Concern 1. Yesterday I stumbled onto the PSI additions to the pthread
> mutexes and conditions in 5.5.
>
> In particular, the statement:
>
> static PSI_mutex_info all_server_mutexes[]=
> /* ... */
>  { &key_LOCK_sync, "TC_LOG_MMAP::LOCK_sync", 0},
>  { &key_LOCK_active, "TC_LOG_MMAP::LOCK_active", 0},
>  { &key_LOCK_pool, "TC_LOG_MMAP::LOCK_pool", 0},
> /* ... */
>  { &key_BINLOG_LOCK_index, "MYSQL_BIN_LOG::LOCK_index", 0},
>  { &key_BINLOG_LOCK_prep_xids, "MYSQL_BIN_LOG::LOCK_prep_xids", 0},
>  /* ... */
>
> raised my eyebrows. (It looks like PSI is new in 5.5 ...)
>
> I really hope that this doesn't imply that the PSI will act as a (sort
> of) "static, one-for-all-class-instances" limitation. That MIGHT (??)
> force threads that are binlogging to different files, to all be gated
> by the same mutexes ...?? (I wonder ...)
>
> I really haven't had time to look at the PSI additions much yet. I'm
> hoping that they don't "throw a wrench in the spokes" (so to speak).
>
> Concern 2. The ability to do per database (or table) locking.
> Apparently it is now possible in release 5.5.
>
> However, my boss would prefer continuing using MySQL 5.1 for a bit longer.
>
> - 5.1 can't do per-db/per-table locking
>
> - OTOH, if PSI poses a problem with having multiple instances of
> MYSQL_BIN_LOG (or its derivitives), that might force us to use 5.1 ...
> or at least delay us as we figure out how to make PSI work on a
> per-instance basis.
>
> (Hopefully whoever added PSI will have done it in such a way that it
> works allows class instances to have their own complement of locks--if
> they choose ...)
>
> Thanks for listening! Feedback is welcome! (I'm sure I've left
> something important out of this reply.)
>
> Weldon Whipple
> weldon@stripped
>
>
> On Fri, Sep 24, 2010 at 4:46 PM, MARK CALLAGHAN <mdcallag@stripped> wrote:
>> I think you might get something working fast by using triggers to
>> capture changes during the migration.
>>
>> On Tue, Sep 14, 2010 at 7:31 AM, Weldon Whipple <weldon@stripped>
> wrote:
>>> Mark,
>>>
>>> Thank you very much for your quick response! (See my comments below.)
>>>
>>
>> --
>> Mark Callaghan
>> mdcallag@stripped
>>
>



-- 
Mark Callaghan
mdcallag@stripped
Thread
Per-db binloggingWeldon Whipple13 Sep
  • Re: Per-db binloggingMARK CALLAGHAN14 Sep
    • Re: Per-db binloggingWeldon Whipple14 Sep
      • Re: Per-db binloggingMARK CALLAGHAN25 Sep
        • Re: Per-db binloggingWeldon Whipple25 Sep
          • Re: Per-db binloggingMARK CALLAGHAN25 Sep
    • Re: [Maria-discuss] Per-db binloggingHenrik Ingo15 Sep
      • Re: [Maria-discuss] Per-db binloggingWeldon Whipple16 Sep
  • Re: Per-db binloggingSergei Golubchik15 Sep
    • Re: Per-db binloggingWeldon Whipple16 Sep
      • Re: Per-db binloggingSergei Golubchik16 Sep
        • Re: Per-db binloggingWeldon Whipple16 Sep
          • Re: Per-db binloggingSergei Golubchik17 Sep
          • Re: Per-db binloggingWeldon Whipple14 Dec