Mani's Blog

November 15, 2011

Sample MySQL my.cnf file for OLTP

Filed under: Database,MySQL — mani @ 11:54 pm

This is sample my.cnf file for MySQL server running on 32G memory and 200G data for OLTP operations.   With Queries per second avg: 1321.714 and 35 threads.  Configured with Statement based replication.

[client]
socket = /tmp/mysql.sock
port = 3306

[mysql]
prompt = (\\u@\\h) [\\d]>\\_
default-character-set = utf8

[mysqld]
core-file
socket = /tmp/mysql.sock
port = 3306
pid-file = /local/mysql/data/mysqld.pid
old-passwords = 0
skip-name-resolve
#basedir = /usr/bin
datadir = /local/mysql/data
back_log = 100
max_connections = 1000
max_connect_errors = 10000
key_buffer_size = 16M
myisam_sort_buffer_size = 32M
max_tmp_tables = 64
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 3000
query_cache_limit = 16M
query_cache_size = 0M
query_cache_type = 0
thread_cache = 50
thread_stack = 512k
wait_timeout = 500
interactive_timeout = 500
net_read_timeout = 15
net_write_timeout = 30
tmp_table_size = 64M
max_heap_table_size = 512M
group_concat_max_len = 5120
max_allowed_packet = 256M
local_infile = 0
character_set_client = utf8
character_set_server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake

performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

slow-query-log = 1
long_query_time = 1
log-queries-not-using-indexes
slow-query-log-file = /var/log/mysqld/mysqld.slow.log

default_storage_engine = INNODB
innodb_file_format = barracuda
innodb_file_per_table = 1
#innodb_use_native_aio = 0

innodb_log_group_home_dir = /local/mysql/data
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

transaction_isolation = REPEATABLE-READ
innodb_data_home_dir = /local/mysql/data
innodb_data_file_path = ibdata1:512M:autoextend
innodb_buffer_pool_size = 24G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 20M
innodb_doublewrite = 1
innodb_checksums = 1
innodb_io_capacity = 400
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_locks_unsafe_for_binlog = 0
innodb_max_purge_lag = 0
innodb_fast_shutdown = 0

# replication
server-id = 1
sync_binlog = 1
binlog-format = STATEMENT
log-bin=/local/mysql/mojo/binary-logs
binlog_cache_size=262144
log_slave_updates
#skip-slave-start
relay-log=/local/mysql/mojo/mysqld-relay-bin
relay-log-index=/local/mysql/mojo/mysqld-relay-bin

[mysqld_safe]
log-error = /var/log/mysqld/mysqld.err
open-files-limit = 65535

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: