Joe Hruska . com

What do I want to be when I grow up?

Example MySQL configuration tuned for InnoDB engine

Someone asked for a sample InnoDB configuration and since nearly all of the MySQL tables in RescueTime are InnoDB, I thought I would post this.

################# /etc/my.cnf ###################
[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
datadir                              = /db/data/mysql
log-bin                              = /db/log/mysql/
mysqld-binlog-bin-index                        = /db/log/mysql/mysqld-bin.index
expire-logs-days                     = 7
log-error                            = /db/log/mysql/mysqld.err
log-slow-queries
long-query-time                      = 1
relay-log                            = /db/log/mysql/mysql-relay
relay-log-index                      = /db/log/mysql/mysql-relay.index
#default-storage-engine              = innobase
innodb_data_home_dir                 = /db/data/mysql
innodb_file_per_table
innodb_autoextend_increment          = 50
innodb_log_group_home_dir            = /db/log/mysql
innodb_log_files_in_group            = 2
innodb_log_file_size                 = 100M

# Start Replication Parameters
#server-id                           = 1
innodb_flush_log_at_trx_commit       = 1
#sync-binlog                         = 1
# End Replication Parameters

# Start Performance Parameters
#tmpdir                              = /tmp-ram
max_connections                      = 50
max_heap_table_size                  = 512M
tmp_table_size                       = 512M
table_cache                          = 128
sort_buffer_size                     = 4M
query_cache_min_res_unit             = 1K
query_cache_limit                    = 1M
query_cache_size                     = 100M
max_allowed_packet                   = 16M
thread_stack                         = 128K
thread_cache_size                    = 8
innodb_buffer_pool_size              = 2000M
innodb_additional_mem_pool_size      = 4M
innodb_lock_wait_timeout             = 2
innodb_file_io_threads               = 4
innodb_thread_concurrency            = 8
innodb_flush_method                  = O_DIRECT
transaction-isolation                = READ-COMMITTED
skip-external-locking
# End Performance Parameters

################# /etc/my.cnf ###################
3 comments Digg this

3 Comments so far

  1. Fredericzb March 24th, 2008 12:06 pm

    Brilliant post., man

  2. Mark Maunder April 10th, 2008 5:23 pm

    Firsty, thanks LOADS for the blog entry re clustered indexes. I’ve just redesigned our schema to have one clustered index per table and no others and wow.

    I noticed in your config you have innodb_flush_log_at_trx_commit = 1

    Setting this to 0 will roughly double your performance. The down side is that if you lose power to the machine you may lose a few seconds worth of transactions. I _think_ a battery backed cache on a RAID controller may decrease the likelihood you’ll lose data if you use this setting, but I may be wrong.

    Mark.

  3. Joe April 10th, 2008 5:35 pm

    I’m glad you got some use out of this Mark.

    You are right on the trx_commit change. I haven’t set this to 0 yet specifically because we are on commodity hardware and wanted to see how a “safer” flush was going to accommodate our slave db.

    Hopefully soon we’ll have the money to get our db running on a real server ;) 15k SCSI disks, raid 10, battery backed cache, mmmmm good!