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)
• Status: Closed
• 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:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (version source revid:[e-mail]-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

[23 Jul 2010 12:33] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100723121929-90e9zemk3jkr2ocy) (version source revid:[e-mail]-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:[e-mail]-20100819151858-muaaor6jojb5ouzj) (version source revid:[e-mail]-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:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-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:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-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:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-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.