"Reprint" standby due to a table without primary key causes delay

Abstract: Because row mode replication is already widely used, for tables without a primary key, if a large update occurs, there is a significant delay on the standby because a large number of row records generated in Binlog will not be able to be quickly found based on the primary key, and the worst case scenario is a full table scan of each modified record. 5.6 has solved this problem, you can scan the table only once, 5.5 the latest version just output some letters in the error log ...

Because row mode replication is already widely used, for tables that do not have a primary key, if a large update occurs, there is a significant delay on the standby because a large number of row records generated in Binlog will not be able to be quickly found based on the primary key, and the worst case scenario is a full table scan of each modified record.

5.6 has solved this problem, you can scan the table only once, 5.5 the latest version just output some information in the error log.

The implementation of Port 5.6 is not realistic because the changes are too large. So I made some minor changes to the delete/update on the non-primary key table and converted to the Binlog record in statement mode.

The following is a very simple patch, based on the Percona5.5.18

 

Index:/ps5518/branches/ps-r3633-nopk-logstmt/sql/sys_vars.cc================================================== =================---/ps5518/branches/ps-r3633-nopk-logstmt/sql/sys_vars.cc (revision 3639) + + +/PS5518/branches/        ps-r3633-nopk-logstmt/sql/sys_vars.cc (revision 3641) @@ -396,6 +396,13 @@ -396,6 (Opt_arg), DEFAULT (FALSE), No_mutex_guard, Not_in_binlog, On_check (Binlog_direct_check)); +static Sys_var_mybool Sys_binlog_use_stmt_for_non_       PK (+ "BINLOG_USE_STMT_FOR_NON_PK", + "if a table doesn ' t has primary key, then log the changes (sqlcom_delete" + "and Sqlcom_update) using STATEMENT.", + Session_var (BINLOG_USE_STMT_FOR_NON_PK), + cmd_line (opt_arg), Defau  LT (FALSE)); + Static Sys_var_ulong sys_bulk_insert_buff_size ("Bulk_insert_buffer_size", "size of the tree cache used in Bulk "" Insert optimisation. Note that this is a limit per thread! ", Index:/ps5518/branches/ps-r3633-nopk-logstmt/sql/sql_class.h================== =================================================---/ps5518/branches/ps-r3633-nopk-logstmt/sql/sql_class.h (revision 3639) + + +/PS5518/ Branches/ps-r3633-nopk-logstmt/sql/sql_class.h (revision 3641) @@ -492,6 +492,7 @@ -492,6 Binlog_format; < Binlog format for this THD (see Enum_binlog_format) my_bool binlog_direct_non_trans_update;+ my_bool Binlog_use   _STMT_FOR_NON_PK;   My_bool Sql_log_bin;   ULONG Completion_type; ULONG Query_cache_type;index:/ps5518/branches/ps-r3633-nopk-logstmt/sql/sql_class.cc=========================== ========================================---/ps5518/branches/ps-r3633-nopk-logstmt/sql/sql_class.cc (revision 3639) + + +/ps5518/branches/ps-r3633-nopk-logstmt/sql/sql_class.cc (revision 3641) @@ -4495,10 +4495,14 @@ -4495,10 the CAPAB     Ilities vector for all involved storage engines and mask out the flags for the binary log.     */+ My_bool table_no_key= false; For (table_list *table= tables; TABLE; table= table->next_global) {if (Table->placeholder ())        continue;+ + if (table->table->s->primary_key >= max_key) + table_no_key= true;           if (table->table->s->table_category = = Table_category_performance | | Table->table->s->table_category = = table_category_log) @@ -4680,6 +4684,13 @@ -4680,6 * LOG in row format!         */set_current_stmt_binlog_format_row_if_mixed ();                 }+/*if There is a table without any primary key,log in stmt format*/+ else if (Table_no_key &&+ (VARIABLES.BINLOG_USE_STMT_FOR_NON_PK) &&+ (Variables.binlog_format = = Binlog_format_ ROW) &&+ (Lex->sql_command = = Sqlcom_delete | | + Lex->sql_command = = SQLC       Om_update) + Clear_current_stmt_binlog_format_row (); }     }

"reprinted" standby because the table has no primary key causes delay