maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

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