November 1, 2014

thread_concurrency doesn’t do what you expect

Over the last months I’ve seen lots of customers trying to tune the thread concurrency inside MySQL with the variable thread_concurrency. Our advice is: stop wasting your time, it does nothing on GNU/Linux :)

Some of the biggest GNU/Linux distributions includes the variable thread_concurrency in their my.cnf file by default. One example is Debian and its variants. Furthermore the default my.cnf files of MySQL like my-large.cnf, my-innodb-heavy-4G.cnf and so on have the thread_concurrency enabled with a tune advice helping to spread the confusion:

# Try number of CPU's*2 for thread_concurrency

Don’t try it. The problems with thread_concurrency are two:

1- It’s deprecated and removed on 5.6.1

http://bugs.mysql.com/bug.php?id=55001

2- It only works on old Solaris versions < 9

The idea of thread_concurrency for Solaris is using thr_setconcurrency() function to give a hint to the operating system about the number of threads that we want to run at the same time. If you really want to tune the number of threads that runs concurrently you should read about innodb_thread_concurrency:

http://www.percona.com/blog/2011/07/28/how-innodb-contention-may-manifest-itself/
http://www.percona.com/blog/2011/12/02/kernel_mutex-problem-cont-or-triple-your-throughput/
http://www.percona.com/blog/2010/05/24/tuning-innodb-concurrency-tickets/

innodb_thread_concurrency places a limit on the number of threads that can be running inside InnoDB. The default value in 5.5 is 0 which means “infinite number of threads” but in some workloads limiting the value can give use some performance benefits. As we always say, set up benchmark tests to identify which tuning parameters provide the most benefit to your workload.

thread_concurrency is causing lot of confusion and there are hundred of MySQL tuning howtos that explain how to tune the variable to get better performance. If you have the variable in your my.cnf don’t panic because it does nothing and you’ve only lost some of your valuable time tuning it.

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

  1. +1! I see this every where on Serverfault. Every once and a while I double check
    the docs because I think I’m going crazy for how often I see it …

  2. kedar says:

    Yep! That’s why there exists the acronym RTFM.
    “This variable is specific to Solaris systems”
    [http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_thread_concurrency]

    Good read :)

  3. Dimitri says:

    It was deprecated as soon as “alternate” thread library became default since Solaris9:
    http://dimitrik.free.fr/db_STRESS_BMK_2008.html#note_5229

    Rgds,
    -Dimitri

Speak Your Mind

*