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@xxxxxxxxxx
> wrote:
Hello Weldon!
* Weldon Whipple <weldon@xxxxxxxxxxx> [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?)
