RSS

Best INNODB Configuration (my.cnf) file

11 Nov

[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

About these ads
 
4 Comments

Posted by on November 11, 2009 in Database Administration, MySql

 

Tags: , , , ,

4 responses to “Best INNODB Configuration (my.cnf) file

  1. J.Anuraja

    November 6, 2011 at 3:13 pm

    sir, we have mysql database have 55 lak data(8.5GB data). we have 200 users. while 125 users connect to database through our application, saving time is taking is less than 2,3 sec only. it goes to more than 125 users connect database saving time is taking toomuch time for ex: 15 to 40 sec. please guide me. we have 8GB ram 2*quad core processor DELL R510 server., linux RHEL 5.4. please guide me. this is my maid id jvanurajecil@gmail.com

    by J.Anuraja

     
    • grabameat

      January 22, 2012 at 2:31 pm

      sorry for my late email, what kind of configuration are you using for MYSQL. if you can send me the config file, i will give you my recommendations on speeding up the process. beyond that please answer me couple of questions..

      1) how often you see transactions from users (very fast, moderate or slow)
      2) what number did you set for key buffer cache.
      3) what kind of storage engine are you using (INNODB, MYISAM)
      4) how much buffer pool size did you allocate to mysql.

      send me those answers, i will send you a reply soon.
      sorry for late response again.

      Thanks,
      Sree.

       
  2. Aix

    February 4, 2013 at 7:01 pm

    hi please advise me on best configurations esp on innodb configurations.
    here is the info:
    1. all tables are innodb
    2. there are 8 processors
    3. ram is 10gb
    4. key buffer cache is 1073741824
    5. buffer pool size is 2147483648
    please reply soon.
    thank you.

     
    • grabameat

      March 12, 2013 at 12:15 pm

      got tools.percona.com and provide required information, this will give you the best configuration.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: