Example InnoDB my.cnf file for high end GNU/Linux machines

  1. #----------------------------------------------------------------
  2. #
  3. # my.cnf file
  4. #
  5. #
  6. # See:
  7. #
  8. # http://dev.mysql.com/doc/refman/5.1/en/server-options.html
  9. # http://dev.mysql.com/doc/refman/5.1/en/option-files.html
  10. #
  11. # You can also dump all the variables set for mysqld with:
  12. #
  13. # mysqld --verbose --help
  14. #
  15. #----------------------------------------------------------------
  16. [client]
  17. # These options apply to all client applications
  18. # Port and the socket
  19. port = 3306
  20. socket = /tmp/mysqld.sock
  21. #password = my_password
  22. # Default character set to utf-8
  23. default_character_set = utf8
  24. #----------------------------------------------------------------
  25. [safe_mysqld]
  26. # Log file
  27. err_log = /home/poplar/mysql/logs/mysql.err
  28. #----------------------------------------------------------------
  29. [mysqld]
  30. # Skip options
  31. #skip_bdb
  32. #skip_innodb
  33. skip_locking
  34. #skip_networking
  35. # Server ID must be unique to allow for replication
  36. server_id = 9000
  37. # User name to run as
  38. user = mysql
  39. # Port and the socket
  40. port = 3306
  41. socket = /tmp/mysqld.sock
  42. # Bind to a specific address, otherwise listen to all addresses
  43. #bind_address = 127.0.0.1
  44. # Maximum number of connections
  45. max_connections = 2048
  46. # Connection backlog, raise this if we run out of
  47. # connections (128 is the linux default)
  48. back_log = 128
  49. # Maximum number of connection error per host
  50. max_connect_errors = 1000
  51. # Connection timeout
  52. connect_timeout = 2
  53. # Timeout for inactive connections
  54. wait_timeout = 60
  55. # Maximum packet length, no single MySQL statement can be longer than this
  56. max_allowed_packet = 16M
  57. # Network buffer length (I think this is the linux default)
  58. net_buffer_length = 8K
  59. # Set the default character set to utf8
  60. default_character_set = utf8
  61. # Set the server character set
  62. character_set_server = utf8
  63. # Set the default collation to utf8_general_ci
  64. default_collation = utf8_general_ci
  65. # Set the names to utf8 when a client connects
  66. init_connect = 'SET NAMES utf8; SET sql_mode = STRICT_TRANS_TABLES'
  67. # Server directories
  68. basedir = /usr/local/mysql
  69. datadir = /home/poplar/mysql/data
  70. tmpdir = /home/poplar/mysql/tmp
  71. # Language file location
  72. language = /usr/local/mysql/share/english
  73. # Error log file (need dash in variable name)
  74. log-error = /home/poplar/mysql/logs/mysqld.err
  75. # Process ID file (need dash in variable name)
  76. pid-file = /var/run/mysqld/mysqld.pid
  77. # Log slow queries, time threshold set by 'long_query_time',
  78. log_slow_queries = /home/poplar/mysql/logs/slow-queries.log
  79. log_output = FILE # 5.1 only
  80. long_query_time = 5
  81. # Log queries which don't use indices in the slow query log
  82. log_long_format
  83. # Enable this to get a log of all the statements coming from a client,
  84. # this should be used for debugging only as it generates a lot of stuff
  85. # very quickly
  86. #log = /home/poplar/mysql/logs/queries.log
  87. # Binary log and replication log file names prefix
  88. log_bin = /home/poplar/mysql/binary-logs/server1-bin
  89. relay_log = /home/poplar/mysql/binary-logs/server1-relay-bin
  90. # Binary log format, see:
  91. # http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
  92. # http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html
  93. binlog_format = row # 5.1 only
  94. # Binary log cache size
  95. binlog_cache_size = 1M
  96. # Skip automatic replication start up, replication will have to be
  97. # started manually with 'start slave' once the server is started
  98. skip_slave_start
  99. # Make the slave read-only
  100. #read_only
  101. # Select which replication errors to skip, see
  102. # http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
  103. #slave_skip_errors = 1062
  104. # Select which databases/tables to ignore during replication, see
  105. # http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
  106. #replicate_ignore_db =
  107. #replicate_ignore_table =
  108. #replicate_do_db =
  109. #replicate_do_table =
  110. # Number of open tables at any one time
  111. table_cache = 4096
  112. # Join buffer size for index-less joins
  113. join_buffer_size = 8M
  114. # Maximum size for in memory temporary tables, anything
  115. # larger gets spun out to disc
  116. tmp_table_size = 64M
  117. # Sort buffer size for ORDER BY and GROUP BY queries, data
  118. # gets spun out to disc if it does not fit
  119. sort_buffer_size = 8M
  120. # Thread cache size, concurrency and stack
  121. thread_cache_size = 64
  122. thread_concurrency = 8
  123. thread_stack = 192K
  124. # Query cache, disabled for now
  125. query_cache_size = 0
  126. query_cache_type = 1
  127. query_cache_limit = 2M
  128. # Default table storage engine when creating new tables
  129. # (comment out when running mysql_install_db)
  130. default_storage_engine = InnoDB
  131. # Default transaction isolation level, levels available are:
  132. # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
  133. # see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
  134. transaction_isolation = REPEATABLE-READ
  135. # MyISAM options, see:
  136. # http://dev.mysql.com/doc/refman/5.1/en/myisam-start.html
  137. key_buffer_size = 256M
  138. read_buffer_size = 2M
  139. read_rnd_buffer_size = 8M
  140. myisam_sort_buffer_size = 128M
  141. bulk_insert_buffer_size = 64M
  142. myisam_max_sort_file_size = 10G
  143. myisam_repair_threads = 2
  144. #myisam_recover_options = DEFAULT
  145. # InnoDB options, see:
  146. # http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
  147. # Data directory, and data file
  148. innodb_data_home_dir = /home/poplar/mysql/data
  149. innodb_data_file_path = ibdata1:10M:autoextend
  150. # Use one file per table
  151. innodb_file_per_table
  152. # Buffer pool size
  153. innodb_buffer_pool_size = 2G # 4GB RAM
  154. #innodb_buffer_pool_size = 10G # 16GB RAM
  155. innodb_additional_mem_pool_size = 32M
  156. # Transaction log location and sizes
  157. innodb_log_group_home_dir = /home/poplar/mysql/innodb-logs
  158. innodb_log_files_in_group = 4
  159. innodb_log_file_size = 128M # 4GB RAM
  160. #innodb_log_file_size = 512M # 16GB RAM
  161. innodb_log_buffer_size = 8M
  162. # Percentage of unwritten dirty pages not to exceed
  163. innodb_max_dirty_pages_pct = 80
  164. # Transaction commit policy
  165. innodb_flush_log_at_trx_commit = 1
  166. # Timeout to wait for a lock before rolling back a transaction
  167. innodb_lock_wait_timeout = 50
  168. # Flush method
  169. innodb_flush_method = O_DIRECT
  170. # Number of concurrent threads to run
  171. innodb_thread_concurrency = 32
  172. # Autoinc lock mode ('consecutive' lock mode), see:
  173. # http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
  174. innodb_autoinc_lock_mode = 1
  175. # Prevent extra locking, we can only use this if we have row
  176. # level replication, see 'binlog_format'
  177. innodb_locks_unsafe_for_binlog # 5.1 only
  178. # Enable fast innodb shutdown (skip full purge and insert buffer merge)
  179. innodb_fast_shutdown = 1
  180. #innodb_fast_shutdown = 0
  181. # Dont delay insert, update and delete operations when purge
  182. # operations are lagging
  183. innodb_max_purge_lag = 0
  184. #innodb_max_purge_lag = 1
  185. # Force recovery mode, do not mess with unless you really know
  186. # what you are doing, see:
  187. # http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
  188. #innodb_force_recovery = 4
  189. #----------------------------------------------------------------
  190. [mysqldump]
  191. # Enable quick dumping
  192. quick
  193. # Set the max allowed packet size
  194. max_allowed_packet = 16M
  195. #----------------------------------------------------------------
  196. [mysql]
  197. # Disable tab completion in mysql
  198. #no_auto_rehash
  199. #----------------------------------------------------------------
  200. [myisamchk]
  201. # Set the buffer sizes used by myisamchk when checking/rebuilding
  202. # databases
  203. key_buffer = 256M
  204. sort_buffer = 256M
  205. read_buffer = 64M
  206. write_buffer = 64M
  207. #----------------------------------------------------------------
  208. [mysqlhotcopy]
  209. # No clue
  210. interactive_timeout
  211. #----------------------------------------------------------------

Post By Gishore J Kallarackal (2,121 Posts)

Gishore J Kallarackal is the founder of techgurulive. The purpose of this site is to share information about free resources that techies can use for reference. You can follow me on the social web, subscribe to the RSS Feed or sign up for the email newsletter for your daily dose of tech tips & tutorials. You can content me via @twitter or e-mail.

Website: → Techgurulive

Connect