Bug #35843 Slow replication slave when using partitioned myisam table
Submitted: 4 Apr 2008 19:56 Modified: 17 Oct 2008 17:23
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.1.23 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any

[4 Apr 2008 19:56] Mattias Jonsson 
Description:
After fixing bug#26527 the performance of a applying a row-based binlog on a partitioned myisam table is very slow.

How to repeat:
replicate some row based inserts into a heavily partitioned myisam table. It is easy to verify that all of the partitions *.MYI files are updated, even those not changed. (This does not happen in 5.1.22 or before the fix for Bug#26527)

Suggested fix:
Write_rows_log_event::do_before_row_operations in log_event.cc:
7323	  /*
7324	    TODO: the cluster team (Tomas?) says that it's better if the engine knows
7325	    how many rows are going to be inserted, then it can allocate needed memory
7326	    from the start.
7327	  */
7328	  m_table->file->ha_start_bulk_insert(0);

Cluster team is correct, on a partitioned myisam this leads to a repair of the index for every partition for every binlog statement for such table.

If possible, use a correct or approximate value instead of 0. (0 means unknown or 'a lot')
[4 Apr 2008 20:16] Mattias Jonsson 
I also reported Bug#35845 which could increase the performance of partitioned tables regarding to this issue.
[14 Apr 2008 16:45] Susanne Ebrecht 
Verified as described.
[22 Jul 2008 23:43] Matt Lohier 
Hi,

Thank you for that bug report. 
I think I am experiencing the same issues:
Linux EMT64, AMD, 2 x dual core 2.4Ghx, 4GB RAM, MySQL 5.1.25 for Master server.
Linux EMT64, Intel, dual core 2.6Ghz, 8GB RAM, MySQL 5.1.25 for Slave server.
RBR between master and slave (for some special stored procedure).
MyISAM partitioned tables (30 partitions per table - each MYD~200MB & MYI~23MB)

What i get is:
- VERY VERY slow slave replication for RBR: unable to catch up.
- High CPU 100% reported by top on slave
 
Can we please see a resolution for this bug ASAP - it's a very serious issue.

Rgds / Matt
[9 Sep 2008 9:18] 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/53582

2666 He Zhenxing	2008-09-09
      BUG#35843 Slow replication slave when using partitioned myisam table
      
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, a new function 'estimate_rows'
      is added for Rows_log_event to estimate the number of rows of current
      rows log event and use the estimated number to allocate cache memory.
      ******
      BUG#35843 Slow replication slave when using partitioned myisam table
      
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, a new function 'estimate_rows'
      is added to estimate the number of rows of current rows log event and
      use the estimated number to allocate memory
[9 Sep 2008 9:22] 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/53583

2666 He Zhenxing	2008-09-09
      BUG#35843 Slow replication slave when using partitioned myisam table
      
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, a new function 'estimate_rows'
      is added for Rows_log_event to estimate the number of rows of current
      rows log event and use the estimated number to allocate cache memory.
[10 Sep 2008 9:15] 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/53669

2666 He Zhenxing	2008-09-10
      BUG#35843 Slow replication slave when using partitioned myisam table
      
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, the number of rows of current 
      rows log event is estimated and used to setup storage engine for bulk
      inserts.
[26 Sep 2008 11:49] 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/54541

2690 He Zhenxing	2008-09-26
      BUG#35843 Slow replication slave when using partitioned myisam table
            
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, the number of rows of current 
      rows log event is estimated and used to setup storage engine for bulk
      inserts.
[30 Sep 2008 13:23] 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/54766

2841 He Zhenxing	2008-09-29 [merge]
      Merge BUG#35843
[30 Sep 2008 13:24] 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/54767

2841 He Zhenxing	2008-09-29 [merge]
      Merge BUG#35843
[30 Sep 2008 13:24] 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/54768

2690 He Zhenxing	2008-09-26
      BUG#35843 Slow replication slave when using partitioned myisam table
            
      In order to improve the performance when replicating to partitioned
      myisam tables with row-based format, the number of rows of current 
      rows log event is estimated and used to setup storage engine for bulk
      inserts.
[7 Oct 2008 19:11] Paul DuBois 
Noted in 5.1.29 changelog.

Replication to partitioned MyISAM tables could be slow with row-based
binary logging.  

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 18:00] Bugs System 
Pushed into 5.1.30  (revid:hezx@mysql.com-20080926093947-0c18pcwrx09ic1r8) (version source revid:kgeorge@mysql.com-20081001094725-vf4mqjkmajlm22qy) (pib:4)
[9 Oct 2008 18:29] Paul DuBois 
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:46] Bugs System 
Pushed into 6.0.8-alpha  (revid:hezx@mysql.com-20080929134117-l0nusdk4c3c4ve2a) (version source revid:kgeorge@mysql.com-20081001100520-exs1tupnfanm1mij) (pib:5)
[17 Oct 2008 17:23] Paul DuBois 
Noted in 6.0.8 changelog.
[28 Oct 2008 21:06] Bugs System 
Pushed into 5.1.29-ndb-6.2.17  (revid:hezx@mysql.com-20080926093947-0c18pcwrx09ic1r8) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:24] Bugs System 
Pushed into 5.1.29-ndb-6.3.19  (revid:hezx@mysql.com-20080926093947-0c18pcwrx09ic1r8) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:50] Bugs System 
Pushed into 5.1.29-ndb-6.4.0  (revid:hezx@mysql.com-20080926093947-0c18pcwrx09ic1r8) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)