Sample starter my.cnf for different systems

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

Also of consideration to add is:

innodb_file_per_table
innodb_flush_method=O_DIRECT