MySQL Bugs: #12702: Long queries take 100% of CPU and freeze other applications under Windows

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

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...

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 ?

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.

Thank you for the bug report feature request.

--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.

It's been a long time since my bug report, but I'm happy this setting became the default in 5.1.

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.

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-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.

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)

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)

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)