Description:
There is a regression in replication performance in MySQL community 5.6 and 5.7 when using partitioned and large tables.
In MySQL 5.5.35, large updates over partitioned and large tables are executed in slave instances at same speed than in master.
When executing same large updates in MySQL 5.6.35 or 5.7.17, replication performance slows down and slave instance gets behind master for a long time.
If I remove paritioning for this table in slave instance in MySQL 5.6 or 5.7, replication performance is the same as showed in MySQL 5.5.
How to repeat:
Requirements:
A) Master is using row based replication.
Procedure:
1) Create a partitioned and large table in MySQL 5.6, for example:
CREATE TABLE `large_table` (
`sid` bigint(20) NOT NULL AUTO_INCREMENT,
`id` bigint(20) NOT NULL,
`version` bigint(20) NOT NULL DEFAULT '0',
`accrued_interest` decimal(25,13) DEFAULT NULL,
`accumulated_interest` decimal(25,13) DEFAULT NULL,
`amount` decimal(25,13) DEFAULT NULL,
`apr360` decimal(25,13) DEFAULT NULL,
`apr` decimal(25,13) DEFAULT NULL,
`as_of_date` datetime DEFAULT NULL,
`average_days_delinquent` float DEFAULT '0',
`balance` decimal(25,13) DEFAULT NULL,
`charged_off_balance` decimal(25,13) DEFAULT NULL,
`charged_off_by_id` int(11) NOT NULL DEFAULT '0',
`charged_off_date` datetime DEFAULT NULL,
`charged_off_fees` decimal(25,13) DEFAULT NULL,
`charged_off_interest` decimal(25,13) DEFAULT NULL,
`charged_off_principal` decimal(25,13) DEFAULT NULL,
`collateral_number` varchar(255) DEFAULT NULL,
`creator_id` int(11) NOT NULL DEFAULT '0',
`credit_line_offset` int(11) NOT NULL DEFAULT '0',
`credit_line_strategy` varchar(255) DEFAULT NULL,
`daily_rate` decimal(25,13) DEFAULT NULL,
`date_created` datetime DEFAULT NULL,
`days_delinquent` int(11) NOT NULL DEFAULT '0',
`disbursement_date` datetime DEFAULT NULL,
`fees_due` decimal(25,13) DEFAULT NULL,
`first_payment_amount_override` decimal(25,13) DEFAULT NULL,
`first_payment_amount` decimal(25,13) DEFAULT NULL,
`first_payment_date_override` datetime DEFAULT NULL,
`first_payment_date` datetime DEFAULT NULL,
`interest_due` decimal(25,13) DEFAULT NULL,
`last_full_payment_date` datetime DEFAULT NULL,
`last_paycheck_date` datetime DEFAULT NULL,
`last_payment_date` datetime DEFAULT NULL,
`last_payment_event_date` datetime DEFAULT NULL,
`last_updated` datetime DEFAULT NULL,
`late_date` datetime DEFAULT NULL,
`loan_amount` decimal(25,13) DEFAULT NULL,
`loan_rules` varchar(255) DEFAULT NULL,
`max_days_delinquent` int(11) NOT NULL DEFAULT '0',
`migrated` bit(1) NOT NULL DEFAULT b'0',
`minimum_payment_to_rollback_delinquency119` decimal(25,13) DEFAULT NULL,
`minimum_payment_to_rollback_delinquency29` decimal(25,13) DEFAULT NULL,
`minimum_payment_to_rollback_delinquency59` decimal(25,13) DEFAULT NULL,
`minimum_payment_to_rollback_delinquency89` decimal(25,13) DEFAULT NULL,
`monthly_rate` decimal(25,13) DEFAULT NULL,
`months` int(11) NOT NULL DEFAULT '0',
`actual_months` int(11) DEFAULT '0',
`next_late_fee_date` datetime DEFAULT NULL,
`next_minimum_payment_to_rollback_delinquency119` decimal(25,13) DEFAULT NULL,
`next_minimum_payment_to_rollback_delinquency29` decimal(25,13) DEFAULT NULL,
`next_minimum_payment_to_rollback_delinquency59` decimal(25,13) DEFAULT NULL,
`next_minimum_payment_to_rollback_delinquency89` decimal(25,13) DEFAULT NULL,
`next_payment_due_date` datetime DEFAULT NULL,
`number_of_payments` int(11) NOT NULL DEFAULT '0',
`origination_fee` decimal(25,13) DEFAULT NULL,
`paid_date` datetime DEFAULT NULL,
`payment_amount_override` decimal(25,13) DEFAULT NULL,
`payment_amount` decimal(25,13) DEFAULT NULL,
`payment_day` int(11) NOT NULL DEFAULT '0',
`payment_day2` int(11) NOT NULL DEFAULT '0',
`payment_frequency` int(11) DEFAULT NULL,
`payment_period` int(11) NOT NULL DEFAULT '0',
`periodic_rate` decimal(25,13) DEFAULT NULL,
`pf_loan` bigint(20) NOT NULL DEFAULT '0',
`pool_no` varchar(255) DEFAULT NULL,
`principal_due` decimal(25,13) DEFAULT NULL,
`qb_client_id` bigint(20) NOT NULL DEFAULT '0',
`qb_loan_id` bigint(20) NOT NULL DEFAULT '0',
`real_apr` decimal(25,13) DEFAULT NULL,
`rewritten_date` datetime DEFAULT NULL,
`rewritten_loan_id` bigint(20) NOT NULL DEFAULT '0',
`sip_client_id` bigint(20) NOT NULL DEFAULT '0',
`sip_loan_id` bigint(20) NOT NULL DEFAULT '0',
`snapshot_date` datetime NOT NULL,
`status` varchar(255) DEFAULT NULL,
`stop_credit_reporting` bit(1) NOT NULL DEFAULT b'0',
`store_id` int(11) NOT NULL DEFAULT '0',
`sub_pool_no` varchar(255) DEFAULT NULL,
`total_accrued_interest` decimal(25,13) DEFAULT NULL,
`total_additional_payments` int(11) NOT NULL DEFAULT '0',
`total_charged_late_fees` decimal(25,13) DEFAULT NULL,
`total_extra_interest` decimal(25,13) DEFAULT NULL,
`total_extra_principal` decimal(25,13) DEFAULT NULL,
`total_fees` decimal(25,13) DEFAULT NULL,
`total_paid_amount` decimal(25,13) DEFAULT NULL,
`total_paid_fees` decimal(25,13) DEFAULT NULL,
`total_paid_installments_amount` decimal(25,13) DEFAULT NULL,
`total_paid_interest` decimal(25,13) DEFAULT NULL,
`total_paid_principal` decimal(25,13) DEFAULT NULL,
`total_payment_due` decimal(25,13) DEFAULT NULL,
`total_payment_overdue` decimal(25,13) DEFAULT NULL,
`updater_id` int(11) NOT NULL DEFAULT '0',
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`first_full_payment_date` datetime DEFAULT NULL,
`final_scheduled_payment_date` datetime DEFAULT NULL,
`actual_interest` decimal(25,13) DEFAULT '0.0000000000000',
`scheduled_principal_due` decimal(25,13) DEFAULT '0.0000000000000',
`expected_accrued_interest` decimal(25,13) DEFAULT '0.0000000000000',
`payoff_amount` decimal(25,13) DEFAULT '0.0000000000000',
`origination_fees_due` decimal(25,13) DEFAULT '0.0000000000000',
`collateral_no` int(11) NOT NULL DEFAULT '0',
`previous_late_fee_date1` datetime DEFAULT NULL,
`previous_late_fee_date2` datetime DEFAULT NULL,
`delinquency_date` datetime DEFAULT NULL,
`total_number_of_payments` int(11) DEFAULT '0',
`refinanced_loan_id` bigint(20) NOT NULL DEFAULT '0',
`refinanced_date` datetime DEFAULT NULL,
`effective_through` date NOT NULL DEFAULT '0000-00-00',
`prior` bigint(20) DEFAULT NULL,
`starting_receivables` decimal(25,13) DEFAULT '0.0000000000000',
`ending_receivables` decimal(25,13) DEFAULT '0.0000000000000',
`renewal` int(2) NOT NULL DEFAULT '0',
`refinance_ind` int(2) NOT NULL DEFAULT '0',
`product_type` varchar(45) NOT NULL DEFAULT 'CONSUMER_INSTALLMENT_LOAN',
`channel_id` int(11) DEFAULT NULL,
`merchant_normal` int(2) NOT NULL DEFAULT '0',
`merchant_adjust` int(2) NOT NULL DEFAULT '0',
`payoff_balance_diff` decimal(25,13) DEFAULT '0.0000000000000',
`eligible_for_pledge_bond_2013` int(2) NOT NULL DEFAULT '0',
`eligible_for_pledge_bond_2014` int(2) NOT NULL DEFAULT '0',
`pledged` int(2) NOT NULL DEFAULT '0',
`pledged_to` varchar(255) DEFAULT NULL,
`pledge_date` date DEFAULT NULL,
`pledged_amount` decimal(25,13) DEFAULT '0.0000000000000',
`auto_pledged_rw` int(2) DEFAULT '0',
`ck_app_score` int(11) DEFAULT NULL,
`pf_score` int(11) DEFAULT NULL,
`rationalized_pf_score` int(11) DEFAULT NULL,
`vantage_score` int(11) DEFAULT NULL,
`payments` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`interest_billed` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`fees_billed` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`gross_charge_off` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`transfers_rewrite` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`overpaid_principal` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`retail_installment_adj` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`misc_adjustment` decimal(25,13) NOT NULL DEFAULT '0.0000000000000',
`remaining_months` int(11) NOT NULL,
`funding_months` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`sid`,`effective_through`),
UNIQUE KEY `loan_summ_detail_id_fact_date_idx` (`id`,`effective_through`),
KEY `loan_summ_detail_fact_date_id_idx` (`effective_through`,`id`),
KEY `loan_summ_detail_pf_loan_date_idx` (`pf_loan`,`effective_through`),
KEY `loan_summ_detail_fact_date_sip_loan_id_pf_loan_idx` (`effective_through`,`sip_loan_id`,`pf_loan`),
KEY `loan_summ_detail_pledging_idx` (`pledge_date`,`pledged_to`),
KEY `loan_summ_detail_fact_date_pledged_to_idx` (`effective_through`,`pledged_to`),
KEY `loan_summ_detail_fact_date_sip_client_id_pf_loan_idx` (`effective_through`,`sip_client_id`,`pf_loan`),
KEY `loan_summ_detail_prior_ref` (`prior`)
) ENGINE=InnoDB AUTO_INCREMENT=1197238680 DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(effective_through)
(PARTITION p20150325 VALUES LESS THAN ('2015-03-26') ENGINE = InnoDB,
PARTITION p20150326 VALUES LESS THAN ('2015-03-27') ENGINE = InnoDB,
..
PARTITION p20170225 VALUES LESS THAN ('2017-02-26') ENGINE = InnoDB,
PARTITION p20170226 VALUES LESS THAN ('2017-02-27') ENGINE = InnoDB,
PARTITION p20170227 VALUES LESS THAN ('2017-02-28') ENGINE = InnoDB,
PARTITION p20170228 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB);
2) Insert random data in the table:
DELIMITER $$
CREATE PROCEDURE fill_large_table()
BEGIN
DECLARE sid INT ;
DECLARE id INT ;
DECLARE prior INT ;
DECLARE date date ;
DECLARE date_p date ;
SET sid = 100000 ;
SET id = 1 ;
SET prior = 1 ;
SET date = '2017:02:28' ;
SET date_p = '2017:02:27' ;
WHILE sid <= 200000 DO
insert into large_table (sid,id,effective_through) values (prior,id,date_p), (sid,id,date);
SET sid = sid + 1 ;
SET prior = prior + 1 ;
SET id = id + 1;
END WHILE;
END$$
DELIMITER ;
call fill_large_table();
3) Execute large update in this table:
update `large_table` n inner join `large_table` o
on n.effective_through = '2017-02-28' and o.effective_through = date_sub('2017-02-28', interval 1 day) and
n.id = o.id
set n.prior = o.sid
where n.effective_through = '2017-02-28';
4) Check replication lag using show slave status. Compare replication performance with MySQL version 5.5 or with unpartitioned table.
Description: There is a regression in replication performance in MySQL community 5.6 and 5.7 when using partitioned and large tables. In MySQL 5.5.35, large updates over partitioned and large tables are executed in slave instances at same speed than in master. When executing same large updates in MySQL 5.6.35 or 5.7.17, replication performance slows down and slave instance gets behind master for a long time. If I remove paritioning for this table in slave instance in MySQL 5.6 or 5.7, replication performance is the same as showed in MySQL 5.5. How to repeat: Requirements: A) Master is using row based replication. Procedure: 1) Create a partitioned and large table in MySQL 5.6, for example: CREATE TABLE `large_table` ( `sid` bigint(20) NOT NULL AUTO_INCREMENT, `id` bigint(20) NOT NULL, `version` bigint(20) NOT NULL DEFAULT '0', `accrued_interest` decimal(25,13) DEFAULT NULL, `accumulated_interest` decimal(25,13) DEFAULT NULL, `amount` decimal(25,13) DEFAULT NULL, `apr360` decimal(25,13) DEFAULT NULL, `apr` decimal(25,13) DEFAULT NULL, `as_of_date` datetime DEFAULT NULL, `average_days_delinquent` float DEFAULT '0', `balance` decimal(25,13) DEFAULT NULL, `charged_off_balance` decimal(25,13) DEFAULT NULL, `charged_off_by_id` int(11) NOT NULL DEFAULT '0', `charged_off_date` datetime DEFAULT NULL, `charged_off_fees` decimal(25,13) DEFAULT NULL, `charged_off_interest` decimal(25,13) DEFAULT NULL, `charged_off_principal` decimal(25,13) DEFAULT NULL, `collateral_number` varchar(255) DEFAULT NULL, `creator_id` int(11) NOT NULL DEFAULT '0', `credit_line_offset` int(11) NOT NULL DEFAULT '0', `credit_line_strategy` varchar(255) DEFAULT NULL, `daily_rate` decimal(25,13) DEFAULT NULL, `date_created` datetime DEFAULT NULL, `days_delinquent` int(11) NOT NULL DEFAULT '0', `disbursement_date` datetime DEFAULT NULL, `fees_due` decimal(25,13) DEFAULT NULL, `first_payment_amount_override` decimal(25,13) DEFAULT NULL, `first_payment_amount` decimal(25,13) DEFAULT NULL, `first_payment_date_override` datetime DEFAULT NULL, `first_payment_date` datetime DEFAULT NULL, `interest_due` decimal(25,13) DEFAULT NULL, `last_full_payment_date` datetime DEFAULT NULL, `last_paycheck_date` datetime DEFAULT NULL, `last_payment_date` datetime DEFAULT NULL, `last_payment_event_date` datetime DEFAULT NULL, `last_updated` datetime DEFAULT NULL, `late_date` datetime DEFAULT NULL, `loan_amount` decimal(25,13) DEFAULT NULL, `loan_rules` varchar(255) DEFAULT NULL, `max_days_delinquent` int(11) NOT NULL DEFAULT '0', `migrated` bit(1) NOT NULL DEFAULT b'0', `minimum_payment_to_rollback_delinquency119` decimal(25,13) DEFAULT NULL, `minimum_payment_to_rollback_delinquency29` decimal(25,13) DEFAULT NULL, `minimum_payment_to_rollback_delinquency59` decimal(25,13) DEFAULT NULL, `minimum_payment_to_rollback_delinquency89` decimal(25,13) DEFAULT NULL, `monthly_rate` decimal(25,13) DEFAULT NULL, `months` int(11) NOT NULL DEFAULT '0', `actual_months` int(11) DEFAULT '0', `next_late_fee_date` datetime DEFAULT NULL, `next_minimum_payment_to_rollback_delinquency119` decimal(25,13) DEFAULT NULL, `next_minimum_payment_to_rollback_delinquency29` decimal(25,13) DEFAULT NULL, `next_minimum_payment_to_rollback_delinquency59` decimal(25,13) DEFAULT NULL, `next_minimum_payment_to_rollback_delinquency89` decimal(25,13) DEFAULT NULL, `next_payment_due_date` datetime DEFAULT NULL, `number_of_payments` int(11) NOT NULL DEFAULT '0', `origination_fee` decimal(25,13) DEFAULT NULL, `paid_date` datetime DEFAULT NULL, `payment_amount_override` decimal(25,13) DEFAULT NULL, `payment_amount` decimal(25,13) DEFAULT NULL, `payment_day` int(11) NOT NULL DEFAULT '0', `payment_day2` int(11) NOT NULL DEFAULT '0', `payment_frequency` int(11) DEFAULT NULL, `payment_period` int(11) NOT NULL DEFAULT '0', `periodic_rate` decimal(25,13) DEFAULT NULL, `pf_loan` bigint(20) NOT NULL DEFAULT '0', `pool_no` varchar(255) DEFAULT NULL, `principal_due` decimal(25,13) DEFAULT NULL, `qb_client_id` bigint(20) NOT NULL DEFAULT '0', `qb_loan_id` bigint(20) NOT NULL DEFAULT '0', `real_apr` decimal(25,13) DEFAULT NULL, `rewritten_date` datetime DEFAULT NULL, `rewritten_loan_id` bigint(20) NOT NULL DEFAULT '0', `sip_client_id` bigint(20) NOT NULL DEFAULT '0', `sip_loan_id` bigint(20) NOT NULL DEFAULT '0', `snapshot_date` datetime NOT NULL, `status` varchar(255) DEFAULT NULL, `stop_credit_reporting` bit(1) NOT NULL DEFAULT b'0', `store_id` int(11) NOT NULL DEFAULT '0', `sub_pool_no` varchar(255) DEFAULT NULL, `total_accrued_interest` decimal(25,13) DEFAULT NULL, `total_additional_payments` int(11) NOT NULL DEFAULT '0', `total_charged_late_fees` decimal(25,13) DEFAULT NULL, `total_extra_interest` decimal(25,13) DEFAULT NULL, `total_extra_principal` decimal(25,13) DEFAULT NULL, `total_fees` decimal(25,13) DEFAULT NULL, `total_paid_amount` decimal(25,13) DEFAULT NULL, `total_paid_fees` decimal(25,13) DEFAULT NULL, `total_paid_installments_amount` decimal(25,13) DEFAULT NULL, `total_paid_interest` decimal(25,13) DEFAULT NULL, `total_paid_principal` decimal(25,13) DEFAULT NULL, `total_payment_due` decimal(25,13) DEFAULT NULL, `total_payment_overdue` decimal(25,13) DEFAULT NULL, `updater_id` int(11) NOT NULL DEFAULT '0', `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `first_full_payment_date` datetime DEFAULT NULL, `final_scheduled_payment_date` datetime DEFAULT NULL, `actual_interest` decimal(25,13) DEFAULT '0.0000000000000', `scheduled_principal_due` decimal(25,13) DEFAULT '0.0000000000000', `expected_accrued_interest` decimal(25,13) DEFAULT '0.0000000000000', `payoff_amount` decimal(25,13) DEFAULT '0.0000000000000', `origination_fees_due` decimal(25,13) DEFAULT '0.0000000000000', `collateral_no` int(11) NOT NULL DEFAULT '0', `previous_late_fee_date1` datetime DEFAULT NULL, `previous_late_fee_date2` datetime DEFAULT NULL, `delinquency_date` datetime DEFAULT NULL, `total_number_of_payments` int(11) DEFAULT '0', `refinanced_loan_id` bigint(20) NOT NULL DEFAULT '0', `refinanced_date` datetime DEFAULT NULL, `effective_through` date NOT NULL DEFAULT '0000-00-00', `prior` bigint(20) DEFAULT NULL, `starting_receivables` decimal(25,13) DEFAULT '0.0000000000000', `ending_receivables` decimal(25,13) DEFAULT '0.0000000000000', `renewal` int(2) NOT NULL DEFAULT '0', `refinance_ind` int(2) NOT NULL DEFAULT '0', `product_type` varchar(45) NOT NULL DEFAULT 'CONSUMER_INSTALLMENT_LOAN', `channel_id` int(11) DEFAULT NULL, `merchant_normal` int(2) NOT NULL DEFAULT '0', `merchant_adjust` int(2) NOT NULL DEFAULT '0', `payoff_balance_diff` decimal(25,13) DEFAULT '0.0000000000000', `eligible_for_pledge_bond_2013` int(2) NOT NULL DEFAULT '0', `eligible_for_pledge_bond_2014` int(2) NOT NULL DEFAULT '0', `pledged` int(2) NOT NULL DEFAULT '0', `pledged_to` varchar(255) DEFAULT NULL, `pledge_date` date DEFAULT NULL, `pledged_amount` decimal(25,13) DEFAULT '0.0000000000000', `auto_pledged_rw` int(2) DEFAULT '0', `ck_app_score` int(11) DEFAULT NULL, `pf_score` int(11) DEFAULT NULL, `rationalized_pf_score` int(11) DEFAULT NULL, `vantage_score` int(11) DEFAULT NULL, `payments` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `interest_billed` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `fees_billed` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `gross_charge_off` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `transfers_rewrite` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `overpaid_principal` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `retail_installment_adj` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `misc_adjustment` decimal(25,13) NOT NULL DEFAULT '0.0000000000000', `remaining_months` int(11) NOT NULL, `funding_months` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`sid`,`effective_through`), UNIQUE KEY `loan_summ_detail_id_fact_date_idx` (`id`,`effective_through`), KEY `loan_summ_detail_fact_date_id_idx` (`effective_through`,`id`), KEY `loan_summ_detail_pf_loan_date_idx` (`pf_loan`,`effective_through`), KEY `loan_summ_detail_fact_date_sip_loan_id_pf_loan_idx` (`effective_through`,`sip_loan_id`,`pf_loan`), KEY `loan_summ_detail_pledging_idx` (`pledge_date`,`pledged_to`), KEY `loan_summ_detail_fact_date_pledged_to_idx` (`effective_through`,`pledged_to`), KEY `loan_summ_detail_fact_date_sip_client_id_pf_loan_idx` (`effective_through`,`sip_client_id`,`pf_loan`), KEY `loan_summ_detail_prior_ref` (`prior`) ) ENGINE=InnoDB AUTO_INCREMENT=1197238680 DEFAULT CHARSET=utf8 PARTITION BY RANGE COLUMNS(effective_through) (PARTITION p20150325 VALUES LESS THAN ('2015-03-26') ENGINE = InnoDB, PARTITION p20150326 VALUES LESS THAN ('2015-03-27') ENGINE = InnoDB, .. PARTITION p20170225 VALUES LESS THAN ('2017-02-26') ENGINE = InnoDB, PARTITION p20170226 VALUES LESS THAN ('2017-02-27') ENGINE = InnoDB, PARTITION p20170227 VALUES LESS THAN ('2017-02-28') ENGINE = InnoDB, PARTITION p20170228 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB); 2) Insert random data in the table: DELIMITER $$ CREATE PROCEDURE fill_large_table() BEGIN DECLARE sid INT ; DECLARE id INT ; DECLARE prior INT ; DECLARE date date ; DECLARE date_p date ; SET sid = 100000 ; SET id = 1 ; SET prior = 1 ; SET date = '2017:02:28' ; SET date_p = '2017:02:27' ; WHILE sid <= 200000 DO insert into large_table (sid,id,effective_through) values (prior,id,date_p), (sid,id,date); SET sid = sid + 1 ; SET prior = prior + 1 ; SET id = id + 1; END WHILE; END$$ DELIMITER ; call fill_large_table(); 3) Execute large update in this table: update `large_table` n inner join `large_table` o on n.effective_through = '2017-02-28' and o.effective_through = date_sub('2017-02-28', interval 1 day) and n.id = o.id set n.prior = o.sid where n.effective_through = '2017-02-28'; 4) Check replication lag using show slave status. Compare replication performance with MySQL version 5.5 or with unpartitioned table.