What’s up with the advice tools ?

For the first time since the beginning of my long long career, I decided today to use an advice tool to check my MySQL configuration.

I decided to launch pt-variable-advisor and mysqltuner V2 on one of my MySQL server.
Here are the result outputs :

Then I ask myself what to do with these results, just raw results without much explanations, these tools are supposed to be done for beginners…

Just one example from mysqltuner : Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size
If I set the size of my temporary tables at 16Mb, what that’s means, I have to increase to 32Mb, 64Mb or more ?
What is the impact of this change to the memory of my server ?
How can I be sure that the new value that I set is not really too big ?

And another one from pt-variable-advisor innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode
Really ? Find me a beginner who knows the ACID properties…
If I know ACID properties, do I really need these advice ?

Two other elements have excited my mind because there is a contradiction between the two tools :

  • sort_buffer_size parameter :
    • pt-variable-advisor :  The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it
    • mysqltuner :  consider increasing sort_buffer_size (sorting)
  • query_cache_size parameter :
    • pt-variable-advisor : Nothing about query cache
    • mysqltuner :  Increase query_cache_size — there are too many low memory prunes

I don’t know what you think but if you read some news for the last few years, you know that there are several algorithms to sorting in MySQL and you know that the best query cache tuner is here (even if I’m not to agree with the latter)

So what ?

I am not trying to denigrate these tools, these advice are certainly justified in one way or another.
This is obviously very very complex to achieve this kind of tool.
I just wonder what a beginner can do with such informations ?
And I also wonder if these tools are really useful (for beginners) ?

What’s your thoughts about that ?

Few words about pt-archiver

I really like the percona toolkit, we all love the percona toolkit.
I know how it’s difficult to write operational and efficient scripts (I try to do that myself everyday)
And it is even more difficult to share a script, to take the responsibility to share its own code.
From there, understand that this article is simply a review of my own thoughts about pt-archiver (with the invaluable assistance of @maximefouilleul), I don’t want to question the quality or usefulness of this tool.

I tried pt-archiver for the first time this week, and the first thing I do before using a tool is read the documentation (yes, I really like to read documentations)

I was intrigued by some options of this tool, first, I can read “It deletes data from the source by default“.
Personally, I hate that you want to remove my data by default, I prefer to have a –delete option instead of a –no-delete option (even if there is a –dry-run option).
The difference is not trivial for me, I love my data…

–why-quit ??? I don’t understand the real interest of this option, if the tool stops without doing its job, why hide this information by default ?

Also, it seems that –txn-size–progress and –limit options must have the same values for the tool to work properly. But I have to open a bug report for that.

It is only few words, no bitterness, just a feedback.