Skip to content

Instantly share code, notes, and snippets.

@jedprentice
Last active October 25, 2019 15:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save jedprentice/8426425 to your computer and use it in GitHub Desktop.
Save jedprentice/8426425 to your computer and use it in GitHub Desktop.
MySQL 8 configuration; on Ubuntu this is included by my.cnf, so some of the most basic settings are not present. Values in this file override those in my.cnf.
[mysqld]
# Slow Log
#slow_query_log = 1
#log_queries_not_using_indexes = 1
#long_query_time = 1
# Allow GROUP BY to work like it did before MySQL 5.7.5, for Rails 3.2
# compatibility, i.e., remove ONLY_FULL_GROUP_BY from the default
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Disable DNS lookups for performance
skip_name_resolve
# Store each InnoDB table in a separate file rather than in the system
# tablespace
innodb_file_per_table
# Avoid double-buffering of I/O; this can help throughput.
innodb_flush_method = O_DIRECT
# Larger sizes offer better performance but result in longer recovery times.
# MySQL Performance blog recommends 64-512M depending on server size.
# NOTE: mysql will fail to start after you resize the log files if the files
# already exist. Move ib_logfile0 and ib_logfile1 out of the way to fix.
innodb_log_file_size = 64M
# MySQL Performance Blog recommends 70-80% of memory allocated to the InnoDB
# buffer pool for a dedicated server. This is only allocated at startup so it
# should be large. For development, 25-50% of available RAM is a good guideline
# depending on what else is running on the box. The default is 128MB, although
# OS packages seem to set it to 512MB.
innodb_buffer_pool_size = 1G
# Setting this to 2 flushes to disk every second, but not on each commit.
# This ensures that only an OS crash or a power outage can erase the last second
# of logs. For development, setting this to 0 will result in better performance
# because we are generally not worried about losing log data. 1 is for full ACID
# compliance but is too slow for most uses.
innodb_flush_log_at_trx_commit = 0
# Note that this is a minimum size; MySQL will allocate this or whatever it
# determines it needs for the join cache. Small is good because each join in a
# query gets its own join buffer. See the MySQL Performance Blog for
# details.
join_buffer_size = 1M
# This only applies to MyISAM tables, but keep it between 4-32M as per
# mysql performance blog because large temp tables will be written to disk as
# MyISAM tables.
key_buffer_size = 32M
# MySQL uses min(tmp_table_size, max_heap_table_size) to decide whether to
# write the temp table to disk as a MyISAM table, so we set both to the same
# value for consistency.
tmp_table_size = 64M
max_heap_table_size = 64M
# Minimize thread creation, since thread creation/destruction is expensive and
# happens with each connection. We allow a lot of connections so keeping this
# higher will help throughput.
thread_cache_size = 16
# Opening tables is expensive; this is a good value for apps that require
# ~200 tables.
table_open_cache = 1024
max_allowed_packet = 16M
# Maximum number of connections to the server
max_connections = 200
# This defines how long the server will wait for inactive application
# connections, in seconds. Keep it as short as possible in production
# environments, e.g., 30-60 seconds, to prevent inactive connections from
# stacking up under heavy load, but long enough to prevent dropped connections
# during the most frequent long-running operations. For development, values
# on the order of a few minutes are fine.
wait_timeout = 120
# This sets the timeout for mysql command-line clients and utilities; it does
# not impact application performance.
interactive_timeout = 300
# Replication
# Server ID must be unique across all servers in the cluster
#server_id = <%= server_id %>
# Offset the auto-increment to avoid collisions if there are multiple masters.
# auto_increment_increment should be set to the number of masters.
# auto_increment_offset should be set to the server_id, assuming server_id is
# sequential.
#auto_increment_increment = <%= masters %>
#auto_increment_offset = <%= server_id %>
# Enable and configure the binary log
#log-bin=mysqld-bin
#relay_log = mysql-relay-bin
#relay_log_index = mysql-relay-bin.index
#expire_logs_days = 3
#max_binlog_size = <%= max_binlog_size %>
# Active master should be 0 (read-write); passive master and/or slaves should
# be 1 (read-only)
#read_only = <%= read_only %>
# Exclude MySQL system databases from replication
#replicate_ignore_db = mysql
#replicate_ignore_db = information_schema
#replicate_ignore_db = performance_schema
#binlog_ignore_db = mysql
#binlog_ignore_db = information_schema
#binlog_ignore_db = performance_schema
#[mysqld_safe]
#log_error = /var/log/mysqld.log
#pid_file = /var/run/mysqld/mysqld.pid
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment