This is an example config file for a 64bit 8GB GNU/Linux machine running mainly InnoDB.

This config file will not work on a 32bit instance as it is configured to address more memory than 32bit procceses allow.

This configuration assumes that this will be a replication master. If this is a slave and it doesn’t have any additional slaves reading (ie, this isn’t a relay slave) then you may want to turn off: sync_binlog log_bin log_slave_updates

  1. [client]
  2. #password = [your_password]
  3. port = 3306
  4. socket = /tmp/mysqld.sock
  5. # *** Application-specific options follow here ***
  6. #
  7. # The MySQL server
  8. #
  9. [mysqld]
  10. # generic configuration options
  11. port = 3306
  12. socket = /tmp/mysqld.sock
  13. # sync_binlog ensures that all writes to the binary log are immediately
  14. # flushed to disk. This is important, especially for replication, because
  15. # if the server crashes and has not written all of the binary log to disk (and flushed it)
  16. # then some rows will not make it to the slave
  17. sync_binlog=1
  18. # back_log is the number of connections the operating system can keep in
  19. # the listen queue, before the MySQL connection manager thread has
  20. # processed them. If you have a very high connection rate and experience
  21. # "connection refused" errors, you might need to increase this value.
  22. # Check your OS documentation for the maximum value of this parameter.
  23. # Attempting to set back_log higher than your operating system limit
  24. # will have no effect.
  25. back_log = 50
  26. max_connections = 301
  27. # I don't know why 0 doesn't disable max_connect_errors checking
  28. # but it doesn't, so set it to a high value to prevent MySQL from
  29. # refusing to accept connections from a flaky host, especially if you
  30. # are using a load balancer!
  31. max_connect_errors = 9999999
  32. # The number of open tables for all threads.
  33. # make sure that the open file limit is at least twice this in the
  34. # mysqld_safe section
  35. table_cache = 4096
  36. # The maximum size of a query packet the server can handle as well as
  37. # maximum query size server can process (Important when working with
  38. # large BLOBs). enlarged dynamically, for each connection.
  39. max_allowed_packet = 16M
  40. # The size of the cache to hold the SQL statements for the binary log
  41. # during a transaction. If you often use big, multi-statement
  42. # transactions you can increase this value to get more performance. All
  43. # statements from transactions are buffered in the binary log cache and
  44. # are being written to the binary log at once after the COMMIT. If the
  45. # transaction is larger than this value, temporary file on disk is used
  46. # instead. This buffer is allocated per connection on first update
  47. # statement in transaction
  48. binlog_cache_size = 1M
  49. # Maximum allowed size for a single HEAP (in memory) table. This option
  50. # is a protection against the accidential creation of a very large HEAP
  51. # table which could otherwise use up all memory resources.
  52. max_heap_table_size = 64M
  53. # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
  54. # queries. If sorted data does not fit into the sort buffer, a disk
  55. # based merge sort is used instead - See the "Sort_merge_passes"
  56. # status variable. Allocated per thread if sort is needed.
  57. sort_buffer_size = 8M
  58. # This buffer is used for the optimization of full JOINs (JOINs without
  59. # indexes). Such JOINs are very bad for performance in most cases
  60. # anyway, but setting this variable to a large value reduces the
  61. # performance impact. See the "Select_full_join" status variable for a
  62. # count of full JOINs. Allocated per thread if full join is found
  63. join_buffer_size = 8M
  64. # How many threads we should keep in a cache for reuse. When a client
  65. # disconnects, the client's threads are put in the cache if there aren't
  66. # more than thread_cache_size threads from before. This greatly reduces
  67. # the amount of thread creations needed if you have a lot of new
  68. # connections. (Normally this doesn't give a notable performance
  69. # improvement if you have a good thread implementation.)
  70. thread_cache_size = 16
  71. # Query cache is used to cache SELECT results and later return them
  72. # without actual executing the same query once again. Having the query
  73. # cache enabled may result in significant speed improvements, if your
  74. # have a lot of identical queries and rarely changing tables. See the
  75. # "Qcache_lowmem_prunes" status variable to check if the current value
  76. # is high enough for your load.
  77. # Note: In case your tables change very often or if your queries are
  78. # textually different every time, the query cache may result in a
  79. # slowdown instead of a performance improvement.
  80. query_cache_size = 128M
  81. # Only cache result sets that are smaller than this limit. This is to
  82. # protect the query cache of a very large result set overwriting all
  83. # other query results.
  84. query_cache_limit = 4M
  85. # query_alloc_block_size controls how much memory is reserved for
  86. # parsing SQL statements and some other junk. I increase it on boxes
  87. # that run complex queries to reduce possible memory fragmentation. YMMV
  88. # default is 8k
  89. query_alloc_block_size = 16K
  90. # if your OS supports it, you can lock the buffer pool into memory
  91. # with this option to prevent swapping. I'm not sure if linux supports this
  92. # but Solaris does. On linux, using the swapiness sysctl is probably nearly
  93. # as effective.
  94. #memlock
  95. # Table type which is used by default when creating new tables, if not
  96. # specified differently during the CREATE TABLE statement.
  97. default_table_type = InnoDB
  98. # Thread stack size to use. This amount of memory is always reserved at
  99. # connection time. MySQL itself usually needs no more than 64K of
  100. # memory, while if you use your own stack hungry UDF functions or your
  101. # OS requires more stack for some operations, you might need to set this
  102. # to a higher value.
  103. thread_stack = 192K
  104. # Set the default transaction isolation level. Levels available are:
  106. # REPEATABLE-READ requires next-key locks. If your application isn't sensitive # to 'phantom rows', (it probably isn't) then read-committed is fine. Feel
  107. # free to change this to REPEATABLE-READ at a small performance penalty if it
  108. # makes you feel better.
  109. transaction_isolation = READ-COMMITTED
  110. # Maximum size for internal (in-memory) temporary tables. If a table
  111. # grows larger than this value, it is automatically converted to disk
  112. # based table This limitation is for a single table. There can be many
  113. # of them. Also, if max_heap_table_size < tmp_table_size, it will be used
  114. # as the limit instead, so making it bigger than that is not useful.
  115. tmp_table_size = 64M
  116. # Enable binary logging. This is required for acting as a MASTER in a
  117. # replication configuration. You also need the binary log if you need
  118. # the ability to do point in time recovery from your latest backup.
  119. # it is recommened to specify a filename for the binary log, hopefully
  120. # something that is not host specific. I've chosen master-bin.
  121. log-bin=master-bin
  122. # If you're using replication with chained slaves (A->B->C), you need to
  123. # enable this option on server B. It enables logging of updates done by
  124. # the slave thread into the slave's binary log.
  125. log_slave_updates
  126. # Enable the full query log. Every query (even ones with incorrect
  127. # syntax) that the server receives will be logged. This is useful for
  128. # debugging, it is usually disabled in production use.
  129. #log
  130. # Log warnings to the error log
  131. log_warnings
  132. # Log slow queries. Slow queries are queries which take more than the
  133. # amount of time defined in "long_query_time" or which do not use
  134. # indexes well, if log_long_format is enabled. It is normally good idea
  135. # to have this turned on if you frequently add new queries to the
  136. # system.
  137. log_slow_queries
  138. # All queries taking more than this amount of time (in seconds) will be
  139. # trated as slow. Do not use "1" as a value here, as this will result in
  140. # even very fast queries being logged from time to time (as MySQL
  141. # currently measures time with second accuracy only).
  142. long_query_time = 1
  143. # Log more information in the slow query log. Normally it is good to
  144. # have this turned on. This will enable logging of queries that are not
  145. # using indexes in addition to long running queries.
  146. log_long_format
  147. # The directory used by MySQL for storing temporary files. For example,
  148. # it is used to perform disk based large sorts, as well as for internal
  149. # and explicit temporary tables. It might be good to put it on a
  150. # swapfs/tmpfs filesystem, if you do not create very large temporary
  151. # files. Alternatively you can put it on dedicated disk. You can
  152. # specify multiple paths here by separating them by ";" - they will then
  153. # be used in a round-robin fashion.
  154. # BE CAREFUL IF YOU PUT `tmpdir` ON tmpfs. You should set slave_load_tmpdir
  155. # to a non-tmpfs filesystem if you do that
  156. tmpdir = /tmp
  157. slave_load_tmpdir = /tmp
  158. # *** Replication related settings
  159. # Unique server identification number between 1 and 2^32-1. This value
  160. # is required for both master and slave hosts. It defaults to 1 if
  161. # "master-host" is not set, but will MySQL will not function as a master
  162. # if it is omitted.
  163. server-id = 168291
  164. # Make the slave read-only. Only users with the SUPER privilege and the
  165. # replication slave thread will be able to modify data on it. You can
  166. # use this to ensure that no applications will accidently modify data on
  167. # the slave instead of the master
  168. #read_only
  169. # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
  170. # Do not set it larger than 30% of your available memory, as some memory
  171. # is also required by the OS to cache rows. Even if you're not using
  172. # MyISAM tables, you should still set it to 8-64M as it will also be
  173. # used for internal temporary disk tables.
  174. key_buffer_size = 32M
  175. # Size of the buffer used for doing full table scans of MyISAM tables.
  176. # Allocated per thread, if a full scan is needed.
  177. read_buffer_size = 2M
  178. # When reading rows in sorted order after a sort, the rows are read
  179. # through this buffer to avoid disk seeks. You can improve ORDER BY
  180. # performance a lot, if set this to a high value.
  181. # Allocated per thread, when needed.
  182. read_rnd_buffer_size = 16M
  183. # MyISAM uses special tree-like cache to make bulk inserts (that is,
  184. # INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
  185. # INFILE) faster. This variable limits the size of the cache tree in
  186. # bytes per thread. Setting it to 0 will disable this optimisation. Do
  187. # not set it larger than "key_buffer_size" for optimal performance.
  188. # This buffer is allocated when a bulk insert is detected.
  189. bulk_insert_buffer_size = 64M
  190. # This buffer is allocated when MySQL needs to rebuild the index in
  191. # REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE
  192. # into an empty table. It is allocated per thread so be careful with
  193. # large settings.
  194. myisam_sort_buffer_size = 128M
  195. # The maximum size of the temporary file MySQL is allowed to use while
  196. # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
  197. # If the file-size would be bigger than this, the index will be created
  198. # through the key cache (which is slower).
  199. myisam_max_sort_file_size = 10G
  200. # If the temporary file used for fast index creation would be bigger
  201. # than using the key cache by the amount specified here, then prefer the
  202. # key cache method. This is mainly used to force long character keys in
  203. # large tables to use the slower key cache method to create the index.
  204. myisam_max_extra_sort_file_size = 10G
  205. # If a table has more than one index, MyISAM can use more than one
  206. # thread to repair them by sorting in parallel. This makes sense if you
  207. # have multiple CPUs and plenty of memory.
  208. myisam_repair_threads = 1
  209. # Automatically check and repair not properly closed MyISAM tables.
  210. myisam_recover
  211. # *** BDB Specific options ***
  212. # Use this option if you run a MySQL server with BDB support enabled but
  213. # you do not plan to use it. This will save memory and may speed up some
  214. # things.
  215. skip-bdb
  216. # *** INNODB Specific options ***
  217. # Use this option if you have a MySQL server with InnoDB support enabled
  218. # but you do not plan to use it. This will save memory and disk space
  219. # and speed up some things.
  220. #skip-innodb
  221. # Additional memory pool that is used by InnoDB to store metadata
  222. # information. If InnoDB requires more memory for this purpose it will
  223. # start to allocate it from the OS. As this is fast enough on most
  224. # recent operating systems, you normally do not need to change this
  225. # value. SHOW INNODB STATUS will display the current amount used.
  226. innodb_additional_mem_pool_size = 32M
  227. # This config file assumes a main memory of at least 8G
  228. innodb_buffer_pool_size = 6.5G
  229. # InnoDB stores data in one or more data files forming the tablespace.
  230. # If you have a single logical drive for your data, a single
  231. # autoextending file would be good enough. In other cases, a single file
  232. # per device is often a good choice. You can configure InnoDB to use raw
  233. # disk partitions as well - please refer to the manual for more info
  234. # about this.
  235. # to prevent fragmentation of the InnoDB tablespace, either create a
  236. # very big initial datafile, or set the autoextend amount to a large
  237. # value. The disadvantage of using a large autoextend size is that the
  238. # server may take some time to extend the file when needed
  239. # can't specify tablespace sizes for innodb-file-per-table tablespaces
  240. # so using a big autoextend is preferable in those cases.
  241. innodb_data_file_path = ibdata1:1G:autoextend
  242. innodb_autoextend_increment=128M
  243. innodb_file_per_table
  244. # Set this option if you would like the InnoDB tablespace files to be
  245. # stored in another location. By default this is the MySQL datadir.
  246. #innodb_data_home_dir = <directory>
  247. # Number of threads allowed inside the InnoDB kernel. The optimal value
  248. # depends highly on the application, hardware as well as the OS
  249. # scheduler properties. A too high value may lead to thread thrashing.
  250. innodb_thread_concurrency = 16
  251. # If set to 1, InnoDB will flush (fsync) the transaction logs to the
  252. # disk at each commit, which offers full ACID behavior. If you are
  253. # willing to compromise this safety, and you are running small
  254. # transactions, you may set this to 0 or 2 to reduce disk I/O to the
  255. # logs. Value 0 means that the log is only written to the log file and
  256. # the log file flushed to disk approximately once per second. Value 2
  257. # means the log is written to the log file at each commit, but the log
  258. # file is only flushed to disk approximately once per second.
  259. innodb_flush_log_at_trx_commit = 1
  260. # Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
  261. # and insert buffer merge on shutdown. It may increase shutdown time a
  262. # lot, but InnoDB will have to do it on the next startup instead.
  263. #innodb_fast_shutdown
  264. # The size of the buffer InnoDB uses for buffering log data. As soon as
  265. # it is full, InnoDB will have to flush it to disk. As it is flushed
  266. # once per second anyway, it does not make sense to have it very large
  267. # (even with long transactions).
  268. innodb_log_buffer_size = 8M
  269. # Size of each log file in a log group. You should set the combined size
  270. # of log files to about 25%-100% of your buffer pool size to avoid
  271. # unneeded buffer pool flush activity on log file overwrite. However,
  272. # note that a larger logfile size will increase the time needed for the
  273. # recovery process.
  274. # make sure the log files are large enough that you don't hold up
  275. # checkpoints when the logs rotate!
  276. innodb_log_file_size = 1G
  277. # Total number of files in the log group. A value of 2-3 is usually good
  278. # enough.
  279. innodb_log_files_in_group = 2
  280. # Location of the InnoDB log files. Default is the MySQL datadir. You
  281. # may wish to point it to a dedicated hard drive or a RAID1 volume for
  282. # improved performance
  283. # be careful if you use LVM and plan to snapshot your filesystem for hot
  284. # backup. your log files must be on the same logical volume as your data
  285. # files in order for this to work.
  286. #innodb_log_group_home_dir
  287. # Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
  288. # If it is reached, InnoDB will start flushing them out agressively to
  289. # not run out of clean pages at all. This is a soft limit, not
  290. # guaranteed to be held.
  291. innodb_max_dirty_pages_pct = 80
  292. # The flush method InnoDB will use for Log. The tablespace always uses
  293. # doublewrite flush logic. The default value is "fdatasync", another
  294. # option is "O_DSYNC".
  295. # use directIO to bypass filesystem cache where possible
  296. innodb_flush_method=O_DIRECT
  297. # How long an InnoDB transaction should wait for a lock to be granted
  298. # before being rolled back. InnoDB automatically detects transaction
  299. # deadlocks in its own lock table and rolls back the transaction. If you
  300. # use the LOCK TABLES command, or other transaction-safe storage engines
  301. # than InnoDB in the same transaction, then a deadlock may arise which
  302. # InnoDB cannot notice. In cases like this the timeout is useful to
  303. # resolve the situation.
  304. innodb_lock_wait_timeout = 120
  305. # Let as many clients commit at once as necessary
  306. # If you have a very intensive write application or if you have
  307. # innodb_flush_logs_at_trx <> 1 it may make sense to play with this.
  308. # with this configuration it probably won't matter anyway, because binary
  309. # logging is enabled, which enforces serialized commits, even when the
  310. # isolation level isn't serializable.
  311. innodb_commit_concurrency=0
  312. innodb_open_files=2000
  313. #
  314. [mysqldump]
  315. # Do not buffer the whole result set in memory before writing it to
  316. # file. Required for dumping very large tables
  317. quick
  318. max_allowed_packet = 16M
  319. [mysql]
  320. no-auto-rehash
  321. # Only allow UPDATEs and DELETEs that use keys.
  322. #safe-updates
  323. [isamchk]
  324. key_buffer = 512M
  325. sort_buffer_size = 512M
  326. read_buffer = 8M
  327. write_buffer = 8M
  328. [myisamchk]
  329. key_buffer = 512M
  330. sort_buffer_size = 512M
  331. read_buffer = 8M
  332. write_buffer = 8M
  333. [mysqlhotcopy]
  334. interactive-timeout
  335. [mysqld_safe]
  336. # Increase the amount of open files allowed per process. Warning: Make
  337. # sure you have set the global system limit high enough! The high value
  338. # is required for a large number of opened tables
  339. open-files-limit = 16384

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