Example my.cnf files

This blog has moved to http://brian.moonspot.net/.

UPDATE: There are some examples being added at the MySQL Forge now.

When I first started installing MySQL for myself, it was quite handy to have the example my.cnf files in the source package. I was a noob to the MySQL configuration. Even after I became more experienced, I would use them as a starting point. However, I now find that they are so behind the times they are not as useful. Here are some of the comments from the files.

my-small.cnf

# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it’s important that the mysqld daemon
# doesn’t use much resources.

my-medium.cnf

# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)

my-large.cnf

# This is for a large system with memory = 512M where the system runs mainly
# MySQL.

my-huge.cnf

# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.

I end up using the large or huge files as a starting point for every server I set up by hand. The small and medium should be renamed underpowered and teeny-tiny. Who has less than 64MB of RAM on a server now? Can you even buy sticks of memory that small in any modern system? Most come with 256MB sticks minimum. And they never come with just one stick.

I will use the large example as a starting point for a server that has 2GB of RAM and will be running an entire site on one server. I use huge for any server that runs only MySQL. And even then, most of them have 4GB of RAM or more.

I don’t know if anyone at MySQL has plans on tweaking these files or not. Perhaps those good guys at the MySQL Performance Blog or Percona could create some example my.cnf files. I could put some out there, but I fear their sole purpose would be for someone to point out what I am doing wrong. =P Hey, they work for me. Hmm, maybe this would make a good MySQL Forge section. A whole area of user contributed my.cnf files. They could be architecture specific and everything. What runs best on Solaris? Linux? BSD? Windows? 32-bit? 64-bit?

One thing I would for sure like to see is example files for InnoDB dominant servers. Most of our servers all run primariy InnoDB tables. None of these above examples covers InnoDB. They have comments, but no preconfigured values. I have seen more than one server using InnoDB tables without any custom configuration in their my.cnf. In the end that is the fault of the server admin/owner no doubt.

What do you say? Anyone up for a MySQL Forge section for my.cnf files?

About these ads

9 Responses to Example my.cnf files

  1. I have a good sample my.cnf file for a large innodb installation which I would be happy to share with you if you are interested. I spent a lot of time tweaking it when I was at Feedster.

  2. Vid Luther says:

    I’d love to see this happen as well. I’ve done the same thing you’ve done Brian, I just change the values by the factor of RAM I have now, and then tweak things where they need to be tweaked, but I am by no means an expert in MySQL Performance.

  3. One of Jay Pipes first ideas in creating MySQL Forge (http://forge.mysql.com) was to make it possible for community people to share their configs….maybe someone should just start the craze by simply submitting it as a “code snippet” (and please, use the description tab to document whatever this config is supposed to achieve) and blogging about it as hell….please “spam” planetmysql about it, it seems to be a sure recipe for others to join in…

    As we can all vote for the planetmysql entries now, we should all be in a good position to find out which configs are appreciated most.

    Roland Bouman

    http://rpbouman.blogspot.com/

  4. Tom Krouper says:

    I’d love to see config files. I think it could be an incredible teaching and learning experience for a lot of people. I know in my current position I don’t have a ton of time to tweak the my.cnf files on our servers. It’d be helpful to see what others are doing, or even how they are using their configs. I’m looking into using !includedir. I’m not sure how common that is. Also, I know looking at the docs, there are a lot more option file items than there are in the generic my.cnf files that MySQL provides. I’m not at work, so I can’t submit my config right now. :-)

  5. I’ve posted my InnoDB sample config for 8G machines as a snippet on MySQLforge.

    http://forge.mysql.com/tools/tool.php?id=137

  6. Brian Moon says:

    I added a mycnf tag to your snippet and hope others will follow.

    http://forge.mysql.com/tools/search.php?t=tag&k=mycnf

  7. Thanks! I knew I forgot something ;)

  8. Just wanted to let you know that I uploaded my my.cnf file (same tag), it can be used for a 4GB machine or a 16GB machine, values are provided for both sizes.

  9. […] — François Schiettecatte @ 12:29 pm I came across MySQL Forge a few days ago when Brian Moon was asking about sample my.cnf configuration files for MySQL. The samples provided in the distribution are […]

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: