List:Internals« Previous MessageNext Message »
From:Weldon Whipple Date:September 25 2010 7:58pm
Subject:Re: Per-db binlogging
View as plain text  

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


MYSQL_BIN_LOG *mysql_bin_log

At the place in where the first call to mysql_bin_log was
made (on startup), I first create ("new") a MYSQL_BIN_LOG from the

I went through all the code and changed all occurrences of:




(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 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

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, 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() ...)


if (mysql_bin_log && mysql_bin_log->is_open() ...)

throughout the source tree--in order to avoid signal 11 NULL pointers ...)


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

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
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