Skip to content

MySQLTuner recommendations about thread_pool_size #404

Open
@makhomed

Description

@makhomed

For Percona Server MySQLTuner write wrong recommendation when thread pool not enabled:

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[!!] thread_pool_size between 16 and 36 when using InnoDB storage engine.

or

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[OK] thread_pool_size between 16 and 36 when using InnoDB storage engine.

For MariaDB MySQLTuner write useless information when thread pool not enabled:

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (5.5.60-MariaDB)

Expected behavior of MySQLTuner:

  1. Write "ThreadPool Metrics" only if thread pool is enabled in config.
  if ( defined( $myvar{'thread_handling'} )
        and $myvar{'thread_handling'} eq 'pool-of-threads' )
  {
    # print ThreadPool Metrics
  }
  1. Make correct recommendation about thread_pool_size.
    MariaDB documentation say: "...ideally, there should be a single active thread for each CPU on the machine" - https://mariadb.com/kb/en/library/thread-pool-in-mariadb/

Percona Server manual:
https://www.percona.com/doc/percona-server/LATEST/performance/threadpool.html

Alexey Stroganov, Performance Engineer at Percona make more useful recommendation:
"Usually most efficient value of thread_pool_size is in range between NCPU and NCPU+NCPU/2. NCPU = Number of CPU’s" - https://www.percona.com/blog/2014/01/23/percona-server-improve-scalability-percona-thread-pool/

  1. thread_pool_size recommendations are the same for InnoDB, MyISAM and any other storage engine,
    so current MySQLTuner recommendations about "thread_pool_size between 4 and 8 when using MyIsam storage engine" is also wrong.

MySQLTuner should recommend to use thread_pool_size in range between NCPU and NCPU+NCPU/2. Where NCPU = Number of CPU cores, includes HT cores.

  1. Also, enabling thread pool have sense only for servers with max_connections >= 512.
    See performance tests:
    https://www.percona.com/blog/2014/01/23/percona-server-improve-scalability-percona-thread-pool/
    https://www.percona.com/blog/2014/01/29/percona-server-thread-pool-improvements/

May be it is good idea (if server is Percona Server or MariaDB) recommend turn on thread pool if $mystat{'Max_used_connections'} >= 512 and recommend to turn off thread pool if $mystat{'Max_used_connections'} < 512 ?

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions