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 18:48	Modified:	30 Jul 2007 21:29
Reporter:	Heikki Tuuri
Status:	Closed
Category:	Server: InnoDB	Severity:	S5 (Performance)
Version:	5.1	OS:	Any
Assigned to:	Vasil Dimov	Target Version:

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.