Bug #85352 Replication regression with RBR and partitioned tables
Submitted: 8 Mar 2017 0:44 Modified: 14 Aug 2017 12:36
Reporter: Juan Arruti Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S5 (Performance)
Version:5.6, 5.7, 5.6.35 OS:Any
Assigned to: CPU Architecture:Any

[8 Mar 2017 0:44] Juan Arruti
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.
[8 Mar 2017 11:56] MySQL Verification Team
Hello Juan Arruti,

Thank you for the report and test case.

Thanks,
Umesh
[21 Mar 2017 17:48] Neeraj Wadhwani
Any update on this?
[14 Aug 2017 12:36] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.38, 5.7.20, 8.0.3 release, and here's the changelog entry:

Replication lag occurred on slave instances during large update
operations on tables with many partitions.