wsrep_slave_threads

[solracsf]

Running a node on a VPS single core with:

wsrep_slave_threads = 4

Script suggests:

wsrep_slave_threads= Nb of Core CPU * 4

What's the difference ?

[jmrenouard]

No difference: preconisation says 4 * Nb core

[solracsf]

Ok, so why do the script suggests it if the setting is OK?

[ofigueroa]

In my opinion the issue is that in the Galera documentacion says 'twice'

http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-slave-threads "It is rarely beneficial to use a value that is less than twice the number of CPU cores on your system."

So "wsrep_slave_threads = 2" should be a valid configuration, or at least, n_cores * 2

Anyway, this parameter is searched with method 'get_wsrep_option' in line 5044, which parses 'wsrep_provider_options' value, and 'wsrep_slave_threads' does not form part of possible options, is a GLOBAL variable.

mysql> SHOW GLOBAL VARIABLES LIKE 'wsrep_slave%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| wsrep_slave_FK_checks | ON    |
| wsrep_slave_UK_checks | OFF   |
| wsrep_slave_threads   | 8     |
+-----------------------+-------+
3 rows in set (0,00 sec)

http://galeracluster.com/documentation-webpages/galeraparameters.html

[marcofl]

what is number of cores anyway? virtual cpus on a VM? I still see

    wsrep_slave_threads= Nb of Core CPU * 4

even when I set wsrep_slave_threads to 16 on a 8 vCPU VM.

Setting this to 32 seems a bit overkill for me as docs say:

From the perspective of resource utilization, it’s recommended that you keep to the lower end of slave threads.

[rrreche]

I think acs-ferreira has run into the same problem as I did: wsrep_slave_threads are not being queried correctly. I am guessing he had the correct number of wsrep_slave_threads configured, yet he is getting the suggestion to adjust it.

As far as I have come, when the script calls get_wsrep_option('wsrep_slave_threads') it returns 0, so it will never satisfy the condition.

I have run into this issue while setting up a Galera Cluster of 3 MariaDB nodes. If I had to point somewhere, I would say that mysqltuner.pl is not examining a custom file galera.cnf in my conf.d directory (etc/mysql/conf.d). If I am not mistaken, the script polls this info directly from .cnf files instead of querying it to the database with a command such as SHOW VARIABLES LIKE 'wsrep_slave_threads'.

Still, take all of this with a pinch of salt, as I am just started in this.

[jmrenouard]

Hi, Actually, mysqltuner.pl is requesting only variables with SHOW VARIABLES LIKE ....

So, can you give us a correct calculus formula for this parameter ?

I'm a bit confused now with it :) Thanks by advance.

[solracsf]

At this time, in a one core CPU system:

MariaDB` [(none)]> SHOW VARIABLES LIKE 'wsrep_slave_threads';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| wsrep_slave_threads | 4     |
+---------------------+-------+
1 row in set (0.00 sec)

MySQLTuner outputs: [!!] wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s)

Variables to adjust:
    wsrep_slave_threads= Nb of Core CPU * 4

[rgpublic]

I'm also confused. I thought "nproc" on the command-line gives the number of Cores. Calling nproc prints "8". wsrep_slave_threads is "32" on our server. Yet, the script proposes to set it to wsrep_slave_threads= Nb of Core CPU 4. I don't know about you, but 84 = 32 for me ;-)

[emphazer]

@rgpublic Do you have an intel cpu with hyperthreading activated?

nproc tells you the HT cores but not the physical cores. The mysqltuner script checks for physical cores. So if you have a quad core with HT. It's (8/2)*4=16

According to PR #310

It is best practice to use only the physical ones in this variable.

Btw. It's terrible to post comments with a mobile phone! Had to edit everything 10x times. Stay tuned ;-)

[rgpublic]

@emphazer : Ah, you are right, of course. My bad. Thanks for letting me know. Although: it definitely would've been nice if the tuner script simply told the expected value instead of just complaining and giving a formula. That would've made it much clearer.

[emphazer]

@rgpublic i was confused about the formula too. thats why i looked in the source code and noticed that the calculation for intel cpu's with ht was wrong.

so im totally with you. the script should print the value of the variable $cntCPU

[jmrenouard]

Pushing a value for wsrep_slave_threads instead of formula.

[jmrenouard]

Can you give me your feedback ?

[rgpublic]

Hmm. I tried the new version. I have 4 physical cores. The script says "[!!] wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s)" and "Variables to adjust: wsrep_slave_threads = 4 ". Shouldnt it propose 8? And I wonder: Wouldnt it be much nicer, if the script also said what the current value actually is instead of leaving it up to the user to find out?

[jmrenouard]

Hi @rgpublic

I have added info line before indicating how many cores are found by cpu_cores sub.

[rgpublic]

@jmrenouard Yes, thats pretty helpful, but:

CPU core detected: 4 [!!] wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s) Variables to adjust: wsrep_slave_threads = 4

I still think the recommendation is wrong. The number of CPU cores detected is 4 (correct for my system). wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s) (i.e. wsrep_slave_threads should be 8, 12 or 16 for my system - yes, also correct - it isnt currently, it's 1 at the moment). But then: Variables to adjust. Set wsrep_slave_threads to 4. Huh? No! It should be 8, 12 or 16, or didnt I understand sth.?

[emphazer]

@rgpublic yes my friend you are right.

@jmrenouard thx for that info line feature. i think rgpublic is definetly right. the recommendation values should be 8, 12 or 16 with 4 core cpu_cores * 2, 3 or 4

maybe this part here isnt working correctly https://github.com/major/MySQLTuner-perl/blob/1e8315425659577fe471fbfa69bd81e5b8821471/mysqltuner.pl#L5155-L5159

[jmrenouard]

Hi,

I have change test and add extra info related to wsrep_slave_thread.

Can you give us you feedback ?

[rgpublic]

I did a GIT pull and I get:

[--] CPU core detected : 4 [--] wsrep_slave_threads: 1 [!!] wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s) Variables to adjust: wsrep_slave_threads = 4

I think I should get: wsrep_slave_threads = 8, right?

As a sidenote: After doing the "git pull", all my colors are gone. Except when calling with --color. Which is a bit sad IMHO. Don't know if it's intended.

[jmrenouard]

Hi @rgpublic

Coded value for adjusted value should be 16. (4 CPU core *4)

There is a mistake in calculation.

[jmrenouard]

Hi @rgpublic,

Can you have a new trial :) ?

thanks

[emphazer]

@jmrenouard looks good to me.

what do you say rgpublic?

@rgpublic i noticed that too color issue to. i guess they changed the default. but you can still run it in color mode with --color

[jmrenouard]

No anwser, I close this issue.

Feel free to reopen it if needed.