Bug #58897 Rpl_info_table::do_is_transactional causes too much overhead
Submitted: 13 Dec 2010 12:12 Modified: 5 Apr 2011 11:07
Reporter: Alfranio Tavares Correia Junior Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6+ OS:Any
Assigned to: Alfranio Tavares Correia Junior CPU Architecture:Any
Tags: performance, WL#2775

[13 Dec 2010 12:12] Alfranio Tavares Correia Junior 
Description:
After WL#2775, Slave's information,i.e. IO Thread and SQL Thread's information, can be stored into different types of repositories. The current implementation provides two repositories, i.e. FILE or TABLE, and any type of engine can be used.

The engine can be dynamically defined by calling

   ALTER TABLE mysql.slave_relay_log_info ENGINE=<engine>

However, only transactional engines, such as Innodb, can provide a crash-safe behaviour in the sense that a slave can continue operating after a crash without requiring any manual-intervention.

If a transactional table is in use, a different execution path is called to provide a crash-safe behaviour. Testing if a transactional engine is in use, unfortunately, requires to open and close the table, slave_relay_log_info, and this has a negative impact on performance.

How to repeat:
Check the code.

bool Rpl_info_table::do_is_transactional()
{
  ulong saved_mode;
  TABLE *table= NULL;
  Open_tables_backup backup;
  bool is_trans= FALSE;

  DBUG_ENTER("Rpl_info_table::do_is_transactional");

  THD *thd= access->create_thd();

  saved_mode= thd->variables.sql_mode;

  /*
    Opens and locks the rpl_info table before accessing it.
  */
  if (!access->open_table(thd, str_schema, str_table,
                          get_number_info(), TL_READ,
                          &table, &backup))
    is_trans= table->file->has_transactions();

  access->close_table(thd, table, &backup, 0);
  thd->variables.sql_mode= saved_mode;
  access->drop_thd(thd);
  DBUG_RETURN(is_trans);
}

Suggested fix:
Verify if the table is transactional or non-transactional and at the same time allow to change its type only at startup.
[13 Dec 2010 18:19] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126699

3423 Alfranio Correia	2010-12-13
      BUG#58897 Rpl_info_table::do_is_transactional causes too much overhead
      
      After WL#2775, Slave's information,i.e. IO Thread and SQL Thread's information, can be
      stored into different types of repositories. The current implementation provides two
      repositories, i.e. FILE or TABLE, and any type of engine can be used.
      
      However, only transactional engines, such as Innodb, can provide a crash-safe behaviour
      in the sense that a slave can continue operating after a crash without requiring any
      manual-intervention.
      
      If a transactional table is in use, a different execution path is called to provide a
      crash-safe behaviour. Testing if a transactional engine is in use, unfortunately,
      requires to open and close the table, slave_relay_log_info, and this has a negative
      impact on performance.
      
      To fix this, we configure the engine in use while either starting up the IO or SQL
      threads, and the routine that checks if a transactional engine is in use accesses a
      variable.
     @ mysql-test/extra/rpl_tests/rpl_crash_safe.inc
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_mixed_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_row_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_stm_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/t/rpl_mixed_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_row_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_stm_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ scripts/mysql_install_db.pl.in
        Removed the option --rpl-engine.
     @ scripts/mysql_install_db.sh
        Removed the option --rpl-engine.
     @ sql/lock.cc
        Added code to avoid printing out an error message if a slave is trying
        to execute ALTER TABLE on a rpl info table although a regular user
        cannot do the same.
     @ sql/mysqld.cc
        Added options to define the type of engine used by rpl into tables.
     @ sql/mysqld.h
        Added options to define the type of engine used by rpl into tables.
     @ sql/rpl_info.h
        Added set_transactional() to the interface.
     @ sql/rpl_info_factory.cc
        Removed change_engine() as executing a command when the server is starting up fails
        because several structures are not initialized.
     @ sql/rpl_info_factory.h
        Removed change_engine() as executing a command when the server is starting up fails
        because several structures are not initialized.
     @ sql/rpl_info_file.cc
        Added do_set_transactional() to the interface.
     @ sql/rpl_info_file.h
        Added do_set_transactional() to the interface.
     @ sql/rpl_info_handler.h
        Added set_transactional() to the interface which calls a virtual private
        function do_set_transactional().
        
        Its implementation depends on the type of the handler. For a table, it
        changes the type of the engine. For a file, the current implementation
        does nothing.
     @ sql/rpl_info_table.cc
        Changed do do_is_transactional() and implemented do_set_transactional().
        
        do_is_transactional returns is_transactional which is initialized after
        calling do_set_transactional when the IO and SQL threads are created.
     @ sql/rpl_info_table.h
        Introduced the member variable is_transactional that identifies
        if the table used as repository is transactional or not.
        
        The variable is initialized after calling do_set_transactional().
     @ sql/rpl_slave.cc
        Called function to change the engine in use by rpl info table while
        starting the SQL or IO Threads.
     @ sql/sql_parse.cc
        Disabled the possibility of directly executing an alter table on
        rpl info tables.
[13 Dec 2010 19:56] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126705

3423 Alfranio Correia	2010-12-13 [merge]
      BUG#58897 Rpl_info_table::do_is_transactional causes too much overhead
      
      After WL#2775, Slave's information,i.e. IO Thread and SQL Thread's information, can be
      stored into different types of repositories. The current implementation provides two
      repositories, i.e. FILE or TABLE, and any type of engine can be used.
      
      However, only transactional engines, such as Innodb, can provide a crash-safe behaviour
      in the sense that a slave can continue operating after a crash without requiring any
      manual-intervention.
      
      If a transactional table is in use, a different execution path is called to provide a
      crash-safe behaviour. Testing if a transactional engine is in use, unfortunately,
      requires to open and close the table, slave_relay_log_info, and this has a negative
      impact on performance.
      
      To fix this, we configure the engine in use while starting the server and and the
      routine that checks if a transactional engine is in use accesses a variable.
     @ mysql-test/extra/rpl_tests/rpl_crash_safe.inc
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_mixed_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_row_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_stm_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/t/rpl_mixed_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_row_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_stm_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ scripts/mysql_install_db.pl.in
        Removed the option --rpl-engine.
     @ scripts/mysql_install_db.sh
        Removed the option --rpl-engine.
     @ sql/lock.cc
        Added code to avoid printing out an error message if a slave is trying
        to execute ALTER TABLE on a rpl info table although a regular user
        cannot do the same.
     @ sql/mysqld.cc
        Added options to define the type of engine used by rpl into tables.
     @ sql/mysqld.h
        Added options to define the type of engine used by rpl into tables.
     @ sql/rpl_info_factory.cc
        Removed defines that were used to simulate options.
     @ sql/rpl_info_table.cc
        Changed do do_is_transactional() and implemented change_engine().
        
        do_is_transactional returns is_transactional which is initialized after
        calling change_engine during startup.
     @ sql/rpl_info_table.h
        Introduced the member variable is_transactional that identifies
        if the table used as repository is transactional or not.
        
        The variable is initialized after calling change_engine().
     @ sql/rpl_info_table_access.cc
        Skipped security validation if doing updates through the Rpl_info_table_access.
     @ sql/sql_parse.cc
        Disabled the possibility of directly executing an alter table on
        rpl info tables.
[29 Dec 2010 10:57] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127655

3423 Alfranio Correia	2010-12-29 [merge]
      BUG#58897 Rpl_info_table::do_is_transactional causes too much overhead
      
      After WL#2775, Slave's information,i.e. IO Thread and SQL Thread's information, can be
      stored into different types of repositories. The current implementation provides two
      repositories, i.e. FILE or TABLE, and any type of engine can be used.
      
      However, only transactional engines, such as Innodb, can provide a crash-safe behaviour
      in the sense that a slave can continue operating after a crash without requiring any
      manual-intervention.
      
      If a transactional table is in use, a different execution path is called to provide a
      crash-safe behaviour. Testing if a transactional engine is in use, unfortunately,
      requires to open and close the table, slave_relay_log_info, and this has a negative
      impact on performance.
      
      To fix this, we configure the engine in use while starting the server and and the
      routine that checks if a transactional engine is in use accesses a variable.
     @ mysql-test/extra/rpl_tests/rpl_crash_safe.inc
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_mixed_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_row_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/r/rpl_stm_crash_safe.result
        Removed ALTER TABLE as it is forbidden now.
     @ mysql-test/suite/rpl/t/rpl_mixed_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_row_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ mysql-test/suite/rpl/t/rpl_stm_crash_safe-slave.opt
        Changed the test case to set the engine in use for the relay log repository.
     @ scripts/mysql_install_db.pl.in
        Removed the option --rpl-engine.
     @ scripts/mysql_install_db.sh
        Removed the option --rpl-engine.
     @ sql/lock.cc
        Added code to avoid printing out an error message if a slave is trying
        to execute ALTER TABLE on a rpl info table although a regular user
        cannot do the same.
     @ sql/mysqld.cc
        Added options to define the type of engine used by rpl into tables.
     @ sql/mysqld.h
        Added options to define the type of engine used by rpl into tables.
     @ sql/rpl_info_factory.cc
        Removed defines that were used to simulate options.
     @ sql/rpl_info_table.cc
        Changed do do_is_transactional() and implemented change_engine().
        
        do_is_transactional returns is_transactional which is initialized after
        calling change_engine during startup.
     @ sql/rpl_info_table.h
        Introduced the member variable is_transactional that identifies
        if the table used as repository is transactional or not.
        
        The variable is initialized after calling change_engine().
     @ sql/rpl_info_table_access.cc
        Skipped security validation if doing updates through the Rpl_info_table_access.
     @ sql/sql_parse.cc
        Disabled the possibility of directly executing an alter table on
        rpl info tables.
[17 Jan 2011 10:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128926

3475 Alfranio Correia	2011-01-17 [merge]
      BUG#58897 Rpl_info_table::do_is_transactional causes too much overhead
      
      After WL#2775, Slave's information,i.e. IO Thread and SQL Thread's information, can be
      stored into different types of repositories. The current implementation provides two
      repositories, i.e. FILE or TABLE, and any type of engine can be used.
      
      However, only transactional engines, such as Innodb, can provide a crash-safe behaviour
      in the sense that a slave can continue operating after a crash without requiring any
      manual-intervention.
      
      If a transactional table is in use, a different execution path is called to provide a
      crash-safe behaviour. Testing if a transactional engine is in use, unfortunately,
      requires to open and close the table, slave_relay_log_info, and this has a negative
      impact on performance.
      
      To fix this, we only allow to change the type of the repository when replication is
      stopped. We also have implemented the following changes:
      
      . Refactored Rpl_info_factory and removed unnecessary code.
      . Do not allow to change replication repository tables when replication is runnning,
        i.e., alter, drop, rename, etc.
      . Alerted when a configuration is not crash-safe.
     @ mysql-test/extra/rpl_tests/rpl_crash_safe.inc
        Updated test case.
     @ mysql-test/suite/rpl/r/rpl_migration_crash_safe.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_row_crash_safe.result
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_stm_mixed_crash_safe.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_migration_crash_safe.test
        Updated test case.
     @ mysql-test/suite/sys_vars/r/master_info_repository_basic.result
        Updated result file.
     @ mysql-test/suite/sys_vars/r/relay_log_info_repository_basic.result
        Updated result file.
     @ mysql-test/suite/sys_vars/t/master_info_repository_basic.test
        Updated test case.
     @ mysql-test/suite/sys_vars/t/relay_log_info_repository_basic.test
        Updated test case.
     @ scripts/mysql_install_db.pl.in
        Removed the option --rpl-engine.
     @ scripts/mysql_install_db.sh
        Removed the option --rpl-engine.
     @ sql/lock.cc
        Checked if a SYSTEM_THREAD_INFO_REPOSITORY is trying to acquire a lock.
     @ sql/rpl_info.cc
        Removed unnecessasry code as delete and my_free may be called when pointers
        are null.
     @ sql/rpl_info.h
        Improved design making constructor protected.
        
        Added set_rpl_info_type(), get_rpl_info_type(), get_rpl_info_handler()
        and update_transactional().
     @ sql/rpl_info_factory.cc
        Refactored it.
     @ sql/rpl_info_factory.h
        Refactored it.
     @ sql/rpl_info_file.cc
        Implemented do_update_transactional().
     @ sql/rpl_info_file.h
        Improved design making constructor private and Rpl_info_factory a friend.
     @ sql/rpl_info_handler.cc
        Removed unnecessasry code as delete and my_free may be called when pointers
        are null.
     @ sql/rpl_info_handler.h
        Defined do_update_transactional() and improved design making constructor protected.
     @ sql/rpl_info_table.cc
        Changed do do_is_transactional() and implemented do_update_transactional().
        
        Removed unnecessasry code as delete and my_free may be called when pointers
        are null.
     @ sql/rpl_info_table.h
        Introduced the member variable is_transactional that identifies if the
        table used as repository is transactional or not. This value is updated
        by calling do_update_transactional.
        
        Improved design making constructor private and Rpl_info_factory a friend.
     @ sql/rpl_info_table_access.cc
        Skipped security validation if doing updates through the Rpl_info_table_access and
        set thread information.
     @ sql/rpl_info_values.cc
        Removed unnecessary code as delete [] already calls the destructor.
     @ sql/rpl_mi.h
        Improved design making constructor private and Rpl_info_factory a friend.
     @ sql/rpl_rli.h
        Improved design making constructor private and Rpl_info_factory a friend.
     @ sql/rpl_slave.cc
        Added code to check if the current replication configuration is transactional
        and to alert when it is not.
     @ sql/share/errmsg-utf8.txt
        Added error message to be fired if a failure happens while changing the type of the repository.
     @ sql/sql_class.h
        Added system information to indentify threads created to update replication
        repository tables.
     @ sql/sql_db.cc
        Database that contains replication repository tables may be dropped if
        replication is stopped.
     @ sql/sql_partition_admin.cc
        Replication repository tables may have its partitions changed if replication is stopped.
     @ sql/sql_rename.cc
        Replication repository tables may have their name changed if replication is stopped.
     @ sql/sql_table.cc
        Replication repository tables may be dropped or altered if replication is stopped.
     @ sql/sql_table.h
        Added a function to check if it is a replication repository table.
     @ sql/sys_vars.cc
        Changed sys_vars.cc to allow master_info_repository and relay_log_info_repository
        to be changed on-line if replication is stopped.
     @ sql/table.h
        Defined global variables to identify the replication repository tables.
[5 Apr 2011 11:07] Jon Stephens 
Documented as follows in the 5.6.3 changelog:

      It is no longer possible to change the storage engine used by the
      mysql.slave_master_info and mysql.slave_relay_log_info tables while 
      replication is running. This means that, in order to make replication 
      crash-safe, you must make sure that both of these tables use a 
      transactional storage engine *before* starting replication.

Also added/updated info in "What's New" section, descriptions of --master-info-repository and --slave-relay-info-repository options.

Closed.