Bug #35164 Large number of invalid pthread_attr_setschedparam calls
Submitted: 8 Mar 2008 16:53 Modified: 17 Jun 2010 1:00
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0+ OS:Linux
Assigned to: Davi Arnaut
Triage: Triaged: D2 (Serious) / R3 (Medium) / E2 (Low)

[8 Mar 2008 16:53] Davi Arnaut
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.
[11 Mar 2008 15:05] Davi Arnaut
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.
[29 Sep 2008 14:54] Bugs System
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.
[30 Sep 2008 18:12] Bugs System
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.
[1 Oct 2008 12:25] Bugs System
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.
[1 Oct 2008 12:27] Davi Arnaut
Deprecation patch queued to mysql-5.1-5.1.29-rc
[1 Oct 2008 12:28] Bugs System
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.
[3 Oct 2008 20:41] Konstantin Osipov
See also Bug#12702
[9 Oct 2008 17:56] Bugs System
Pushed into 5.1.30  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:kgeorge@mysql.com-20081001144838-0ei6jil2x86vwa4c) (pib:4)
[12 Oct 2008 16:20] Bugs System
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.
[13 Oct 2008 13:38] Paul Dubois
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.
[15 Oct 2008 22:53] Bugs System
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.
[15 Oct 2008 22:57] Davi Arnaut
Queued to 6.0-bugteam
[17 Oct 2008 16:41] Bugs System
Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20081001122435-zj47jhgkpasjhfxk) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[19 Oct 2008 17:27] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:01] Bugs System
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)
[28 Oct 2008 22:20] Bugs System
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)
[1 Nov 2008 9:45] Bugs System
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)
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:davi.arnaut@sun.com-20081015225318-dt8jzsy6dvn1nkiv) (version source revid:davi.arnaut@sun.com-20081015225318-dt8jzsy6dvn1nkiv) (pib:5)
[23 Nov 2009 16:56] Bugs System
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.
[23 Nov 2009 17:01] Konstantin Osipov
Pushed into MySQL 5.6 (next-mr-runtime)
[23 Nov 2009 17:03] Bugs System
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.
[23 Nov 2009 17:09] Bugs System
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/91338

2937 Konstantin Osipov	2009-11-23
      A follow up for the fix for Bug#35164 (remove priorities on Windows).
[25 Nov 2009 13:32] Bugs System
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)
[25 Nov 2009 13:33] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version source revid:kostja@sun.com-20091123170837-finb8anixw9w7oce) (merge vers: 5.6.0-beta) (pib:13)
[25 Nov 2009 15:41] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:05] Bugs System
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)
[6 Mar 2010 20:34] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.
[15 Jun 2010 8:17] Bugs System
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)
[15 Jun 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)