There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.
I don’t know exactly the percentage, but quite a few of the servers I take a look at have been “optimized” with some tuning primer or question-and-answer script that spits out “optimal” parameters for my.cnf.
Most of the time these servers are far from optimal. Sometimes the my.cnf parameters are extremely wrong, to the point of causing a severe performance penalty.
If it were as easy as writing a tool to do this, don’t you think Maatkit would have mk-optimal-mycnf already? In my opinion — as someone who knows very well the complexity of creating a good my.cnf — it’s practically impossible. Much harder than syncing data, or manipulating a replication hierarchy, or any of the other things Maatkit can do already. And I doubt I’ll ever even feel motivated to try creating such a tool.
Don’t bother with scripts. Don’t waste your time with most of the advice you see on the web in forums — much of it is fundamentally wrong, even when it seems to come from an informed source. Don’t put too much faith in the my.cnf samples that come with your operating system; many of them have very bad advice in the comments, such as instructing you on how to set up replication in ways that guarantee breakage.
If you want solid advice, ask someone who knows what they’re doing (and can prove it). Or buy our book.
But even more fundamentally, you should not focus so much on my.cnf. It is not the be-all and end-all of performance. Tuning your server settings has far less impact on performance than tuning your schema, indexing, queries and — you guessed it — thinking deeply about your application architecture. Server settings are a distraction and a waste of time for most people.
Most my.cnf files I see only need minor tweaks, which give only so-so performance improvements. Tuning my.cnf only helps a lot when my.cnf has extremely bad parameters. The kind you’ll get from tuning primers and automated my.cnf optimization scripts.
Absolutely, this is very true for httpd.conf as well. a good config tuning may take months to craft and tune and is based on many many factors
I completely agree. No automated tool can still replace human experience and knowledge of all complexities and implications behind the scenes.
Anyway, I think there is room for some orientation tools (ranging from basic templates, like my.cnf samples provided with MySQL distribution, to extensive and valuable books, like yours) which offer common (and not so common) sense and recognized advices for average situations (and some special cases), so that they set a kind of centroid or startup point in a way that further optimizations are a step nearer in most of the cases.
Metagrama, the problem is there’s virtually never a “common case” that doesn’t have some important factor the tool will need to be able to ask the human about, and the tool can’t be asking the human 10,000 questions like “yes or no: do you do a lot of queries that access ranges of data in order of some key?” For the really common cases, performance simply isn’t an issue, and you can use the defaults supplied with the server and forget about it. No need for a tool at all there.
>Sometimes the my.cnf parameters are extremely wrong, to the point of causing a severe performance penalty.
>Server settings are a distraction and a waste of time for most people.
Well well well. So how does one decide whether it’s a waste of time or if his my.cnf is causing a severe performance penalty..
Touche! Read it this way: people who obsess over server settings and use the automated tools I mentioned often end up with far worse settings than if they had just left the defaults alone. Now they have both wasted a lot of time and made their server perform badly.
I’m sure it is possible, _if_ one is a able to script a typical database workload on current system. From this point on, one can apply his favourite AI techniques (simulated annealing, hill climbing, genetic algorithms) to create a configuration with minimal runtime or maximal throughput. It is a joke a bit, but I wondered a lot of times why benchmarkers do not use something like that instead of manual and seemingly random fiddling around with multiple variables.
Baron,
Does a perfect my.cnf exist? How can you choose the best static values when the workload on a server may change?
Well, a “good” my.cnf usually exists for a given workload. (Some are hard to tune even when they don’t change). Changing workload — now that IS something to write a tool for.
> But even more fundamentally, you should not focus so much on my.cnf.
This. At my last job we ran the MySQL off of the ‘small’ cnf (other than max connections, and slow query at 1 sec) and I was able to get and keep performance very healthy for a database that pushed millions of page views a month.
I think that same sort of thing could be said about people trying to find optimal datatypes. I have never seen a performance difference between a (tiny|small|medium)int and an int, but I have had to clean up when an auto increment column runs out of room to expand. I am not saying such workloads don’t exist, I just have never seen one.
Mark,
If you’re looking for absolute correctness most if not all workloads are changing and dynamic so the optimal values would evolve all the time. Though typically you can find reasonably efficient my.cnf for the load often with assistance of per session and dynamic variabled 🙂
There are a lot of wrong approaches out there people use to write configuration files. One great one is “multiply by N” – there is a sample file for 64M and I have 64G so let me multiply all parameters by 1000 🙂 so we end up with 1GB sort_buffer_size and similar things which can hurt performance quite badly.
I increase the myisam sort buffer size and the max in-memory size of a temp table. I leave the caching to the OS. Works out pretty well in real life – WITH THE APPLICATION WE DEVELOP HERE AT MY WORK. And that’s the essence of this post – your milage will vary.
Peter,
Oracle has a much better solution for this. The server dynamically tunes memory limits for things like sort and hash based on the current workload — http://www.vldb.org/conf/2002/S29P03.pdf.
Per session and dynamic variables in MySQL do not scale as applications get more complex and the number of users grow. I would love to have that as a solution but I wouldn’t want users setting values.
But lets look at this from the opposite direction.
Which settings are bad to use?
Most likely using 1GB as readbuffer for a general mysql server is far worse than having it set to a small value such as 1MB.
The problem with the default my.cnf which comes with mysql is that those figures doesnt seem to reflect recommendations from mysql.com themselfs. Even if there are special cases where 1GB readbuffer might be a better option I believe that optimal options for 99% of the installations still can be obtained using optimization scripts.
The problem is that some people tend to claim they know better but they refuse to tell other what it is that they know that is so much greater than the public opinion…
I would also vote for some autotuning in mysql so you as an admin only have to care for lets say keybuff + max mem usage and then let mysql tune itself along the run.
Couldn’t it also be said an ‘expert’ can also make bad changes, just as well as a automated application? In my opinion the automated tools should be suggestions and try them out seeing what happens. Some of the recommend solutions are obviously wrong and shouldn’t be used.
Also leads to a bigger question how do you know the options you changed lead to better/worse performance? What we do is graph many of the mysql info and look over time but it’s not fool proof. Running tests (as you recommend in your book) is hard to get ‘real world’ results.
It also comes down to performance gains come from better database design or methods to cache data queries, than my.cnf changes. In the end if needed throwing additional hardware is sometimes needed.
Apachez, 99% is overstating the case. And we don’t hold back any knowledge — we share pretty much everything possible in our book and on this blog. The problem is, sometimes an expert human’s knowledge just can’t be put into code or onto the page easily. That’s exactly why we share freely here — we have no secrets. Our value is in what can’t be gotten by reading: experience. Just today a potential client called me and said they needed help and had gotten very far by reading our book and this blog.
Autotuning is a nice thought, but it’s really hard to do, probably for the same reasons a script for optimal config is hard. Remember how Falcon’s designers said Falcon was going to be self-tuning? Now look at how many settings have emerged over time for it. One of them is even in crontab format, of all things! Oracle and some other systems show that it can be done, but ask an Oracle DBA — tuning is still needed.
I don’t think anything is held back other than experience, and that is difficult to quantify. Much of the information that a MySQL expert has is not secret. It is published in many forms and frequently on this blog. The value is knowing how, when and why to apply it.
Baron: Well I have read this blog entry twice and still cannot see any hints regarding my.cnf tuning, only some trolling on how bad “automated scripts” are and how superior the writer is into tuning my.cnf (but on the other hand perhaps the purpose was just this), but you have perhaps some other text displayed on your monitor when you read the blog post named “The ultimate tool for generating optimal my.cnf files for MySQL” ? 😉
The meaning of tuning my.cnf is not do to some magic (which cannot be said too often) but to fix the last pieace AFTER you have created a good table design, used good indexes and using good selects (which use these indexes in an optimized fashion)… specially since MySQL itself is so bad regarding managing the memory (mainly due to lack of any form of autotuning). Not to mention that a very bad my.cnf can bring performance down even if you have correct design, index and optimized queries.
Innodb on the other hand (when used through MySQL) has some nifty features regarding O_DIRECT and such, has anyone seen if MySQL itself will start to use such things to better utilize myisam files (since today MySQL let the operating system use its diskcache before the MySQL engine access the myisam tables)?
Apachez, I see what you mean now. I wrote the title of the post tongue-in-cheek, and indeed my only point was to say the automated tuning tools aren’t very good or even possible to make very good (IMO). Sorry for sounding arrogant.
I think there is a difference between “better than default my.cnf” and “optimal my.cnf” – It is indeed possible to automate things, often by fixing obviously wrong values, to get configuration which will be better than default my.cnf in 99% cases. However it is not possible to get the most optimal config file automatically. This is in particularly so because during tuning there are many questions which human needs to answer. For example recovery time (smaller innodb_log_file_size than optimal), fast warmup after MySQL Restarts (so you need double buffering and no O_DIRECT) balancing reads vs writes etc.
Not to hijack the thread, but I stumbled across this trying to answer a question.
Is there a way to get per MySQL table I/O physical statistics (reads/writes, etc.) from InnoDB? I have a stored procedure that is reading way too much stuff from the DB. I am using innotop, but since it is an SP, you can’t get an explain plan. iostat -x shows lots of read activity, but I have no way of knowing which table is being accessed.
Besides, the code for the SP is several pages long and quite complicated. I think I’ll go slap the developer that wrote it, but he is not longer with the company!!!
Please help.
Apachez: “The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.”
Baron: Oracle’s auto tuning is super great, but it has its limits. It’s designed for systems with shifting workloads (ie: multiple databases on a single node) running below, say, 80% capacity. Once you cross a given load threshold wherein you’re taxing any element (cpu, disk i/o, network, etc) to the limit, all bets are off. It’ll do a very good job of balancing parameters to best suit the heaviest consumer of the moment, but if you have multiple databases capable of completely tapping the system, it’s time to upgrade or redesign. The same goes for VMWare, or any other service capable of automatic load-balancing of complex systems. As soon as the collective consumption approaches or exceeds the limits of the system, you’re just delaying doom.
No one ever said tuning-primer.sh was supposed to be the “ultimate” tuning tool. It’s a “primer”, to be get you started looking at certain things and not to be followed blindly. My tuning script started out as basically a check list of things I wanted to make sure were setup always. binlog & slow query log enabled, max_used_connections != max_connection, thread_cache not exhausted, tmp_table_size not greater than max_heap_table_size, etc… Some people have had success with it and have improved performance and visibility of their server.
I’m sure if you’re being called in they’re well beyond the need for my little ‘check-list’ script. However, there are some who are not.
Matt Montgomery
@Matthew – I like the idea of tuning-primer.sh but with a different name. I am occasionally asked to diagnose a performance problem on a one-off mysqld that doesn’t have full-time DBA support. For this I want to give them whats-wrong.sh that checks for the obvious problems.
It is great that you did this with a shell script. Amazing things can be done with Bash + Awk.
I think this article is way too bombastic.
I understand where the author is coming from, and indeed agree that there can never be a tool which can properly tune everything for everyone. However the tools that you so harshly come down on are hardly meant as such. They are handy tools in a toolkit, and best used with a dose of common sense and understanding that the suggestions they provide are simply that; suggestions.
tuning-primer.sh is a very nice little script which does help less inexperienced DBAs get started with tuning. I would argue that a lot of installations will fare a lot better implementing a few of the suggestions than sticking with the distributed my.cnf..
Denis
Uhm, “less inexperienced” should have been “less experienced”..
This website runs extremely slow, pages just sit there connecting so whatever your using to optimize it i think you should go back to the default conf files because you have botched it buddy.
It’s also important to note that there is no end-all solution to a badly performing application that depends on MySQL. You can run a script, hire a DBA, and improve your hardware, but you’ll find new bottlenecks at each step. I’ve talked to many customers who ran scripts and noticed a performance gain. I’ve talked to many that had no change or negative results. The same can be said of other customers who contacted DBA’s and paid large amounts for assistance.
By the way, I write MySQLTuner, and I know the limitations of the script. There’s no way that my script can perfectly tune a server, but I hope that mine helps bring people closer to a higher performance configuration at no cost.
What about MySQL Monitor, the Monitoring tool that is part of MySQL Enterprise?
I have installed MySQL Monitor and it gives specific information about parameters which need to be modified.
I agree that also with MySQL Monitor the optimisation of my.cnf is a long term project.
Also as tables grow, or when table are converted from MyISAM to InnoDB and vice versa, parameters in my.cnf should be modified.
Unfortunately modification of certain parameters is not easy in an production environment where downtime should be reduced to the minimum.
I know this is a late reply, however I’m trying to execute script tuning-primer.sh on my server, I have bash installed, but I always get this error:
./tuning-primer.sh: 40: Syntax error: “(” unexpected
Any help would be much appreciated!
“Or buy our book.” Hahahaha!
I agree with the previous poster who had noted that this article’s title was misleading (tongue in cheek or tongue in other places, it don’t matter, and misleading still stands). And in reading the post and the subsequent comments of the esteemed author. This worthless (in terms of specific help) posting is nothing but an obvious attempt to sell a few books without bothering to even say anything worth saying to give people a hint as to what kind of information his book might have. Let me use your logic against you, you wrote a book to help people optimize their my.cnf settings, yet you were wailing against the available (and reasonably useful) automated tools. How is reading your book different. These tools may not be able able to help out in certain situations because servers and the programs they run are never exactly the same. YET, your book without a doubt has exactly the same limitation and short-comings. Indeed a DBA might be able to fine tune it better, but your book is not a living breathing DBA, and information that it provides can NOT be every existing configuration. If your book can do it, then a tool (without a DBA intervention) is indeed possible.
The 2nd edition was a great book and I learned a lot by reading it. I can’t wait for the 3rd edition to be available.