RSS

Tag Archives: innodb configuration

Improving Replication Performance

Have  you ever seen your replica not catching upto speed even though you have everything setup properly in your config file, i faced this situation when i setup a new replication slave and replication lag keeps increasing or not catching up fast,  tried many ways to tweak the my.cnf  configuration file, increased memory, modified buffer_pool_size to the max limit of the memory, increased additional buffer, added more cpu’s does not helped me much to fix the replication lag. finally after reading to some other blogs i found  innodb_flush_log_at_trx_commit =1 is the reason for replication lag,

When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file

Solution: Set the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.

innodb_flush_log_at_trx_commit = 1

Hope this helps..

Advertisements
 

Tags: , , , , , , , , ,

Best INNODB Configuration (my.cnf) file

[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

 
4 Comments

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

 

Tags: , , , ,

 
%d bloggers like this: