MySQL Bugs: #25078: Make the replication thread to ignore innodb_thread

Bug #25078	Make the replication thread to ignore innodb_thread_concurrency
Submitted:	14 Dec 2006 17:48	Modified:	20 Jun 2010 17:29
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.1	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any

Description:
This idea comes from Mark Callaghan:

> This is probably an easy change to make, edit 
> innodb_srv_conc_enter_innodb to pass a flag to srv_conc_enter_innodb 
> that indicates whether this is the replication thread and 
> srv_conc_enter_innodb can always let it enter. The benefit of this 
> change is that replicas are more likely to stay current with a primary.

How to repeat:
N/A

Note that we also got requests (from customers) for the opposite:
an option to "throttle" the slave SQL thread so that it leaves enough resources for the normal client threads on the slave.

Hmm... this has to wait until the 5.1 engine-configurable my.cnf is implemented and works.

We could implement a new option:

innodb_replication_priority

that could have values 0 ... 10. We could define 10 as the default. At lower priorities, srv conc code could delay the replication thread by (10 - n) milliseconds if there are other queries inside InnoDB. At level 10, the replication thread would always be allowed to enter in the srv conc code, even if there already are >= innodb_thread_concurrency threads inside InnoDB.

I would propose

innodb_replication_delay

which would be specified in milliseconds.  Some user might want to
specify a delay longer than 10 milliseconds, and we do not want to
repeat the mistakes made with innodb_thread_concurrency.

	Marko

Assigning this feature request to Marko.

Lars, Guilhem,
the parameter to delay replication threads could be global to all storage engines, but I believe that it would have to be interpreted in each storage engine.

I would implement Mark Callaghan's feature request like this:

Index: handler/ha_innodb.cc
===================================================================
--- handler/ha_innodb.cc	(revision 1319)
+++ handler/ha_innodb.cc	(working copy)
@@ -464,7 +464,12 @@ innodb_srv_conc_enter_innodb(
 /*=========================*/
 	trx_t*	trx)	/* in: transaction handle */
 {
-	if (UNIV_LIKELY(!srv_thread_concurrency)) {
+	if (UNIV_LIKELY(!srv_thread_concurrency
+			|| (trx->mysql_thd
+			    && ((THD*) trx->mysql_thd)->slave_thread))) {
+		/* Disable the thread throttling if
+		innodb_thread_concurrency=0
+		or this is a replication slave thread. */
 
 		return;
 	}
@@ -481,7 +486,12 @@ innodb_srv_conc_exit_innodb(
 /*========================*/
 	trx_t*	trx)	/* in: transaction handle */
 {
-	if (UNIV_LIKELY(!srv_thread_concurrency)) {
+	if (UNIV_LIKELY(!srv_thread_concurrency
+			|| (trx->mysql_thd
+			    && ((THD*) trx->mysql_thd)->slave_thread))) {
+		/* Disable the thread throttling if
+		innodb_thread_concurrency=0
+		or this is a replication slave thread. */
 
 		return;
 	}

Obviously, this won't work in plugins. I propose an accessor function:
bool thd_is_slave_thread(const THD*).

Assigning this to Vasil.

Some users want the replication thread to have a low priority, but most users want it to have a high priority. We can implement this when the pluggable 5.1 comes out, since we need to add yet another configuration option:

innodb_prioritize_replication

whose default value is TRUE.

Regards,

Heikki

Queued to 5.1-maint team tree

Pushed into 5.1.21-beta

Noted in 5.1.21 changelog.

The replication thread on a slave now is always allowed to enter
InnoDB even if this would exceed the limit imposed by the
innodb_thread_concurrency system variable.

Updated changelog entry:

The SQL thread on a slave now is always allowed to enter InnoDB even
if this would exceed the limit imposed by the
innodb_thread_concurrency system variable. In cases of high load on
the slave server (when innodb_thread_concurrency is reached), this
change helps the slave stay more up to date with the master; in the 
previous behavior, the SQL thread was competing for resources with
all client threads active on the slave server.

There is bug #30706 about innodb_prioritize_replication feature.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)