MySQL Bugs: #58897: Rpl_info_table::do_is_transactional causes too much overhead

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 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 Junior	CPU Architecture:	Any
Tags:	performance, WL#2775

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.

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.

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.

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.

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.

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.