Hi, Kristian!
On Oct 06, Kristian Nielsen wrote:
>
> Would it make sense to have transactional behaviour for status
> variables, and/or information_schema tables? Or does this break the
> code and/or user expectations too much?
>
> The motivation is to follow up on MWL#116, group commit, which
> introduces consistent commit order between storage engines and binary
> log.
>
> I want to use this to get a consistent binlog position for START
> TRANSACTION WITH CONSISTENT SNAPSHOT, without taking any additional
> locks. Currently, I believe it is common (eg. mysqlbinlog
> --master-data --single-transaction) to do something like this:
>
> FLUSH TABLES WITH READ LOCK;
> START TRANSACTION WITH CONSISTENT SNAPSHOT;
> UNLOCK TABLES
> <take consistent backup by dumping tables using the consistent snapshot>
>
> and this takes a lock that can depending on circumstances severely
> affect the server.
>
> One idea is to let the binlog storage engine participate in START
> TRANSACTION WITH CONSISTENT SNAPSHOT, by installing a
> start_consistent_snapshot() method in its handlerton. And then do
> something like this:
>
> START TRANSACTION WITH CONSISTENT SNAPSHOT;
> SELECT variable_value FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE
> variable_name IN ('binlog_master_file', 'binlog_master_position');
> <dump other transactional tables>
>
> If the SELECT of binlog_master_file and binlog_master_position could
> be transactional, then the binlog engine could return the correct
> values associated with the consistent snapshot, without blocking any
> other transactions.
This should be easy to do, I expect. And although it's a change in
behavior, I think, it'll practically go unnoticed - because it only
affects START TRANSACTION WITH CONSISTENT SNAPSHOT.
> I like the simplicity of this idea, but I do not understand server
> transaction handling enough to be sure it will work well, some
> concerns:
>
> - Using SHOW STATUS / INFORMATION_SCHEMA.SESSION_STATUS like this
> could be surprising to people that do not expect different parts of
> the results to display different kinds of transactional behaviour.
> This could be helped by using instead a separate INFORMATION_SCHEMA
> table for the binlog position.
I think it's not an issue, as only START TRANSACTION WITH CONSISTENT
SNAPSHOT is affected.
> - But this raises another concern, if an INFORMATION_SCHEMA can be
> transactional in this respect?
Why not? It simply shows information taken from elsewhere. If that
information behaves in a transactional manner, so will I_S table.
> - Also, it seems to me that merely selecting from such transactional
> INFORMATION_SCHEMA table would then start a new transaction inside
> the binlog engine. I wonder if this would cause any unpleasant side
> effects?
This is a bigger change - not more complex, but but more certainly more
noticeable. Currently I_S alsways shows the current binlog position, not
the one from the beginning of a transaction. Changing that may break
applications.
> ----
> If this does not work, I have another idea, which I think is more
> general, but also more complicated to implement.
>
> The idea is that every transaction has a local transaction ID,
> assigned at the start (we already have this, in
> thd->transaction.xid_state.xid).
>
> Each engine will transactionally store the local transaction ID of the
> last transaction committed. The binary log will similarly store this
> ID along with every transaction that is binlogged.
>
> Then START TRANSACTION WITH CONSISTENT SNAPSHOT could optionally
> return the local transaction ID of the last committed transaction
> visible in the snapshot obtained. This local transaction ID could then
> be mapped to binlog position (with eg. mysqldump), and more generally
> any binlog plugin could provide a way to map such local transaction ID
> into its own global transaction ID.
>
> Similarly, after restore of InnoDB hot backup or LVM snapshot, one
> could ask the engine for last committed local transaction ID, and map
> this to binlog position / global transaction ID to be able to use the
> restored backup to provision a new slave.
>
> This would work with any storage engine and any binlog/replication
> implementation, without any need for FLUSH TABLES WITH READ LOCK.
Uhm, I don't think I understood your idea :(
Regards,
Sergei
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
