Temporary tables as seen by replication slave

Few days back, one of my colleagues posted a good question. It sounds something like this;

"Temporary tables are session based that means under different sessions we can create temporary tables with similar names. Now since slave thread is singleton, how does it manage to keep them separate?"

He was very much right in asking this and the answer is not all that intuitive. Lets go through the binlog events to see why it is not that intuitive.

   1: mysql> SHOW BINLOG EVENTS IN 'log-bin.000016';

   2: . . .     

   3: | log-bin.000016 |  389 | Query       | 2515922453 |         488 | use `test`; CREATE TEMPORARY TABLE test.t(a int)         |    

   4: | log-bin.000016 |  488 | Query       | 2515922453 |         582 | use `test`; INSERT INTO test.t(a) VALUES(1)         |    

   5: | log-bin.000016 |  582 | Query       | 2515922453 |         676 | use `test`; INSERT INTO test.t(a) VALUES(3)         |    

   6: | log-bin.000016 |  676 | Query       | 2515922453 |         775 | use `test`; CREATE TEMPORARY TABLE test.t(a int)         |    

   7: | log-bin.000016 |  775 | Query       | 2515922453 |         869 | use `test`; INSERT INTO test.t(a) VALUES(7)         |    

   8: | log-bin.000016 |  869 | Query       | 2515922453 |         944 | use `test`; drop table t         |    

   9: ...

Under general conditions if you run these statements in sequence, you will end up with a Table `t` already exists when you put second create temporary table. But with replication this seems to just work, how? Well, the truth is SHOW BINLOG EVENTS doesn't show the full truth.

The Magic Behind:

For such situations, MySQL uses a special flag LOG_EVENT_THREAD_SPECIFIC_F that is set if the event is dependent on the connection it was executed on. This translates into setting a session level variable pseudo_thread_id instructing the slave thread to treat a bundle of statements in a special way and do not create any confusion. Now this is actually a very safe method of doing things and being very extra paranoid I wondered why this was not there for every session? Simple answer is; performance reasons. :)

Check the outcome of mysqlbinlog:

   1: $ mysqlbinlog log-bin.000016    

   2: ...    

   3: # at 389    

   4: #080617  2:06:11 server id -1779044843  end_log_pos 488     Query    thread_id=138    exec_time=0    error_code=0    

   5: SET TIMESTAMP=1213693571/*!*/;    

   6: SET @@session.pseudo_thread_id=138/*!*/;    

   7: CREATE TEMPORARY TABLE test.t(a int)/*!*/;    

   8: # at 488    

   9: #080617  2:06:15 server id -1779044843  end_log_pos 582     Query    thread_id=138    exec_time=0    error_code=0    

  10: SET TIMESTAMP=1213693575/*!*/;    

  11: SET @@session.pseudo_thread_id=138/*!*/;    

  12: INSERT INTO test.t(a) VALUES(1)/*!*/;    

  13: # at 582   

  14: #080617  2:06:36 server id -1779044843  end_log_pos 676     Query    thread_id=138    exec_time=0    error_code=0    

  15: SET TIMESTAMP=1213693596/*!*/;    

  16: SET @@session.pseudo_thread_id=138/*!*/;    

  17: INSERT INTO test.t(a) VALUES(3)/*!*/;    

  18: # at 676    

  19: #080617  2:06:55 server id -1779044843  end_log_pos 775     Query    thread_id=141    exec_time=0    error_code=0    

  20: SET TIMESTAMP=1213693615/*!*/;    

  21: SET @@session.pseudo_thread_id=141/*!*/;    

  22: CREATE TEMPORARY TABLE test.t(a int)/*!*/;    

  23: # at 775   

  24: #080617  2:11:07 server id -1779044843  end_log_pos 869     Query    thread_id=141    exec_time=0    error_code=0    

  25: SET TIMESTAMP=1213693867/*!*/;    

  26: SET @@session.pseudo_thread_id=141/*!*/;    

  27: INSERT INTO test.t(a) VALUES(7)/*!*/;    

  28: # at 869    

  29: #080617  2:15:50 server id -1779044843  end_log_pos 944     Query    thread_id=141    exec_time=0    error_code=0    

  30: SET TIMESTAMP=1213694150/*!*/;    

  31: SET @@session.pseudo_thread_id=141/*!*/;    

  32: drop table t/*!*/;   

  33: ...

I delved into the code to know what all situations need this flag to be set. So far, it is set only when using temporary tables and with a possibility/extensibility of using with other situations in future. Other situations like transactions take care of themselves by the way they are logged.

I'm running few tests to see if it handles all the conditions related to temporary tables created inside transactions. Will publish soon if something pops up.

Variable's Day Out #13: binlog_format

Properties:

Applicable To	MySQL Server
Introduced In	5.1.5
Server Startup Option	--binlog-format=<value>
Scope	Both
Dynamic	Yes
Possible Values	enum(ROW, STATEMENT, MIXED)
Default	< 5.1.12: STATEMENT >= 5.1.12: MIXED
Categories	Replication, Performance

Description:

Starting with 5.1.5, MySQL has implemented ROW based replication format which logs the physical changes to individual row changes. This looks like the most optimal way to many users. But it is not always, rather not optimal most of the times. E.g. consider a statement that does bulk insert of thousands of rows. In ROW based logging, there will be those many entries in binlog and otherwise it would have been just one single statement.

STATEMENT based replication, the old good historical way, propagates SQL statements from master to slave and has been working good all those years except for few cases like statements using non deterministic UDFs.

In MIXED format, MySQL uses STATEMENT based replication by default other than a few cases like

• when USER(), CURRENT_USER() are used
• when a call to UDF is involved
• when 2 or more tables with AUTO_INCREMENT columns are updated.

... For full list of all such cases, refer the official documentation.

What about UDF's?

A user defined function or stored procedure is very hard to predict. In such cases, statement based replication can create inconsistencies. Few days back, I saw a case where a table  included in Replicate_Ignore_Table list was propagating statements from a procedure. If one has procedures and such cases, consider using ROW or MIXED mode replication.

What to use?

Though ROW based replication is the safest in case of creating inconsistencies, it may lead to sheer performance degradation thanks to binlog size. On the other hand STATEMENT based replication has drawbacks like with UDFs etc. IMHO, MIXED mode is the best way out unless you have some very special case. The only problem is that there might be more cases that need to be handled by mixed mode than currently being served. We need time's stamp on it. :)

Read more:

• MySQL manual entry on binlog_format
• MySQL manual on Replication Formats
• MySQL manual: Mixed Binary Logging Format
• Statement based vs Row based replication

 

Hope you enjoyed reading this.

Variable's Day Out #7: innodb_autoinc_lock_mode

Properties:

Applicable To	InnoDB
Introduced In	5.1.22
Server Startup Option	--innodb-autoinc-lock-mode=<value>
Scope	Global
Dynamic	No
Possible Values	enum(0,1,2) Interpretation: Value Meaning 0 Traditional 1 Consecutive 2 Interleaved
Default Value	1 (consecutive)
Categories	Scalability, Performance

Description:

This variable was introduced in 5.1.22 as a result of the [Bug 16979] and comes very handy when stuck with auto_increment scalability issue, also mentioned in my previous post. So what do traditional, consecutive and interleaved mean?

Traditional is "traditional", this takes back InnoDB to pre-innodb_autoinc_lock_mode and a table level AUTO-INC lock is obtained and held until the statement is done. This ensures consecutive auto-increment values by a single statement. Remember, this lock is scoped for a statement and not transaction and hence is not equivalent to serializing transactions as someone raised a question to me recently.

Consecutive, the default lock mode, works in context switching method. For inserts where the number of rows is not known (bulk inserts), ie INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA, it takes a table level AUTO-INC lock. Otherwise for inserts where the number of rows is known in advance (simple inserts), it uses a light weight mutex during the allocation of auto-increment values. The mutex is of course checked for only if no other transaction holds the AUTO-INC lock. However for inserts where user provides auto-increment values for some rows (mixed mode inserts), InnoDB tends to allocate more values and lose them.

Interleaved mode just ensures uniqueness for each generated auto-incremented value. This mode never takes an AUTO-INC lock and multiple statements can keep generating values simultaneously.

How to use?

My overall recommendation is not to change this variable and keep it to default. And if you are having mixed mode insert statements that contradict the usage, better look into them. Otherwise, following are the constraints on usage.

• Use interleaved only when your tables don't have auto-increment columns. Also if you don't know/don't care if they have, then you have more issues to resolve. :)
• "mixed mode inserts" can lead to losing values with consecutive mode.
• It's not safe to use statement based or mixed replication with interleaved mode.
• Traditional mode has scalability issues, but is safe when used with mixed mode inserts.

Read more:

• MySQL manual entry on innodb_autoinc_lock_mode
• Configurable InnoDB Auto-Increment Locking
• [Bug 16979]

Hope you enjoyed reading this.