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 ?




Share the love!

Subscribe to RSS feed or by email to automatically receive real-time notifications
Oracle, MySQL, and InnoDB are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners

17 Comments

  1. I hear you saying… What good is a recommendation if you don’t give more details.

    You have hit the heart of the problem with just these kinds of tools. You’d like it to just fix your problem, or give you some optimal settings. The issue is one setting, say Max_Connections, might need to be increased because you’re about to run out and applications will break. However each connection comes with some memory requirements that them self’s are dependent on how you set your query_cache and the kinds of queries you might be making. If you set Max_connection to high you will crash and no application will get service. You could limit your memory requirements for a connection and you can service more connections but then you queries will be show. Which is better, more connections or faster queries?

    That’s just one of hundreds of combinations that can effect how your MySQL server runs. New users want recommendations but real numbers are hard to give and can be dangers if you don’t understand the balance.

    ACID is an acronym (http://en.wikipedia.org/wiki/ACID) used by DBA. Simply put, can you afford to drop a part of a transaction. Think bank transfer. That’s two queries, a withdraw and a deposit, that have to happen together. Just one would be bad.

    Reply
    • Mark, I haven’t any problem to understand what MySQL parameters suits for me or what ACID properties are, I just wanted to point that advice tools aren’t really useful for dummies (I hope I’m not a beginner anymore…).
      I’m sure in some cases these tips can be dangerous, because you need to “understand the balance”…

      Reply
  2. To answer if tuning tools are for new users…

    Some say NO! Tuning tools are DANGEROUS. New user’s shouldn’t use them and Pro’s don’t need them, so we should stop making them.

    I say they are OK but they should remain vague so users will have to do some studding. Pro’s can take a quick look.

    Reply
  3. I got fed up with the old advisory scripts. They _still_ discuss things as if MyISAM were the only engine.

    So, I wrote this instead; it’s just a document, not a script:
    http://mysql.rjweb.org/doc.php/memory
    Have at it! Flame me! Maybe I will make it better after you critique it.

    Reply
    • Hi Rick, yes, I’ve already seen your “document” and I like it !
      Not sure these “old” advisory scripts considers that MyISAM is the only engine.
      Thx for your comment.

      Reply
  4. Writing a general* advice tool is very difficult. (*By general I mean useful for anyone, any platform, any setup, etc.) Mark pretty much summed it up by saying, “New users want recommendations but real numbers are hard to give and can be dangers if you don’t understand the balance.” We’ve tried this a few times at Percona, and each time we concluded that unless you build some insanely complex system, the nuance and understanding required for sound, context-senstive advice is not easily programmed. So, to answer your question about what’s up with such tools: they’re generally meant for two people: 1) someone who’s not a complete beginner but isn’t an expert either, so they provide pointers for further study (and who presumably will study because they cared enough to run the tool); 2) experts doing consulting/support who know the systems then know exactly what do when a tool say such-and-such isn’t set, etc.

    Reply
    • Thank you again for this clarification Daniel.
      I know it’s very difficult, that’s why I wrote “This is obviously very very complex to achieve this kind of tool”.
      I don’t want to specifically blame a tool, just want to point the gap betwwen these tools and a real expertise.
      There is also conflicting information between the two main tools used by the MySQLers…

      And sorry for my last two posts about tools from Percona team, it was just a coincidence

      Reply
  5. The last two posts are good. We’re always glad when people blog/podcast/whatever about the tools. Even negative feedback (not that yours was) is a chance to learn and improve.

    Reply
  6. Maybe they should come with a strongly worded recommendation to not take their advise without consulting the author.

    I think they would make a great “business card”. I’d say… This tool may give you a few percent increase in throughput. If you need more please call xxx-xxx-xxxx.

    I think Percona’s idea is better. (https://tools.percona.com/) The results of this service is much better then MySQL defaults and not as risky as the tunning tools.

    Reply
  7. “insanely complex system” — Sure, if you want to tune everything perfectly.

    90% of “ordinary” users get 90% of the benefit just from setting either key_buffer_size or innodb_buffer_pool_size to an appropriate percentage of available RAM.

    A few more tips would take care of those “non-ordinary” users with RAID, SSDs, etc.

    When you get into application style (Data Warehousing, EAV, blogs, etc), then the important advice drifts into schema design, indexing, etc. Tuning issues take a back seat. Now we talk about summary tables, eschewing key-value stores, not doing pagination via OFFSET and LIMIT, etc.

    Reply
  8. (sorry, I’m a bit behind reading blogs!)

    The point of mysql tuner v2.0 is not to give advice. It’s for an experienced DBA to decide what the best thresholds for monitoring/tuning are, and then the tool is a way for you to check your servers against what you want. That’s why there’s a configurable file of what to check and what the thresholds should be. There is a sample configuration file that took most of the checks that existed in mysqltuner v1.0.

    The output is intentionally vague – it’s not for a newbie, because blanket advice is never 100% right, and it gives folks a false sense of security to inexperienced folks, that if the tuner says everything’s OK, it must be. The output it vague because YOU have to decide what to increase the threshold to.

    Hope that helps.

    Reply
  9. Thinking deeply… I’d like to see a “Tuner” that runs in the background and reports to a log. Graphs are nice but a program with a little more logic could be better. Maybe we could even have an “auto tuner” adjust some MySQL settings based on the traffic as it changes through out the day?

    Reply
  10. Or maybe even an “auto tuner” that runs something like MySQL slap and analyzes the results for the hardware resulting in a my.cnf file. This would be even better then a few questions ala the Percona tools.

    Reply

Leave a Comment.