List:Internals« Previous MessageNext Message »
From:Mark Leith Date:October 28 2010 7:42am
Subject:Re: How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
View as plain text  
Hi,

On 27 Oct 2010, at 23:26, Weldon Whipple wrote:

> Dmitry,
>
> Thank you very much for your response! (I apologize for the slowness
> of my reply. I've been fighting a cold for several days.)
>
> See below:
>
> On Thu, Oct 21, 2010 at 11:15 PM, Dmitry Lenev <Dmitry.Lenev@stripped 
> > wrote:
>> Hello Weldon!
>>
>> * Weldon Whipple <weldon@stripped> [10/10/20 00:26]:
>>> Thanks for the great feedback. It got me looking at the capabilities
>>> of MySQL 5.5.
>>>
>>> It's POSSIBLE that the new syntax:
>>>
>>> FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK
>>>
>>> is PRECISELY the functionality we need!
>>>
>>> Our (perl) migration script MIGHT (for example) do the following:
>>>
>>> Issue the following command (implemented by me ...):
>>>
>>> 1. START BINLOG FOR DATABASE <db>;
>>>
>>> (for each database a given user owns. [Hmmm... Maybe my  
>>> implementation
>>> allows multiple <db> arguments. I need to check.] Each database  
>>> would
>>> start binlogging to its own file. [We will configure--in my.cnf--the
>>> 1-database-per-file binlogs to be stored on a very fast solid-state
>>> drive.])
>>>
>>> Then the script creates a list of all tables in all the user's
>>> databases being migrated to another server and issues the command  
>>> (new
>>> to MySQL 5.5):
>>>
>>> 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK;
>>>
>>> (where the tables t1, t2, ..., tn are the tables being migrated.)
>>>
>>> Then issue the following command (implemented by me ...):
>>
>> AFAIU you also want to prohibit creation of tables in this database
>> after this point as otherwise you can't guarantee binlog stability.
>> Also what about non-table objects like stored routines - don't you
>> want to block their modification as well? The above statement won't
>> provide this as it locks only *existing* *tables*.
>>
>> OTOH in my opinion it should be fairly easy to implement  
>> functionality
>> that you need (i.e. blocking of all DDL and DML that changes data in
>> database) on the basis of FLUSH TABLES <table_list> WITH READ LOCK
>> implementation. You just need to take database-scope S metadata lock
>> in addition to locking and flushing all tables in database. Such lock
>> will block creation of new tables and any DDL on stored routines in
>> the database.
>
> Maybe I need to implement a new command (something like FLUSH DATABASE
> db WITH READ LOCK) that--conceptually, at least--read locks all the
> tables in the database (like FLUSH TABLES ... WITH READ LOCK) *as well
> as* blocks all DDL and DML that changes data in database.
>
> I'll look through the code and find where FLUSH TABLES ... WITH READ
> LOCK is implemented. If I can't figure out how to "take database-scope
> S metadata lock," I'll get back with you...
>
> (I suppose I'll need a corresponding UNLOCK DATABASES command as  
> well.)
>
> I've observed (and read) that the read locks are held as long as the
> client connection remains open, and that the UNLOCK applies to all the
> locks created during the same connection that created the read locks.
> I guess that means that our migration tool will need to maintain the
> connection with mysqld while it dumps (or scp's, in the case of MyISAM
> tables) the database snapshot. (That shouldn't necessarily be a
> problem--it's just something we need to be aware of. It makes sense
> for it to work that way.)
>
>>
>>>
>>> BUG IDENTIFIED.
>>>
> <snip/>
>
>> I have taken the liberty to report this issue as bug #57649
>> (http://bugs.mysql.com/bug.php?id=57649).
>>
>> Thanks for pointing it out!
>
> Thanks for reporting it.
>
>>
>>>
>>> QUESTION:
>>> I read that Larry Ellison stated (at a conference) that 5.5 would be
>>> out by year end. How likely is that? Our company generally waits for
>>> some point releases before changing our customer boxes to the next
>>> release.
>>>
>>> Should I try to back port those locks to 5.1 in the interim? ... or
>>> leave well enough alone?
>>
>> I don't think it makes sense to backport metadata locks to 5.1 as  
>> it is
>> likely to require back-porting of too much other changes from 5.5.
>>
>> OTOH implementing what you need on top of infrastructure already  
>> present
>> in 5.1 is likely to require significant efforts as well.
>>
>> So I think it is better to base your work on 5.5 tree.
>
> I agree. There are quite a few major changes in 5.5. I haven't noticed
> meta-data-locks (MDL) before. The PSI additions to the mutex and
> conditions also look new. (I'm sure I'm missing lots of other
> changes.)
>
> (Regarding the PSI_mutex_key, PSI_rwlock_key and similar additions:
> I've tried to follow the patterns in 5.5 as I added my own mutexes.
> I'm assuming [hoping?] that they work when multiple instances of an
> object all refer to the same-named PSI_xxx-key ??? ...)
>
> (Is there any externally available documentation on MySQL's use of  
> PSI?)


There was a MySQL University session on it:

http://forge.mysql.com/wiki/Performance_Schema:_Instrumenting_Code

You should be able to use a single key with multiple instances of the  
mutex.

Best regards

Mark

-- 
Mark Leith
Software Development Senior Manager
MySQL Enterprise Tools @ Oracle Corp.

Thread
How to lock individual tables in 1-db-per-file binlogging (Was: Re:Per-db binlogging)Weldon Whipple18 Oct
  • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Davi Arnaut18 Oct
    • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple18 Oct
    • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple19 Oct
      • Re: How to lock individual tables in 1-db-per-file binlogging(Was: Re: Per-db binlogging)Dmitry Lenev22 Oct
        • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple28 Oct
          • Re: How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)Mark Leith28 Oct
            • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple29 Oct