Bug #52517 Regression in ROW level replication performance with partitions
Submitted: 1 Apr 2010 3:10 Modified: 14 Oct 2010 13:00
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.45 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: partitioning, qc, regression, replication

[1 Apr 2010 3:10] Peter Zaitsev
Description:
With same schema and statement as http://bugs.mysql.com/bug.php?id=52455
I can see this statement replicates about 50 times faster in MySQL 5.1.30 compared to MySQL 5.1.45    In STATEMENT level replication both of them have the same time.

How to repeat:
Repeating Statement in test1.sql 9 times:

MySQL 5.1.30
ROW:

slave2 [localhost] {msandbox} ((none)) > select master_pos_wait("mysql-bin.000001",13405838);
+----------------------------------------------+
| master_pos_wait("mysql-bin.000001",13405838) |
+----------------------------------------------+
| 9 |
+----------------------------------------------+
1 row in set (3.08 sec)

Statement:

slave2 [localhost] {msandbox} ((none)) > select master_pos_wait("mysql-bin.000001",19695515);
+----------------------------------------------+
| master_pos_wait("mysql-bin.000001",19695515) |
+----------------------------------------------+
| 9 |
+----------------------------------------------+
1 row in set (1 min 40.53 sec)

MySQL 5.1.45

ROW:

slave2 [localhost] {msandbox} ((none)) > select master_pos_wait("mysql-bin.000001",9451870);
+---------------------------------------------+
| master_pos_wait("mysql-bin.000001",9451870) |
+---------------------------------------------+
| 9 |
+---------------------------------------------+
1 row in set (1 min 37.92 sec)

STATEMENT:

slave2 [localhost] {msandbox} ((none)) > select master_pos_wait("mysql-bin.000001",15741547);
+----------------------------------------------+
| master_pos_wait("mysql-bin.000001",15741547) |
+----------------------------------------------+
| 9 |
+----------------------------------------------+
1 row in set (1 min 41.65 sec)
[1 Apr 2010 3:11] Peter Zaitsev
Sorry for submitting but on April 1st.  This is not the joke. Repeat this is not the joke :)
[1 Apr 2010 7:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[1 Apr 2010 16:09] Georgi Kodinov
Peter, your reports are always treated with the appropriate seriousness. Happy 1st of April ;-)
[2 Apr 2010 11:38] Yasufumi Kinoshita
The difference is caused by disabling
HA_PRIMARY_KEY_REQUIRED_FOR_POSITION table_flag of partition handler.

table->file->rnd_pos_by_record(table->record[0])
seems to call ha_innobase::change_active_index only 2 times per record.
But 
table->file->ha_index_init(0, FALSE)
seems to call ha_innobase::change_active_index for all partition per records...

like #52455, is prune_partitions() needed before the ha_index_init(0, FALSE) ?
[2 Apr 2010 11:39] Yasufumi Kinoshita
Sorry, that is at
Rows_log_event::find_row()
[13 Apr 2010 1:12] Mattias Jonsson
Might be solved by delaying the index_init call to the partitions until there really will be an index call to that partition, like bulk_insert is delayed in the partitioning handler until there is an insert.
[24 May 2010 7:00] Mattias Jonsson
Changing to Server: Partitioning, since the overhead is due to unnecessary index_init calls.
[9 Jul 2010 11: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/113229

3394 Mattias Jonsson	2010-07-09
      Bug#52517: Regression in ROW level replication performance with partitions
      
      In bug-28430 HA_PRIMARY_KEY_REQUIRED_FOR_POSITION
      was disabled in the partitioning engine in the first patch,
      That bug was later fixed a second time, but that flag
      was not removed.
      
      No need to disable this flag, as it leads to bad
      choise in row replication.
     @ sql/ha_partition.h
        Not disabling HA_PRIMARY_KEY_REQUIRED_FOR_POSITION flag.
        Updated comment (has nothing to do with hidden key.
     @ sql/handler.h
        Updated comments to about HA_PRIMARY_KEY_REQUIRED_FOR_POSITION.
[9 Jul 2010 11:20] Mattias Jonsson
No test case included in the patch, since performance only bug.

Manually tested:

              52517 (time to replicate by row)
              Broken           Fixed
              parts  no parts  parts  no parts
first insert   3.41      1.53   2.05      1.75 (no updates)
Second insert *55.4*     1.53   2.46      1.52 (all updates)
update WHERE  *55.7*     1.48   2.43      1.47 (same parse, all updates)
update        *54.9*     1.48   2.45      1.47 (no parse, all updates)
insert select  1.87      1.05   1.82      1.03 (no updates)
insert select *57.3*     1.54   3.34      1.55 (all updates)
insert select *56.6*     2.06   3.26      1.47 (same table, all updates)
first replace  1.80      1.02   1.83      0.99 (no replace)
second replace 0.00      0.00   0.00      0.00 (all replace, no real change)
replace select 1.81      1.01   1.79      1.02 (no replace)
replace select 0.00      0.00   0.00      0.00 (all replace, no real change)
replace select 0.00      0.00   0.00      0.00 (same table, all replace, no real change)

Note that the delete operation on the slave also took ~1 min (54.3 s)!
which was also fixed
Running with same setup as rpl.rpl_innodb_bug28430

Bug was a regression by bug#28430.
[9 Jul 2010 13:17] Mattias Jonsson
pushed to mysql-5.1-bugteam and mysql-trunk-merge
[9 Jul 2010 13:41] Mattias Jonsson
The regression was introduced in bug#39084 (5.1.31). My mistake.
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[28 Jul 2010 14:20] Jon Stephens
Documented bugfix in the 5.1.50 and 5.5.6 changelogs as follows:

        UPDATE and INSERT statements affecting partitioned tables
        performed poorly when using row-based replication.

Closed.
[19 Aug 2010 15:40] Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[14 Oct 2010 8:33] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:49] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:03] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 13:00] Jon Stephens
Already documented in 5.1.50 changelog.

No new changelog entries required. 

Setting back to Closed.