Bug #69585 low-priority-updates not working on replication slave
Submitted: 26 Jun 2013 11:23 Modified: 22 Jul 2014 18:40
Reporter: adrian smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.5.30 x64, 5.5.39, 5.6.21, 5.7.5 OS:Any (MS Windows W2K8 64bit, Linux)
Assigned to: CPU Architecture:Any
Tags: low-priority-updates, myisam, RBR, row-based replication
Triage: Needs Triage: D3 (Medium)

[26 Jun 2013 11:23] adrian smith
Description:
Hi, 
We have upgraded from 5.0.15 32-bit to5.5.30 64bit on Windows Server 2008.  We're running row-based replication and the low_priority_updates option (either in the option file or as a service parameter) doesn't seem to have the effect it is documented to have (and which they did in 5.0.15 (statement-based replication.)
while replication is running, which is elevating selects priorities above replication inserts/updates so that you don't get a situation as follows:

1.select running (long)
2.insert/update waiting for 1
3.select waiting for 2
4.select waiting for 2
5.select waiting for 2
etc... 
3,4 and 5 should still run without waiting.

mysqladmin debug shows:
Thread database.table_name          Locked/Waiting        Lock_type

7       autotrak.vehpos             Waiting - write       High priority write lock
394     autotrak.vehpos             Waiting - read        Low priority read lock
413     autotrak.vehpos             Locked - read         Low priority read lock

#ini file
low-priority-updates=1
startup option --low-priority-updates=1 also doesn't work

mysql> show global variables like '%priority%';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| low_priority_updates     | ON    |
| sql_low_priority_updates | ON    |
+--------------------------+-------+
2 rows in set (0.00 sec)

How to repeat:
-Run MySQL 5.5.30 64-bit with row based replication with MyISAM tables on W2K8 server x64 
-run a long select on a table that will lock a table being updated by the replication thread
-run one or more similar long selects accessing the same table - these will wait for the replication insert/update to go through 

Suggested fix:
Please fix the option low_priority updates to behave as documented.

We have temporarily worked around this by running HIGH_PRIORITY selects but this can't be done in 
subqueries
stored functions
unions

and this is a major pain and requires rewriting many of our queries.  It means doing a blank left join to tables that are used by any of these constructs in the main query e.g.
select high_priority getvehspid(vehpos.id) from vehpos where id=1
left join vehsp on vehsp.id=null
left join etc. etc.
in order to force the priority of the selects contained in these constructs
[1 Aug 2013 8:35] adrian smith
Hi, 
I've upgraded this to S1 because the issue is creating total unresponsiveness for customers on a regular basis when running reports.  Our workaround alleviates the situation somewhat but it is impractical to implement fully to totally remove the issue.

Thanks
Adrian
[22 Jul 2014 18:38] Sveta Smirnova
Thank you for the report.

Verified as described. One more workaround: use SBR.
[22 Jul 2014 18:41] Sveta Smirnova
test case for MTR

Attachment: rpl_bug69585.test (application/octet-stream, text), 479 bytes.

[22 Jul 2014 18:41] Sveta Smirnova
option file for slave

Attachment: rpl_bug69585-slave.opt (application/octet-stream, text), 45 bytes.