Best INNODB Configuration (my.cnf) file
Posted by grabameat on November 11, 2009
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 192.168.2.200
key_buffer = 16M
max_allowed_packet = 16000M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 1024M
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size=400M
innodb_log_file_size=25M
innodb_log_buffer_size=80M
sort_buffer=512M
thread_concurrency=32
record_buffer=128M
query_cache_type=2
tmp_table_size=1G
#log = /var/log/mysql/mysql.log
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#skip-innodb
# chroot = /var/lib/mysql/
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
* Turn off HyperThreading -- it tends to slow things down.
* key_buffer = 16M -- very small if you use any MyISAM. In which case, set it to 3000M. (It must not be bigger than 4G.) Since you are not using any MyISAM, leave it at 16M.
* query_cache_size = 1024M -- The bigger it is, the slower it runs. Suggest 200M.
* innodb_buffer_pool_size=2048M -- You have 16GB of RAM. Is it 'all' available to MySQL? If so, and you are using InnoDB exclusively, suggest 13G.
* If mixture of MyISAM and Innodb, adjust proportionally.
* InnoDB database with 1,7 GiB of data? Wasted your money on so much RAM.
* #log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
Do you have any control over the schema or queries? Perhaps not if you are using a canned product. If you did have control, I would recommend turning on the slowlog and using it to help tune things.
* #max_connections = 100 -- I don't know what the default is; keep an eye on max_used_connections. If you run out of connections set max_connections appropriately.
* #table_cache = 64 -- You can probably set this to something, then watch open_tables to see whether it needs to be bigger.
Executive summary: Your current settings are close to the best available.
This is one of the Best Configuration file from MYSQL
ADMINISTRATION BIBLE BOOK.
The configuration file supports options for multiple programs. Each program has its own directive,
which is a keyword in square brackets, such as [mysqld] or [client]. Here is a sample
configuration file from a production system:
# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
#this will prevent mysql from starting
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir =/var/lib/mysql
log_slow_queries = /var/log/mysql/mysqld_slow-queries.log
long_query_time = 4
max_connections=200
max_connect_errors = 400
wait_timeout=7200
connect_timeout=10
key_buffer = 512M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
table_cache = 1800
join_buffer_size = 8M
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 524288
myisam_sort_buffer_size = 256M
thread_cache_size = 384
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
362
MySQL Server Tuning 10
query_prealloc_size = 65536
query_alloc_block_size = 131072
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4
#innodb configuration
innodb_data_home_dir = /mysql/data
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_group_home_dir = /mysql/data/data
innodb_log_arch_dir = /mysql/data/data
innodb_buffer_pool_size = 24576M
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
sync-binlog = 1
innodb_support_xa = 0
innodb_thread_concurrency = 128
innodb_file_per_table
# binary logging is required for replication
log-bin=mysql-bin
max_binlog_size = 1024M
server-id = 4
slave-skip-errors = 1062
expire-logs-days = 7
[mysqldump]
quick
max_allowed_packet = 16M
[mysql.server]
user=mysql
group=mysql
basedir=/var/lib
[mysqld_safe]
nice = -5
open_files_limit = 8192
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
The configuration file supports options for multiple programs. Each program has its own directive,which is a keyword in square brackets, such as [mysqld] or [client]. Here is a sampleconfiguration file from a production system:# The following options will be passed to all MySQL clients[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]#this will prevent mysql from startingport = 3306socket = /var/lib/mysql/mysql.sockdatadir =/var/lib/mysqllog_slow_queries = /var/log/mysql/mysqld_slow-queries.loglong_query_time = 4max_connections=200max_connect_errors = 400wait_timeout=7200connect_timeout=10key_buffer = 512Mtmp_table_size = 32Mmax_heap_table_size = 32Mmax_allowed_packet = 32Mtable_cache = 1800join_buffer_size = 8Msort_buffer_size = 16Mread_buffer_size = 8Mread_rnd_buffer_size = 524288myisam_sort_buffer_size = 256Mthread_cache_size = 384bulk_insert_buffer_size = 8Mquery_cache_limit = 4Mquery_cache_size = 128Mquery_cache_type = 1362MySQL Server Tuning 10query_prealloc_size = 65536query_alloc_block_size = 131072# Try number of CPU’s*2 for thread_concurrencythread_concurrency = 4#innodb configurationinnodb_data_home_dir = /mysql/datainnodb_data_file_path=ibdata1:200M:autoextendinnodb_log_group_home_dir = /mysql/data/datainnodb_log_arch_dir = /mysql/data/datainnodb_buffer_pool_size = 24576Minnodb_additional_mem_pool_size = 32Minnodb_log_file_size = 1024Minnodb_log_files_in_group = 2innodb_log_buffer_size = 16Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 120sync-binlog = 1innodb_support_xa = 0innodb_thread_concurrency = 128innodb_file_per_table# binary logging is required for replicationlog-bin=mysql-binmax_binlog_size = 1024Mserver-id = 4slave-skip-errors = 1062expire-logs-days = 7[mysqldump]quickmax_allowed_packet = 16M[mysql.server]user=mysqlgroup=mysqlbasedir=/var/lib[mysqld_safe]nice = -5open_files_limit = 8192log-error=/var/log/mysql/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
