MySQL Bugs: #37536: Thread scheduling causes performance degradation at low thread count

Bug #37536	Thread scheduling causes performance degradation at low thread count
Submitted:	19 Jun 2008 22:34	Modified:	17 Jun 2010 1:01
Reporter:	Vincent Carbone	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1.24	OS:	Solaris (Nevada Bld 87 on X86)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	mysql-perf-core, performance, thread-priority

Description:
I ran some tests to look at the effect of thread scheduling on MySQL 5.1.24 and Solaris Nevada X86 bld 87. Sysbench runs were executed at 4, 8, 12 and 16 cores with default thread scheduling  and with it turned off (--skip-thread-priority). These tests were run with the default TS scheduling class.

For Sysbench Read-Only tests, when the number of threads is less than processor core count turning off thread scheduling can result in up to a 5% performance improvement. However once the number of threads exceeds processor core count  the default thread scheduling behavior results in better performance, especially at core count >=8 and thread count >=32. As thread scheduling benefits MySQL when the number of threads equals or exceeds the number of processor cores, there should not be a performance penalty for the case where number of threads is less than the number of processor cores

sysbench Read-Only
	
	%Change
	
	
Thrds 	4cpu 	8cpu 	12cpu 	16cpu
1 	0.94 	0.93 	0.93 	0.95
2 	0.97 	0.97 	0.98 	0.97
4 	0.96 	0.93 	0.94 	0.95
8 	1.01 	0.85 	1.01 	0.99
12 	1.07 	1.18 	0.96 	1.06
16 	1.06 	1.2 	1.04 	1.1
32 	1.1 	1.26 	1.73 	2.05
48 	1.12 	1.4 	2.91 	4.34
64 	1.12 	1..55 	4.96 	7.67

Sysbench Read-Write results are similar for thread count < cpu  count. At 8 cpus the thread scheduling benefit scales nicely. At 12 and 16 cpus, the benefit peaks at 32 and 48 threads, respectively and then drops. However at high  cpu count and thread count the performance of Read-Write tests fall apart in general.

Sysbench Read-Write 	
	%Change
	
	
Thrds 	4cpu 	8cpu 	12cpu 	16cpu
1 	0.98 	0.98 	0.98 	0.97
2 	0.96 	0.96 	0.96 	0.96
4 	0.87 	0.96 	0.99 	0.93
8 	0.99 	0.98 	1 	0.99
12 	1.08 	1.03 	1.03 	1.01
16 	1.12 	1.1 	1.13 	1.12
32 	1.17 	1.32 	3.23 	4.27
48 	1.22 	2.24 	4.41 	2.08
64 	1.27 	3.23 	2.22 	1.4

How to repeat:
On an Solaris X86 system w/ at least 4 cores execute sysbench read-only and/or read-write tests. Execute successive sysbench tests ramping up the number of threads from 1 to at least n+1 cores.

my.cnf parameters:
query_cache_size = 0
innodb_data_home_dir = /data/mysql5.1.24/var
innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /home2/mysql5.1.24/logs
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 400M
innodb_flush_log_at_trx_commit= 1
innodb_thread_concurrency = 0
innodb_log_buffer_size = 8M
table_open_cache = 2048

To start mysql w/ default thread scheduling behavior:
mysqld_safe &

To start mysql w/ thread scheduling disabled:
mysqld_safe --skip-thread-priority &

Create 10,000,000 row table:
sysbench --mysql-user=mysql --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --num-threads=16 prepare

For each test first warm up the innodb buffer cache:

sysbench --mysql-user=mysql --max-time=$RUNTIME --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --num-threads=$THREADS run

For read-only test:

 sysbench --mysql-user=mysql --max-time=$RUNTIME --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --oltp-read-only=on --num-threads=$THREADS run

For read-write test:

 sysbench --mysql-user=mysql --max-time=$RUNTIME --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --num-threads=$THREADS run

Bug#35164

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/54803

2746 Davi Arnaut	2008-09-30
      Bug#35164: Large number of invalid pthread_attr_setschedparam calls
      Bug#37536: Thread scheduling causes performance degradation at low thread count
      
      Deprecated --skip-thread-priority startup option as newer version of
      the server won't change the thread priorities by default.
      
      Giving threads different priorities might yield marginal improvements
      in some platforms (where it actually works) but on the other hand it
      might cause significant degradation depending on the thread count and
      number of processors. Meddling with the thread priorities is a not a
      safe bet as it is very dependent on the behavior of the cpu scheduler
      and system where MySQL is being run.
      
      From MySQL 6.0 and up the default behavior is that of not modifying
      the threads priorities.

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/54922

2693 Davi Arnaut	2008-10-01
      Bug#35164: Large number of invalid pthread_attr_setschedparam calls
      Bug#37536: Thread scheduling causes performance degradation at low thread count
      
      Deprecated --skip-thread-priority startup option as newer versions of
      the server won't change the thread priorities by default.
      
      Giving threads different priorities might yield marginal improvements
      in some platforms (where it actually works) but on the other hand it
      might cause significant degradation depending on the thread count and
      number of processors. Meddling with the thread priorities is a not a
      safe bet as it is very dependent on the behavior of the cpu scheduler
      and system where MySQL is being run.
      
      From MySQL 6.0 and up the default behavior is that of not modifying
      the threads priorities.

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/54923

2693 Davi Arnaut	2008-10-01
      Bug#35164: Large number of invalid pthread_attr_setschedparam calls
      Bug#37536: Thread scheduling causes performance degradation at low thread count
      
      Deprecated --skip-thread-priority startup option as newer versions of
      the server won't change the thread priorities by default.
      
      Giving threads different priorities might yield marginal improvements
      in some platforms (where it actually works) but on the other hand it
      might cause significant degradation depending on the thread count and
      number of processors. Meddling with the thread priorities is a not a
      safe bet as it is very dependent on the behavior of the cpu scheduler
      and system where MySQL is being run.
      
      From MySQL 6.0 and up the default behavior is that of not modifying
      the threads priorities.

Deprecation patch queued to mysql-5.1-5.1.29-rc

See also Bug#12702

Pushed into 5.1.30  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:kgeorge@mysql.com-20081001144838-0ei6jil2x86vwa4c) (pib:4)

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/56087

2825 Davi Arnaut 2008-10-12
Bug#35164: Large number of invalid pthread_attr_setschedparam calls
Bug#35164: Large number of invalid pthread_attr_setschedparam calls
Bug#37536: Thread scheduling causes performance degradation at low thread count
Bug#12702: Long queries take 100% of CPU and freeze other applications under Windows

The problem is that although having threads with different priorities
yields marginal improvements [1] in some platforms [2], relying on some
statically defined priorities (QUERY_PRIOR and WAIT_PRIOR) to play well
(or to work at all) with different scheduling practices and disciplines
is, at best, a shot in the dark as the meaning of priority values may
change depending on the scheduling policy set for the process.

Another problem is that increasing priorities can hurt other concurrent
(running on the same hardware) applications (such as AMP) by causing
starvation problems as MySQL threads will successively preempt lower
priority processes. This can be evidenced by Bug#12702.

The solution is to not change the threads priorities and rely on the
system scheduler to perform its job. This also enables a system admin
to increase or decrease the scheduling priority of the MySQL process,
if intended.

Furthermore, the internal wrappers and code for changing the priority
of threads is being removed as they are now unused and ancient.

1. Due to unintentional side effects. On Solaris this could artificially
help benchmarks as calling the priority changing syscall millions of
times is more beneficial than the actual setting of the priority.

2. Where it actually works. It has never worked on Linux as the default
scheduling policy SCHED_OTHER only accepts the static priority 0.

Noted in 5.1.29 changelog.

The --skip-thread-priority option is now deprecated in MySQL 5.1 and
will be removed in MySQL 6.0 such that the server won't change the
thread priorities by default. Giving threads different priorities
might yield marginal improvements in some platforms (where it
actually works), but it might instead cause significant degradation
depending on the thread count and number of processors. Meddling with
the thread priorities is a not a safe bet as it is very dependent on
the behavior of the CPU scheduler and system where MySQL is being
run.    

Setting report to NDI pending push into 6.0.x.

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/56311

2877 Davi Arnaut 2008-10-15
Bug#35164: Large number of invalid pthread_attr_setschedparam calls
Bug#37536: Thread scheduling causes performance degradation at low thread count
Bug#12702: Long queries take 100% of CPU and freeze other applications under Windows

The problem is that although having threads with different priorities
yields marginal improvements [1] in some platforms [2], relying on some
statically defined priorities (QUERY_PRIOR and WAIT_PRIOR) to play well
(or to work at all) with different scheduling practices and disciplines
is, at best, a shot in the dark as the meaning of priority values may
change depending on the scheduling policy set for the process.

Another problem is that increasing priorities can hurt other concurrent
(running on the same hardware) applications (such as AMP) by causing
starvation problems as MySQL threads will successively preempt lower
priority processes. This can be evidenced by Bug#12702.

The solution is to not change the threads priorities and rely on the
system scheduler to perform its job. This also enables a system admin
to increase or decrease the scheduling priority of the MySQL process,
if intended.

Furthermore, the internal wrappers and code for changing the priority
of threads is being removed as they are now unused and ancient.

1. Due to unintentional side effects. On Solaris this could artificially
help benchmarks as calling the priority changing syscall millions of
times is more beneficial than the actual setting of the priority.

2. Where it actually works. It has never worked on Linux as the default
scheduling policy SCHED_OTHER only accepts the static priority 0.

Queued to 6.0-bugteam

Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)

Noted in 6.0.8 changelog.

Pushed into 5.1.29-ndb-6.2.17  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20081015225318-dt8jzsy6dvn1nkiv) (version source revid:davi.arnaut@sun.com-20081015225318-dt8jzsy6dvn1nkiv) (pib:5)

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/91330

2936 Konstantin Osipov 2009-11-23
Backport of:
-------------------------------------------------------------
revno: 2877
committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
branch nick: 35164-6.0
timestamp: Wed 2008-10-15 19:53:18 -0300
message:
Bug#35164: Large number of invalid pthread_attr_setschedparam calls
Bug#37536: Thread scheduling causes performance degradation at low thread count
Bug#12702: Long queries take 100% of CPU and freeze other applications under Windows

The problem is that although having threads with different priorities
yields marginal improvements [1] in some platforms [2], relying on some
statically defined priorities (QUERY_PRIOR and WAIT_PRIOR) to play well
(or to work at all) with different scheduling practices and disciplines
is, at best, a shot in the dark as the meaning of priority values may
change depending on the scheduling policy set for the process.

Another problem is that increasing priorities can hurt other concurrent
(running on the same hardware) applications (such as AMP) by causing
starvation problems as MySQL threads will successively preempt lower
priority processes. This can be evidenced by Bug#12702.

The solution is to not change the threads priorities and rely on the
system scheduler to perform its job. This also enables a system admin
to increase or decrease the scheduling priority of the MySQL process,
if intended.

Furthermore, the internal wrappers and code for changing the priority
of threads is being removed as they are now unused and ancient.

1. Due to unintentional side effects. On Solaris this could artificially
help benchmarks as calling the priority changing syscall millions of
times is more beneficial than the actual setting of the priority.

2. Where it actually works. It has never worked on Linux as the default
scheduling policy SCHED_OTHER only accepts the static priority 0.
@ configure.in
Remove checks for functions that are not used anymore.
@ include/config-netware.h
Remove unused define.
@ include/my_pthread.h
Remove thread priority changing wrappers.
@ mysys/my_pthread.c
Remove thread priority changing wrappers. They do not work properly
and their implementations were incorrectly protected by a check for
HAVE_PTHREAD_SETSCHEDPARAM.
@ mysys/thr_alarm.c
Remove meaningless (100) increase of a thread priority.
@ sql/mysql_priv.h
Remove meaningless thread priority values.
@ sql/mysqld.cc
Don't change thread priorities.
@ sql/slave.cc
Don't change thread priorities.
@ sql/slave.h
Update function prototype.
@ sql/sql_parse.cc
Don't change thread priorities.
@ sql/sql_prepare.cc
Don't change thread priorities.
@ sql/unireg.h
Mark flag as obsolete.
@ storage/innobase/handler/ha_innodb.cc
Remove use of obsolete flag and associated behavior.
@ storage/innobase/include/srv0srv.h
Remove use of obsolete flag and associated variables.
@ storage/innobase/os/os0thread.c
Remove use of obsolete flag and associated behavior.
@ storage/innobase/srv/srv0srv.c
Remove use of obsolete flag and associated variables.

Queued into next-mr-runtime (5.6)

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/91336

2936 Konstantin Osipov 2009-11-23
Backport of:
-------------------------------------------------------------
revno: 2877
committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
branch nick: 35164-6.0
timestamp: Wed 2008-10-15 19:53:18 -0300
message:
Bug#35164: Large number of invalid pthread_attr_setschedparam calls
Bug#37536: Thread scheduling causes performance degradation at low thread count
Bug#12702: Long queries take 100% of CPU and freeze other applications under Windows

The problem is that although having threads with different priorities
yields marginal improvements [1] in some platforms [2], relying on some
statically defined priorities (QUERY_PRIOR and WAIT_PRIOR) to play well
(or to work at all) with different scheduling practices and disciplines
is, at best, a shot in the dark as the meaning of priority values may
change depending on the scheduling policy set for the process.

Another problem is that increasing priorities can hurt other concurrent
(running on the same hardware) applications (such as AMP) by causing
starvation problems as MySQL threads will successively preempt lower
priority processes. This can be evidenced by Bug#12702.

The solution is to not change the threads priorities and rely on the
system scheduler to perform its job. This also enables a system admin
to increase or decrease the scheduling priority of the MySQL process,
if intended.

Furthermore, the internal wrappers and code for changing the priority
of threads is being removed as they are now unused and ancient.

1. Due to unintentional side effects. On Solaris this could artificially
help benchmarks as calling the priority changing syscall millions of
times is more beneficial than the actual setting of the priority.

2. Where it actually works. It has never worked on Linux as the default
scheduling policy SCHED_OTHER only accepts the static priority 0.
@ configure.in
Remove checks for functions that are not used anymore.
@ include/config-netware.h
Remove unused define.
@ include/my_pthread.h
Remove thread priority changing wrappers.
@ mysys/my_pthread.c
Remove thread priority changing wrappers. They do not work properly
and their implementations were incorrectly protected by a check for
HAVE_PTHREAD_SETSCHEDPARAM.
@ mysys/thr_alarm.c
Remove meaningless (100) increase of a thread priority.
@ sql/mysql_priv.h
Remove meaningless thread priority values.
@ sql/mysqld.cc
Don't change thread priorities.
@ sql/slave.cc
Don't change thread priorities.
@ sql/slave.h
Update function prototype.
@ sql/sql_parse.cc
Don't change thread priorities.
@ sql/sql_prepare.cc
Don't change thread priorities.
@ sql/unireg.h
Mark flag as obsolete.
@ storage/innobase/handler/ha_innodb.cc
Remove use of obsolete flag and associated behavior.
@ storage/innobase/include/srv0srv.h
Remove use of obsolete flag and associated variables.
@ storage/innobase/os/os0thread.c
Remove use of obsolete flag and associated behavior.
@ storage/innobase/srv/srv0srv.c
Remove use of obsolete flag and associated variables.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (version source revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version source revid:kostja@sun.com-20091123165731-nh7oss40x0gaciw7) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)