The sample my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf, and my-innodb-heavy-4G.cnf files from MariaDB and Percona (I didn’t bother checking Oracle’s distro) were from the dinosaur age, with comment like this for my-huge.cnf:
This is for a large system with memory of 1G-2G where the system runs mainly MySQL.
So I suggested some updating is needed on IRC and mailing lists. After getting a kick in the pants from the always affable Kurt von Finck (I thought “kick in the pants” is a pretty good rendition of “遣将不如激将”, for those who followed our exchanges in mailing lists), I decided to take this upon myself.
It’s actually not hard to update this. I believe just 6 files in total, at most, need to be touched under the support-files directory inside the trunk: my-small.cnf.sh, my-medium.cnf.sh, my-large.cnf.sh, my-huge.cnf.sh, my-innodb-heavy-4G.cnf.sh, and Makefile. Please let me know if my understanding is wrong or incomplete.
Here are a few ideas with the new version of sample my.cnf files, just to get started:
1. Remove the server-id = 1, add a comment like this: use the last octet of the server’s IP address might be a good idea;
2. Add
user = mysql
3. Remove any mention of replication. It is better to set up replication via command line, and let that information be saved into master.info
4. I am on the fence on this one: should I set the default character to utf8?
5. Simplicity is good. One implication of that is to make the sample my.cnf short, I think. Don’t put things there if they are not necessary or important;
6. Watch out for those per session settings, unless you enjoy seeing random crashes;
7. set wait_timeout to be 600?
8. Default is not necessarily bad;
I then started researching various settings for MyISAM and InnoDB, such as key_buffer_size, innodb_buffer_pool_size, table_cache, query_cache_size, etcetera. I came across a few interesting articles, such as Peter Zaitsev’s “What to tune in MySQL Server after installation”, dataStrangler’s “Custom MySQL config files to ensure maximum performance”, and Baron Schwartz’s tongue-in-cheek “The ultimate tool for generating optimal my.cnf files for MySQL”. It looks like it is pretty difficult to find something that is generic enough.
What do you think? What best practices should be converted into entries inside sample my.cnf? Or is this even necessary, after removing the long-in-the-tooth comment?
6 responses to “Seeking input on updating sample my.cnf files”
This has been a known issue for ages and it covered in great detail in the last 2 OurSQL podcasts, you may want to review their comments and the feedback they got.
Check http://technocation.org/ and/or follow @oursql in Twitter to track the feedback.
My $.02
G
I feel the samples should be removed completely.
It’s not possible to easily configure a MySQL installation without additional information including:
* What storage engines are used?
* What is the available RAM?
* Is the machine a DB machine or multi-purpose machine?
* Character set needs?
* etc
I would for example always enable slow query log, and binary log, however even that starts a debate about configuration options.
The best option really is to provide no option.
Providing a basic tool to help in initial configuration (the minimum defaults only) would be more ideal however this is not a trivial undertaking.
MySQL ships with some pretty poor config files. As you mentioned the BIG config is for a 2GB RAM server which is hardly big these days. The defaults are almost always limiting and need to be changed for big workloads and servers running with lots of ram and hundreds of GB of data. However, you have people like Baron who want users to pay him/percona for consulting services and not to use a cnf generator or a standardized cnf file… yet he refuses to share his information about what should be used for defaults or what his baseline cnf happens to be because he wants everyone to think he’s “The Baron of MySQL”, what a joke. Anyway, joking aside I think above all that DBAs should know their systems, workloads, and their application very well before using a standardized cnf file and not enable any settings that others are using without knowing what the settings do – since of course all workloads are different. Blind trust in any expert is foolishness. Hopefully one day MySQL/oracle will change the defaults to at least get rid of the replication nonsense that isn’t needed, enable extended logging by default, and various things that are useful that the defaults don’t have. But what do I know… I haven’t paid my pittance to percona to know how to tune a cnf 😉
Thanks guys for the comments!
@Ronald,
I know where you come from and I think enabling slow log and bin log is a good idea. I now think there is value in having just one sample my.cnf, mostly to give beginners something to start with, and to help people overcome the intimidation factor.
@strangl3r,
“Blind trust in any expert is foolishness”, definitely. I also agree that DBAs should know their systems, workloads, and applications.
However, I don’t share your sentiment toward Percona and Baron Schwartz at all. In fact, I have the exact opposite opinion. Maatkit and aspersa, which Baron is the chief author, are fantastic free tools that I, and I believe the whole community, have benefited from tremendously, not to mention the great blogs where Percona share their knowledge and experience extensively. On a personal level, I’ve interacted with Baron enough that I feel I can vouch for his credibility and integrity without hesitation. So no, I don’t believe that Baron/Percona posses some magic sauce for my.cnf and withholds that purposely to drive up their consulting rate.
Reader Daniël van Eeden emailed me the comment below:
Using the last octet as server id is a bad idea. Do you use the IPv4 or IPv6 address? using a MAC address from the first NIC might be a better choice.
The files should not be called medium, big or huge. Just the amount of RAM and some other identifications like myisam, innodb, utf8, master, slave, oltp, olap and such.
Maybe a script which combines hardware info and some questions about the work load could be a good idea?
And be sure to remove some obsolete options like master-host, skip-locking and BDB.
I’ve filed a bug some time ago about this issues with the Oracle distribution:
http://bugs.mysql.com/bug.php?id=60306
I do not agree with the fifth point.
5. Don’t put things there if they are not necessary or important.
I do always keep almost all the options commented those I may need at some time in the future.
For e.g. general/ slow logs. There are 3 reason for doing this.
a) When I need to check if general logs are enabled or not, I can quickly go to the “logging” section and I do not need to search. I know where to find it!
b) Versions: I have a common my.cnf file for both 5.0 and 5.1 where I simply uncomment the parameters of my version.
c) Syntax: When I need to add something for e.g. foreign_key_checks=0 There are chances that I may not remember the exact syntax and yes, sometimes I get confused with Underscores and dashes.