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@oracle.com

...

wrote: Hello Weldon!

* Weldon Whipple <weldon@whipple.org> [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.