MySQL Bugs: #35164: Large number of invalid pthread_attr

Bug #35164	Large number of invalid pthread_attr_setschedparam calls
Submitted:	8 Mar 2008 17:53	Modified:	19 Oct 2008 19:27
Reporter:	Davi Arnaut
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Linux
Assigned to:	Davi Arnaut	Target Version:	6.0-rc
Triage:	Triaged: D2 (Serious) / R3 (Medium) / E2 (Low)

Description:
During some recent rounds of benchmarking (using sysbench) comparing MySQL on Linux and
FreeBSD 7.0, one linux kernel developer pointed (*) out that roughly 35% of the syscalls
made by mysqld were invalid sched_setparam calls. To verify the statement, I've run the
sp.test (5.1 debug build) and of the 180446 syscalls made, 5032 were sched_setscheduler
calls with invalid parameters.

The sched_setscheduler calls are originated by the my_pthread_setprio which should change
the priority of the of the passed thread.  The my_pthread_setprio is normally called twice
for each query the server receives.

* http://lkml.org/lkml/2008/3/6/162

How to repeat:
Run a query while the server and being straced and observe the number of invalid
sched_setscheduler calls.

Suggested fix:
my_pthread_setprio should be fixed to pass correct parameters and actually set the
desired priority or be dropped altogether.

The problem is that the scheduler policy SCHED_OTHER can only be used at static priority
0.

The man page of sched_setscheduler says:

SCHED_OTHER is the standard Linux time-sharing scheduler that is intended for all
processes that do not require special static priority real-time mechanisms. The process
to run is chosen from the static priority 0 list based on a dynamic priority that is
determined only inside this list. The dynamic priority is based on the nice level (set by
nice(2) or setpriority(2)) and increased for each time quantum the process is ready to
run, but denied to run by the scheduler. This ensures fair progress among all SCHED_OTHER
processes.

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

2746 Davi Arnaut	2008-09-29
      Bug#35164: Large number of invalid pthread_attr_setschedparam calls
      
      Deprecated --skip-thread-priority startup option as newer version of
      the server won't change the thread priorities by default.

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.

Deprecation patch queued to mysql-5.1-5.1.29-rc

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.

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)