Bug #12702 Long queries take 100% of CPU and freeze other applications under Windows
Submitted: 21 Aug 2005 13:55 Modified: 3 Oct 2008 22:32
Reporter: Nicolas Grilly Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1.13 OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[21 Aug 2005 13:55] Nicolas Grilly
Description:
With a standard installation of MySQL 4.1.13 under Windows XP SP2, a long query (for example a query during 30 seconds) takes 100% of CPU (it's visible in the Task Manager) and freezes other applications.

This is because under Windows, most applications have a priority 8. But applications can give to their threads a bonus or a malus between +2 and -2. This is what MySQL does with each thread handling a query, giving them a +2 bonus, boosting their priority to 10.

Windows scheduler always gives control to threads having the greatest priority. This is why MySQL query handling thread takes all CPU time (with a priority 10) and other applications (i.e. Firefox, OpenOffice, Outlook...) are freezed (with a priority 8). Just after the query was done, everything revert to the normal and applications are responsive again.

This makes MySQL very difficult to use for a developer working on a big database with MySQL installed locally. Is it normal or is it a bug ?

How to repeat:
- Make a standard installation of MySQL 4.1.13 under Windows XP SP2
- Load an existing database using the MyISAM engine
- Launch a long query (during 30 seconds or more)
- Try to use another application during execution of the query : 100% of CPU is taken by MySQL and other applications seem frozen

Suggested fix:
Temporary workaround:

- Use the option "skip-thread-priority" to give all threads the same priority. With this option, every MySQL threads have a priority 8 and Windows scheduler does its job as usual, distributing CPU time to every applications, not only to MySQL. Even when a long query is running, other applications seem responsive.

More definitive solution:

- People starting with MySQL under Windows often use the MySQL Server Instance Config Wizard. This wizard has a "server type" dialog. If the user chooses "developer machine" type, we can add the option "skip-thread-priority" to the my.ini file.

- In the MySQL Reference Manual, in section 2.12.4.6, relative to BSD operating system specific notes, there is the following sentence: "If you have problems with performance under heavy load, try using the -skip-thread-priority option to mysqld. This runs all threads with the same priority. On BSDI Version 3.1, this gives better performance, at least until BSDI fixes its thread scheduler.". We probably should add a similar note in section 2.3, titled "Installing MySQL under Windows".

- Instead of giving a +2 priority bonus to each query handling thread, perhaps it is possible to let query handling thread without bonus and give every other thread a -1 or -2 malus. But I don't know if it's good idea...
[21 Aug 2005 17:42] Peter Laursen
Thanks Nicolas for this.
I find it very interesting :-)
These long queries killing everything has bothered me too!

Is the "skip-thread-priority" a my.ini setting that will need a restart of MySQL, or could this setting be changed while MySQL is running ?
[22 Aug 2005 14:09] Nicolas Grilly
You're welcome: I'm glad to share this info with you.
Yes, "skip-thread-priority" is a my.ini setting and need a restard of MySQL.
[2 Feb 2007 22:46] MySQL Verification Team
Thank you for the bug report feature request.
[3 Oct 2008 20:39] Konstantin Osipov
--skip-thread-priority is now the default in 5.1.
We're considering a complete removal of juggling with thread priorities.
See  Bug#38477 Bug#35164 Bug#37536 for details.
[3 Oct 2008 22:32] Nicolas Grilly
It's been a long time since my bug report, but I'm happy this setting became the default in 5.1.
[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.
[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
[10 Nov 2008 10:53] 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: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.
[25 Nov 2009 13:33] 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-20091123165731-nh7oss40x0gaciw7) (merge vers: 5.6.0-beta) (pib:13)
[6 Mar 2010 10:57] 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)
[15 Jun 2010 8:10] 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:25] 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)