Thursday, June 19, 2008

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.

Tuesday, June 3, 2008

Go! Take the survey

Keith Murphy and Mark Schoonover have put together an excellent survey which is already doing good (seeing the numbers they have been clocking). Survey results will be out in the summer issue of MySQL magazine. I feel this is a "must take" survey for everyone in the MySQL community. And do not forget to mention my blog in the "top 5 favorite MySQL blogs" ;-). Just kidding!

Overall, this is the best survey regarding MySQL that I have ever taken, don't miss it. It will hardly take 10 minutes of your busy schedule. A busy man has the time do anything, right?

Quick Links: