The 5 minute DBA: Default My.cnf File

Because I was asked….What should I set my my.cnf parameters to? What are good default values? How much memory should I allocate to the db if I have X amount of ram? What is a good starting point for the mysql config files?

You’re not really Googling for a my.cnf to use are you? You probably are, Shame on you!  The best thing to do is to test before you make changes, and find the best configuration for your application.  But your not going to are you? oh well I might as well accommodate you. I make no claims these will work for everyone. In fact if you hire me later on I may look at you funny after I analyze your system, and may call you funny names behind your back for using the wrong settings.  Because there are a lot of people out their who are only database folks 5 minutes at a time (that’s what these posts are about), they are probably going to stick the my-huge.cnf and go with that anyways.  So why not throw out some common my.cnf’s I use. Keep in mind, these are the most common, they may cause weird things to happen in some environments… that’s why we always say test and benchmark before using. But if your googling for it, chances are your going to put it in anyways:)

And for those who spend more then 5 minutes a day as a DBA, please post your comments and make me look foolish for forgetting your favorite parameter. Remember these are generic starting points, for people who are not going to have a ton of time.

Alright here goes:

32 bit system
2GB of memory
Dedicated DB Box
All innodb tables
32 bit system
4GB of memory
Dedicated DB Box
All Innodb tables
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 64
table_cache = 64
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 64
table_cache = 64
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=16M
max_heap_table_size=16M
query_cache_size=64M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=3
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size =2048M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
32 bit system
8GB+ of memory
Dedicated DB Box
All Innodb tables
64 bit system
8GB of memory
Dedicated DB Box
All innodb tables
******  Go download a 64Bit OS.  ****** [mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 128
table_cache = 256
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 5120M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
64 bit system
16GB of memory
Dedicated DB Box
All Innodb tables
64 bit system
32GB of memory
Dedicated DB Box
All Innodb tables
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 128
table_cache = 512
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 12288M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 12
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /mysql/
datadir = /data01/data
tmpdir = /tmp
thread_cache_size = 256
table_cache = 1024
key_buffer = 64M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=1
log_output=FILE
slow_query_log_file=/mysql/slow1.log
slow_query_log=1
long_query_time=2
log-error=/mysql/error.log
innodb_data_home_dir = /data01/data
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size =24676M
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 16

NOTE: If you change you log file size, you will get errors unless you move the old ones and allow innodb to recreate them ( do it with the DB down by the way )… once again I offer no warranty. You may also want to turn of swappiness to avoid swapping (http://www.bigdbahead.com/?p=101).

This entry was posted in 5 minute dba, mysql, performance. Bookmark the permalink.

7 Responses to The 5 minute DBA: Default My.cnf File

  1. Add in one more thing:

    innodb_file_per_table

    It will help you reclaim space as your schema changes and works around bugs I have seen in the past with large data sets.

  2. SaveTheRbtz says:

    innodb_flush_method=O_DIRECT
    Is it dangerous without UPS?

  3. Agreed on the innodb_file_per_table parameter.

    Also, group=mysql. It will allow you to have group-readable log files, so you can have automated processes from other users/roles (say “dbaadmin” or similar, we often have the “pythian” user on client systems for this purpose) to check the logs. Otherwise the group will be the group that you started safe_mysqld as (likely root).

  4. Why table_cache or 128 in some cases ?

  5. matt says:

    ooops noticed that the table cache & thread cache on the 32gb system were 64 which is too low. That’s what you get for starting off with a cut an paste.

    I am indifferent to file per table, but that’s just me… don’t get me wrong it has its place, I just generally don’t switch someone unless their is a need. I would assume that someone would grab the above config and copy it into their my.cnf ( which I wouldn’t ), file per table in that case is going to make a mess with all new tables going as file per table, all old ones being single data file.

    On the table cache, Yep I could have gone higher, especially with larger data sets, as I mentioned noticed my cut and paste issue. With table cache its hard to really say what’s needed, same with thread cache ( as I tried to mention I don’t think anyone should use canned config files, but their going to anyways ). Why 128? Why not, for the lower ones its double the default. As I mentioned I will bump up the bigger ones as that was just wrong to begin with. But the bottom line for all of these is they are a starting point, Better then what I am seeing most people start out with.

    O_direct should not hurt without a UPS.

  6. Marjeta says:

    Matt, thank you very much! I’m a 5-minute DBA and I hope you will write many more 5-minute articles.

    A question – you list various default My.cnf files, depending on memory size and stuff, but “All Innodb tables” seems to be constant in all your examples. Can you do a 5-minute DBA article on choosing the engine?

  7. I’d also like to see the join_buffer specified when you specify the other 3 core per-session buffers of (sort_buffer,read_buffer,read_rnd_buffer)