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).