From: Weldon Whipple Date: September 25 2010 7:58pm Subject: Re: Per-db binlogging List-Archive: http://lists.mysql.com/internals/38118 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 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 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 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 wrote: >> Mark, >> >> Thank you very much for your quick response! (See my comments below.) >> > > -- > Mark Callaghan > mdcallag@stripped >