Bug #15790 bin log xid tracking feature request
Submitted: 15 Dec 2005 21:06 Modified: 20 Nov 2006 12:48
Reporter: Jonathan Miller Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.1 OS:Linux (Linux)
Assigned to: Lars Thalmann CPU Architecture:Any

[15 Dec 2005 21:06] Jonathan Miller
Description:
Hi Jeb!

Thanks for bringing this up; good that you are checking to make sure this is correct.

Currently, the transactions are not interleaved in the binary log, and we will wait until after 5.1 to interleave them (since feature freeze for 5.1 is now counting in days). Currently, the COMMIT/XID refers to the events which immediately precede it in the log. Once we interleave the transactions I think we will need to make the change you are talking about to explicitly associate each event with an XID, but now the association is implicit based on this serialization of transactions.

I don't think there is a worklog currently for interleaving transactions in the binlog, so if you wanted to write a feature request in the bug DB to be sure that we don't lose track of your suggestion to put XID in its own column and assign it to me, that sounds like a good idea. I don't think that this XID in a comment is a bug (= does not run correctly) in 5.0 and 5.1 based on my current understanding, but hey, I'm fallible :-)

cheers,
-elliot

Jonathan Miller wrote:
> Hi Elliot,
> 
> As replication lead I wanted to express my concerns over current xid usage. 
> 
> According to an email I received earlier today, xid id for the following:
> 
> 2) why do we need SHOW BINLOG EVENTS to display the XID in a MySQL 
> build (release or debug): because the XID is the transaction's number 
> and we need to know to which transaction the "COMMIT" string in binlog refers.
> This will be even more critical when the repl team implements the next 
> step of RBR, known as "interleaved transactions". "Interleaved 
> transactions in binlog" is the acknowledged key to solve the problem 
> that the current RBR is dramatically slow when two large transactions 
> are running in parallel. The current RBR causes some large swapping - 
> Brian can confirm. The solution is that RBR will be changed to do less 
> buffering and write its transaction events directly to the disk 
> binlog; if two transactions (of XID 1 and 2) run in parallel of course the binlog will look like:
> 
> event related to XID 1
> event related to XID 2
> event related to XID 2
> event related to XID 2
> event related to XID 1
> event related to XID 2
> commit event related to XID 2
> event related to XID 1
> commit event related to XID 1.
> 
> Without the XID printed in SHOW BINLOG EVENTS, it will be absolutely 
> impossible for the DBA to know to which transaction each events belong 
> to, and thus impossible for the DBA to understand the sequence of 
> events in binlog. So we'll need the XID even more than now.
> 
> Problem is that this is not what is implemented. Currently a show bin 
> log events would return some thing like:
> 
> Log_name	Pos	Event_type	Server_id	End_log_pos	Info
> master-bin.000001	98	Query	1	#	use `test`; insert
> into t1 values(10)
> master-bin.000001	186	Xid	1	#	COMMIT /* xid=66 */
> master-bin.000001	213	Query	1	#	use `test`; insert
> into t2 select * from t1
> master-bin.000001	307	Query	1	#	use `test`; BEGIN
> master-bin.000001	375	Query	1	#	use `test`; insert
> into t1 values(11)
> master-bin.000001	463	Xid	1	#	COMMIT /* xid=68 */
> 
> 
> There is no mapping to what insert, delete, update a Commit Xid 
> belongs. So the above explanation has either showed me that we have a 
> bug, or the work is not complete.
> 
> I understand the need to the Xid, but worry about the implementation. 
> I had suggest in a different email that we create a column for Xid and 
> each item can the be marked for which Xid it belongs. This to me make 
> more sense then using a comment field. An example might look like:
> 
> Log_name	Pos	Event_type	Server_id	End_log_pos	Xid
> Info
> bin.000001	98	Query	         1	           #	 68  use
> `test`; insert into t1 values(10)
> bin.000001	213	Query	          1	           #	 66  use
> `test`; insert into t2 select * from t1
> bin.000001	186	 Xid	         1	            #	 66  COMMIT 
> bin.000001	307	Query	         1	            #	 68  use
> `test`; BEGIN
> bin.000001	375	Query	         1	            #	 68  use
> `test`; insert into t1 values(11)
> bin.000001	463	Xid	         1	            #	 68  COMMIT 
> 
> 
> It would make the most sense to correct this in 5.1 and avoid upgrade 
> issues in 5.2 once users (DBAs) have already started using RBR, if 
> this is the case.
> 
> Please let me know 
> 
> Thanks! Your time is appreciated. 
> 
> Jonathan Miller
> Austin, Texas USA
> Senior Quality Assurance Developer
> MySQL AB www.mysql.com
>     __  ___     ___ ____  __
>    /  |/  /_ __/ __/ __ \/ /  
>   / /|_/ / // /\ \/ /_/ / /__
>  /_/  /_/\_, /___/\___\_\___/ 
>         <___/   www.mysql.com 
> 
> Jumpstart your cluster!
> http://www.mysql.com/consulting/packaged/cluster.html
> 
> Get training on clusters
> http://www.mysql.com/training/courses/mysql_cluster.html
> 
> All-in-one Enterprise-grade Database, Support and Services 
> http://www.mysql.com/network/
> 

How to repeat:
N/A

Suggested fix:
See Above