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

Bug #37536	Thread scheduling causes performance degradation at low thread count
Submitted:	20 Jun 2008 0:34	Modified:	19 Oct 2008 19:28
Reporter:	Vincent Carbone
Status:	Closed
Category:	Server	Severity:	S5 (Performance)
Version:	5.1.24	OS:	Sun Solaris (Nevada Bld 87 on X86)
Assigned to:	Davi Arnaut	Target Version:	6.0-beta
Tags:	thread-priority, mysql-perf-core, performance
Triage:	Triaged: D3 (Medium)

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.