Wrong recommendation "thread_pool_size between 16 and 36 for InnoDB usage" for Percona Server

[makhomed]

Recommendation "thread_pool_size between 16 and 36 for InnoDB usage"
from MySQL 8.0 Reference Manual / 5.6.3.4 Thread Pool Tuning
is only about Oracle MySQL Enterprise Thread Pool.

If Percona Server used - this recommendation about thread_pool_size MUST be different:

Alexey Stroganov, Performance Engineer at Percona in comments to article Percona Server: Improve Scalability with Percona Thread Pool write: "Usually most efficient value of thread_pool_size is in range between NCPU and NCPU+NCPU/2. NCPU = Number of CPU’s":

MySQLTuner should recommend to use thread_pool_size in range between NCPU and NCPU+NCPU/2. Where NCPU = Number of CPU cores, if Percona Server is used.

Recommendation "thread_pool_size between 16 and 36 for InnoDB usage" should be used only for Oracle MySQL Enterprise Thread Pool and this recommendation is wrong for Percona Server, causing buggy MySQL configurations.

[jmrenouard]

Hi,

Feel free to reopen it if needed

[makhomed]

For Percona Server for MySQL variable $myvar{'version'} is 8.0.25-15,
so check if ( $myvar{'version'} =~ /percona/i ) does not work as intended.
If $myvar{'version_comment'} used instead - all work as intended.
$myvar{'version_comment'} is Percona Server (GPL), Release 15, Revision a558ec2

Patch to fix this issue and other typos:

--- mysqltuner.pl.orig  2021-10-15 15:30:41.833169225 +0300
+++ mysqltuner.pl       2021-10-15 16:10:56.538935492 +0300
@@ -303,6 +303,7 @@
     if ( $^O eq 'linux' ) {
         my $cntCPU =
 `awk -F: '/^core id/ && !P[\$2] { CORES++; P[\$2]=1 }; /^physical id/ && !N[\$2] { CPUs++; N[\$2]=1 };  END { print CPUs*CORES }' /proc/cpuinfo`;
+        chomp $cntCPU;
         return ( $cntCPU == 0 ? `nproc` : $cntCPU );
     }

@@ -3635,19 +3636,19 @@
     infoprint "ThreadPool stat is enabled.";
     infoprint "Thread Pool Size: " . $myvar{'thread_pool_size'} . " thread(s).";

-    if ( $myvar{'version'} =~ /percona/i ) {
+    if ( $myvar{'version_comment'} =~ /percona/i ) {
         my $np = cpu_cores;
         if (    $myvar{'thread_pool_size'} >= $np
             and $myvar{'thread_pool_size'} < ( $np * 1.5 ) )
         {
             goodprint
-"thread_pool_size for Percona betwwen 1 and 1.5 times nimber of CPUs ("
+"thread_pool_size for Percona between 1 and 1.5 times number of CPUs ("
               . $np . " and "
               . ( $np * 1.5 ) . ")";
         }
         else {
             badprint
-"thread_pool_size for Percona betwwen 1 and 1.5 times nimber of CPUs ("
+"thread_pool_size for Percona between 1 and 1.5 times number of CPUs ("
               . $np . " and "
               . ( $np * 1.5 ) . ")";
             push( @adjvars,

[makhomed]

Feel free to reopen it if needed

@jmrenouard, I have no ability to reopen this bugreport, I have only 'Comment' button available.

[jmrenouard]

Hi,

Patch included in 1.8.4

[makhomed]

Patch included in 1.8.4

@jmrenouard, thanks, but where are two typos:

1. must be chomp instead of chmop
2. must be between instead of betwwen

--- mysqltuner.pl.orig  2021-10-15 18:37:09.022094622 +0300
+++ mysqltuner.pl       2021-10-15 18:40:30.058987439 +0300
@@ -303,7 +303,7 @@
     if ( $^O eq 'linux' ) {
         my $cntCPU =
 `awk -F: '/^core id/ && !P[\$2] { CORES++; P[\$2]=1 }; /^physical id/ && !N[\$2] { CPUs++; N[\$2]=1 };  END { print CPUs*CORES }' /proc/cpuinfo`;
-        chmop $cntCPU;
+        chomp $cntCPU;
         return ( $cntCPU == 0 ? `nproc` : $cntCPU );
     }

@@ -3642,13 +3642,13 @@
             and $myvar{'thread_pool_size'} < ( $np * 1.5 ) )
         {
             goodprint
-"thread_pool_size for Percona betwwen 1 and 1.5 times number of CPUs ("
+"thread_pool_size for Percona between 1 and 1.5 times number of CPUs ("
               . $np . " and "
               . ( $np * 1.5 ) . ")";
         }
         else {
             badprint
-"thread_pool_size for Percona betwwen 1 and 1.5 times number of CPUs ("
+"thread_pool_size for Percona between 1 and 1.5 times number of CPUs ("
               . $np . " and "
               . ( $np * 1.5 ) . ")";
             push( @adjvars,

[jmrenouard]

Hi,

Patch included in 1.8.4

[jmrenouard]

Hi

New commit with fix 1.8.5