maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Sergei Golubchik <serg@xxxxxxxxxxxx> writes:

> On Oct 06, Kristian Nielsen wrote:

>>  - 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.

Right, sounds reasonable, thanks. I think this is a good approach then.

>> 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.

Yes. In fact (at least to my knowledge), currently there is nowhere in SHOW
STATUS or INFORMATION_SCHEMA that the binlog position is available.

So I think I will introduce two new status variables:

    binlog_trx_file
    binlog_trx_position

These will be the binlog position corresponding to the point of START
TRANSACTION WITH CONSISTENT SNAPSHOT. If no snapshot was started, they will
just be the current position.

>>  - 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 certainly more
> noticeable. Currently I_S always shows the current binlog position, not
> the one from the beginning of a transaction. Changing that may break
> applications.

Yeah. Since this would be new status variables, it probably doesn't matter
... on the other hand, I do not think this is particularly useful either. It
does not really give anything better than simply asking for the position at
the start of the transaction, and it would require taking extra mutexes at the
start of every transaction touching the binary log.

I think I will just let the status reflect the current position, unless START
TRANSACTION WITH CONSISTENT SNAPSHOT has been run, in which case they will
return the consistent position.

>> If this does not work, I have another idea, which I think is more
>> general, but also more complicated to implement.

> Uhm, I don't think I understood your idea :(

Nevermind, I think the simple approach will work. And for the complex idea,
when I have worked more on it and thought things through better, we can
re-visit it.

Mark Nielsen <menprojects@xxxxxxxxx> writes:

> For my personal opinion, I can go either way. What is most important on a
> high query database is non-blocking. For example, if the hard drive is
> acting funny, show master status, show slave status, and certain other show
> commands can stall. Other show commands, like show variables and show global
> status never stall.
>
> When it comes to information schema, the top priority is no stalling. This
> makes monitoring more reliable. We've had situations where a query detecting
> the status of a machine would stall, and then every 5 minutes it would check
> again, and stall, and over time it eats up the connections with dead
> connections.

I agree with this. The way this will be implemented, there should be no
stalls.

InnoDB commits are broken up in two parts, a "fast" part which only commits
the transaction to memory and makes it visible to other transactions, and a
"slow" part which flushes the commit record to disk. The synchronisation with
the binlog position only needs to synchronise with the "fast" part, the "slow"
part can run independently.

 - Kristian.