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;
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?