← Back to team overview

maria-developers team mailing list archive

Re: Group commit id in mysqlbinlog output


AFAIK, timestamp for a binlog event is the time when corresponding
statement started to execute. The order of statements in the binlog is
according to the time when statements were committed, which of course
doesn't have anything to do with the time statement started to execute.
That's why timestamps in the binlog can jump back and forth randomly. But
I'd think that if one looks at timestamps only on GTID events then he
should expect to see monotonically increasing time within each domain.


On Fri, Mar 14, 2014 at 8:54 AM, Robert Hodges <robert.hodges@xxxxxxxxxxxxxx
> wrote:

> Hi Kristian,
> Thanks for the prompt and detailed response!
> I’m glad you clarified that GTIDs cannot ever walk backwards.  It’s really
> bad design if they are not monotonically increasing and comparable.  This
> will really help restart logic in a number of places, not just for your own
> replication.
> The timestamp issue is mysterious one.  I also don’t fully understand how
> the timestamp is generated for the event header.  All I know is that it
> sometimes walks backwards, possibly as a result of large transactions or
> load data infile commands.  If I can find a reproducible case I will post
> it, as it can cause inscrutable problems downstream when loading to other
> systems.  (Ask me how I know…)
> Meanwhile, good luck on your replication work.  It seems to be proceeding
> in a good direction.
> Cheers, Robert
> On March 14, 2014 at 8:01:04 AM, Kristian Nielsen (
> knielsen@xxxxxxxxxxxxxxx) wrote:
> Robert Hodges <robert.hodges@xxxxxxxxxxxxxx> writes:
> > a.) It seems logical that transactions within a group commit should
> appear
> > together in the binlog and should be serialized before and after other
> > transactions in the binlog. Is there *any* way this ordering could be
> > violated, for example to mix in a non-grouped transaction?
> No the ordering is completely fixed. All transactions that group-commit
> together will be written into the binlog as a unit, without any other
> transactions in-between being possible. The ordering will also be
> identical in
> the binlogs on any slaves (if using multiple replication domains, then the
> ordering is identical within a single domain).
> > b.) Is there any ordering of the transactions within the group commit in
> > the binlog for example sorted based on the resources each uses? Or is it
> > more or less random based on time locks are acquired, etc.?
> There is no ordering. It is just the order in which each thread happens to
> reach the point in the code where it obtains the necessary lock and adds
> itself to the list of transactions waiting for group commit.
> > c.) How do you handle commit timestamps on group-committed transactions?
> > Are they identical? In past MySQL releases I have found instances where
> > timestamps can walk backwards across succeeding transactions. Such
> > anomalies can be very troublesome for downstream consumers like data
> > warehouses that want to create materialized, point-in-time views or
> > partition data based on time of commit. (Ask me how I know.)
> I am not very familiar with timestamps in binlog events. However, all the
> transactions in the group commit are written out one after the other, in a
> single thread holding the lock on the binlog. And the GTID events are
> generated during that loop. So I think that at least the timestamps of the
> GTID events in the group can never walk backwards, nor can they from one
> group
> to the next. But I am not sure if the time stamps of other events in the
> transaction can be earlier (maybe they were generated when the query was
> run,
> not when it was committed?
> > Any clarifications you can offer would be most welcome.
> I hope the above helps, else please ask again.
> - Kristian.
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp

Follow ups