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
Brilliant post., man
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.
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!